This tutorial introduces database interaction in .NET, and more specifically the ADO.NET architecture and how to use it to work with databases.
In this article:
- Prerequisites
- Preamble
- The ADO.NET Architecture
- The DataSet
- ADO.NET Data Providers
- Using DataReader
- Using DataAdapter
- Using Database Transactions
- SQL Parameters
- SQL Server Management Objects (SMO)
- Data Factories
Prerequisites
To fully comprehend this tutorial you may need to have a basic understanding of:
- SQL (Structured Query Language), which is used to interact with databases.
- Relational Databases and how they work.
- Database server(s) and basic administration of them (creating databases, tables, etc.)
If you don’t have the above skills/experience, you can still continue with this tutorial but you may struggle with some concepts.
Preamble
ADO (ActiveX Data Objects) is an historial COM architecture that was developed by Microsoft to promote a consistent mechanism for interacting with data sources on Windows operating systems. ADO provided a common layer (in the form of COM libraries) that transcended specific programming languages, enabling a unified approach to data access and database management.
When Microsoft introduced .NET they included a new data access architecture as part of the Framework Class Library (FCL), and called it ADO.NET. Despite retaining the ADO prefix, the new architecture is so different from the original that it should be considered a separate architecture entirely. In fact, the ADO prefix is a bit of a misnomer since ADO.NET is not an ActiveX/COM technology at all, and Microsoft probably only adopted it as a familiarity for developers.
The ADO.NET Architecture
Traditionally, database interaction relied on a two-tier, connection-based architecture but, as distributed applications are becoming increasingly multi-tier, a more disconnected approach was required.
ADO.NET provides a common approach to interacting with data sources based on the concept of separating data provision from data presentation. In .NET, data providers offer a means to connect to and interact with data sources and the System.Data.DataSet class provides a means to represent the data in memory.
The DataSet class is core to the disconnected nature of ADO.NET.
The DataSet
The System.Data.DataSet class provides a means to hold an in-memory cache of data read from a data source. The class was designed to be independent of any specific data provider, enabling it to be a common object for nearly all in-memory data representation scenarios.
A primary consideration when ADO.NET was being designed was the transmission of data. ADO.NET was developed hand-in-hand with the XML classes of the Framework Class Llibrary to better facilitate this. The DataSet class is the point where all these classes converge – the default serialisation mechanism being XML. A DataSet object can be directly populated with data from an XML source (be it an XML file or stream), or serialised to a W3C-compliant XML sink.
XML is now an industry standard mechanism for streaming data between end-points, be they application constructs on the same computer or spread across several computers on a network (or even across the world!) By ingraining this functionality into the DataSet class, the .NET Framework provides a means to disconnect data read from – or about to be written to – a physical data store from the connection to that data source itself.
The power of this implementation is in its ability to pass data transparently throughout a system and manipulate it in different ways without requiring direct access to the underlying data source. One of the most obvious situations where this could be utilised is in service orientated architectures (SOA.) The SOA is manifested as a set of loosely-coupled services tied together by presentation media (user interfaces (UI)), giving the impression of a single, seamless, application construct from the user’s perspective. The SOA has become a standard implementation for distributed application development.
ADO.NET Data Providers
.NET Data Providers were designed with data manipulation and fast, forward-only read access primarily in mind. The majority of interactions with a data source involve reading data from it – with the number of writes usually being far fewer – so it made sense to focus on improving performance in this area.
All .NET Framework Data Providers must implement functionality based on pre-defined base-classes; the key ones being:
System.Data.Common.DbConnection. Represents a connection to a database.System.Data.Common.DbDataAdapter. An adapter the can be used to load and manipulate data.System.Data.Common.DbDataReader. An optimised adapter that can be used to read data.System.Data.Common.DbCommand. Respresents a database command (or query).System.Data.Common.DbParameter. Represents a database parameter used in commands.System.Data.Common.DbTransaction. Represents a database transaction.
The Framework Class Library ships with a number of Data Providers, most notably for MS SQL Server and Oracle. Third party database providers may also be available that offer implementations supporting connectivity with their databases, where that functionality is not available by default within the FCL. (For example, the FCL includes a data provider for ‘Oracle’ – the most popular Oracle database server type – but does not include one for ‘Oracle RDB’.)
Each .NET Framework Data Provider exposes two mechanisms for reading data from a data source. The first is an implementation of the DbDataAdapter base-class that is used to fill a DataSet object with data. The second is an implementation of DbDataReader.
The DbDataReader implementation provides access to an unbuffered stream of data that allows a developer to process data in small chunks and optimise memory usage. The DataReader is a good choice when reading a large amount of data as the streaming approach removes the necessity to cache the entire dataset in memory. Data can be read a row at a time, and the DataReader provides additional methods that enable individual field values to be cast directly to CLS data-types.
The .NET Framework ships with a total of 4 data providers by default: ODBC, OLE DB, MS SQL Server, and Oracle. This is being mentioned specifically because any SQL being authored needs to be correct for the data provider being used (e.g. MS SQL Server adopts T-SQL, and Oracle adopts PL/SQL).
Using DbDataReader
The DbDataReader implementation provides access to an unbuffered stream of data that allows a developer to process data in small chunks and optimise memory usage. The DbDataReader is a good choice when reading a large amount of data as the streaming approach removes the necessity to cache the entire dataset in memory. Data can be read a row at a time, and the DbDataReader provides additional methods that enable individual field values to be cast directly to CLS data-types.
Let’s look at a worked example. We’ll be using SqlDataReader here, which is a specialised class of DbDataReader targeting MS SQL Server.
If you don’t already have MS SQL Server installed on your machine you can download and use SQL Server Express Edition from Microsoft for free. You can also download SQL Server Management Studio (SSMS) Express Edition, which is a great UI tool for managing MS SQL Server instances.
Once you have everything installed, open SSMS and create a new database called ‘DotNetTutorials’. Run the following script to initialise the database with some tables and content:
USE [DotNetTutorials]
GO
CREATE TABLE [dbo].[Addresses](
[Id] [int] IDENTITY(1,1) NOT NULL,
[HouseNameOrNumber] [nvarchar](50) NOT NULL,
[Street] [nvarchar](max) NOT NULL,
[Area] [nvarchar](max) NOT NULL,
[Town] [nvarchar](50) NOT NULL,
[County] [nvarchar](50) NOT NULL,
[Country] [nvarchar](50) NOT NULL,
[PostCode] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Addresses] 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]
GO
CREATE TABLE [dbo].[People](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[AddressesId] [int] NOT NULL,
CONSTRAINT [PK_People] 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]
GO
ALTER TABLE [dbo].[People] WITH CHECK ADD CONSTRAINT [FK_People_Addresses] FOREIGN KEY([AddressesId]) REFERENCES [dbo].[Addresses] ([Id])
GO
ALTER TABLE [dbo].[People] CHECK CONSTRAINT [FK_People_Addresses]
GO
-- Insert default data into Addresses table
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (1,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (2,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (3,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (4,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (5,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (6,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (7,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (8,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (9,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (10,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (11,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
INSERT INTO dbo.Addresses ([HouseNameOrNumber],[Street],[Area],[Town],[County],[Country],[PostCode])
VALUES (12,'Letsbe Avenue','Ploddington','Surbiton','Kent','England','KT4 3ZZ');
-- Insert default data into People table
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Martha','Stewart',1);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Sissie','Sledge',2);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Ian','Pian',3);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Sian','Pian',3);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Shawn','Escheep',4);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Cher','Escheep',4);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Tom','Bowler',5);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Morris','Dancer',6);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Ethel','Dancer',6);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Alan','Tickosheen',7);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Chris','Tofer',8);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Ellie','Fant',9);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('John','Thomas',10);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Gloria','Esrere',11);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Duncan','Esrere',11);
INSERT INTO dbo.People ([FirstName],[LastName],[AddressesId]) VALUES ('Phil','Eetupp',12);
Code language: SQL (Structured Query Language) (sql)Now we’ve got a database with some data in it, let’s create a program to access it. Open Visual Studio and create a new Console Application.
Add a reference to System.Configuration to your code project. Right click on the code project node in Solution Explorer, then choose Add, Reference... from the context menu.
Add an App.Config file to the code project. Open the file and edit so it contains the connection string for your local MS SQL Express database. For example, it may be something like this if you used the default naming convention when install the database server:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="DotNetTutorials" connectionString="Server=localhost\SQLEXPRESS;Database=DotNetTutorials;Trusted_Connection=yes;" />
</connectionStrings>
</configuration>Code language: HTML, XML (xml)In the Program class, replace the Main method with the following:
static void Main(string[] args)
{
string connectionString =
ConfigurationManager.ConnectionStrings["DotNetTutorials"].ConnectionString;
List<int> validIds = new List<int>();
int id;
string input = "";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [Id],[FirstName],[LastName] FROM People";
using (SqlDataReader reader = command.ExecuteReader())
{
Console.Write("Id".PadRight(5));
Console.WriteLine("Name");
while (reader.Read())
{
id = reader.GetInt32(0);
validIds.Add(id);
Console.Write(id.ToString().PadRight(5));
Console.WriteLine("{0} {1}",
reader.GetString(1),
reader.GetString(2));
}
}
}
}
finally
{
connection.Close();
}
}
while (true)
{
Console.WriteLine();
Console.WriteLine("Enter a row ID to view the person's address.");
Console.WriteLine("(Or X to exit the application.)");
input = Console.ReadLine();
if (input != null && input.Trim().ToUpper() == "X")
{
break;
}
if (!int.TryParse(input, out id) || !validIds.Contains(id))
{
Console.WriteLine(
"ERROR: the supplied value was invalid. Please try again.");
Console.WriteLine();
continue;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText =
"SELECT [HouseNameOrNumber],[Street],[Area],[Town]," +
"[County],[Country],[PostCode] " +
"FROM Addresses WHERE [Id] = " + id.ToString();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
string houseNameOrNum = reader.GetString(0);
string street = reader.GetString(1);
string area = reader.GetString(2);
string city = reader.GetString(3);
string country = reader.GetString(4);
string postCode = reader.GetString(5);
string address =
houseNameOrNum +
(string.IsNullOrEmpty(houseNameOrNum) ? "" : ", ") +
street + (string.IsNullOrEmpty(street) ? "" : ", ") +
area + (string.IsNullOrEmpty(area) ? "" : ", ") +
city + (string.IsNullOrEmpty(city) ? "" : ", ") +
country + (string.IsNullOrEmpty(country) ? "" : ", ") +
postCode;
Console.WriteLine("Address: " + address);
Console.WriteLine();
}
}
}
finally
{
connection.Close();
}
}
}
}Code language: C# (cs)The above code should read and display all the records from the ‘People’ table in the ‘DotNetTutorials’ database in a tabular style. It should then prompt you to select a row by ID. If you enter a valid ID then it will display that person’s address. If you enter an invalid value or an out of range ID then an error message will be displayed instead.
Let’s look at the code in a little more detail.
- A
SqlConnectionobject is instantiated, configured with a connection string, and then opened. Quite a few developers new to ADO.NET miss theOpen()method call thinking the constructor would do that too (create and open a connection) – but it doesn’t; you have to callOpen()explicitly. - Once the connection is ready to use, a
SqlCommandobject can be instantiated so that a query can be executed against the database server. - Since we only want to read some data in this case, we can use the command object to create a new
SqlDataReaderobject and read from the database using that. - The
SqlDataReaderclass allows you to query individual cells on the row currently being pointed to usingGetInt32(),GetString(), etc. and specifying the 0-based index of the column. - We may have just introduced a new keyword you haven’t seen before too:
using.
For types that implement IDisposable, encapsulating the instantiation and use of the object in a using code block automatically handles disposing of the object when execution leaves that using code block’s scope. This can be beneficial in that it means you don’t have to worry about disposing of the objects yourself as it will always be done automatically if you forget, but it comes at a small cost: any software exceptions that occur during clean-up / disposal will be suppressed.
Another problem with the using statement in this scenario is that both Close() and Dispose() are called on the connection. If your code reads some data, does some other stuff, and then needs to read from the database again it might be more efficient to hold the connection object for longer and just close and open it as needed. You can’t do this with a using block unless it wraps everything else – and it could lead to unnecessary indentation and nesting in your code if you did that.
Instead of using the above technique with a using directive, you could use a try-finally code block like this (if being able to detect and handle clean-up exceptions is important to you):
SqlConnection connection = null;
SqlDataReader reader = null;
SqlCommand command = null;
try
{
connection = new SqlConnection(connectionString);
command = connection.CreateCommand();
command.CommandText = "SELECT [Id],[FirstName],[LastName] FROM People";
reader = command.ExecuteReader();
// etc.
}
finally
{
if (reader != null) reader.Dispose();
if (command != null) command.Dispose();
if (connection != null)
{
if (connection.State != ConnectionState.Closed) connection.Close();
connection.Dispose();
}
}Code language: C# (cs)As you can see, you save a lot of code with the using statement.
Using DbDataAdapter
Where DataReader can only be used to read data from the database, DbDataAdapter can be used to create, read, update, and delete data (i.e. it supports the full CRUD suite of database interactions).
Let’s look at how we can read data using the SqlDataAdapter instead of the SqlDataReader. We’ll re-do the same program as above but this time read the data with SqlDataAdapter instead (a specialised class of DbDataAdapter targeting Microsoft SQL Server).
static void Main(string[] args)
{
string connectionString =
ConfigurationManager.ConnectionStrings["DotNetTutorials"].ConnectionString;
List<int> validIds = new List<int>();
int id;
string input = "";
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [Id],[FirstName],[LastName] FROM People";
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
// Need to declare a DataTable to load the database data into.
// (The table name is optional but improves readability.)
// Where SqlDataReader returns a row at a time, SqlDataAdapter
// loads the entire set of results (from the query).
DataTable table = new DataTable("People");
adapter.Fill(table);
Console.Write("Id".PadRight(5));
Console.WriteLine("Name");
foreach (DataRow row in table.Rows)
{
Console.Write(row["Id"].ToString().PadRight(5));
Console.WriteLine("{0} {1}", row[1], row[2]);
}
}
}
}
finally
{
connection.Close();
}
}
while (true)
{
Console.WriteLine();
Console.WriteLine("Enter a row ID to view the person's address.");
Console.WriteLine("(Or X to exit the application.)");
input = Console.ReadLine();
if (input != null && input.Trim().ToUpper() == "X")
{
break;
}
if (!int.TryParse(input, out id) || !validIds.Contains(id))
{
Console.WriteLine(
"ERROR: the supplied value was invalid. Please try again.");
Console.WriteLine();
continue;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText =
"SELECT [HouseNameOrNumber],[Street],[Area],[Town]," +
"[County],[Country],[PostCode] " +
"FROM Addresses WHERE [Id] = " + id.ToString();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataSet data = new DataSet("DotNetTutorials");
adapter.Fill(data);
DataTable addresses = data.Tables["People"];
string houseNameOrNum = addresses.Rows[0][0].ToString();
string street = addresses.Rows[0][1].ToString();
string area = addresses.Rows[0][2].ToString();
string city = addresses.Rows[0][3].ToString();
string country = addresses.Rows[0][4].ToString();
string postCode = addresses.Rows[0][5].ToString();
string address =
houseNameOrNum +
(string.IsNullOrEmpty(houseNameOrNum) ? "" : ", ") +
street + (string.IsNullOrEmpty(street) ? "" : ", ") +
area + (string.IsNullOrEmpty(area) ? "" : ", ") +
city + (string.IsNullOrEmpty(city) ? "" : ", ") +
country + (string.IsNullOrEmpty(country) ? "" : ", ") +
postCode;
Console.WriteLine("Address: " + address);
Console.WriteLine();
}
}
}
finally
{
connection.Close();
}
}
}
}Code language: C# (cs)Notice how things have changed slightly.
We’re now instantiating instances of SqlDataAdapter and passing the command variable into it (instead of using the variable to create a reader). The adapter has to put the response from the database query somewhere, so a DataTable needs to be instantiated and passed to the adapter’s Fill() method.
Also, we aren’t buffering the data a row at a time any more… it gets loaded into the DataTable object immediately. We can then process the rows of the table using DataTable‘s Rows property, which we can treat like a normal 2-D array where data types should match the database schema (albeit translated into CLS-compliant types).
You may have noticed we’ve demonstrated accessing cell data at row level two different ways, using either the column index or the column name. This offers more flexibility than SqlDataReader, which requires you to know the column indexes. Knowing the column indexes is easy if you query explicitly defines them like we did above, but what if you used a wildcard (e.g. SELECT * FROM People)? Using SqlDataReader would require you to know the full table specifications in that case.
In the first query (retrieving the contents of the ‘People’ table), we declare a DataTable object to receive the database results. In the second query we use a DataSet object and then access the ‘Addresses’ table from within that via the Tables property.
Both approaches would work fine in our example, but an advantage of the 2nd approach is that you could do something like this:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
DataSet data = new DataSet("DotNetTutorials");
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM People";
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(data);
command.CommandText = "SELECT * FROM Addresses";
adapter.Fill(data);
}
}
}
finally
{
connection.Close();
}
}Code language: C# (cs)At the end of execution of this code, the data variable would hold 2 tables of data (‘People’ and ‘Addresses’), populated with the results of the queries. This is where you’d usually use a DataSet instead of a DataTable.
Now let’s look at how we can manipulate database data using DbDataAdapter. For example, let’s say we wanted to add someone new to the ‘People’ table. We could try the following:
DataSet data = new DataSet("DotNetTutorials");
// Existing code to fill the dataset omitted for brevity.
// using (SqlDataAdapter adapter = new SqlDataAdapter(command))
// etc.
data.Tables["Addresses"].Rows.Add(
"Merrymount Villa",
"Clownton Ave",
"",
"Circustown",
"Avon",
"England",
"CC12 5FT");
// Need to update the database immediately, to get the new address ID.
adapter.Update(data);
int addressId = (int)
data.Tables["Addresses"].Rows[data.Tables["Addresses"].Rows.Count - 1]["Id"];
Data.Tables["People"].Rows.Add(
"Jerry",
"Berry",
addressId);
adapter.Update(data);Code language: C# (cs)We could achieve the above using SQL commands directly too. For example:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = string.Format(
"INSERT INTO " +
"({0},{1},{2},{3},{4},{5},{6})" +
" VALUES " +
"(@House,@Street,@Area,@Town,@County,@Country,@PostCode); " +
"SELECT @@IDENTITY;",
"[HouseNameOrNumber]",
"[Street]",
"[Area]",
"[Town]",
"[County]",
"[Country]",
"[PostCode]");
command.Parameters.AddWithValue("@House", "Merrymount Villa");
command.Parameters.AddWithValue("@Street","Clownton Ave");
command.Parameters.AddWithValue("@Area","");
command.Parameters.AddWithValue("@Town","Circustown");
command.Parameters.AddWithValue("@County","Avon");
command.Parameters.AddWithValue("@Country","England");
command.Parameters.AddWithValue("@PostCode","CC12 5FT");
int newAddressId = command.ExecuteScalar();
}
}
finally
{
connection.Close();
}
}Code language: C# (cs)The above code uses a concept we haven’t introduced yet: SQL Parameters. We’ll cover them later in the tutorial so just ignore them for now. Also, notice we’ve actually got 2 SQL statements in our CommandText property: the INSERT statement and a SELECT @@IDENTITY one. The latter just returns the most recent identity field value that was generated by SQL Server; so, in this case we insert a row and then query the ID for it straight away.
Existing rows can be updated or deleted using DbDataAdapter too. So let’s look at an example of doing just that:
DataSet data = new DataSet("DotNetTutorials");
// Existing code to fill the dataset omitted for brevity.
// using (SqlDataAdapter adapter = new SqlDataAdapter(command))
// etc.
// Let's modify a row first...
DataRow philsRow = null;
foreach (DataRow row in data.Tables["People"].Rows)
{
if (row["FirstName"].ToString() == "Phil" &&
row["LastName"].ToString() == "Eetupp")
{
philsRow = row;
break;
}
}
philsRow["LastName"] = "Itin";
adapter.Update(data);
// Now let's delete the row instead...
data.Tables["People"].Rows.Remove(philsRow);
adapter.Update(data);Code language: C# (cs)Using Database Transactions
Up until now, all the code we’ve written adopted implicit transactions on our database interaction. That is to say the runtime handled the transactions for us.
What transactions do is give developers more control over what changes are made to the database and how errors are processed. For example, consider our people and addresses case. We need to write an address first, get the ID for the row, and then use that to write the person’s details (as the People table requires a valid address ID).
Above we did this using two separate transactions (add address + save to the database, and then add person + save), but what if the first transaction worked and the second did not?
In this case, the database would contain an orphaned address record. If we attempted to add the person again (and the whole process was repeated) then the address would be added again as a new record and, assuming it worked this time, the person would be linked to the newest address record.
The problem is we’d now have two identical addresses with different IDs. If this happened regularly (e.g. if the network was unstable or the database server was regularly unavailable due to stability issues) then we’d need some way to clean up the Addresses table, or the dataset could sprawl with unnecessary, orphaned, data rows.
To protect against this happening we could wrap our calls into a transaction. That way if one of the interactions failed we could rollback the whole thing. For example:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
using (SqlCommand command = connection.CreateCommand())
{
DataSet data = new DataSet("DotNetTutorials");
command.CommandText = "SELECT * FROM People";
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(data);
command.CommandText = "SELECT * FROM Addresses";
adapter.Fill(data);
// Code to add a new address and person, linked to that address
// goes here, but we'll omit it for brevity since it has already
// been covered above.
}
}
// Everything went fine so commit the transaction and persist the changes
// to the database.
transaction.Commit();
}
catch (Exception)
{
// Something went wrong so rollback all the changes we just made.
transaction.Rollback();
}
}
}Code language: C# (cs)DbTransaction also supports a Save() method. This, known as a ‘save-point’, snapshots a position in the transaction that can be rolled back to, instead of having to undo everything when something goes wrong. The Rollback() method includes an overload that accepts the ‘save-point’ name to achieve this.
SQL Parameters
SQL parameters are not new, and have been around for a long time. They are most commonly associated with database stored procedures that encapsulate SQL in a procedural function that accepts 0 or more parameters, executes some functionality, and can return data if appropriate too.
When working with SQL statements, you need to be alert to the possibility of malicious intent, such as SQL injection attacks. Often, data is collected via fields in a bespoked app or website that was developed and then used to query a database.
A SQL injection attack works by carefully crafting the data entered into UIs (e.g. form fields) to achieve different behaviours to normal.
For example, let’s say we have an app with a search bar that can be used to search for people. A user enters a search term and hits the Search button. Our app builds a SQL query using the search term and sends the request to its database. A response is returned which is copied directly into a table and displayed to the user. Because ADO.NET is self-describing (from a tables and columns perspective), this is entirely possible and a reasonable thing to do.
The C# code for the SQL statement used to search for matching people in a back-end database table might look like this:
command.CommandText =
"SELECT * FROM People WHERE [FirstName] LIKE '%" + searchTerm + "%'";Code language: SQL (Structured Query Language) (sql)The code may appear innocuous enough, and doing what it is supposed to, but what if this was input into the search box instead of a name?
a'; SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' --Code language: plaintext (plaintext)The above text entered into the search box would change the SQL query to:
SELECT * FROM People WHERE [FirstName] LIKE '%a';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' --%'Code language: SQL (Structured Query Language) (sql)What’s happening here? Well, the start of the text would terminate the original select statement and then add a new select statement that would query all the tables in the database. The final part (--) would comment out the remainder of the original SQL query so the request could be serviced.
If our app just copied the returned data to a data viewer then it could output the data from both queries. Now the user has a list of all the tables in your database and can execute similar queries to hijack your data!
What if the malicious actor entered something like this instead:
a'; waitfor delay '0:1:0'--Code language: SQL (Structured Query Language) (sql)The above would execute the query and then wait a minute before returning the result. Depending on the row and table locking mechanism being used, this could lock rows or the whole table from being accessed until the query completed making the app slow and unresponsive for other users.
We can protect against SQL injection attacks by adding copius validation of any and all data read from a UI, but there is a much simpler way, and that’s using the DbParameter class and its derivatives. For example, we could re-write our search for people SQL query liks so:
command.CommandText = "SELECT * FROM People WHERE [FirstName] LIKE @SearchTerm";
command.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%");Code language: C# (cs)How does this protect us from SQL injection attacks? Well, SQL parameters are used later in the execution lifecycle than plain SQL. Because of this, the query being performed has already been interpreted (from the SQL) by the time the parameter is accessed, so instead of the database misunderstanding the SQL it simply uses the parameter data on the query it has already correctly interpreted.
SQL Server Management Objects (SMO)
A really annoying ‘feature’ of ADO.NET is its complete inability to allow you to programmatically create a database on a server instance. Previous data access architectures (DAO, ADO (ActiveX), etc.) all provided a means to do this but a decision was made to omit this functionality from the ADO.NET architecture as implementation across all data providers could not be guaranteed.
In most formal relational database interactions your software undertakes it is unlikely that you’ll have to programmatically create or drop a database, and in fact your database administrators may not allow you such control in a commercial environment.
There may be occasions where you do need to do this though, and fortunately, for Microsoft SQL Server at least, an alternative solution is now available. All new versions of MS SQL Server install with a set of .NET assemblies that enable full programmatic management of MS SQL Server. We’ve already talked about SQL Server Management Studio (SSMS) so you’ll have used those libraries in that product (since that’s what it uses behind the scenes in the Server/Database Explorer tool).
We’ll only cover enough to create a new database in this tutorial, as SMO’s capabilities are vast. Here is an example of how to connect to a MS SQL Server instance and create a new database:
Server myServer = new Server(@"localhost\SQLEXPRESS");
Database myDatabase = new Database(myServer, "Hobbies");
myDatabase.Create();
// OR... if your account doesn't run with enough permissions and you need to use a
// local SQL account instead...
ServerConnection connectionDetails =
new ServerConnection(@"localhost\SQLEXPRESS", "adminis", "abc123$");
Server myServer = new Server(connectionDetails);
Database myDatabase = new Database(myServer, "Hobbies");
myDatabase.Create();
Code language: C# (cs)If we’re creating a database programmatically, especially if we need to use a SQL account when doing it, then we’ll probably need to configure the database permissions at the same time. For example:
// Create a new server login if it doesn’t exist
// (setting 'Hobbies' as default database)
const string username = “hobbiesAdmin”;
const string password = “abc123$”;
Login hobbiesLogin = myServer.Logins[username];
if (hobbiesLogin == null)
{
hobbiesLogin = new Login(myServer, username);
hobbiesLogin.DefaultDatabase = "Hobbies";
hobbiesLogin.LoginType = LoginType.SqlLogin;
hobbiesLogin.Create(password);
}
// Create the user mapping to the 'Hobbies' database if it doesn’t exist.
User hobbiesUser = myDatabase.Users[username];
if (hobbiesUser == null)
{
hobbiesUser = new User(myDatabase, username);
hobbiesUser.Login = username;
hobbiesUser.Create();
}
// Make sure 'hobbiesAdmin' has admin access to the 'Hobbies' database.
// NOTE: instead of 'db_owner' you could set 'db_datareader', 'db_datawriter', etc.
// for more granular permissions if you aren't setting up an admin user.
const string ownerPermission = "db_owner";
if (!hobbiesUser.IsMember(ownerPermission))
{
hobbiesUser.AddToRole(ownerPermission);
hobbiesUser.Alter();
}Code language: C# (cs)WARNING: the SMO libraries are installed to the Global Assembly Cache (GAC) on the local computer. This means that when you deploy an application that relies on SMO libraries you need to make sure they are going to be available on the machine you’re deploying to. To make matters worse, the library versions used by the Express Edition of SQL Server and those used by the full product are different. That means if you develop your software on a machine using an installation of SQL Server Express Edition (and SSMS Express Edition) and then deploy to a server with the full product version, your software may not work.
A work-around for this problem can be achieved by retrieving the specific assemblies from the GAC on the development machine and then distributing them with your application (as the .NET Runtime’s assembly search routine checks the local application directory before trying to load assemblies from the GAC.) If you adopt this approach, make sure you are licensed to distribute the libraries you are using.
Data Factories
The Factory design pattern works on the principle that you define a contract for some service or behaviour and then implementations of that service or behaviour all have to conform to that contract.
The different implementations are considered interchangable, and the ‘factory’ serves out the instances of them when requested.
The ADO.NET architecture provides an implementation of the Factory design pattern. To see it in action, consider the following:
// Run a query against MS SQL Server.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = sqlServerConnectionString;
SqlDataAdapter sqlReader = new SqlDataAdapter();
sqlReader.SelectCommand = new SqlCommand();
sqlReader.SelectCommand.Connection = sqlConnection;
sqlReader.SelectCommand.CommandText = "SELECT * FROM MyTable";
DataTable sqlTable = new DataTable();
sqlReader.Fill(sqlTable);
// Run a query against an OLE database.
OleDbConnection oleConnection = new OleDbConnection();
oleConnection.ConnectionString = oleConnectionString;
OleDbDataAdapter oleReader = new OleDbDataAdapter();
oleReader.SelectCommand = new OleDbCommand();
oleReader.SelectCommand.Connection = oleConnection;
oleReader.SelectCommand.CommandText = "SELECT * FROM MyTable";
DataTable oleTable = new DataTable();
oleReader.Fill(oleTable);Code language: C# (cs)As you can see the code looks almost identical save for the provider specific derivative class names.
We can implement the same code using data factories like so:
// Use the static GetFactory() method to retrieve a factory of the required type
string providerName = "System.Data.SqlClient";
DbProviderFactory myFactory = DbProviderFactories.GetFactory(providerName);
// Create a new connection.
DbConnection myConnection = myFactory.CreateConnection();
myConnection.ConnectionString = myConnectionString;
// Create a data adapter to read from the database.
DbDataAdapter reader = myFactory.CreateDataAdapter();
reader.SelectCommand = myFactory.CreateCommand();
reader.SelectCommand.Connection = myConnection;
reader.SelectCommand.CommandText = "SELECT * FROM MyTable";
DataTable myTable = new DataTable();
reader.Fill(myTable);Code language: JavaScript (javascript)The System.Data.Common.DbProviderFactories class decides which provider specific data factory to return based on the provider name details that are supplied at runtime. The remaining code is then written generically, not catering for specific provider classes.
If the provider name is specified in a configuration file (e.g. App.Config), then it can be changed at any time and the app would pick up the new data provider being used automatically. .NET configuration’s connection strings sub-section includes a providerName attribute that can be added, like so:
?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="DoNetTutorials"
connectionString="Server=localhost\SQLEXPRESS;Database=DoNetTutorials;Trusted_Connection=yes;"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>Code language: HTML, XML (xml)The data factories based implementation differs slightly from the provider-specific ones in that all interaction objects (the DbConnection object, the DbDataReader object, etc.) are initialised through a DbProviderFactory object instance.
This might seem like an ideal solution to provider specific interaction problems but beware as the reality is a little more clouded.
Firstly, Data Factories don’t cater for any provider-specific SQL syntax so care must be taken to only write ‘generic’ queries (ones that adopt ANSI SQL). Microsoft, Oracle, SyBase, etc. have all extended or diverted from the standard ANSI SQL command-set in different ways.
Secondly, for any provider specific data factory implementation that doesn’t ship with the .NET Framework Runtime Engine, you must register the provider’s class types in the machine configuration file (machine.config) on any computer that will be using it. If you don’t, the provider lookup will fail.