First ADO.NET Program in C#

In this tutorial, you will learn how to write ADO.NET command in the console, and windows form and then connect to the database. This is just a demo program to make you understand how ADO.NET works. In the next few chapters, you will learn everything about ADO.Net.

Start Programming


1. Open Visual Studio.
2. Go to Server Explorer. If Server Explorer is not opened go to View Server Explorer.
3. Right click on Data Connections and select Create New SQL Server Database.
4. Fill details like this.

---

1. Now, Expand your database Right click on Tables Add New Table.

2. Create a new table like this.

Query:

CREATE TABLE [dbo].[Student] (
    [Id]      INT           NOT NULL,
    [Name]    NVARCHAR (50) NULL,
    [Subject] NVARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

3. After finishing table click on Update button that is the upper right corner of design pane.
4. Right click on Student Table and select Show Table Data. Now, fill some demo data in it like this.

---

1. Now, your database and tables are ready. You can find your connection string in properties of the database.
a. Right Click on Database Properties

Now, you have a database with a table and a connection string. The next step is to connect to the database and retrieve the records using ADO.Net Commands.

---

 

Retrieve Record in Console Application

Start New Console Application.

1. Go to File > New > Project. Create a new console application, FirstProgram and click OK.
2. Paste the following code in the program window and press ctrl + F5

using System;
using System.Data.SqlClient;

namespace FirstProgram
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True";
            SqlConnection con = new SqlConnection(ConString);
            string querystring = "Select * from Student";
            con.Open();
            SqlCommand cmd = new SqlCommand(querystring, con);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString());
            }
        }
    }
}

Output

Retrieve Records in Windows Application

Create New Project

1. Go to File New Project. Select Windows Form Application (C#), give project name FirstForm and click OK.

2. Drag and Drop a Button and a Label Control to Form. You can find these controls on Tool Box. If Tool Box is not available to open it from View Tool Box

3. Double Click on the Button control. It will open button_click event in code windows. Now paste the following code.

using System;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace FirstForm
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=TestDB;User ID=sa;Password=System123;Pooling=False";
            SqlConnection con = new SqlConnection(ConString);
            string querystring = "Select * from Student";
            con.Open();
            SqlCommand cmd = new SqlCommand(querystring, con);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                label1.Text = reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString();
            }
        }
    }
}

Summary

In this tutorial, you just learn how to create a database in server explorer, how to connect this database with your project. In this chapter, I have used two methods to connect with a database, Console application, and Windows application. This is just a demo program to making you comfortable with ADO.NET programming. Once you complete this chapter carefully you get a clear picture that how ADO.NET works. In the next chapter, you will learn all the basic commands of ADO.NET that is used widely to connect and work with databases.