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.
Generate data table from SSMS
Right click on the Contacts database and select New Query
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]
GOSimilarly, 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
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
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 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.
