How to insert records into the database?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 17284 red flag
Rating: 4.5 out of 5  
 2 vote(s)

In this article, we shall learn how to insert records into the SQL Server database.

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In this scenario, we need to connect to the database and insert records into the database, that can be done by following this approach.

Below is my aspx page (aspx.page) to insert records into the database.

ASPX PAGE

<asp:Label ID="lblMessage" runat="server" ForeColor="Green" />

<h5>Create, Read, Update, Delete operation</h5>

<div>

<table>

<tr><td>First name: </td><td><asp:TextBox ID="txtFirstName"

runat="server" /> </td></tr>

<tr><td>Last name: </td><td><asp:TextBox ID="txtLastName"

runat="server" /> </td></tr>

<tr><td>Age: </td><td><asp:TextBox ID="txtAge" runat="server" />

</td></tr>

<tr><td>Active: </td><td>

<asp:DropDownList ID="dropActive" runat="server">

<asp:ListItem Text="Yes" Value="True" />

<asp:ListItem Text="No" Value="False" />

</asp:DropDownList>

</td></tr>

<tr><td>&nbsp;</td><td><asp:Button ID="btnSubmit" runat="server"

Text="Submit" OnClick="SubmitData" /></td></tr>

</table>

</div>

It has basically three text boxes, one dropdown list and a button. On click of the button, SubmitData server side method executes.

CODE BEHIND

 

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

/// <summary>

/// Submits the data.

/// </summary>

/// <param name="sender">The sender.</param>

/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event

data.</param>

protected void SubmitData(object sender, EventArgs e)

{

int returnValue = 0;

 

// get the connection

using (SqlConnection conn = new SqlConnection(_connStr))

{

// write the sql statement to execute

string sql = "INSERT INTO PersonalDetail (FirstName, LastName, Age, Active)

VALUES " +

"(@FirstName, @LastName, @Age, @Active)";

// instantiate the command object to fire

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

// attach the parameter to pass, if no parameter is in the sql no need to

attach

SqlParameter[] prms = new SqlParameter[4];

prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);

prms[0].Value = txtFirstName.Text.Trim();

prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);

prms[1].Value = txtLastName.Text.Trim();

prms[2] = new SqlParameter("@Age", SqlDbType.Int);

prms[2].Value = int.Parse(txtAge.Text.Trim());

prms[3] = new SqlParameter("@Active", SqlDbType.Bit);

prms[3].Value = bool.Parse(dropActive.SelectedValue);

 

cmd.Parameters.AddRange(prms);

conn.Open();

returnValue = cmd.ExecuteNonQuery();

conn.Close();

}

}

if (!returnValue.Equals(0))

{

lblMessage.Text = " Records inserted successfully !";

}

}

In the above code snippet, on the aspx page we have three TextBoxes, a DropDownList (First name, Last name, Age & Active) and a Submit button. On click of the Submit, we have attached the SubmitData server side method.

In the SubmitData method, we have a SqlConnection object and SqlCommand object in the same way we had for fetching the records from the database. The only change here is the sql command that is going to execute. As our sql command has four parameters, we need to attach four SqlParameters to the command object. To execute the Insert, Update and Delete sql query, we need to fire the ExecuteNonQuery method of the command object (before executing this command, ensure that the database connection is open and after executing close this immediately to avoid any memory leak).

ExecuteNonQuery method returns number of records affected with the Sql statement. So we have checked if it is not equal to 0 and then wrote the success message as displayed in the picture below.

OUTPUT

As written in the last article, we are using the using {} block so we do not need to explicitly dispose the SqlConnection, SqlCommand objects once we are done with them.

NOTE: In the real time project, you should use Stored Procedure rather than using inline sql statements. We shall learn about how to use Stored Procedure in coming up article.

Thanks for reading and keep reading, learning and sharing, after all it increases the knowledge !


Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: A4u_6178 on: 8/16/2011 | Points: 25
Hello sir ,
correction needed..
It has basically three radio buttons, one dropdown list and a button. On click of the button, SubmitData server side method executes
Your form doesn't contain any radio button in it.It contains three textboxes.
Posted by: SheoNarayan on: 8/16/2011 | Points: 25
Dear A4u_6178,

Thanks for pointing out the typo. I have corrected.

Regards

Posted by: Vishvvas on: 8/17/2011 | Points: 25
Just curious about the note where it is mentioned to use stored procedure inplace of inlinesql. Any specific reasons?

Login to post response

Comment using Facebook(Author doesn't get notification)