SQLDataSource: Insert, Update and Delete data
http://codeasp.net/articles/asp-net/249/sqldatasource-insert-update-and-delete-data
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:
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:
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:
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.
<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:
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.
<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.
No comments:
Post a Comment