SpreadsheetDataBaseProvider

Thread is closed for posting
13 posts, 0 answers
  1. 63F75A2C-1F16-4AED-AFE8-B1BBD57646AD
    63F75A2C-1F16-4AED-AFE8-B1BBD57646AD avatar
    1572 posts
    Member since:
    Oct 2004

    Posted 02 Feb 2016 Link to this post

     

    Requirements

    Telerik Product and Version

    UI for ASP.NET AJAX 2016 Q1

    Supported Browsers and Platforms

    all browsers supported by RadControls

    Components/Widgets used (JS frameworks, etc.)

    .NET 4.0/4.5  C#/VB

    PROJECT DESCRIPTION 

    Тhe CodeLibrary aims to demonstrate how a custom DataBase Provider can be used to connect the Spreadsheet to SqlDataSource. The example is build to use Northwind database, which is not included in the attached zip files

    Spreadsheet setup

    <telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1"></telerik:RadSpreadsheet>
    protected void Page_Load(object sender, EventArgs e)
    {
        SpreadsheetDataBaseProvider provider = new SpreadsheetDataBaseProvider();
    
        RadSpreadsheet1.Provider = provider;
    }

    Custom provider: 

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using Telerik.Web.Spreadsheet;
    using Telerik.Web.UI;
    
    public class SpreadsheetDataBaseProvider : SpreadsheetProviderBase
    {
        public SpreadsheetDataBaseProvider()
            : base()
        {
        }
    
        public override void SaveWorkbook(Workbook workbook)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                conn.Open();
                string query = "DELETE FROM [Products]";
                SqlCommand clearCmd = new SqlCommand(query, conn);
                clearCmd.ExecuteNonQuery();
    
                string insertQuery = "INSERT INTO Products(ProductName,Description) VALUES(@param1,@param2)";
                foreach (var row in workbook.Sheets[0].Rows)
                {
                    SqlCommand insertCmd = new SqlCommand(insertQuery, conn);
    
                    insertCmd.Parameters.Add("@param1", SqlDbType.NVarChar, 50).Value = row.Cells[0].Value;
                    insertCmd.Parameters.Add("@param2", SqlDbType.NVarChar, 50).Value = row.Cells[1].Value;
    
                    insertCmd.CommandType = CommandType.Text;
                    insertCmd.ExecuteNonQuery();
                }
            }
        }
    
        public override List<Worksheet> GetSheets()
        {
            var result = new List<Worksheet>();
    
            DataTable data = GetData();
    
            var sheet = new Worksheet();
    
            int rowIndex = 0;
            foreach (DataRow dataRow in data.Rows)
            {
                var row = new Row() { Index = rowIndex++ };
    
                int columnIndex = 0;
                foreach (DataColumn dataColumn in data.Columns)
                {
                    if (dataColumn.ColumnName == "ID") continue; // Skip the ID column
    
                    var cellValue = dataRow[dataColumn.ColumnName];
    
                    var cell = new Cell() { Index = columnIndex++, Value = cellValue };
    
                    row.AddCell(cell);
                }
    
                sheet.AddRow(row);
            }
    
            result.Add(sheet);
    
            return result;
        }
    
        public DataTable GetData()
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
            {
                conn.Open();
                string query = "SELECT * FROM [Products]";
                SqlCommand cmd = new SqlCommand(query, conn);
    
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                return dt;
            }
        }
    
    }


  2. 40EC4EA0-E183-4504-A22A-BC79F0E08E77
    40EC4EA0-E183-4504-A22A-BC79F0E08E77 avatar
    6 posts
    Member since:
    Sep 2016

    Posted 10 Sep 2016 in reply to 63F75A2C-1F16-4AED-AFE8-B1BBD57646AD Link to this post

    Hi,

    When I try this solution, clicking on save gives me error, type initializer for Telerik.Web.Spreasheed.WorkBook threw an exception

     

  3. 916F62BC-79A6-4FB2-AC15-121C6DCC49DE
    916F62BC-79A6-4FB2-AC15-121C6DCC49DE avatar
    1 posts
    Member since:
    Feb 2016

    Posted 13 Oct 2016 Link to this post

    The links to the examples are broken...
  4. 11B81D5D-1EA0-4CB3-AB67-4DAD6414022C
    11B81D5D-1EA0-4CB3-AB67-4DAD6414022C avatar
    1242 posts
    Member since:
    Apr 2022

    Posted 17 Oct 2016 Link to this post

    Hello Robin,

    The links should work now as expected. Please excuse us for the caused inconvenience.

    Regards,
    Veselin Tsvetanov
    Telerik by Progress
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  5. 70B72281-3EA8-4DDC-ADE3-83F8A6F2FC73
    70B72281-3EA8-4DDC-ADE3-83F8A6F2FC73 avatar
    3 posts
    Member since:
    Jun 2017

    Posted 19 Jun 2017 Link to this post

    I am trying to build a proof of concept with the Spreadsheet control using a Database to feed the worksheets. I am able to retrieve the data from the database.

    However, when I click the save button in the spreadsheet. I am getting a "The type initializer for 'Telerik.Web.Spreadsheet.Workbook' threw an exception." error.

    What am I missing? Please help.

  6. 5347EB84-C6F6-428F-8E18-EC18176F3A60
    5347EB84-C6F6-428F-8E18-EC18176F3A60 avatar
    14 posts
    Member since:
    Jun 2012

    Posted 27 Sep 2017 Link to this post

    Hi, the project work fine form me until saving data, it doesn't even call the SaveWorkbook event.

    The GetSheets event work fine.

    Is there anything I'm missing?

     

    Thanks

  7. 9D39C7B5-4284-4147-8570-60750962EFB7
    9D39C7B5-4284-4147-8570-60750962EFB7 avatar
    1 posts
    Member since:
    Nov 2014

    Posted 26 Jan 2018 Link to this post

    Hi,

    I'm have implemented the custom provider like your sample, my question is, how do I provide feedback to the end user about save result? How can I send a message to user know that workbook was saved with success?

    I notice that if a exception occurs the exception is automatically catched and an alert is displayed. What if I want do say to the user that everything went well?

    Many Thanks

     

  8. 5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF
    5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF avatar
    3 posts
    Member since:
    Aug 2017

    Posted 09 Feb 2018 in reply to 9D39C7B5-4284-4147-8570-60750962EFB7 Link to this post

    Did you get answer or a solution to display messages back I am looking at the same thing!
  9. 0A6BB375-BEFB-42A5-AC8F-F34CA4CBECC2
    0A6BB375-BEFB-42A5-AC8F-F34CA4CBECC2 avatar
    2 posts
    Member since:
    Oct 2017

    Posted 21 Mar 2018 in reply to 5347EB84-C6F6-428F-8E18-EC18176F3A60 Link to this post

    It took me a while to figure this one out with no help from Telerik.

    I had to move the following from inside to outside the common ASP if statement: If Not Page.IsPostBack Then

    Dim provider As New SpreadsheetDataBaseProvider()
    RadSpreadsheet1.Provider = provider


    I had this code and it did as you described (would load data but not save):
            If Not Page.IsPostBack Then
                Dim provider As New SpreadsheetDataBaseProvider()
                RadSpreadsheet1.Provider = provider


    When I changed it to this it worked:
            Dim provider As New SpreadsheetDataBaseProvider()
            RadSpreadsheet1.Provider = provider
            If Not Page.IsPostBack Then

  10. 5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF
    5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF avatar
    3 posts
    Member since:
    Aug 2017

    Posted 29 Mar 2018 in reply to 0A6BB375-BEFB-42A5-AC8F-F34CA4CBECC2 Link to this post

    What save method are you using ...            

    foreach (var row in workbook.Sheets[0].Rows)
    {
    SqlCommand insertCmd = new SqlCommand(insertQuery, conn);
    insertCmd.CommandType = CommandType.Text;
    insertCmd.ExecuteNonQuery();
     I find this not practical. I should be able create a dataset/datatable and slam all the data using SQLBulkCopy method back to the database instead of writing each row of data? I find that around 4,000 lines the system just sits and does nothing.

    Anybody else have this issues or a work around?

  11. 0A6BB375-BEFB-42A5-AC8F-F34CA4CBECC2
    0A6BB375-BEFB-42A5-AC8F-F34CA4CBECC2 avatar
    2 posts
    Member since:
    Oct 2017

    Posted 29 Mar 2018 in reply to 5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF Link to this post

    This is Telerik's sample code.  It's true that if you try to view or save more then 1000 rows the spreadsheet performs very poorly.
    I got the code here:
    https://docs.telerik.com/devtools/aspnet-ajax/controls/spreadsheet/data-binding/providers/custom-database-provider

    I found no fix, just a workaround.  If you find a fix please let me know.  The work around is to limit the input to the spreadsheet with controls on the page that create SQL WHERE clause criteria.  I first do a SQL COUNT and if it is less then 1000 rows I continue, else I alert the user to user more criteria.

  12. 5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF
    5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF avatar
    3 posts
    Member since:
    Aug 2017

    Posted 25 Apr 2018 Link to this post

    public override void SaveWorkbook(Workbook workbook)
    {
    string DBName = "";
    DBName = GetSql.POGProd;

    using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(DBName))
    {
    //conn.Open();
    //string query = "DELETE FROM BulkSkuUpdate";
    //SqlCommand clearCmd = new SqlCommand(query, conn);
    //clearCmd.ExecuteNonQuery();
    //conn.Close();
    //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    // Create a datatable to store the data in - do not create dataset - a dataset will add to much
    // overhead to the insert process. ---- First create and VAR a datarow in the loop, Next in the VAR
    // the Row.Cell and loop thru each row and add the data to dataset row
    // Last use SqlBulkCopy to insert all the data into the database.
    // Below are the setting that I have found work the best currently 04-16-2018
    // sqlBulkCopy.BatchSize = 10000; sqlBulkCopy.NotifyAfter = 15000; sqlBulkCopy.BulkCopyTimeout = 300;
    // We can isert 15,000 rows and 10 columns = 150,000 datapoints in less than 1:30s in Edge/Firefox/Chrome
    // Internet explore does not insert the data is just sets there. IE can only handle about 6,000 rows of data
                //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    int rowcount = workbook.Sheets[0].Rows.Count;
    string hits = Convert.ToString(rowcount);
    DataTable dt = new DataTable("MyTable");
    dt.Columns.Add(new DataColumn("storenum", typeof(string)));
    dt.Columns.Add(new DataColumn("ard", typeof(string)));
    dt.Columns.Add(new DataColumn("dm", typeof(string)));
    dt.Columns.Add(new DataColumn("som", typeof(string)));
    dt.Columns.Add(new DataColumn("sku", typeof(string)));
    dt.Columns.Add(new DataColumn("description", typeof(string)));
    dt.Columns.Add(new DataColumn("First_Request_Date", typeof(string)));
    dt.Columns.Add(new DataColumn("qtytokeep", typeof(string)));
    dt.Columns.Add(new DataColumn("notes", typeof(string)));
    dt.Columns.Add(new DataColumn("lastupdateddate", typeof(string)));
    foreach (var row in workbook.Sheets[0].Rows)
    {
    DataRow dr = dt.NewRow();
    foreach (var cell in row.Cells)
    {
    dr[(int)cell.Index] = cell.Value;
    }
    dt.Rows.InsertAt(dr, (int)row.Index);
    }
    string insertQuery = "INSERT INTO BulkSkuUpdate(storenum, ard, dm, som, sku, description, First_Request_Date, Qty_to_Keep, notes, Last_Updated_Date) " +
    " VALUES(@storenum, @ard, @dm, @som, @sku, @description, @First_Request_Date, @qtytokeep, @notes, @lastupdateddate)";
    System.Data.SqlClient.SqlConnection DBConn2;
    try
    {
    string bulksql = GetSql.POGProd;
    DBConn2 = new System.Data.SqlClient.SqlConnection(bulksql);
    {
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(DBConn2))
    {
    DBConn2.Open();
    //Set the database table name
    sqlBulkCopy.BatchSize = 10000;
    sqlBulkCopy.NotifyAfter = 15000;
    sqlBulkCopy.BulkCopyTimeout = 300;
    sqlBulkCopy.DestinationTableName = "BulkSkuUpdate";
    sqlBulkCopy.WriteToServer(dt);
    DBConn2.Close();
    }
    }
    }
    finally
    {
    DBConn2 = null;
    }
    LogAction LogAction2 = new LogAction(Globals._servername, Globals._URLName, Globals._ADID, hits, insertQuery);
    }
    }
  13. 3FF098A5-54AF-44F0-8C92-23CB8EDD749A
    3FF098A5-54AF-44F0-8C92-23CB8EDD749A avatar
    2 posts
    Member since:
    Dec 2016

    Posted 18 Aug 2019 in reply to 5E3ADA80-D9AF-4058-84A6-A4E777C1C8FF Link to this post

    Were you able to create some kind of feedback to tell the end user the data was saved?  I would prefer to add a column to the spreadsheet confirming each row and if there was a validation issue provide a comment.
Back to Top

This Code Library is part of the product documentation and subject to the respective product license agreement.