Update data 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 update data in ADO.NET and C#. Summary of the article:

• Database Design
• Adding a Connection String
• Update 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]

Insert some data into Students table.

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"/>

Update Operation in ADO.NET and C#
We can update values from SQL Server database using ado.net and C#. 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 update a record:

string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "UPDATE Students SET StudentName = 'Mr. John' WHERE  (StudentID =1)";

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();

    }
}

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