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

[Brian]

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,

[Peshito]

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

[Brian]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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,