New to Telerik UI for ASP.NET AJAX? Start a free 30-day trial
DataBase provider
Environment
| Product | Telerik 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;
}
}
}