Need help using a grid for CRUD against a Azure SQLDB

[Jeff Lucal]

I have a trial of the .net core package and I can't seem to find an easy way to leverage my AzureSQL DB connection string to use the DataGrid.  Any samples using a SQL connection string with a filter on a grid for CRUD would be greatly appreciated. 

[Manivannan Padmanaban]

Hi Jeff, 

Thanks for contacting syncfusion support. 

We have analyzed your query and we are able to understand that you need render the grid with SQL DB connection string  and also you need to perform the CRUD action using the sql connection string. We have achieved your requirement  please refer the below code example, 

<ej-grid id="FlatGrid" allow-paging="true" > 

 

    <e-datamanager json="(System.Data.DataTable)ViewBag.datasource" insert-url="/Home/Normal_Insert"   update-url="/Home/Normal_Update" remove-url="/Home/Normal_Delete"  adaptor="remoteSaveAdaptor" /> 

    <e-edit-settings allow-adding="true" allow-deleting="true" allow-editing="true" /> 

    <e-toolbar-settings show-toolbar="true" toolbar-items="@(new List<string> { "add","delete", "update", "cancel"})"/> 

    <e-columns> 

        <e-column field="OrderID" header-text="Order ID" text-align="Right" is-primary-key="true"></e-column> 

        <e-column field="ShipCity" header-text="Ship City"></e-column> 

         

    </e-columns> 

</ej-grid> 

 

                    

 // controller page 

 

public class HomeController : Controller 

    { 

        private NORTHWNDContext _context; 

        public HomeController(NORTHWNDContext context) 

        { 

            _context = context; 

        } 

       

        public IActionResult Index() 

        { 

            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\manivannan.padmanaba\Desktop\WebApplication1-1976054900\WebApplication1\WebApplication1\Data\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30"; 

            SqlCommand sqlCommand = new SqlCommand(); 

            System.Data.DataTable dt = new DataTable(); 

            SqlConnection sqlc = new SqlConnection(connectionString); 

            SqlCommand cmd = new SqlCommand(); 

            cmd.CommandText = "SELECT * from Orders"; 

            SqlDataAdapter da= new SqlDataAdapter(cmd.CommandText, sqlc); 

            da.Fill(dt); 

            ViewBag.datasource = dt; 

            return View(); 

 

        } 

        public ActionResult Normal_Insert([FromBody]CRUDModel<Orders> param) 

        { 

            

             

            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\manivannan.padmanaba\Desktop\WebApplication1-1976054900\WebApplication1\WebApplication1\Data\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30"; 

            SqlConnection con = new SqlConnection(connectionString); 

            con.Open(); 

            SqlCommand cmd = new SqlCommand(); 

            cmd.Connection = con; 

            cmd.CommandText = "SET IDENTITY_INSERT Orders ON"; 

            cmd.ExecuteNonQuery(); 

            cmd.CommandText = "INSERT INTO Orders(OrderID,ShipCity)   VALUES(@OrderID,@ShipCity)"; 

            cmd.Parameters.AddWithValue("@OrderID", param.Value.OrderID); 

            cmd.Parameters.AddWithValue("@ShipCity", param.Value.ShipCity); 

            cmd.ExecuteNonQuery(); 

            con.Close(); 

            return Json(param.Value); 

 

        } 

 

        public ActionResult Normal_Update([FromBody]CRUDModel<Orders> param) 

        { 

            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\manivannan.padmanaba\Desktop\WebApplication1-1976054900\WebApplication1\WebApplication1\Data\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30"; 

            SqlConnection con = new SqlConnection(connectionString); 

            con.Open(); 

            SqlCommand cmd = new SqlCommand(); 

            cmd.Connection = con; 

            cmd.CommandText = "UPDATE Orders SET ShipCity = @ShipCity WHERE OrderID = @OrderID"; 

            cmd.Parameters.AddWithValue("@ShipCity", param.Value.ShipCity); 

            cmd.Parameters.AddWithValue("@OrderID", param.Value.OrderID); 

            cmd.ExecuteNonQuery(); 

            con.Close(); 

 

            return Json(param.Value); 

        } 

 

        public ActionResult Normal_Delete([FromBody]CRUDModel<Orders> param) 

        { 

            string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\manivannan.padmanaba\Desktop\WebApplication1-1976054900\WebApplication1\WebApplication1\Data\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30"; 

            SqlConnection con = new SqlConnection(connectionString); 

            con.Open(); 

            SqlCommand cmd = new SqlCommand(); 

            cmd.Connection = con; 

            cmd.CommandText = "SET IDENTITY_INSERT Orders ON"; 

            cmd.ExecuteNonQuery(); 

            cmd.CommandText = "DELETE FROM Orders WHERE OrderID = @OrderID"; 

            cmd.Parameters.AddWithValue("@OrderID", param.Key); 

            cmd.ExecuteNonQuery(); 

            return Json(param.Value); 

        } 

 

 

 

 

        public static System.Data.DataTable ListToDataTable(IEnumerable list) 

        { 

            var dt = new System.Data.DataTable(); 

            var item = list.OfType<object>().FirstOrDefault(); 

            if (item == null) 

                return dt; 

            var typeProperties = item.GetType().GetProperties(); 

            foreach (PropertyInfo info in typeProperties) 

            { 

                dt.Columns.Add(new DataColumn(info.Name, info.PropertyType)); 

            }                  

            foreach (var t in list) 

            { 

                DataRow row = dt.NewRow(); 

                foreach (PropertyInfo info in typeProperties) 

                { 

                    row[info.Name] = info.GetValue(t, null); 

                } 

                dt.Rows.Add(row);    

            } 

 

          

            return dt;    

        } 

 

 

 

 

For your convenience we have created the sample that can be downloaded from the below location, 

http://www.syncfusion.com/downloads/support/directtrac/general/7z/WebApplication1-2055850166.7z  

Please get back to us if you need further details. 

Regards, 

Manivannan Padmanaban.