Последнее обновление: 03.03.2021
-
Глава 1. MS SQL Server в .NET 6
-
Строка подключения для MS SQL Server
-
Подключение к MS SQL Server
-
Пул подключений
-
Выполнение команд и SqlCommand
-
Чтение результатов запроса и SqlDataReader
-
Типизация результатов SqlDataReader. Сопоставление типов C# и SQL
-
Получение скалярных значений
-
Параметризация запросов
-
Выходные параметры запросов
-
Добавление и выполнение хранимых процедур
-
Выходные параметры хранимых процедур
-
Транзакции
-
Сохранение и извлечение файлов из базы данных
-
-
Глава 2. DataSet
-
SqlDataAdapter и загрузка данных в DataSet
-
Работа с DataSet без базы данных
-
Сохранение изменений DataSet в базе данных
-
-
Глава 3. SQLite в C# и .NET
-
Подключение к базе данных SQLite
-
Выполнение запросов к БД SQLite и SqliteCommand
-
Чтение результатов запроса и SqliteDataReader
-
Сопоставление типов C# и SQLite. Типизация SqliteDataReader
-
Параметризация запросов к БД Sqlite
-
Получение скалярных значений в SQLite
-
Сохранение и извлечение файлов из базы данных SQLite
-
- Глава 1. MS SQL Server в .NET 5
- Строка подключения для MS SQL Server
- Подключение к MS SQL Server
- Пул подключений
- Выполнение команд и SqlCommand
- Чтение результатов запроса и SqlDataReader
- Типизация результатов SqlDataReader. Сопоставление типов C# и SQL
- Получение скалярных значений
- Параметризация запросов
- Выходные параметры запросов
- Добавление и выполнение хранимых процедур
- Выходные параметры хранимых процедур
- Транзакции
- Сохранение и извлечение файлов из базы данных
- Глава 2. DataSet
- SqlDataAdapter и загрузка данных в DataSet
- Работа с DataSet без базы данных
- Сохранение изменений DataSet в базе данных
- Глава 3. SQLite в C# и .NET
- Подключение к базе данных SQLite
- Выполнение запросов к БД SQLite и SqliteCommand
- Чтение результатов запроса и SqliteDataReader
- Сопоставление типов C# и SQLite. Типизация SqliteDataReader
- Параметризация запросов к БД Sqlite
- Получение скалярных значений в SQLite
- Сохранение и извлечение файлов из базы данных SQLite
Помощь сайту
YooMoney:
410011174743222
Перевод на карту
Номер карты:
4048415020898850
Введение
ADO.NET – это набор классов (фреймворк) для работы с базами данных, а также XML файлами. Аббревиатура ADO расшифровывается как ActiveX Data Objects. Данная технология имеет методы и классы для извлечения и обработки данных.
Список .NET приложений, которые используют возможности ADO.NET для различных действий с БД:
-
ASP.NET Web Applications
-
Console Applications
-
Windows Applications.
Структуры подсоединения к БД
Можно определить два типа архитектуры подключения:
- Архитектура, подключенная к базе: подсоединена к БД на протяжении всего рабочего времени.
- Архитектура, не подсоединённая к БД: приложение, автоматически подключается/отключается в процессе работы. Приложения на такой архитектуре используют временные данные, хранящиеся на стороне клиента (DataSet).
ADO.NET и его библиотеки классов
На данной диаграмме видны различные типы приложений (Веб приложения, консольные приложения, приложения для Windows и так далее), использующие ADO.NET для подсоединения к БД (SQL Server, Oracle, OleDb, ODBC, XML-файлы и так далее).
Классы в ADO.NET
Также на предыдущем рисунке мы видим различные классы, а именно:
- Connection Class
- Command Class
- DataReader Class
- DataAdaptor Class
- DataSet.Class
1. Connection Class
Данные классы применяются в ADO.NET для подсоединения к БД.
2. Command Class
Данный класс обеспечивает хранение и выполнение SQL команд. Ниже приведены различные команды, выполняющиеся с помощью данного класса.
- ExecuteReader: Возвращает данные к клиенту в виде строк.
- ExecuteNonQuery: Выполняет команду, изменяющую данные в базе данных.
- ExecuteScalar: Данный класс возвращает только одно значение.
- ExecuteXMLReader: (Только для классов SqlClient) Получает данные из базы данных SQL Server 2000 с помощью XML-потока.
3. DataReader Class
DataReader используется для получения данных. Он используется в сочетании с Command Class для выполнения SQL-запроса.
5. DataSet Class
Класс DataSet – сердце ADO.NET, представляющее из себя набор объектов DataTable. Каждый такой объект содержит много объектов DataColumn и DataRow.
Подключение ADO.NET к базе данных
Для настройки подключения Вы должны быть знакомы со строками подключения (connection strings). ConnectionString – строка переменной (регистр не учитывается). Строки подключения нужны нам для параметра SQLConnection. Данные примеры содержат основные значения, а именно: provider, server, database, userid и password.
SQL Аутентификация
String constr=«server=.;database=institute;user id=rakesh;password=abc@123»;
Или:
String constr=«data source=.;initial catalog=institute;uid=rakesh;pwd=abc@213»;
Windows Аутентификация (Windows Authentication)
String constr=«server=.;database=institute;trusted_connection=true»
Или:
String constr=«server=.;initial catalog=institute;integrated security=true»
Видео курсы по схожей тематике:
Получение и отображение данных из базы данных
Получить и отобразить данные можно по такой схеме:
- Создайте объект SqlConnection, используя строку подключения.
- Откройте соединение.
- Создайте SQLCommand. Укажите тип SQLCommand.
- Выполните команду (используйте executereader).
- Получить результат (используйте SqlDataReader).
- Закройте соединение.
- Получите результат.
Ниже приведен код для подсоединения к SQL:
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;
using System.Data.Odbc;
namespace AdoDemo
{
public partial class WebForml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(«data source=.; database=Sample; integrated security=SSPI»);
SqlCommand cmd = new SqlCommand(«Select * from tblProduct», con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();
}
}
}
Вы должны использовать System.Data.SqlClient для подключения к SQL. В предыдущем коде мы использовали классы SqlConnection, SqlCommand и SqlDataReader, потому что наше приложение обращалось к SQL Server, а он понимает только SQL.
Бесплатные вебинары по схожей тематике:
Подключение к базе данных Oracle
При подключении к БД Oracle Вам нужно изменить имя некоторых классов, а именно SqlConnection на OracleConnection, SqlCommand на OracleCommand и SqlDataReader на OracleDataReader. Также вначале используйте System.Data.OracleClient.
Источник: http://www.c-sharpcorner.com/UploadFile/18fc30/understanding-the-basics-of-ado-net/
Coding Bootcamp: Consuming data in C# with ADO.NET
or how to access data the old way
ADO.NET
ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file.
Data Providers
ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source. These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with.
SQL Data Provider
Since, we are interested in the SQL Server, we will use the .NET Framework Data Provider for SQL Server which resides in the System.Data.SqlCient
namespace.
using System.Data.SqlClient;
Overview
Before jumping into the code, we will have to understand some of the important objects of ADO.NET. In a typical scenario requiring data access, we need to perform four major tasks:
- Connecting to the database
- Passing the request to the database, i.e., a command like select, insert, or update.
- Getting back the results, i.e., rows and/or the number of rows effected.
- Storing the result and displaying it to the user.
Overview
This can be visualized as:
SqlConnection
The SqlConnection
class is used to establish a connection to the database. The SqlConnection
uses a ConnectionString
to identify the database server location, authentication parameters, and other information to connect to the database.
http://www.connectionstrings.com/ is the website where you can easily find the connection string for your database. They provide the strings, for almost all of the database services and their types.
Example ConnectionString — 1/3
"Data Source=(LocalDB)v11.0;AttachDbFileName=|DataDirectory|DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"
DataSource
: For SQL Server Express, LocalDB, SQL Server, and SQL Database, this setting specifies the name of the server and the SQL Server instance on the server. For example, you can specify ServerNameInstancename. You can use «.», «(local)», or «localhost» in place of the server name to specify the local computer, and you can use an IP address instead of the server name.AttachDbFileName
specifies the path and name of the database file for SQL Server Express or LocalDB databases that are not defined in the local SQL Server Express instance.
Example ConnectionString — 2/3
"Data Source=(LocalDB)v11.0;AttachDbFileName=|DataDirectory|DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"
InitialCatalog
specifies the name of the database in the SQL Server instance catalog. If omitted, ADO.NET connects to the default database for the SQL Server instance.Integrated Security
specifies whether the connection should use the user ID and password in the connection string to log on to the SQL Server instance (=false), or the current Windows account credentials should be used for authentication (=true).
Example ConnectionString — 3/3
"Data Source=(LocalDB)v11.0;AttachDbFileName=|DataDirectory|DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True"
- The
MultipleActiveResultSets
(MARS) option makes it possible to execute multiple queries simultaneously. This is a common scenario when you use the Entity Framework.
SqlConnection Lifecycle
The purpose of creating a SqlConnection
object is so you can enable other ADO.NET code to work with a database. Other ADO.NET objects, such as a SqlCommand
and a SqlDataAdapter
take a connection object as a parameter. The sequence of operations occurring in the lifetime of a SqlConnection
are as follows:
- Instantiate
- Open the connection
- Pass the connection to other ADO.NET objects
- Perform database operations with the other ADO.NET objects
- Close the connection
Demo Code
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
SqlDataReader rdr = null;
try {
// 2. Open the connection
conn.Open();
// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
// 4. Use the connection to get query results
rdr = cmd.ExecuteReader();
} finally {
// close the reader
if (rdr != null)
rdr.Close();
// 5. Close the connection
if (conn != null)
conn.Close();
}
SqlCommand
A SqlCommand
object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table.
SqlCommand
Similar to other C# objects, you instantiate a SqlCommand
object via the new instance declaration, as follows:
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
Quering data
When using a SQL SELECT
command, you retrieve a data set for viewing. To accomplish this with a SqlCommand
object, you would use the ExecuteReader
method, which returns a SqlDataReader
object. The example below shows how to use the SqlCommand
object to obtain a SqlDataReader
object:
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
Getting single values
Sometimes all we need from a database is a single value, which could be a count, sum, average, or other aggregated value from a data set. Performing an ExecuteReader
and calculating the result in the code is not the most efficient way to do this. The best choice is to let the database perform the work and return just the single value we need. The following example shows how to do this with the ExecuteScalar
method:
«`cs
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand(«select count(*) from Categories», conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
«`
Inserting data
To insert data into a database, use the ExecuteNonQuery
method of the SqlCommand
object. The following code shows how to insert data into a database table:
// prepare command string
string insertString = "insert into Categories (CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Updating data
The ExecuteNonQuery method is also used for updating data. The following code shows how to update data:
«`cs
// prepare command string
string updateString = @»
update Categories
set CategoryName = ‘Other’
where CategoryName = ‘Miscellaneous'»;
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
«`
Deleting data
You can also delete data using the ExecuteNonQuery
method. The following example shows how to delete a record from a database:
// prepare command string
string deleteString = @"
delete from Categories
where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Parameterizing the query
Parameterizing the query is done by using the SqlParameter
passed into the command. For example, you might want to search for the records where a criteria matches. You can denote that criteria, by passing the variable name into the query and then adding the value to it using the SqlParameter
object.
// Create the command
SqlCommand insertCommand = new SqlCommand("INSERT INTO TableName
(FirstColumn, SecondColumn, ThirdColumn, ForthColumn)
VALUES (@0, @SecondParameter, @aDate, @3)", conn);
// Add the parameters.
insertCommand.Parameters.Add(new SqlParameter("0", 10));
insertCommand.Parameters.Add(new SqlParameter("SecondParameter", "Test Column"));
insertCommand.Parameters.Add(new SqlParameter("aDate", DateTime.Now));
insertCommand.Parameters.Add(new SqlParameter("3", false));
using using
In C# there are some objects which use the resources of the system. Which need to be removed, closed, flushed and disposed etc. In C# you can either write the code to Create a new instance to the resource, use it, close it, flush it, dispose it. Or on the other hand you can simply just use the using
statement block in which the object created is closed, flushed and disposed and the resources are then allowed to be used again by other processes.
using
example
SqlConnection conn = new SqlConnection("connection string");
conn.Open();
// use the connection here
conn.Close();
conn.Dipose();
// connections don't get flushed
becomes
using (SqlConnection conn = new SqlConnection("connection string"))
{
conn.Open();
// use the connection here
}
Reading Data with the SqlDataReader
SqlDataReader
is a type that is good for reading data in the most efficient manner possible. You can not use it for writing data. SqlDataReader
s are often described as fast-forward firehose-like streams of data.
You can read from SqlDataReader
objects in a forward-only sequential manner. Once you’ve read some data, you must save it because you will not be able to go back and read it again.
Creating a SqlDataReader
Object
Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader
on a SqlCommand
object, like this:
SqlDataReader rdr = cmd.ExecuteReader();
The ExecuteReader
method of the SqlCommand
object returns a SqlDataReader
instance. Creating a SqlDataReader
with the new operator doesn’t do anything for you.
Reading data
The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with a while loop. The following code shows how to accomplish this:
while (rdr.Read())
{
// get the results of each column
string contact = (string) rdr["ContactName"];
string company = (string) rdr["CompanyName"];
string city = (string) rdr["City"];
// print out the results
Console.Write("{0,-25}", contact);
Console.Write("{0,-20}", city);
Console.Write("{0,-25}", company);
Console.WriteLine();
}
Closing SqlDataReader
Always remember to close and dispose your SqlDataReader
, just like you need to close the SqlConnection
. In fact, SqlCommand
also requires disposing (there is no Close method). In order to be safe, it is recommended to use the using
statement to let the Garbage Collector handle all three objects.
Recommended pattern
using(SqlConnection connection = new SqlConnection("connection string"))
{
connection.Open();
using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
//do something
}
}
} // reader closed and disposed up here
} // command disposed here
} //connection closed and disposed here
Working with Disconnected Data
A DataSet
is an in-memory data store that can hold numerous tables. DataSets
only hold data and do not interact with a data source. It is the SqlDataAdapter
that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter
opens a connection only when required and closes it as soon as it has performed its task.
Creating a DataSet
Object
There isn’t anything special about instantiating a DataSet
. You just create a new instance, just like any other object:
DataSet dsCustomers = new DataSet();
Right now, the DataSet
is empty and you need a SqlDataAdapter
to load it.
Creating A SqlDataAdapter
The SqlDataAdapter
holds the SQL commands and connection object for reading and writing data. You initialize it with a SQL select statement and connection object:
SqlDataAdapter daCustomers = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn);
The code above creates a new SqlDataAdapter
, daCustomers. The SQL select statement specifies what data will be read into a DataSet
. The connection object, conn, should have already been instantiated, but not opened. It is the SqlDataAdapter
’s responsibility to open and close the connection.
INSERT, UPDATE, DELETE
The code showed how to specify the select statement, but didn’t show the INSERT
, UPDATE
, and DELETE
statements. These are added to the SqlDataAdapter
after it is instantiated.
There are two ways to add INSERT
, UPDATE
, and DELETE
commands: manually via SqlDataAdapter
properties or with a SqlCommandBuilder
.
SqlCommandBuilder - 1/2
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
Notice in the code above that the SqlCommandBuilder
is instantiated with a single constructor parameter of the SqlDataAdapter
instance. The SqlCommandBuilder
will read the SQL SELECT statement (specified when the SqlDataAdapter
was instantiated), infer the INSERT, UPDATE, and DELETE commands, and assign the new commands to the Insert, Update, and Delete properties of the SqlDataAdapter
, respectively.
SqlCommandBuilder - 2/2
The SqlCommandBuilder
has limitations. It works when you do a simple select statement on a single table. However, when you need a join of two or more tables or must do a stored procedure, it won’t work.
Filling the DataSet
Once you have a DataSet
and SqlDataAdapter
instances, you need to fill the DataSet
. To do it we use the Fill
method of the SqlDataAdapter
:
daCustomers.Fill(dsCustomers, "Customers");
The Fill
method, in the code above, takes two parameters: a DataSet
and a table name. The DataSet
must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the DataSet
.
Updating Changes
After modifications are made to the data, you’ll want to write the changes back to the database. The following code shows how to use the Update method of the SqlDataAdapter
to push modifications back to the database.
daCustomers.Update(dsCustomers, "Customers");
The Update
method, above, is called on the SqlDataAdapter
instance that originally filled the dsCustomers DataSet
. The second parameter to the Update method specifies which table, from the DataSet
, to update.
Stored procedures
A stored procedure is a pre-defined, reusable routine that is stored in a database. SQL Server compiles stored procedures, which makes them more efficient to use. Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures.
Executing a Stored Procedure
In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures. There are two tasks require to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure. These two steps are shown below:
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
Sending Parameters to Stored Procedures
Using parameters for stored procedures is the same as using parameters for query string commands. The following code shows this:
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("CustOrderHist", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));
Catching the errors from SQL Server
SQL Server generates the errors for you to catch and work on them. In the namespace we’re working there are two classes that work with the errors and exceptions thrown by SQL Server,
SqlException
SqlError
These are used to catch the exceptions in the code and get the error details respectively. SqlException
always contains at least one instance of SqlError
.
Catching the errors from SQL Server
Example
try
{
//Do something here
}
catch (SqlException ex)
{
for (int i = 0; i < exception.Errors.Count; i++)
{
Console.WriteLine("Error: " + exception.Errors[i].ToString());
}
}
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
- Download source files — 28.4 KB
Introduction
This is a simple ADO.NET database application that returns results from a database table, writes the output to a DataGrid
and TextBox
es, and uses Button
s (First, Previous, Next, Last) to navigate through the records.
After getting lots of responses and suggestions from the users, I changed some points and made the code more readable. Like everyone else, I also searched for a most requested method in MSDN Library to trap the keystrokes (Up, Down, Esc…) in Windows Forms and included it in the code because some users asked me desperately for that. You can find the other requested methods as well to get the contents of a cell/row in a DataGrid
.
I chose Microsoft Access database (as reflected in the ADO.NET OleDb objects) because it’s easy to use and you don’t need to have Microsoft SQL Server running. But ADO.NET is highly optimized if you are working with Microsoft SQL Server databases (as reflected in the ADO.NET SQL objects).
I have now added a second part to this project (Personal Address Book) on Database Manipulation with ADO.NET for beginners where you can do data manipulation in TextBox
es (Delete, Save/Update, Add).
Contents
- What is ADO.NET?
- Connection to an ADO.NET Database
- Use of a DataSet to Fill with Records
- Use of a DataAdapter to Load Data into the DataSet
- Display Data in a DataGrid / Data Relationship between Two Tables
- DataBindings for TextBoxes
- Using the CurrencyManager
- Navigation through Records with Next, Previous, Last, First buttons
- How to Trap Keystrokes in the DataGrid (Up, Down, Esc, …)
This project was developed using Visual Studio .NET 2003 and Microsoft Access, on Windows XP Pro.
1. What is ADO.NET?
ADO.NET is the new database technology of the .NET (Dot Net) platform, and it builds on Microsoft ActiveX® Data Objects (ADO).
ADO is a language-neutral object model that is the keystone of Microsoft’s Universal Data Access strategy.
ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.
ADO.NET defines DataSet
and DataTable
objects which are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. It also includes the traditional Connection
and Command
objects, as well as an object called a DataReader
that resembles a forward-only, read-only ADO recordset
. If you create a new application, your application requires some form of data access most of the time.
ADO.NET provides data access services in the Microsoft .NET platform.
You can use ADO.NET to access data by using the new .NET Framework data providers which are:
- Data Provider for SQL Server (
System.Data.SqlClient
) - Data Provider for OLEDB (
System.Data.OleDb
) - Data Provider for ODBC (
System.Data.Odbc
) - Data Provider for Oracle (
System.Data.OracleClient
)
ADO.NET is a set of classes that expose data access services to the .NET developer. The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll.
There are two central components of ADO.NET classes: the DataSet
, and the .NET Framework Data Provider.
Data Provider is a set of components including:
- the Connection object (
SqlConnection
,OleDbConnection
,OdbcConnection
,OracleConnection
) - the Command object (
SqlCommand
,OleDbCommand
,OdbcCommand
,OracleCommand
) - the DataReader object (
SqlDataReader
,OleDbDataReader
,OdbcDataReader
,OracleDataReader
) - and the DataAdapter object (
SqlDataAdapter
,OleDbDataAdapter
,OdbcDataAdapter
,OracleDataAdapter
).
DataSet
object represents a disconnected cache of data which is made up of DataTable
s and DataRelation
s that represent the result of the command.
The ADO.NET Object Model
top
2. Connection to an ADO.NET Database
Before working with a database, you have to add (here) the OleDb
.NET Data Provider namespace, by placing the following at the start of your code module:
using System.Data.OleDb;
Similarly for the SqlClient
.NET Data Provider namespace:
using System.Data.SqlClient;
The using
statement should be positioned first in your code.
Now, we have to declare a connection string pointing to a MS Access database «PersonDatabase.mdb«.
public string conString=@"Provider=Microsoft.Jet.OLEDB.4.0;" + @" DataSource=..\..\PersonDatabase.mdb";
The database should be in the specified path, otherwise you should change the path accordingly.
The next step is to create an OleDbConnection
object. We then pass the connection string to this OleDbConnection
object. You can code now to create a new ADO.NET Connection object in order to connect to an OLE DB provider database.
OleDbConnection con = new OleDbConnection(conString);
You can also explicitly reference declared objects if you don’t mind typing a lot.
System.Data.OleDb.OleDbConnection con =
new System.Data.OleDb.OleDbConnection(conString);
Here is the code snippet for connection to a database:
using System.Data.OleDb; public string conString= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\PersonDatabase.mdb"; OleDbConnection con = new OleDbConnection(conString); con.Open();
In many earlier applications, the tendency was to open a connection when you start the application and not close the connection until the application terminates. It is an expensive and time-consuming operation to open and close a database connection. Most databases have a limit on the number of concurrent connections that they allow.
For example: each connection consumes a certain amount of resources on the database server and these resources are not infinite. Most modern OLE DB providers (including SQL Server provider) implement connection pooling. If you create database connections, they are held in a pool. When you want a connection for an application, the OLE DB provider extracts the next available connection from the pool. When your application closes the connection, it returns to the pool and makes itself available for the next application that wants a connection.
This means that opening and closing a database connection is no longer an expensive operation. If you close a connection, it does not mean you disconnect from the database. It just returns the connection to the pool. If you open a connection, it means it’s simply a matter of obtaining an already open connection from the pool. It’s recommended in many ADO.NET books not to keep the connections longer than you need to. Therefore, you should:
- Open a connection when you need it, and
- Close it as soon as you have finished with it
For example: here is another way to get a connection to a database:
private SqlConnection con = null; private string constr ="Integrated Security=SSPI;" + "Initial Catalog=Northwind;" + "Data Source=SONY\MYSQLSERVER;"; private void fnGetConnection() { try { con = new SqlConnection(constr); con.Open(); }catch (Exception ex) { MessageBox.Show("Error in connection : "+ex.Message); }finally { if (con != null) con.Close(); } }
For example: you want to open the connection, fill the DataSet
, and close the connection. If the connection fails, you want to get the error message.
try { con.Open(); dadapter.Fill(dataset1); con.Close(); } catch (Exception ex) { MessageBox.Show("Error in retrieving data: " + ex.Message); }
For example: if you want to save the data you changed, then you just open the connection, update the data, and close the connection and accept the changes. If it fails, display an error message, reject the changes, and close the connection.
try { DataSet changes = dataset.GetChanges(); con.Open(); datapter.Update(changes); con.Close(); dataset1.AcceptChanges(); }catch (Exception ex) { MessageBox.Show("ErrorR: " + ex.Message); dataset1.RejectChanges(); con.Close(); }
top
3. DataSet
The DataSet
is similar to an array of disconnected Recordset
objects. It supports disconnected data access and operations, allowing greater scalability because you no longer have to be connected to the database all the time. DataSet
is a copy of an extracted data being downloaded and cached in the client system.
The DataSet
object is made up of two objects:
DataTableCollection
object containingnull
or multipleDataTable
objects (Columns, Rows, Constraints).DataRelationCollection
object containingnull
or multipleDataRelation
objects which establish a parent/child relation between twoDataTable
objects.
DataSet dset = new DataSet();
There are two types of DataSet
s:
- Typed DataSet
- Untyped DataSet
Typed DataSet is derived from the base DataSet
class and then uses information in an XML Schema file (.xsd file) in order to generate a new class. Information from the schema (tables, columns, and so on) is generated and compiled into this new DataSet
class as a set of first-class objects and properties. Typed dataset is easier to read. It’s also supported by IntelliSense in the Visual Studio Code Editor. At compile time, it has type checking so that there are less errors in assigning values to DataSet
members. Therefore, using Typed DataSet has many advantages.
Example: The following code accesses the CustomerID
column in the first row of the Customers
table.
string str;
str=dset.Customers[0].CustomerID;
Create a Typed DataSet without Designer — Manually
- Call the command prompt (
cmd
) at the location of the XSD schema file. - Use the XSD.EXE utility to create the class for the typed
DataSet
.
xsd.exe /d /l:cs mydataset.xsd /n:mynamespace /d : you create a DataSet. /l:cs - set the language as C#. /n:mynamespace - the class should use the namespace "mynamespace".
The output of XSD.EXE with these arguments will be a .cs class file (mydataset.cs).
Use csc.exe to compile the class.
csc.exe /t:library mydataset.cs /r:System.dll /r:System.Data.dll /r:System.XML.dll /out:bin/mydataset.dll /t:library
Compile as a library component (DLL).
/r
: — specifies assemblies you need to reference/out
: — saves the compiled assembly in the bin subdirectory of the current directory
Untyped DataSet is not defined by a schema, instead, you have to add tables, columns and other elements to it yourself, either by setting properties at design time or by adding them at run time. Typical scenario: if you don’t know in advance what the structure of your program is that is interacting with a component that returns a DataSet
.
The equivalent code above for Untyped DataSet is:
string str; str=(string)dset.Tables["Customers"].Row[0].["CustomerID"];
A DataSet
is a container; therefore, you have to fill it with data.
You can populate a DataSet in a variety of ways:
- by using DataAdapter objects and Fill method
For example:
string strCon = @"Data Source=SONYMYSQLSERVER;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; string strSql="select * from customers"; SqlConnection con=new SqlConnection(strCon); con.Open(); SqlDataAdapter dadapter=new SqlDataAdapter(); dadapter.SelectCommand=new SqlCommand(strSql,con); DataSet dset=new DataSet(); dadapter.Fill(dset); con.Close(); this.dataGrid1.DataSource=dset;
- by creating DataTable, DataColumn and DataRow objects programmatically
After you create a
DataTable
and define its structure using columns and constraints, you can add new rows of data to the table.For example:
DataSet dset; DataTable dtbl; DataRow drow; drow=dtbl.NewRow(); drow["LastName"]="Altindag"; drow[1]="Altindag"; dtbl.Rows.Add(drow); dtbl.Rows.Add(new object[] {1, "Altindag"});
- Read an XML document or stream into the DataSet
The following code creates a
SqlConnection
object that opens a connection to the Pubs database, creates a SQL query to get the data of theAuthors
table as XML, and it creates a newSqlCommand
object. After creating a newDataSet
, it uses theExecuteXmlReader
method to pass anXmlReader
object to theDataSet
‘sReadXml
method, which allows theDataSet
to populate itself from theXmlReader
. Finally, the code sets theDocumentContent
property to the result of theGetXml
method of theDataSet
. XML uses the XSL Transformation document authors.xsl (included in the project) to format the XML content displayed by the XML control.For example:
protected System.Web.UI.WebControls.Xml XmlDisplay; string strCon = @"Data Source=SONYMYSQLSERVER;" + "Initial Catalog=pubs;Integrated Security=SSPI"; SqlConnection con=new SqlConnection(strCon); con.Open(); try { string strSql="select * from FROM authors FOR XML AUTO, XMLDATA"; SqlCommand cmd=new SqlCommand(strSql, con); DataSet dset=new DataSet(); dset.ReadXml(cmd.ExecuteXmlReader(),XmlReadMode.Fragment); XmlDisplay.DocumentContent = dset.GetXml(); }finally { con.Close(); }
- Merge (copy) the contents of another DataSet, with the Merge method
You can merge two
DataSet
objects that have largely similar schemas. You can use a merge typically on a client application to incorporate the latest changes from a data source into an existingDataSet
. This allows the client application to have a refreshedDataSet
with the latest data from the data source.For example:
dataset1.Merge(dataset2);
top
4. DataAdapter
DataAdapter
object is like a bridge that links the database and a Connection object with the ADO.NET-managed DataSet
object through its SELECT
and action query Commands. It specifies what data to move into and out of the DataSet
. Often, this takes the form of references to SQL statements or stored procedures that are invoked to read or write to a database.
The DataAdapter
provides four properties that allow us to control how updates are made to the server:
SelectCommand
UpdateCommand
InsertCommand
DeleteCommand
The four properties are set to Command
objects that are used when data is manipulated.
The DataAdapter
includes three main methods:
Fill
(populates aDataSet
with data)FillSchema
(queries the database for schema information that is necessary to update)Update
(to change the database,DataAdapter
calls theDeleteCommand
, theInsertCommand
and theUpdateCommand
properties)
For example:
When we call the DataAdapter
‘s Fill
method to retrieve data from a data source and pour it into a DataSet
, the Command
object in the SelectCommand
property is used. The DataAdapter
is the gatekeeper that sits between our DataSet
and the data source.
OleDbDataAdapter dAdapter = new OleDbDataAdapter ("select * from PersonTable", con ); dAdapter.Fill(dSet,"PersonTable");
Here is the method used in this project to get a data connection, DataSet
and DataAdapter
. You can find this method in the file «DataAccessTierClass.cs«.
public bool fnGetDataConnection() { try { con =new OleDbConnection(conString); dAdapter=new OleDbDataAdapter("select * from PersonTable", con); dSet=new DataSet(); dAdapter.Fill(dSet,"PersonTable"); }catch(Exception ex) { MessageBox.Show("Error : "+ex.Message); return false; } return true; }
top
5. Display Data in a DataGrid / Data Relationship Between Two Tables
The Windows Forms DataGrid
control displays data in a series of rows and columns. The Windows Forms DataGrid
control provides a user interface to ADO.NET DataSet
s. It displays tabular data and allows for updates to the data source. When you set a DataGrid
control to a valid data source, the control will be automatically populated, creating columns and rows based on the shape of the data.
You can use the DataGrid
control for displaying either a single table or the hierarchical relationships between a set of tables. If you want to work with the DataGrid
control, DataGrid
should be bound to a data source by using:
- the
DataSource
andDataMember
properties at design time or - the
SetDataBinding
method at run time
Here is the binding to the DataGrid
control with DataSet
I used in this project:
this.dataGrid1 DataSource = datc.dSet.Tables["PersonTable"];
You can only show one table in the DataGrid
at a time.
If you define a parent-child relationship between tables, you can navigate between the related tables to select the table you want to display in the DataGrid
control.
For example:
dset.Relations.Add("CustomerOrders", dset.Tables["customers"].Columns["CustomerID"], dset.Tables["orders"].Columns["CustomerID"]); this.dataGrid1.DataSource=dset.Tables["customers"];
OR:
this.dataGrid1.SetDataBinding(dset,"customers");
customers
: Parent tableorders
: Child tableCustomerID
inOrders
is a foreign key referring toCustomerID
primary key inCustomers
table.
Here is a typical example of how to use the parent-child relationship between the tables «Customers
» and «Orders
» on a DataGrid
control.
The DataRelation
in this example allows you to navigate from one DataTable
(«Customers
«) to another DataTable
(«Orders
«) within a DataSet
. The DataSet
class can contain null
or many DataTable
objects. «Customers
» and «Orders
» DataTable
s contain a column named «CustID
«, which is a link between these two DataTable
objects.
To run and test this example, create a new project, drag/drop a Button
(here: button1
) and a DataGrid
(here: dataGrid1
) on the Form
and copy the following code snippets (fnGetConnectionString()
, button1_Click
) and you additionally need SQL Server 2000 running or MS-Access.
public string fnGetConnectionString() { return "data source=SONY\MYSQLSERVER;initial" + " catalog=Northwind;integrated security=SSPI;"; }
private void button1_Click(object sender, System.EventArgs e) { DataSet dset=new DataSet(); string strCustomers="select * from customers"; string strOrders="select * from orders"; SqlConnection sqlcon=new SqlConnection(fnGetConnectionString()); SqlDataAdapter dadapter=new SqlDataAdapter(strCustomers,sqlcon); dadapter.Fill(dset,"Customers"); dadapter=new SqlDataAdapter(strOrders,sqlcon); dadapter.Fill(dset,"Orders"); dset.Relations.Add("Customer Orders", dset.Tables["Customers"].Columns["CustomerID"], dset.Tables["Orders"].Columns["CustomerID"]); this.dataGrid1.DataSource=dset.Tables["Customers"]; }
Now if you update the data in the bound DataSet
through any mechanism, the DataGrid
control reflects the changes. You can update the data in the DataSet
through the DataGrid
control, if the DataGrid
and its table styles and column styles have the ReadOnly
property set to false
. There are four most typical valid data sources for the DataGrid
:
DataTable
classDataView
classDataSet
classDataViewManager
class
The first time this application was published, I got e-mails from users asking me how to get the contents of a DataGrid
cell you clicked, or how to get the DataGrid
row contents you clicked. So now, I’ve one method to do that and didn’t want to withhold it from you.
private void dataGrid1_CurrentCellChanged(object sender, System.EventArgs e) { int iRownr=this.dataGrid1.CurrentCell.RowNumber; int iColnr=this.dataGrid1.CurrentCell.ColumnNumber; object cellvalue1=this.dataGrid1[iRownr, iColnr]; object cellvalue2=null; try { cellvalue2=this.dataGrid1[iRownr, iColnr+1]; this.textBox1.Text=cellvalue1.ToString()+" "+cellvalue2.ToString(); } catch(Exception ex) { MessageBox.Show("No further columns after the last " + "column(Country) -->> "+ex.Message,"STOP"); cellvalue2=this.dataGrid1[iRownr, iColnr-1]; this.textBox1.Text=cellvalue2.ToString()+" "+cellvalue1.ToString(); } }
top
6. DataBindings for TextBoxes
DataBinding is the ability to bind some elements of a data source with some graphical elements of an application.
The data in Windows Forms is bound by calling DataBindings
. Windows Forms allows you to bind easily to almost any structure that contains data.
Windows Forms Controls support two types of data binding:
- Simple Data Binding
- Complex Data Binding
Simple Data Binding allows you to display a single data element, such as a column value from a DataSet
table, in a control. You can simple-bind any property of a control to a data value. Simple Data Binding can be performed either at design time using DataBindings
property of a control or dynamically at run time. This is the type of binding typical for controls such as a TextBox
control or Label
control that displays typically only a single value.
For example:
textBox1.DataBindings.Add("Text", dataset, "studentTable.studentID");
The control «textBox1
» above is bound to the «studentID
» column of a table «studentTable
» on the DataSet
(dataset
) through the BindingContext
object.
Complex data binding is the ability of a control to bind to more than one data element, typically more than one record in a database, or to more than one of any other type of bindable data element. DataGrid
, ListBox
and ErrorProvider
controls support complex data binding.
Typical scenario:
You want to display the names of products in a list box and then retrieve in a TextBox
the ProductID
of a product which you selected.
For example:
You could add complex data binding by using the DataSource
and DataMember
properties.
datagrid1.DataSource = dSet; datagrid1.DataMember = "PersonTable";
Here is the method used in this project to bind all TextBox
es:
private void fnGetDataBindingForTextBoxes() { this.textboxFirstname.DataBindings.Add("Text", datc.dSet.Tables["PersonTable"],"FirstName"); this.textboxLastname.DataBindings.Add("Text", datc.dSet.Tables["PersonTable"],"LastName"); this.textboxTitle.DataBindings.Add("Text", datc.dSet.Tables["PersonTable"],"Title"); this.textboxCity.DataBindings.Add("Text", datc.dSet.Tables["PersonTable"],"City"); this.textboxCountry.DataBindings.Add("Text", datc.dSet.Tables["PersonTable"],"Country"); }
top
7. Using the CurrencyManager
You use the CurrencyManager
object if you want to keep data-bound controls synchronized with each other which means showing data from the same record. For example: if you want to add a TextBox
control to a form and bind it to a column of a table (e.g., Customers.FirstName
) in a DataSet
(e.g., dSet
), the control is going to communicate with the BindingContext
object for this form. In turn, the BindingContext
object is going to talk to the specific CurrencyManager
object for the data the TextBox
control is binding.
Every Windows Form has a BindingContext
object keeping track of all the CurrencyManager
objects on the Windows Form. CurrencyManager
keeps track of the position in the data source. When you bind a data object to a control (i.e., TextBox
), a CurrencyManager
object is automatically assigned. If you bind several controls to the same data source, they share the same CurrencyManager
.
In a normal case where you are using an ADO.NET database (connecting and closing database) and displaying the records, e.g., in a DataGrid
, you never need the CurrencyManager
object. But if you want to know the exact position within a data structure (e.g., table in your database) as I did, you have to use the CurrencyManager
object because the CurrencyManager
has the Position
property for this purpose. You can, for example, manipulate the Position
property in a Next or Previous or First or Last button which I did in my program as well.
For example:
If you want to know how many records are in a DataTable
, you simply query the BindingContext
object’s Count
property.
this.BindingContext[dataset1,"PersonTable"].Count - 1 ;
If you want to get the current position from the BindingContext
object:
this.BindingContext[dataset1, "PersonTable"].Position + 1;
After data binding, you call and initialize CurrencyManager
for your table. Here is the method I used to initialize the CurrencyManager
for the table «PersonTable
«:
public void fnSetCurrencyManager() { currManager = (CurrencyManager)this. BindingContext [ datc.dSet.Tables["PersonTable"]] ; }
top
8. Navigation Through Records With Next, Previous, Last, First Buttons
As soon as you get the data populated in the DataGrid
, you can navigate through records by using Next, Previous, Last, First buttons, or by clicking the rows of the DataGrid
, or by using the arrow keys (Up arrow and Down arrow).
If the DataGrid
is currently displaying data, none of the standard keyboard events are raised for the navigation keys. You can still use Up and Down arrow keys to navigate in the DataGrid
but, because I haven’t implemented it, you don’t get the record position in the StatusBar
.
In order to capture keystrokes on the DataGrid
, you have to override the ProcessCmdKey
method that processes a command key. You can find this method in section 9.
I also included two new methods in order to highlight the records in the DataGrid
by using Next, Previous, Last or First buttons because you don’t normally get the row highlighted in the DataGrid
if you click such a button. By default, if you click the DataGrid
row, the row will be highlighted with BackColor
and ForeColor
.
First method:
fnSelectUnselectLastFirstRow(int posi)
If you click First or Last button, the first or last record will be selected and highlighted in the DataGrid
. You invoke for that the fnSelectUnselectLastFirstRow()
method and pass as parameter 0
(zero) for the first record (fnSelectUnselectLastFirstRow(0);
), and (this.currManager.Count-1)
for the last record.
fnSelectUnselectLastFirstRow(this.currManager.Count-1);
Here is the first method I used for this purpose:
private void fnSelectUnselectLastFirstRow (int posi) { this.dataGrid1.UnSelect(this.dataGrid1.CurrentRowIndex); this.dataGrid1.Select(posi); }
Second method:
fnSelectUnselectCurrentRow(int num1, int num2)
If you click Next or Previous button, the next or previous record will be selected and highlighted in the DataGrid
. You call for that the method fnSelectUnselectCurrentRow();
and pass as parameter (1,-1)
for the next record (fnSelectUnselectCurrentRow(1,-1);
), or (-1,1)
for the previous record (fnSelectUnselectCurrentRow(-1,1);
).
Here is the second method used for it:
private void fnSelectUnselectCurrentRow(int num1, int num2) { this.iRowIndex=this.dataGrid1.CurrentRowIndex; this.iRowIndex=this.iRowIndex+num1; this.dataGrid1.Select(this.iRowIndex); this.iRowIndex=this.iRowIndex+num2; this.dataGrid1.UnSelect(this.iRowIndex); }
Now back to enabling and disabling the buttons:
When you click First button, position will be set to 0
(zero) because the first row starts by zero.
currManager.Position=0;
and:
- Disable First and Previous buttons because there is no previous record in the data source.
- Enable Next and Last buttons because there are records forward.
When you click Next button, position
in the data is increased by 1
and moved to the next row.
currManager.Position +=1;
and:
- Enable First and Previous buttons as long as there are forward records.
- Otherwise, disable Next and Last buttons which means you reached the end of the records.
When you click Previous button, position in the data is decreased by -1
and moved to the previous row.
currManager.Position -=1;
and:
- Enable Next and Last buttons as long as there are records backwards.
- Otherwise, disable First and Previous buttons which means you reached the beginning of the records.
When you click Last button, position in the data is set to the last record (row).
this.currManager.Position=this.currManager.Count-1;
and:
- Disable Next and Last buttons because there are no records forwards any more.
- Otherwise, enable First and Previous buttons so that you can navigate backwards.
To enable and disable the buttons, I use the function/method fnEnableDisableButtons
with four parameters (two Button
s, string
for StatusBar
, bool
for true
=enabling, false
=disabling).
private void fnEnableDisableButtons(Button bt1, Button bt2, string str, bool b) { bt1.Enabled=b; bt2.Enabled=b; this.statusBar1.Text=str; }
top
9. How to Trap Keystrokes (Up, Down, Esc, NumLock…) in the DataGrid
Every time you press the keys Up, Down, NumLock and Esc in the DataGrid
, I display text in the statusBarPanel1
and statusBarPanel2
, but you don’t get record numbers displayed because I thought it would be a bit confusing and too much coding.
Like many users, I also looked for a method to catch the keystrokes in a DataGrid
, and encountered it first in MSDN Library. So I decided to include it in the code so that users can make use of it. For most purposes, the standard KeyUp
, KeyDown
, and KeyPress
events can capture and handle keystrokes. However, not all controls raise the standard KeyUp
, KeyDown
events for all keystrokes under all conditions. The DataGrid
control is one of them.
If no data was assigned to the grid, the arrow keys (LEFT, RIGHT, UP, and DOWN) raise only the KeyUp
event. If the DataGrid
displays data, none of the standard keyboard events are raised for the navigation keys. The DataGrid
is the control for which this feature is most frequently requested. You also can intercept key combinations, including CTRL and ALT. This technique does not capture the Print Screen key. In order to trap keystrokes in a Windows Forms control, you can override the ProcessCmdKey
method in which I changed only StatusBarPanel
Text
.
protected override bool ProcessCmdKey(ref Message msg, Keys keyData) { const int WM_KEYDOWN = 0x100; const int WM_SYSKEYDOWN = 0x104; if ((msg.Msg == WM_KEYDOWN) || (msg.Msg == WM_SYSKEYDOWN)) { switch(keyData) { case Keys.Down: this.statusBarPanel2.Text="Down"; this.statusBarPanel1.Text = "Trapped keystrokes on DataGrid..."; break; case Keys.Up: this.statusBarPanel2.Text="Up"; this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid..."; break; case Keys.NumLock: this.statusBarPanel2.Text="NumLock"; this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid..."; break; case Keys.Escape: this.statusBarPanel2.Text="Escape"; this.statusBarPanel1.Text ="Trapped keystrokes on DataGrid..."; ExitClass ec=new ExitClass(); ec.fnExitUniversal(); break; } } return base.ProcessCmdKey(ref msg,keyData); }
top
In Conclusion
I tried to show the basics of ADO.NET to the beginners, and how to use ADO.NET in a database application and also keep the code as readable as possible. In addition, I also tried to show some interesting tips and methods for the DataGrid
control (on users’ request).
There is now a second part to this project (Personal Address Book): Database Manipulation with ADO.NET for beginners, where you can do data manipulation in TextBox
es (Delete, Save/Update, Add).
I hope it can help you understand (a bit) of what ADO.NET is, and you can find something useful here for your projects.
Good coding!
History
- 12th September, 2005: Initial version
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.
A list of licenses authors might use can be found here.
This member has not yet provided a Biography. Assume it’s interesting and varied, and probably something to do with programming.