How to bind SQL Server data in Vue DataGrid using SqlClient data provider?
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 the DataGrid component. Based on the DataManagerRequest, you can form the SQL query string and retrieve the data from the database using SqlDataAdapter. Then, we process the query string and fill the result into a DataSet. The DataSet will then be converted into a List and returned as a 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
Conclusion
We hope you enjoyed learning how to bind SQL Server data in Vue DataGrid using SqlClient data provider.
You can refer to our Vue DataGrid feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Vue TreeGrid example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion®, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, BoldDesk Support, or feedback portal. We are always happy to assist you!
