We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to bind SQL Server data in TypeScript DataGrid using SqlClient data provider

Platform: JavaScript - EJ 2 |
Control: Grid

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  

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile