Loading ...

SQLDataSource: Insert, Update and Delete data | CodeAsp.Net

SQLDataSource: Insert, Update and Delete data

(27237)
3.64
/5
Avg: 3.64/5: (1 votes)
Published: 6/27/2011 by Raghav Khunger

In this article I will show how to insert, update and delete data with SQLDataSource control. The SqlDataSource control makes use of ADO.NET classes (System.Data.Odbc, System.Data.OleDb, System.Data.SqlClient, System.Data.OracleClient) to connect to any DB which ADO.NET supports. SqlDataSource control grants you to access and manipulate data with database without directly using ADO.NET classes. SqlDataSource exposes some properties to specify the connection string to connect to the database and defines the SQL statements or stored procedures. Behind the scenes SqlDataSource control automatically opens the database connection and run the Stored Procedure or plain SQL statement, returns the necessary data (if any), and then automatically closes the connection when it is done. Below I will explain with reference to SQL Server on:

  1. Insert data with SQLDataSource
  2. Update data with SQLDataSource
  3. Delete data with SQLDataSource

With the help of SQLDataSource control you can perform  insert, update, and delete operation to database directly from an ASP.NET page, bypassing the architecture. For creating simple applications you can go with this way i.e bypassing the architecture but for complex and large applications I will suggest to avoid SQLDataSource and go with separate tiers architecture.

Insert data with SQLDataSource:




Let's come to database and create a table say "UserTable" with the following columns UserID, FirstName, LastName, Age and DateCreated. Below is the script for it:

USE [Test]
GO

/****** Object:  Table [dbo].[UserTable]    Script Date: 06/25/2011 12:46:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserTable](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[DateCreated] [datetime] NULL
) ON [PRIMARY]

GO

Let's come to frontend part. Below is the code how our ASPX page will look:

ASPX:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="HtmlForm" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    First Name:
                </td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Last Name:
                </td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Age:
                </td>
                <td>
                    <asp:TextBox ID="AgeTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="SubmitButton" runat="server" Text="Insert" OnClick="SubmitButton_Click" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Label ID="MyLabel" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
        <asp:SqlDataSource runat="server" ID="MySqlDataSource" 
        ConnectionString="Data Source=RAGHAV-PC;Initial Catalog=Test;
        Persist Security Info=True;User ID=test;Password=test"
            InsertCommand="INSERT INTO UserTable([FirstName],[LastName],[Age],[DateCreated]) 
            VALUES (@FirstName,@LastName,@Age,@DateCreated)" ProviderName="System.Data.SqlClient">
            <InsertParameters>
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Age" Type="Int32" />
                <asp:Parameter Name="DateCreated" Type="DateTime" />
            </InsertParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

We have three ASP.NET textboxes on our page, one button and one label. User will enter the data in the textboxes and click the submit button and his data will be inserted into database. Below is the server side code to do the same:

Codebehind:

protected void SubmitButton_Click(object sender, EventArgs e)
    {
        MySqlDataSource.InsertParameters["FirstName"].DefaultValue = FirstNameTextBox.Text;
        MySqlDataSource.InsertParameters["LastName"].DefaultValue = LastNameTextBox.Text;
        MySqlDataSource.InsertParameters["Age"].DefaultValue = AgeTextBox.Text;
        MySqlDataSource.InsertParameters["DateCreated"].DefaultValue = DateTime.Now.ToString();

        int rowsInserted = MySqlDataSource.Insert();
        
        MyLabel.Text = rowsInserted > 0 ? "Successfully inserted." : "Error occured.";

    }

 

Below is the output of select query run on this table, you can see the records are inserted after inserting the records from front end few times.






Update data wil SQLDataSource:




ASPX:

 

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="HtmlForm" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    UserID:
                </td>
                <td>
                    <asp:TextBox ID="UserIDTextBox" runat="server" Enabled="false"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    First Name:
                </td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Last Name:
                </td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Age:
                </td>
                <td>
                    <asp:TextBox ID="AgeTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="SubmitButton" runat="server" Text="Update" OnClick="SubmitButton_Click" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Label ID="MyLabel" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
        <asp:SqlDataSource runat="server" ID="MySqlDataSource" ConnectionString="Data Source=RAGHAV-PC;Initial Catalog=Test;
        Persist Security Info=True;User ID=test;Password=test" UpdateCommand="UPDATE UserTable SET[FirstName]=@FirstName,[LastName]=@LastName,[Age]=@Age 
            WHERE UserID=@UserID" ProviderName="System.Data.SqlClient">
            <UpdateParameters>
                <asp:Parameter Name="UserID" Type="Int32" />
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Age" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Again we have three ASP.NET textboxes on our page, one button and one label. User will edit the data in the textboxes and click the submit button and his data will be updated into database. Below is the server side code to do the same:

Codebehind:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            UserIDTextBox.Text = "2";
            FirstNameTextBox.Text = "Lionel";
            LastNameTextBox.Text = "Messi";
            AgeTextBox.Text = "32";
        }
    }

    protected void SubmitButton_Click(object sender, EventArgs e)
    {
        MySqlDataSource.UpdateParameters["FirstName"].DefaultValue = FirstNameTextBox.Text;
        MySqlDataSource.UpdateParameters["LastName"].DefaultValue = LastNameTextBox.Text;
        MySqlDataSource.UpdateParameters["Age"].DefaultValue = AgeTextBox.Text;
        MySqlDataSource.UpdateParameters["UserID"].DefaultValue = UserIDTextBox.Text;

        int rowsUpdated= MySqlDataSource.Update();

        MyLabel.Text = rowsUpdated > 0 ? "Successfully updated." : "Error occured.";

    }


Below is the output of select query run on this table, you can see the second row has been updated.



Delete data wil SQLDataSource:



ASPX:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="HtmlForm" runat="server">
    <div>
        <table>
            <tr>
                <td>
                    UserID:
                </td>
                <td>
                    <asp:TextBox ID="UserIDTextBox" runat="server" Enabled="false"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    First Name:
                </td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Last Name:
                </td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Age:
                </td>
                <td>
                    <asp:TextBox ID="AgeTextBox" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="SubmitButton" runat="server" Text="Delete" OnClick="SubmitButton_Click" />
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Label ID="MyLabel" runat="server"></asp:Label>
                </td>
            </tr>
        </table>
        <asp:SqlDataSource runat="server" ID="MySqlDataSource" ConnectionString="Data Source=RAGHAV-PC;Initial Catalog=Test;
        Persist Security Info=True;User ID=test;Password=test" DeleteCommand="DELETE FROM UserTable 
            WHERE UserID=@UserID" ProviderName="System.Data.SqlClient">
            <DeleteParameters>
                <asp:Parameter Name="UserID" Type="Int32" />
            </DeleteParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>


In this section user will have a delete button to delete the data. On clicking the delete button the data will be deleted from the database.

Codebehind:

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            UserIDTextBox.Text = "2";
            FirstNameTextBox.Text = "Lionel";
            LastNameTextBox.Text = "Messi";
            AgeTextBox.Text = "40";
        }
    }

    protected void SubmitButton_Click(object sender, EventArgs e)
    {
        MySqlDataSource.DeleteParameters["UserID"].DefaultValue = UserIDTextBox.Text;

        int rowsDeleted= MySqlDataSource.Delete();

        if (rowsDeleted > 0)
        {
            MyLabel.Text = "Successfully deleted.";
            UserIDTextBox.Text = "";
            FirstNameTextBox.Text = "";
            LastNameTextBox.Text = "";
            AgeTextBox.Text = "";
        }
        else
        {
            MyLabel.Text = "Error occured.";
        }
    }

Below is the output of select query run on this table, you can see the row with UserID 2 has been deleted from the database.

That's it above we discussed how to insert, update and delete data with the help of SQLDataSource control. I hope the above discussion will help you.

 

Comments (3)

vivek_iit
Vivek Thakur said:
nice
7/20/2011
 · 
 
by
raghav_khunger
Thanks
7/20/2011
 · 
 
by
swatibhivgade
thank you.I want to know If I have two tables and i want to show that data in gridview including all(insert,update,delete).how?
9/17/2012
 · 
 
by
tariq
tariq said:
Thanks and it help me
6/22/2013
 · 
 
by
  • Name:*
  • Email:*
  • Website:
Type the characters you see in the image *

Top articles

Quick Vote

What kind of email newsletter would you prefer to receive from CodeAsp.Net?