Insert into database using ADO.NET and C#

In most of the applications we need to access the database. For this lot of technologies are available. ADO.NET is one of them. It is built-in with Microsoft .net framework. By using ADO.NET we can easily perform CRUD Operation (Create, Read, Update, Delete or Select, Insert, Update, Delete). This article describes how to insert data in ADO.NET and C#. Summary of the article:

• Database Design
• Adding a Connection String
• Insert Operation in ADO.NET and C#

Database Design
Create a Database in your SQL Server named TestDB. Create a Students table in the database. The SQL scripts for Students table is:

CREATE TABLE [dbo].[Students](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](150) NULL,
    [Address] [nvarchar](200) NULL,
    [Phone] [nvarchar](50) NULL,
 CONSTRAINT [PK_member] PRIMARY KEY CLUSTERED
(
    [StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Adding a Connection String
we need to add or create a connection string in Web.config file.Add the following element to the <connectionStrings> element in Web.config file:

 <add name="ConStr" connectionString="Data Source=localhost;Initial Catalog=TestDB;Persist Security Info=True;User ID=sa;Password=sa123"
        providerName="System.Data.SqlClient"/>

Insert Operation in ADO.NET and C#
We can Insert values into a SQL Server database using ado.net and C# in two ways. But at first we need to include some namespaces.Please add the following namespaces:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Querying the Database
The following C# code performs a SQL command to insert a record:

string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "INSERT INTO Students(StudentName, Address, Phone) VALUES ('Mr. AAAA', 'London', '44125487')";

using (SqlConnection oSqlConnection = new SqlConnection(ConnectionString))
{
    SqlCommand oSqlCommand = new SqlCommand(Sql, oSqlConnection);
    try
    {
        oSqlConnection.Open();
        oSqlCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message.ToString());
    }
    finally
    {
        oSqlCommand.Dispose();
        oSqlConnection.Close();
        oSqlConnection.Dispose();
    }
}

Querying using Parameterized Queries.
To avoid SQL injection we should always use parameterized queries. They are as fast as stored procedures.

string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "INSERT INTO Students(StudentName, Address, Phone) VALUES (@StudentName, @Address, @Phone)";

using (SqlConnection oSqlConnection = new SqlConnection(ConnectionString))
{
    SqlCommand oSqlCommand = new SqlCommand(Sql, oSqlConnection);
    try
    {
        oSqlConnection.Open();
        SqlCommand cmd = new SqlCommand(Sql, oSqlConnection);
        oSqlCommand.Parameters.AddWithValue("@StudentName", "Mr. BBB");
        oSqlCommand.Parameters.AddWithValue("@Address", "Dhaka");
        oSqlCommand.Parameters.AddWithValue("@Phone", "98");
        oSqlCommand.CommandType = CommandType.Text;
        oSqlCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message.ToString());
    }
    finally
    {
        oSqlCommand.Dispose();
        oSqlConnection.Close();
        oSqlConnection.Dispose();
    }
}

That’s all Database Programming with ADO.NET in C#.