How to perform CRUD operations with ASP SqlDataSource?
The SqlDataSource is used to access data that is in relational database. The Grid fetches the data based on the SELECT command of the ASP SqlDataSource control.
We will see in detail on how to perform CRUD operations directly to database using asp sqlDataSource
Solution:
We can achieve the above requirement by using the InsertCommand, UpdateCommand and DeleteCommand properties of the asp SqlDataSource.
Example:
In the following example, we have rendered a grid with CRUD enabled.
- Render the grid
ASP
[aspx]
<ej:Grid ID="EmployeesGrid2" runat="server" AllowPaging="True" DataSourceID="SqlData" EnableLoadOnDemand="false" DataSourceCachingMode="None">
<EditSettings AllowEditing="true" AllowAdding="true" AllowDeleting="true" />
<ToolbarSettings ToolbarItems="add,edit,delete,update,cancel" ShowToolbar="true"></ToolbarSettings>
<Columns>
<ej:Column Field="OrderID" IsPrimaryKey="true"/>
<ej:Column Field="EmployeeID" />
<ej:Column Field="Freight" Format="{0:c2}" />
<ej:Column Field="ShipCity" />
</Columns>
</ej:Grid>
- Using the InsertCommand, UpdateCommand and DeleteCommand properties of the sqlDataSource, write the sql command corresponding to insert, update and delete operation in grid
<asp:SqlDataSource ID="SqlData" runat="server" ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>" SelectCommand="SELECT * FROM [Orders]" InsertCommand="INSERT INTO [Orders] ([Freight], [EmployeeID], [ShipCity]) VALUES (@Freight, @EmployeeID, @ShipCity)" DeleteCommand= "DELETE FROM [Orders] WHERE [OrderID] = @OrderID" UpdateCommand="UPDATE [Orders] SET [Freight] = @Freight, [EmployeeID] = @EmployeeID, [ShipCity] = @ShipCity WHERE [OrderID] = @OrderID"> <UpdateParameters> <asp:Parameter Name="Freight" Type="Double" /> <asp:Parameter Name="EmployeeID" Type="Int32" /> <asp:Parameter Name="ShipCity" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="Freight" Type="Double" /> <asp:Parameter Name="EmployeeID" Type="Int32" /> <asp:Parameter Name="ShipCity" Type="String" /> </InsertParameters> <DeleteParameters> <asp:Parameter Name="OrderID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource>
We can pass the parameters corresponding to insert, update and delete operations using the Insertparameters, UpdateParameters and DeleteParameters properties of the SqlDataSource.
If the IsIdentity property for a column is set in the database, it is not necessary to define the column within the InsertCommand property as the column value will be automatically generated and thus will conflict with the value we pass.
