SpreadsheetDataBaseProvider

12 posts, 0 answers
  1. Telerik Admin
    Telerik Admin avatar
    1628 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
  2. Shridhar
    Shridhar avatar
    6 posts
    Member since:
    Sep 2016

    Posted 10 Sep 2016 in reply to Telerik Admin 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. Robin
    Robin avatar
    1 posts
    Member since:
    Feb 2016

    Posted 13 Oct 2016 Link to this post

    The links to the examples are broken...
  4. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    859 posts

    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. Daniel
    Daniel 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. Alejandro Genovesi
    Alejandro Genovesi avatar
    11 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. Tiago
    Tiago avatar
    1 posts
    Member since:
    Nov 2014

    Posted 26 Jan 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. Anson
    Anson avatar
    3 posts
    Member since:
    Aug 2017

    Posted 09 Feb in reply to Tiago Link to this post

    Did you get answer or a solution to display messages back I am looking at the same thing!
  9. Jeff
    Jeff avatar
    2 posts
    Member since:
    Oct 2017

    Posted 21 Mar in reply to Alejandro Genovesi 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. Anson
    Anson avatar
    3 posts
    Member since:
    Aug 2017

    Posted 29 Mar in reply to Jeff 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. Jeff
    Jeff avatar
    2 posts
    Member since:
    Oct 2017

    Posted 29 Mar in reply to Anson 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. Anson
    Anson avatar
    3 posts
    Member since:
    Aug 2017

    Posted 25 Apr 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);
    }
    }
Back to Top