Category / Section
How to bind SQL Server data in Vue 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 Vue 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 Vue DataGrid through UrlAdaptor.
C#
[Fetching data]
public class HomeController : Controller
{
private readonly IHostingEnvironment _env;
public HomeController(IHostingEnvironment env)
{
_env = env;
}
static string cons;
static SqlConnection con;
public static List<Order> Orders { get; set; }
public IActionResult Index()
{
cons = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + _env.ContentRootPath + @"\Data\NORTHWND.MDF;Integrated Security=True";
con = new SqlConnection(cons);
return View();
}
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 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([FromBody]DataManagerRequest dm)
{
string qs = "SELECT OrderID, ShipCountry, ShipCity FROM dbo.Orders ORDER BY OrderID OFFSET " + dm.Skip + "ROWS FETCH NEXT " + dm.Take + " ROWS ONLY; ";
if (dm.Skip == 0 && dm.Take == 0)
{
qs = "SELECT OrderID, ShipCountry, ShipCity FROM dbo.Orders ORDER BY OrderID; ";
}
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([FromBody]CRUDModel<Order> value)
{
SqlCommand cmd = new SqlCommand("insert into dbo.Orders(OrderID,ShipCountry,ShipCity) values(@OrderID,@ShipCountry,@ShipCity)", con);
if (con.State != ConnectionState.Open)
con.Open();
cmd.Parameters.AddWithValue("@OrderID", value.Value.OrderID);
cmd.Parameters.AddWithValue("@ShipCountry", value.Value.ShipCountry);
cmd.Parameters.AddWithValue("@ShipCity", value.Value.ShipCity);
cmd.ExecuteNonQuery();
con.Close();
return Json(value);
}
[Update]
public ActionResult Update([FromBody]CRUDModel<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.Value.OrderID);
cmd.Parameters.AddWithValue("@ShipCountry", value.Value.ShipCountry);
cmd.Parameters.AddWithValue("@ShipCity", value.Value.ShipCity);
cmd.ExecuteNonQuery();
con.Close();
return Json(value.Value);
}
}
[Remove]
public ActionResult Remove([FromBody]CRUDModel value)
{
int keyValue = Int32.Parse(value.Key.ToString());
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()));
}
Vue
<template>
<div>
<ejs-grid :dataSource="data" :toolbar="toolbar" allowPaging="true" :editSettings="editSettings">
<e-columns>
<e-column field="OrderID" headerText="Order ID" :isPrimaryKey="true" :validationRules="orderidRules" textAlign="Right" width="100"></e-column>
<e-column field="ShipCountry" headerText="Ship Country" width="120"></e-column>
<e-column field="ShipCity" headerText="Ship City" width="120"></e-column>
</e-columns>
</ejs-grid>
</div>
</template>
<script>
import Vue from "vue";
import { GridPlugin, Edit, Page, Toolbar } from "@syncfusion/ej2-vue-grids";
import { DataManager, UrlAdaptor } from "@syncfusion/ej2-data";
Vue.use(GridPlugin);
export default Vue.extend({
data: () => {
return {
data: new DataManager({
url: "Home/UrlDatasource",
updateUrl: "Home/Update",
insertUrl: "Home/Insert",
removeUrl: "Home/Remove",
adaptor: new UrlAdaptor()
}),
toolbar: ['Add', 'Edit', 'Delete', 'Update', 'Cancel'],
editSettings: { allowEditing: true, allowAdding: true, allowDeleting: true },
orderidRules: {required:true}
}
},
provide: {
grid: [Edit, Page, Toolbar ]
}
});
</script>
Demo: https://www.syncfusion.com/downloads/support/directtrac/general/ze/VueSqlDataGrid1126509960
