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

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

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

DataTable oDataTable = new DataTable();
string ConnectionString = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
string Sql = "SELECT  StudentName, Address, Phone FROM  Students";

using (SqlConnection oSqlConnection = new SqlConnection(ConnectionString))
{
    try
    {
        oSqlConnection.Open();
        SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(Sql, oSqlConnection);
        oSqlDataAdapter.Fill(oDataTable);
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message.ToString());                    
    }
    finally
    {
        oSqlConnection.Close();
        oSqlConnection.Dispose();
    }
}

Remember that the above code will return a DataTable.

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

No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *