We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Open and save data from SQL Table in Spreadsheet.

Thread ID:

Created:

Updated:

Platform:

Replies:

130584 May 19,2017 03:51 AM May 15,2018 08:54 AM ASP.NET Web Forms 3
loading
Tags: Spreadsheet
Pradeep Kumar
Asked On May 19, 2017 03:51 AM

Hi,

I have checked and implemented the examples of saving and opening spreadsheet data from sql server both in stream and json format and these are working fine.

But i have to implement loading and saving sql table data in table format in row and columns as .

OrderIDCust_IDCity
123451001Solan
123461002Ghana


On page load i have to load this table data as it is in Spreadsheet and after making changes to table save this table back to sql server as :


OrderIDCust_IDCity
123451001Miami
123461002FIZI
and adding some chages or adding more data to this table

How can i acheive this requirement , any help??

Shanmugaraja K [Syncfusion]
Replied On May 22, 2017 01:07 AM

Hi Pradeep, 
 
Thanks for using Syncfusion products, 
 
We can achieve your requirement by using ejDataManager, we have created simple sample based on your requirement. Here we have sent the edited records details to server by using $.ajax method and saved the edited data into the database. Please refer the below code example, 
 
[ASPX] 
 
 
<input type="button" id="save" value="Save" onclick="saveData()" /> 
 
    <ej:DataManager ID="dataManager1" runat="server" URL="SpreadsheetFeatures.aspx/Data" BatchURL="SpreadsheetFeatures.aspx/Save" Adaptor="WebMethodAdaptor" /> 
    <ej:Spreadsheet ID="Spreadsheet1" runat='server' ClientIDMode="Static"> 
        <ClientSideEvents LoadComplete="loadComplete" /> 
        <ScrollSettings Width="100%" Height="540" IsResponsive="true" /> 
        <Sheets> 
            <ej:Sheet DataManagerID="dataManager1" Query="ej.Query().take(10).select(['OrderID', 'Quantity', 'Price', 'Amount', 'Discount'])" PrimaryKey="OrderID"></ej:Sheet> 
        </Sheets> 
    </ej:Spreadsheet> 
 
    <script type="text/javascript"> 
        function saveData() { 
            var xlObj = $('#Spreadsheet1').data("ejSpreadsheet"), changes = xlObj.getSheet(xlObj.getActiveSheetIndex()).batchChanges.changed; 
            if (changes.length) { 
                $.ajax({ 
                    type: "POST", 
                    url: "SpreadsheetFeatures.aspx/Save", 
                    data: JSON.stringify({ changed: JSON.stringify(changes) }), 
                    contentType: "application/json; charset=utf-8", 
                    dataType: 'json', 
                    success: function (data) { 
                        if (data.d == "Success") 
                            alert("Edited data updated successfully!!!"); 
                        else 
                            alert("Failed!!!"); 
                    } 
                }); 
            } 
        }    
     </script> 
 
 
 
[C#] 
 
 
    [WebMethod()] 
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
    public static object Data(DataManager value) 
    { 
        string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True"; 
        SqlConnection sqlCon = new SqlConnection(dbConnectionStr); 
        SqlCommand sqlComm = new SqlCommand("SELECT [OrderID],[Quantity],[Price],[Amount],[Discount] FROM [dbo].[Table]", sqlCon); 
        sqlCon.Open(); 
        List<Orders> order = new List<Orders>(); 
        SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
        while (sqlDR.Read()) 
        { 
            order.Add(new Orders() { OrderID = (int)sqlDR.GetValue(0), Quantity = (int)sqlDR.GetValue(1), Price = (int)sqlDR.GetValue(2), Amount = (int)sqlDR.GetValue(3), Discount = (int)sqlDR.GetValue(4) }); 
        } 
        sqlCon.Close(); 
        return new { result = order, count = order.Count }; 
    } 
    [WebMethod] 
    public static string Save(string changed) 
    { 
        string changedData = HttpContext.Current.Request.Params["changed"]; 
        JavaScriptSerializer js = new JavaScriptSerializer(); 
        try 
        { 
            List<Orders> changes = js.Deserialize<List<Orders>>(changed); 
            string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True"; 
            SqlConnection sqlCon = new SqlConnection(dbConnectionStr); 
            foreach (var i in changes) 
            { 
                SqlCommand sqlComm = new SqlCommand("UPDATE [Table] SET [Quantity] = @Quantity, [Price] = @Price, [Amount] = @Amount, [Discount] = @Discount Where [OrderID] = @OrderID", sqlCon); 
                sqlComm.Parameters.AddWithValue("@OrderID", i.OrderID); 
                sqlComm.Parameters.AddWithValue("@Quantity", i.Quantity); 
                sqlComm.Parameters.AddWithValue("@Price", i.Price); 
                sqlComm.Parameters.AddWithValue("@Amount", i.Amount); 
                sqlComm.Parameters.AddWithValue("@Discount", i.Discount); 
                sqlCon.Open(); 
                sqlComm.ExecuteNonQuery(); 
                sqlCon.Close(); 
            } 
            return "Success"; 
        } 
        catch (Exception ex) 
        { 
            return "Failed"; 
        } 
    } 
 
 
 
Also, we have attached sample with these code example and the same can be downloaded from the below location, 
 
 
Regards, 
Shanmugaraja K 


Jose Eslava
Replied On May 14, 2018 04:54 PM

How to opening spreadsheet data from sql server after a dropdownlist selection?

I attache source code sample without success.

Sangeetha Priya Murugan [Syncfusion]
Replied On May 15, 2018 08:54 AM

Hi Jose, 
 
We would like to let you know that, we have already provided a solution for your requirement “To open spreadsheet data from Sql server based on dropdownlist selection” in the below KB link for all platforms (JS, MVC & ASP). However, for your convenience – we have prepared an ASP.NET Web Form sample to demonstrate your requirement and the same can be downloaded from the following sample link. 
 
 
 
Could you please check the above sample & KB link and get back to us if you need any further assistance?  
 
Regards, 
Sangeetha Priya M 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;