Ado.Net Programming: Practice Basic – Create tables, Connect, Read Data

In this lesson we will start with hands-on content: create data tables in Sql Server, write code to connect and read-write data with ADO.NET. The goal of the article is to give you a first-hand feel of ADO.NET programming and how to work with databases from the program. It will make it easier for you to visualize ADO.NET when we discuss the theoretical content in the following article.

In the previous lesson we installed the necessary tools to learn ADO.NET. We also created a database called Contacts for practice and example. In this lesson we will create the necessary data tables. Then we will use ADO.NET to write a simple program that first reads and writes data to the Contacts database.

ADO.NET Programming: Creating Data Tables in SSMS and SQL Server

Structure of data tables

The contacts database created in the previous post is used to store customer contact information. Each record in the phonebook contains the following information:

contact name (contact name),

Nicknames (alias),

Last name + middle name (first name),

Last name,

birthday (birth day),

Email list, each email in the list includes email type (personal, work, organization) and corresponding email address.

List of phone numbers, each phone information includes type (personal, work), and corresponding phone number.

We will create tables with the following structure for the Contacts database.

ADO.NET programming

ADO.NET programming

Generate data table from SSMS

Right click on the Contacts database and select New Query

ADO.NET programming

Copy the following SQL code and paste it into the query window and press F5 to run it.

USE [Contacts]
GO
/****** Object:  Table [dbo].[Contacts] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ContactName] [nvarchar](max) NULL,
    [Alias] [nvarchar](max) NULL,
    [FirstName] [nvarchar](max) NULL,
    [LastName] [nvarchar](max) NULL,
    [DateOfBirth] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.Contacts] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Emails] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [nvarchar](max) NULL,
    [EmailAddress] [nvarchar](max) NULL,
    [Contact_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Emails] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Phones]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Phones](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Type] [nvarchar](max) NULL,
    [Number] [nvarchar](max) NULL,
    [Contact_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Phones] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Emails]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Emails_dbo.Contacts_Contact_Id] FOREIGN KEY([Contact_Id])
REFERENCES [dbo].[Contacts] ([Id])
GO
ALTER TABLE [dbo].[Emails] CHECK CONSTRAINT [FK_dbo.Emails_dbo.Contacts_Contact_Id]
GO
ALTER TABLE [dbo].[Phones]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Phones_dbo.Contacts_Contact_Id] FOREIGN KEY([Contact_Id])
REFERENCES [dbo].[Contacts] ([Id])
GO
ALTER TABLE [dbo].[Phones] CHECK CONSTRAINT [FK_dbo.Phones_dbo.Contacts_Contact_Id]
GO

Similarly, enter some data using the following script:

USE [Contacts]
GO
SET IDENTITY_INSERT [dbo].[Contacts] ON
INSERT INTO [dbo].[Contacts] ([Id], [ContactName], [Alias], [FirstName], [LastName], [DateOfBirth]) VALUES (1, N'Donald Trump', N'trump', N'Trump', N'Donald', N'2019-06-26 12:21:29')
INSERT INTO [dbo].[Contacts] ([Id], [ContactName], [Alias], [FirstName], [LastName], [DateOfBirth]) VALUES (2, N'Barack Obama', N'obama', N'Obama', N'Barack', N'2019-06-26 12:22:00')
SET IDENTITY_INSERT [dbo].[Contacts] OFF
GO
SET IDENTITY_INSERT [dbo].[Emails] ON
INSERT INTO [dbo].[Emails] ([Id], [Type], [EmailAddress], [Contact_Id]) VALUES (1, N'Work', N'trump@gmail.com', 1)
INSERT INTO [dbo].[Emails] ([Id], [Type], [EmailAddress], [Contact_Id]) VALUES (2, N'Home', N'donald@gmail.com', 1)
INSERT INTO [dbo].[Emails] ([Id], [Type], [EmailAddress], [Contact_Id]) VALUES (3, N'Work', N'obama@gmail.com', 2)
INSERT INTO [dbo].[Emails] ([Id], [Type], [EmailAddress], [Contact_Id]) VALUES (4, N'Home', N'barack@gmail.com', 2)
SET IDENTITY_INSERT [dbo].[Emails] OFF
SET IDENTITY_INSERT [dbo].[Phones] ON
INSERT INTO [dbo].[Phones] ([Id], [Type], [Number], [Contact_Id]) VALUES (1, N'Work', N'0123.456.789', 1)
INSERT INTO [dbo].[Phones] ([Id], [Type], [Number], [Contact_Id]) VALUES (2, N'Home', N'0987.654.321', 1)
SET IDENTITY_INSERT [dbo].[Phones] OFF
GO

Please note that this is not a SQL document so we don’t have to spend time explaining the above SQL code blocks. We assume that by following this course you have learned and mastered the basics of relational databases and the SQL language.

Practice 1 – Programming the First ADO.NET Application

This first ADO.NET application program will read the number of contacts in the database and print it to the screen.

Create a new solution named HelloAdoNet with a project of type Console App (.NET framework) like below

ADO.NET programming

Open the Program.cs file and write the following code:

using System;
using System.Data.SqlClient;
namespace Example01
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            // object đảm nhiệm việc kết nối
            var connection = new SqlConnection
            {
                // chuỗi ký tự chứa tham số phục vụ kết nối
                ConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True"
            };
            // object đảm nhiệm việc thực thi truy vấn
            var command = new SqlCommand()
            {
                Connection = connection,
                CommandText = "SELECT COUNT(*) FROM CONTACTS"
            };

            // thử mở kết nối
            connection.Open();
            // thực thi truy vấn và lấy kết quả
            var res = (int)command.ExecuteScalar();
            // đóng kết nối
            connection.Close();
            Console.WriteLine($"{res} contacts found in the database");
            Console.ReadLine();
        }
    }
}

Translate and test the program

ADO.NET programming

First ado.net program: read record number from database

Congratulations on writing your first program with ADO.NET. The program demonstrates how to work with the database through ADO.NET to read a single value.

SqlCommand’s ExecuteScalar method returns only one value of type object. Therefore, it is only suitable for reading statistical values ​​from Sql Server.

The program is very simple and the code does not perform any checks at all. For the time being, we will not analyze the program’s code. In the next lessons when learning the theory of ADO.NET we will return to the detailed analysis.

Practice 2 – Read Records from Database

Create a second project of type Console App (.NET Framework) in the same solution and name it Example02. Set up Example02 as a startup project.

Open the Program.cs file and code as follows:

using System;
using System.Data;
using System.Data.SqlClient;
namespace Example02
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True";
            var connection = new SqlConnection(connectionString);
            var command = new SqlCommand("SELECT * FROM CONTACTS", connection);
            connection.Open();
            var dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    var id = dataReader.GetInt32(0);
                    var contactName = dataReader.GetString(1);
                    var alias = dataReader.GetString(2);
                    Console.WriteLine($"[{id}] {contactName} ({alias})");
                }
            }
            Console.ReadLine();
        }
    }
}

Translate and run the program:

ADO.NET programming

Ado.NET program reads records from database

Congratulations, you have built a program that is able to fully read records from SQL Server data tables. This program is different in that it reads all records from a data table.

You may have noticed that the initial steps (like preparing the connection string, creating an object connection, commanding, opening the connection) are not different from Example 1. It’s just that we use other overloads. of the constructor for illustrative purposes.

At the data read stage, instead of using ExecuteScalar method, we use SqlCommand’s ExecuteReader method to read the whole record. To read the information of each field in the record we use the ordinal number of the data column and convert to the corresponding data type of .NET through the Get methods (GetInt32, GetString).

Practice 3 – DataAdapter and DataTable

We create a third project named Example03 similar to the two examples above and the code is as follows:

using System;
using System.Data;
using System.Data.SqlClient;
namespace Example03
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var connectionString = @"Data Source=.\sqlexpress;Initial Catalog=Contacts;Integrated Security=True";
            var dataTable = new DataTable();
            using (var connection = new SqlConnection(connectionString))
            {
                var command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM CONTACTS";
                var dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
            }
            foreach (DataRow r in dataTable.Rows)
            {
                var id = (int)r["Id"];
                var contactName = (string)r["ContactName"];
                var alias = (string)r["Alias"];
                Console.WriteLine($"[{id}] {contactName} ({alias})");
            }
            Console.ReadLine();
        }
    }
}

Translating and running the program will get the same result as Example 2.

Here we have used a completely different working model. The two examples above we use the Connected model of ADO.NET, and in this example we use the Disconnected model. The difference between these two models will be explained in detail in the following article – ADO.NET architecture.

Conclusion

In this article, we took the first step to familiarize ourselves with ADO.NET programming by performing some simple illustrative examples. These examples are only intended to give you an initial feel for ADO.NET programming.

You should spend some time “playing” with the above code such as changing parameters, testing different overloads of constructors, trying to connect and work with other databases (if any).

In the next article, we will go into each specific problem of ADO.NET programming in turn.

Leave a Reply

Related Posts

  • c# Exception Tostring vs Message – Complete Guide

  • c# Yield Exception Handling – Explained!

  • c# Excel Error 0x800a03ec – Complete Guide

  • c# get Error Message from Modelstate – Explained & Solved

  • c# Error Netsdk1005 – Solved!

  • c# Error Parsing Infinity Value – Explained & Solved!