FORM VIEW INSERT UPDATE DELETE USING SQL DATASOURCE

FORM VIEW INSERT UPDATE DELETE USING SQL DATASOURCE 

Step 1)  Add SQL DATASOURCE to aspx page & configure as shown below.
                                            Figure 1:  Drag&Drop  SQLDataSource Data Control to webpage design

                                            Figure 2: Click on New Connection, popup will ask for SQL-Server Connection, enter details here, I selected NorthWnd database

 Figure 3: Shows Connection String Name ""NorthWndConnectionString" Stored in the web.config

Figure 4: Select Any table/View,  Here I selected Shippers table for demo

 Figure 5:Click on Advanced...  above figure, here select Generate Insert,Update,Delete Statements
                             2.Use optimistic concurrency also

 Figure 6:  Test the Query, This is for Select Statement only, for update,delete SQLDataSource doesn't support

Step 2)  Add FormView to webpage Enable Paging. It will display paging index.

Select DataSource has SqlDataSource1 (Step1).

Note: In FormView Design select DataSource SQLDataSource1, remaining template it will generate.

        <asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataKeyNames="ShipperID" DataSourceID="SqlDataSource1" EmptyDataText="No data Found" GridLines="Horizontal" HeaderText="FormView example for insert,update,delete operations using SQLDATASOURCE" Height="161px" Width="426px">

            <EditItemTemplate>

                ShipperID:

                <asp:Label ID="ShipperIDLabel1" runat="server" Text='<%# Eval("ShipperID") %>' />

                <br />

                CompanyName:

                <asp:TextBox ID="CompanyNameTextBox" runat="server" Text='<%# Bind("CompanyName") %>' />

                <br />

                Phone:

                <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                <br />

                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />

                &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />

            </EditItemTemplate>

            <InsertItemTemplate>

                CompanyName:

                <asp:TextBox ID="CompanyNameTextBox" runat="server" Text='<%# Bind("CompanyName") %>' />

                <br />

                Phone:

                <asp:TextBox ID="PhoneTextBox" runat="server" Text='<%# Bind("Phone") %>' />

                <br />

                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" />

                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />

            </InsertItemTemplate>

            <ItemTemplate>

                ShipperID:

                <asp:Label ID="ShipperIDLabel" runat="server" Text='<%# Eval("ShipperID") %>' />

                <br />

                CompanyName:

                <asp:Label ID="CompanyNameLabel" runat="server" Text='<%# Bind("CompanyName") %>' />

                <br />

                Phone:

                <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>' />

                <br />

                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" />

                &nbsp;<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" />

                &nbsp;<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New" />

            </ItemTemplate>

        </asp:FormView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" DeleteCommand="DELETE FROM [Shippers] WHERE [ShipperID] = @original_ShipperID AND [CompanyName] = @original_CompanyName AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL))" InsertCommand="INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Shippers]" UpdateCommand="UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE [ShipperID] = @original_ShipperID AND [CompanyName] = @original_CompanyName AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL))">

            <DeleteParameters>

                <asp:Parameter Name="original_ShipperID" Type="Int32" />

                <asp:Parameter Name="original_CompanyName" Type="String" />

                <asp:Parameter Name="original_Phone" Type="String" />

            </DeleteParameters>

            <InsertParameters>

                <asp:Parameter Name="CompanyName" Type="String" />

                <asp:Parameter Name="Phone" Type="String" />

            </InsertParameters>

            <UpdateParameters>

                <asp:Parameter Name="CompanyName" Type="String" />

                <asp:Parameter Name="Phone" Type="String" />

                <asp:Parameter Name="original_ShipperID" Type="Int32" />

                <asp:Parameter Name="original_CompanyName" Type="String" />

                <asp:Parameter Name="original_Phone" Type="String" />

            </UpdateParameters>

        </asp:SqlDataSource>

    </div>

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString2 %>" DeleteCommand="DELETE FROM [Shippers] WHERE [ShipperID] = @original_ShipperID AND [CompanyName] = @original_CompanyName AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL))" InsertCommand="INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Shippers]" UpdateCommand="UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE [ShipperID] = @original_ShipperID AND [CompanyName] = @original_CompanyName AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL))">

            <DeleteParameters>

                <asp:Parameter Name="original_ShipperID" Type="Int32" />

                <asp:Parameter Name="original_CompanyName" Type="String" />

                <asp:Parameter Name="original_Phone" Type="String" />

            </DeleteParameters>

            <InsertParameters>

                <asp:Parameter Name="CompanyName" Type="String" />

                <asp:Parameter Name="Phone" Type="String" />

            </InsertParameters>

            <UpdateParameters>

                <asp:Parameter Name="CompanyName" Type="String" />

                <asp:Parameter Name="Phone" Type="String" />

                <asp:Parameter Name="original_ShipperID" Type="Int32" />

                <asp:Parameter Name="original_CompanyName" Type="String" />

                <asp:Parameter Name="original_Phone" Type="String" />

            </UpdateParameters>

        </asp:SqlDataSource>

Just Run the Program.

Note: Here I am not handling Exceptions for Insert,Update and delete
For Insert ItemInserting,ItemInserted
For Update ItemUpdating,ItemUpdated
For Delete ItemDeleting,ItemDeleted
If item failed to insert,update,delete(cascade) event with -ed will have exception object
Same way SQL DataSource also has SQL DataSource Updating,Deleting inserting events, it also has Exception object.