New to Telerik UI for ASP.NET AJAXStart a free 30-day trial

DataBase provider

Environment

ProductTelerik WebForms SpreadSheet for ASP.NET AJAX

Description

Тhe article 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.

Solution

Spreadsheet setup

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

Custom provider

C#
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;
        }
    }
}
In this article
EnvironmentDescriptionSolution
Not finding the help you need?
Contact Support