Category / Section
How to bind SQL Server data in TypeScript DataGrid using SqlClient data provider
3 mins read
You can bind the data from the SQL Server using Microsoft SqlClient and perform CRUD actions in the JavaScript DataGrid component.
This can be achieved by using UrlAdaptor which will pass the paging, filtering, sorting parameters to the server as DataManagerRequest and perform server side CRUD actions in DataGrid component. Based on the DataManagerRequest, you can form the SQL query string and retrieve the data from the database using SqlDataAdapter. And then, we process the query string and fill the result into DataSet. Then the DataSet will be converted into List and returned as result and count pair.
This is explained in the following sample code in which the SQL data has been retrieved using SqlClient and bound to the JavaScript DataGrid through UrlAdaptor.
C#
[Fetching data]
public class HomeController : Controller
{
static string cons = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + HostingEnvironment.ApplicationPhysicalPath + @"App_Data\NORTHWND.MDF;Integrated Security=True";
static SqlConnection con = new SqlConnection(cons);
public ActionResult Index()
{
return View();
}
public static List<Order> Orders { get; set; }
public static DataSet CreateCommand(string queryString, string connectionString)
{
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet dt = new DataSet();
try
{
connection.Open();
adapter.Fill(dt); // Using SqlDataAdapter we can process the query string and fill the data into dataset
}
catch (SqlException se)
{
Console.WriteLine(se.ToString());
}
finally
{
connection.Close();
}
return dt;
}
}
public object UrlDatasource(DataManagerRequest dm)
{
string qs = "SELECT OrderID, ShipCountry, ShipCity FROM dbo.Orders ORDER BY OrderID";
if (dm.Take != 0)
{
qs = qs + " OFFSET " + dm.Skip + "ROWS FETCH NEXT " + dm.Take + " ROWS ONLY; ";
}else
{
qs = qs + "; ";
}
DataSet data = CreateCommand(qs, cons);
Orders = data.Tables[0].AsEnumerable().Select(r => new Order
{
OrderID = r.Field<int>("OrderID"),
ShipCountry = r.Field<string>("ShipCountry"),
ShipCity = r.Field<string>("ShipCity"),
}).ToList(); // Here, we have converted dataset into list
IEnumerable<Order> DataSource = Orders;
if (con.State != ConnectionState.Open)
con.Open();
SqlCommand comm = new SqlCommand("SELECT COUNT(*) FROM dbo.Orders", con);
Int32 count = (Int32)comm.ExecuteScalar();
con.Close();
return Json(new { result = DataSource, count = count });
}
public class DataResult
{
public IEnumerable result { get; set; }
public int count { get; set; }
}
[Insert]
public ActionResult Insert(Order value)
{
SqlCommand cmd = new SqlCommand("insert into dbo.Orders(ShipCountry,ShipCity) values(@ShipCountry,@ShipCity)", con);
if (con.State != ConnectionState.Open)
con.Open();
cmd.Parameters.AddWithValue("@ShipCountry", value.ShipCountry);
cmd.Parameters.AddWithValue("@ShipCity", value.ShipCity);
cmd.ExecuteNonQuery();
con.Close();
return Json(value);
}
[Update]
public ActionResult Update(Order value)
{
SqlCommand cmd = new SqlCommand("update dbo.Orders set ShipCountry=@ShipCountry,ShipCity=@ShipCity where OrderID=@OrderID", con);
if (con.State != ConnectionState.Open)
con.Open();
cmd.Parameters.AddWithValue("@OrderID", value.OrderID);
cmd.Parameters.AddWithValue("@ShipCountry", value.ShipCountry);
cmd.Parameters.AddWithValue("@ShipCity", value.ShipCity);
cmd.ExecuteNonQuery();
con.Close();
return Json(value);
}
[Remove]
public ActionResult Remove(int key)
{
int keyValue = key;
SqlCommand cmd = new SqlCommand("delete dbo.Orders where OrderID=@OrderID", con);
if (con.State != ConnectionState.Open)
con.Open();
cmd.Parameters.AddWithValue("@OrderID", keyValue);
cmd.ExecuteNonQuery();
con.Close();
return Json(UrlDatasource(new DataManagerRequest()));
}
TypeScript
Grid.Inject(Page, Edit, Toolbar);
/**
* Sql Data sample
*/
let data: DataManager = new DataManager({
url: "Home/UrlDatasource",
updateUrl: "Home/Update",
insertUrl: "Home/Insert",
removeUrl: "Home/Remove",
adaptor: new UrlAdaptor
});
let grid: Grid = new Grid({
dataSource: data,
allowPaging:true,
toolbar: ['Add', 'Edit', 'Delete', 'Update', 'Cancel'],
editSettings: { allowEditing: true, allowAdding: true, allowDeleting: true},
columns: [
{ field: 'OrderID', headerText: 'Order ID', isPrimaryKey: true, validationRules: { required: true }, textAlign: 'Right', width: 100 },
{ field: 'ShipCountry', headerText: 'Ship Country', width: 120 },
{ field: 'ShipCity', headerText: 'Ship City', width: 120 }
]
});
grid.appendTo('#Grid');
Demo: https://www.syncfusion.com/downloads/support/directtrac/general/ze/TsSqlDataGrid944214531
