How to bind SQL Server data in Angular DataGrid using SqlClient data provider?
You can bind the data from the SQL Server using Microsoft SqlClient and perform CRUD actions in the Angular 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 Angular 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";
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([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()));
}
Angular
@Component({
selector: 'app-root',
template: `<ejs-grid #grid [dataSource]='data' allowPaging='true' [editSettings]='editSettings' [toolbar]='toolbar'>
<e-columns>
<e-column field='OrderID' headerText='Order ID' isPrimaryKey=true [validationRules]='orderidrules' width='100' textAlign='Right'></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>`
})
export class AppComponent implements OnInit {
public data: DataManager;
public editSettings: Object;
public toolbar: string[];
public orderidrules: object;
@ViewChild('grid')
public grid: GridComponent;
ngOnInit(): void {
this.data = new DataManager({
url: "Home/UrlDatasource ",
updateUrl: "Home/Update",
insertUrl: "Home/Insert",
removeUrl: "Home/Remove",
adaptor: new UrlAdaptor
});
this.editSettings = { allowEditing: true, allowAdding: true, allowDeleting: true };
this.toolbar = ['Add', 'Edit', 'Delete', 'Update', 'Cancel'];
this.orderidrules = { required: true};
}
}
Demo:
Conclusion
I hope you enjoyed learning about how to bind SQL Server data in Angular DataGrid using SqlClient data provider.
You can refer to our Angular Grid feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Angular Grid 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, Direct-Trac, or feedback portal. We are always happy to assist you!
