In this article I am explaining about how to insert data into database using ado.net. This article is helpful for beginners.
Introduction
For each web application, it contains a
database for storing information. In ASP.NET we are commonly using
SQL Server for back end. Microsoft .NET Framework provides ADO.NET
for to
access and modify data stored in relational database systems, though
it can also access data in non-relational sources. ADO. NET is a
collection of classes and tools it is build in to the .net framework
It provides us powerful,reliable and scalable data driven
application. ADO stands for Active
data objects.
Steps
to Insert Data into Sql server Database
Set
the connection string in the web.config file.
Connection
string includes Server Name, Database Name, User Name and
Password.(SQL Server authentication).
Example:(SQL
Server Authentication)
<connectionStrings>
<add name="connString" connectionString="Server=TRAINA\SQLEXPRESS;Database=Susan; User ID=susan; Password=trNg*"/>
</connectionStrings>
Example: (Windows Authentication)
<connectionStrings>
<add name="connectionString" connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;" />
</connectionStrings>
2. Design
a form and Take the code Behind file.
Aspx Page(Design)
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Application._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 100%;">
<tr>
<td>
First Name
</td>
<td>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Last Name
</td>
<td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Email
</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" /></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Please import the
following namespaces in the code behind file.
- using
System.Data;
- using
System.Data.SqlClient;
- using
System.Configuration;
Establishing the
connection between the database,
string
ConnectionString = ConfigurationManager.ConnectionStrings[“connString”].ToString();
Using
ConfigurationManager taking the value of the connection string from
web.config file and holding the value in ConnectionString variable.
public int InsertData()
{
string FirstName = txtFirstName.Text;
string LastName = txtLastName.Text;
string Email = txtEmail.Text;
int inserted = 0;
string QryString = "insert into Toys values('" + FirstName + "','" + LastName + "','" + Email + "')";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand CmdObj = new SqlCommand(QryString, conn);
conn.Open();
inserted = CmdObj.ExecuteNonQuery();
conn.Close();
return inserted;
}We can call this function(InsertDate()) on Save Button's click event. Here
I am using one method named InsertData() to insert data into
database. Here we are inserting FirstName, LastName and Email. In
Qrystring variable, storing the query for insertion of the above
data. Then creating an object for SqlCommand and passing the
Qrystring and ConnectionString to it. Then calling the
ExecuteNonQuery() method. This method will returns the
number of rows affected.
That returned value is assigned to variable called inserted and the
method(InsertData) will return that value. If this method executed
successfully means the data is added to the database. You can check
your table and verify.
Conclusion
Insertion of data into database is a simple task. Simply follow these steps
1. Create Database.
2. Set the connection string.
3. Use Ado.net class and its method.
Reference