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

ADO.NET programming

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.

Understanding ADO.NET Architecture

ADO.NET is an essential component of the .NET Framework, providing developers with a set of classes and tools for developing database-driven applications. Understanding the architecture of ADO.NET is critical in developing efficient and effective database applications.

ADO.NET is a set of classes that allow developers to access and manipulate data stored in various data sources. The primary role of ADO.NET is to provide a unified programming model for working with data from different databases, XML documents, and other data sources.

Explanation of the architecture of ADO.NET

The architecture of ADO.NET consists of several key components, each playing a unique role in the data access process.

  1. Data Providers

Data providers are responsible for connecting to the data source and executing queries. ADO.NET provides a set of built-in data providers for common data sources such as SQL Server, Oracle, and MySQL, as well as a generic data provider for other data sources.

  1. Connection Pooling

Connection pooling is a technique used by ADO.NET to improve performance by reusing existing database connections. When a connection is closed, it is not immediately terminated but returned to the connection pool, ready for reuse.

  1. DataSets and Data Readers

DataSets and Data Readers are two key components of ADO.NET used for working with data. DataSets are in-memory representations of data retrieved from the data source, while Data Readers are forward-only, read-only cursors that provide fast access to data from the data source.

Code samples and explanations

To understand ADO.NET architecture better, here is a code sample that illustrates the use of Data Providers, Connection Pooling, and DataSets:

// Create a connection object using SQL Server data provider
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True”);

// Open the connection
connection.Open();

// Create a command object
SqlCommand command = new SqlCommand(“SELECT * FROM Customers”, connection);

// Create a data adapter object using the command object
SqlDataAdapter adapter = new SqlDataAdapter(command);

// Create a DataSet object
DataSet dataSet = new DataSet();

// Fill the DataSet with data from the data adapter
adapter.Fill(dataSet);

// Close the connection
connection.Close();

In this code sample, we create a SqlConnection object using the SQL Server data provider to connect to a database. We then create a SqlCommand object to execute a SELECT statement, and a SqlDataAdapter object to fill a DataSet with the results. Finally, we close the connection to the database.

Working with Data Providers

Data providers are a critical component of ADO.NET, responsible for connecting to data sources and executing queries. ADO.NET provides several built-in data providers for common data sources, including SQL Server, Oracle, MySQL, and others. Understanding how to work with data providers is essential to developing database-driven applications.

Data providers are responsible for connecting to a data source and executing queries against it. They provide a standardized way of communicating with various data sources and hide the complexities of working with different databases and data formats.

In ADO.NET, data providers implement the IDbConnection, IDbCommand, IDbDataAdapter, and IDataParameter interfaces, providing a uniform way of accessing and manipulating data from different data sources.

Different data providers available in ADO.NET

ADO.NET provides several built-in data providers for working with different data sources. Here are some of the commonly used data providers in ADO.NET:

  1. SQL Server Data Provider

The SQL Server Data Provider is used to connect to Microsoft SQL Server databases. It provides high-performance access to SQL Server databases and supports features such as connection pooling, transaction management, and parameterized queries.

  1. OLE DB Data Provider

The OLE DB Data Provider is used to connect to various data sources, including Microsoft Access, Excel, and Oracle databases. It supports a wide range of data sources and provides high-performance access to them.

  1. Oracle Data Provider

The Oracle Data Provider is used to connect to Oracle databases. It provides high-performance access to Oracle databases and supports features such as connection pooling, transaction management, and parameterized queries.

Code samples and explanations

Here’s an example of using the SQL Server Data Provider to connect to a database and execute a SELECT statement:

// Create a connection object using SQL Server data provider
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True”);

// Open the connection
connection.Open();

// Create a command object
SqlCommand command = new SqlCommand(“SELECT * FROM Customers”, connection);

// Execute the command and retrieve the results
SqlDataReader reader = command.ExecuteReader();

// Loop through the results and print them to the console
while (reader.Read())
{
Console.WriteLine(“CustomerID: {0} \t CompanyName: {1}”, reader[“CustomerID”], reader[“CompanyName”]);
}

// Close the reader and the connection
reader.Close();
connection.Close();

In this code sample, we create a SqlConnection object using the SQL Server data provider to connect to a database. We then create a SqlCommand object to execute a SELECT statement and a SqlDataReader object to retrieve the results. Finally, we loop through the results and print them to the console.

Using Connection Pooling

Connection pooling is a technique used by ADO.NET to improve performance by reusing existing database connections. When a connection is closed, it is not immediately terminated but returned to the connection pool, ready for reuse. Understanding how to use connection pooling in ADO.NET is essential to developing high-performance database applications.

Connection pooling is a technique used to reduce the overhead associated with creating and destroying database connections. By reusing existing connections, connection pooling can significantly improve the performance of database applications.

When a connection is closed, it is returned to the connection pool, where it can be reused for subsequent requests. When a new connection is requested, it is taken from the connection pool if one is available. If not, a new connection is created and added to the connection pool for future use.

Steps in enabling connection pooling in ADO.NET

Enabling connection pooling in ADO.NET is a simple process that involves setting up connection strings and configuring the connection pool.

  1. Setting up connection strings

Connection strings are used to specify the details required to connect to a database. To enable connection pooling, we need to add the “Pooling=True” attribute to the connection string, as shown in the following example:

// Create a connection string with pooling enabled
string connectionString = “Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True;Pooling=True”;

  1. Configuring the connection pool

ADO.NET provides several properties that can be used to configure the connection pool, such as “Min Pool Size”, “Max Pool Size”, and “Connection Lifetime”. These properties can be set in the connection string or programmatically.

Here’s an example of configuring the connection pool programmatically:

// Create a connection object
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True;Pooling=True”);

// Set connection pool properties
connection.MinimumPoolSize = 10;
connection.MaximumPoolSize = 100;
connection.ConnectionLifetime = 300;

// Open the connection
connection.Open();

// Use the connection
// …

// Close the connection
connection.Close();

In this example, we create a SqlConnection object with connection pooling enabled. We then set the minimum and maximum pool sizes and the connection lifetime properties. Finally, we open and close the connection.

Code samples and explanations

Here’s an example of using connection pooling in ADO.NET:

// Create a connection object with pooling enabled
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True;Pooling=True”);

// Open the connection
connection.Open();

// Use the connection
// …

// Close the connection
connection.Close();

In this example, we create a SqlConnection object with connection pooling enabled. We then open and close the connection.

Working with DataSets and Data Readers

DataSets and Data Readers are two key components of ADO.NET used for working with data. DataSets are in-memory representations of data retrieved from the data source, while Data Readers are forward-only, read-only cursors that provide fast access to data from the data source. Understanding how to work with DataSets and Data Readers is essential to developing efficient and effective database-driven applications.

A DataSet is an in-memory representation of data retrieved from a data source. It provides a disconnected view of the data, allowing it to be manipulated without a continuous connection to the data source. A DataSet can contain multiple DataTables, each representing a subset of the data.

A Data Reader is a forward-only, read-only cursor that provides fast access to data from a data source. Unlike a DataSet, a Data Reader maintains a continuous connection to the data source and does not store the data in memory. Data Readers are used for retrieving large amounts of data quickly and efficiently.

Steps in using DataSets and Data Readers in ADO.NET

Working with DataSets and Data Readers in ADO.NET involves several steps, including creating and populating DataSets and using Data Readers for fast data access.

  1. Creating and populating DataSets

To create a DataSet, we first need to create a data adapter and use it to fill the DataSet with data from the data source. Here’s an example:

// Create a connection object
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True”);

// Create a command object
SqlCommand command = new SqlCommand(“SELECT * FROM Customers”, connection);

// Create a data adapter object using the command object
SqlDataAdapter adapter = new SqlDataAdapter(command);

// Create a DataSet object
DataSet dataSet = new DataSet();

// Fill the DataSet with data from the data adapter
adapter.Fill(dataSet, “Customers”);

In this example, we create a SqlConnection object and a SqlCommand object to execute a SELECT statement. We then create a SqlDataAdapter object using the SqlCommand object and a DataSet object. Finally, we fill the DataSet with data from the data adapter.

  1. Using Data Readers for faster data access

To use a Data Reader, we first need to create a command object and execute the command. Here’s an example:

// Create a connection object
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True”);

// Open the connection
connection.Open();

// Create a command object
SqlCommand command = new SqlCommand(“SELECT * FROM Customers”, connection);

// Execute the command and retrieve the results
SqlDataReader reader = command.ExecuteReader();

// Loop through the results and print them to the console
while (reader.Read())
{
Console.WriteLine(“CustomerID: {0} \t CompanyName: {1}”, reader[“CustomerID”], reader[“CompanyName”]);
}

// Close the reader and the connection
reader.Close();
connection.Close();

In this example, we create a SqlConnection object and a SqlCommand object to execute a SELECT statement. We then create a SqlDataReader object and use it to retrieve the results. Finally, we loop through the results and print them to the console.

Code samples and explanations

Here’s an example of using DataSets and Data Readers in ADO.NET:

// Create a connection object
SqlConnection connection = new SqlConnection(“Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;Integrated Security=True”);

// Create a command object
SqlCommand command = new SqlCommand(“SELECT * FROM Customers”, connection);

// Create a data adapter object using the command object
SqlDataAdapter adapter = new SqlDataAdapter(command);

// Create a DataSet object
DataSet dataSet = new DataSet();

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