How do I read sql stored procedure into a RadSpreadSheet? C#

1 Answer 494 Views
Spreadsheet
Brian
Top achievements
Rank 1
Iron
Iron
Brian asked on 09 Nov 2022, 01:33 PM

I am just now attempting a RadSpreadSheet project, and am not finding an example of how to import and populate the form with an SQL Server Stored Procedure.

Can you provide a sample code please?

 

Thanks,

1 Answer, 1 is accepted

Sort by
0
Peshito
Telerik team
answered on 11 Nov 2022, 09:22 AM

Hi Brian,

You should populate a DataTable from your Stored Procedure and then iterate the columns and rows in the DataTable. Then insert the respective cell's value into the ActiveWorksheet. This approach is demonstrated in our Knowledge Base article - Fill RadSpreadsheet with DataTable.

Regards,
Peshito
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Brian
Top achievements
Rank 1
Iron
Iron
commented on 11 Nov 2022, 03:40 PM

Hi Peshito, thank you for responding. I tried the following code, and it does not populate the spreadsheet;

 

I tried SQL Connection after the InitializeComponent() section and SpreadsheetFormLoad event.

 

thanks,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Resources;
using System.Text;
using System.Windows.Forms;
using Telerik.WinControls.UI;
using Telerik.Windows.Documents.Spreadsheet.Model;

namespace CTC_ITCMReport
{

    public partial class SpreadsheetForm : RadForm
    {
        DataTable dt;
        public SpreadsheetForm()
        {
            AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;
            InitializeComponent();
            dt = new DataTable();
            for (int i = 0; i < 10; i++)
            {
                dt.Columns.Add("Col" + i);
            }
            for (int i = 0; i < 1000; i++)
            {
                DataRow row = dt.Rows.Add();
                foreach (DataColumn col in dt.Columns)
                {
                    row[col.ColumnName] = "Data" + i + "." + col.ColumnName;
                }
            }
            Cursor.Current = Cursors.WaitCursor;
            dt.Reset();
            dt = new DataTable();
            //SqlConnection conn = new SqlConnection(Globals.ConStats);
            //conn.Close();
            //SqlCommand cmd = new SqlCommand();
            //conn.Open();
            //cmd = new SqlCommand("3DayPrevMonth", conn)
            //{
            //    CommandType = CommandType.StoredProcedure
            //};
            //SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            //conn.Close();
            //conn.Open();

            bool shouldImportColumnHeaders = true;
            PopulateSpreadsheet(dt, shouldImportColumnHeaders);
            ((RadRibbonFormBehavior)this.FormBehavior).AllowTheming = false;
        }

        private Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            // This is so you can embed the DLL resouces and not have to include them with the Executable.
            string dllName = args.Name.Contains(",") ? args.Name.Substring(0, args.Name.IndexOf(',')) : args.Name.Replace(".dll", "");
            dllName = dllName.Replace(".", "_");
            if (dllName.EndsWith("_resources")) return null;
            ResourceManager rm = new ResourceManager(GetType().Namespace + ".Properties.Resources", Assembly.GetExecutingAssembly());
            byte[] bytes = (byte[])rm.GetObject(dllName);
            return Assembly.Load(bytes);
        }

        private void PopulateSpreadsheet(DataTable data, bool shouldImportColumnHeaders)
        {
            Worksheet worksheet = this.radSpreadsheet1.ActiveSheet as Worksheet;
            int startRowIndex = 0;
            if (shouldImportColumnHeaders)
            {
                startRowIndex++;
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    worksheet.Cells[0, i].SetValue(data.Columns[i].ColumnName);
                }
            }

            for (int i = 0; i < data.Rows.Count; i++)
            {
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    worksheet.Cells[startRowIndex + i, j].SetValue(data.Rows[i][j] + string.Empty);
                }
            }
            worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();
        }

        private void SpreadsheetForm_Load(object sender, EventArgs e)
        {
            Directory.CreateDirectory(@"C:\#support\Reports\CTC-ITCMReport");
            Text = "CTC-ITCM Report" + Assembly.GetExecutingAssembly().GetName().Version.ToString();
            SqlConnection conn = new SqlConnection(Globals.ConStats);
            conn.Close();
            SqlCommand cmd = new SqlCommand();
            conn.Open();
            cmd = new SqlCommand("3DayPrevMonth", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            conn.Close();
            conn.Open();
        }
    }
}


Peshito
Telerik team
commented on 14 Nov 2022, 11:24 AM

Hi Brian,

Could you try using the DataTableFormatProvider of the Spreadsheet's model instead? It will help you easily convert your existing DataTable to a worksheet and vice versa. Please refer to our help article for more information - Using DataTableFormatProvider

Regards,PeshitoProgress Telerik

Brian
Top achievements
Rank 1
Iron
Iron
commented on 14 Nov 2022, 01:43 PM

Peshito,

Ok, I looked at the page  you directed me to. While the code snippet is helpful in the structure. what is NOT helpful in almost all of the examples in the documentation is that they do not provide a complete example of WHERE this would go in my code or how it is to be used in the class. 

So, what would be helpful is a complete example snipped of the code.

I have no problem populating Gridviews, or exporting to Excel from them.

I have deleted the original project and recreated with just the info  you have provided and added a DataSource to the project and bound it to the RadSpreadsheet control, and when I launch the app. it fills all the columns with;

Col0  Col1 Col2 etc...

and the rows;

Data0.Col0  Data0.Col1  Data0.Col2 etc...

So, I ask again, can you provide an complete code example? 

 

Or Modify my code above with working functionality.

 

Thanks, and appreciate your time.

 

 

Peshito
Telerik team
commented on 15 Nov 2022, 02:38 PM

Hello Brian,

The code snippets from the help article demonstrate an approach for showing how a functionality work. In this case, the code snippets show the use of the DataTableFormatProvider and do not create the DataTable itself which is up to the user to make. Please take a look at the sample code below demonstrating a complete working scenario on how to create a sample document and populate it by using the DataTableFormatProvider and a possible DataTable.

class Program
    {
        private static readonly DateTime dateTime = new DateTime(2020, 12, 12, 12, 12, 12);
        private static string fileName = "SampleFile.xlsx";

        private enum Level
        {
            Low,
            Medium,
            High
        }
        static void Main(string[] args)
        {
            CreateSampleDoc();
        }

        private static void CreateSampleDoc()
        {
            DataTable table = new DataTable();
            table.Columns.Add("IntColumn", typeof(int));
            table.Columns.Add("DoubleColumn", typeof(double));
            table.Columns.Add("StringColumn", typeof(string));
            table.Columns.Add("DateColumn", typeof(DateTime));
            table.Columns.Add("BoolColumn", typeof(bool));

            table.Columns.Add("FloatColumn", typeof(float));
            table.Columns.Add("LongColumn", typeof(long));
            table.Columns.Add("ShortColumn", typeof(short));
            table.Columns.Add("ByteColumn", typeof(byte));
            table.Columns.Add("EnumColumn", typeof(Enum));


            table.Rows.Add(25, 12345.12345, "David", dateTime, true, (float)10.10, 25000, short.MaxValue, 255, Level.Low);
            table.Rows.Add(-50, -12345.12345, "Sam", dateTime, false, (float)10.10, 1500000, short.MaxValue, 255, Level.Medium);
            table.Rows.Add(10, 678.999, "Christoff", dateTime, true, (float)10.10, 7000000555, short.MaxValue, 255, Level.High);

            DataTableFormatProvider provider = new DataTableFormatProvider();

            Workbook workbook = provider.Import(table);

            Worksheet worksheet = workbook.Worksheets[0];

            IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();

            using (Stream output = new FileStream(fileName, FileMode.Create))
            {
                formatProvider.Export(workbook, output);
            }

            ProcessStartInfo psi = new ProcessStartInfo(fileName);
            psi.UseShellExecute = true;
            Process.Start(psi);
        }
    }

Attached is a runnable example of the code sample above.

Hope this helps.

Regards,
Peshito
Progress Telerik
Brian
Top achievements
Rank 1
Iron
Iron
commented on 15 Nov 2022, 03:31 PM

Peshito this is a great example of what I did not ask about. 

Thanks, I will go another direction as this has not been helpful.

 

Peshito
Telerik team
commented on 16 Nov 2022, 10:22 AM

Hello Brian,

I am afraid providing SQL support is out of Telerik scope. This is not supported out of the box as it is with the DataTableFormatProvider for example. The best approach as mentioned earlier is to store the procedure in a DataTable and use it with the DataTableFormatProvider. I would suggest you take a look at this StackOverflow forum thread providing information on reading SQL table into a DataTable. Comparing the latter forum thread with your code I see that you probably missed querying the database and returning the result to a DataTable which can be done by using the Fill method of your SQLDataAdapter.

Regards,
Peshito
Progress Telerik
Brian
Top achievements
Rank 1
Iron
Iron
commented on 16 Nov 2022, 02:33 PM

Thanks for the link. 

I did not ask how to do SQL. I asked how to get the data to display in the RadSpreadsheet form control.

If you look at the code, I have my SQL in there and you suggested I do what was already there. I get the feeling you did not read the question.

As I said in the last post, I will figure this out myself as you are not proving helpful information. Additionally I will be contacting our corporate rep concerning this.

 

Have a nice day.

 

Dimitar
Telerik team
commented on 17 Nov 2022, 09:11 AM

Hi Brian, 

My name is Dimitar and I will be assisting you today. I am sorry to hear that you are not satisfied with the support service. 

I have reviewed the entire thread and I want to start by clarifying that there is no direct way to use the stored procedure to populate the data. In general, to populate the RadSpreadsheet you have two options: 

  1. Use one of the supported format providers which allow you to import data from the most common formats (XLS, CSV, DataTable). These providers will create a new Workbooks instance that needs to be assigned to the control. There is no data source property that allows you to bind the data directly like in the Grid.
  2. Directly set the cell values. This approach is used in the article from the original post in this thread. 

If you want to save the data you will need to export it, the most common way is to use DataTable and then save it to the database. I have attached a small WinForms project that shows the key points of this approach. 

If this is not the desired functionality you want, I want to kindly ask you to summarize your reqirement so we can better understand it and provide a proper solution. 

I hope this helps. Should you have any other questions do not hesitate to ask.

Regards,

Dimitar

 

Brian
Top achievements
Rank 1
Iron
Iron
commented on 22 Nov 2022, 02:53 PM

Dimitar, thank you for the response.

I am currently setting up a new laptop. Yes, I am aware that I cannot directly pull the data via a Stored Procedure. I have tried with a DataTable, however the data does not display in to the form.

I will look your attachment over after I get all installed on the new laptop and get back to you next week after the holidays.

Thanks, 

Dimitar
Telerik team
commented on 23 Nov 2022, 07:39 AM

Hi Brian, 

Ok, if you are unable to find out why this is not working please consider posting a new ticket and attaching your project. This will allow us to properly investigate this and provide a solution. 

Do not hesitate to contact us if you have other questions.

Regards,

Dimitar

Brian
Top achievements
Rank 1
Iron
Iron
commented on 05 Dec 2022, 04:18 PM

I finally got my new laptop up to speed. I was unable to run the zip file you attached. it does not like the current release of VS 2022, and the current release of your products. too many errors to list.

Also note. I can display the data in a RadGridView just by creating a databinding with the GridView control and no other actions. I try to do the same thing with the RadSpreadsheet and no data displays.

Dimitar
Telerik team
commented on 06 Dec 2022, 11:40 AM

Hi Brian, 

I have tested the project and it is the correct one. Please note that we cant send assemblies in forums so they are removed from the project. This means that you may need to remove and then add all references. You can use the "Configure Project" or "Upgrade Wizzard" option from Visual Extension as well. Once the references are corrected you will be able to run the project without issues. 

The Spreadsheet control is different from the grid and it does not support similar data binding. You need to set a Workbook instance to the control in order to display the data: 

var table = CreateSampleTable();            
Workbook workbook = provider.Import(table); 
this.radSpreadsheet1.Workbook = workbook;

The above example uses a DataTable, but you can use existing files or manually add the data to the workbook. 

I hope this helps. Should you have any other questions do not hesitate to ask.

 

Brian
Top achievements
Rank 1
Iron
Iron
commented on 06 Dec 2022, 12:53 PM

yes, that explanation is quite helpful. the Missing/out of date references I did remove and add them. it is also missing some .NET ones that I cannot find or see how they reference.

Regardless. I will work it with the project I created and use your sample code that way to see if I at least populate the form with the static data and work backwards from there. I will keep you up to date on my results.

 

Thanks,

Tags
Spreadsheet
Asked by
Brian
Top achievements
Rank 1
Iron
Iron
Answers by
Peshito
Telerik team
Share this question
or