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

• Database Design
• Adding a Connection String
• Delete 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"/>

Delete Operation in ADO.NET and C#
We can delete 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 delete a record:

string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "DELETE FROM Students WHERE (StudentID = 2)";

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#.