Whether we use ADO.NET or not generally we don't know about hierarcy of ADO.NET namespaces and classes. Therefore, I want to write its general hierarcy:
ADO.NET NAMESPACES
* System.Data
* System.Data.Common
* System.Data.SqlClient
System.Data: The core of ADO.NET. Tables, Rows, Columns and DataSet are all tools that are in System.Data. This namespace implements IDbCommand, IDbConnection, IDbDataAdapter interfaces.
System.Data.Common: This namespace is generally for custom data providers because it includes base classes of all providers in ASP.NET. For example if you want to write your own SQL provider, then you should use System.Data.Common.
System.Data.SqlClient: Data Provider for SQL. SqlCommand,SqlConnection etc. are in this class.
Now let's group ADO.NET classes considering their base features.
* Classes don't need live connection to database
* Classes do need live connection to database
* Base classes for Data Providers
* and Data Providers
Classes don't need live connection to database: Their most important and basic future is to recreate tables (with its rows, columns, functional dependencies and all its futures) in memory and manipulate them. When needed they send this tables to client side. These classes includes DataSets, DataTables.
Classes do need live connection to database: Like DataReader class, gets the actual form of database and sends it to the client. If there is an update on the server side it should be resent. To do so, it requires live connection.
Base classes for Data Providers : These classes are in System.Data.Common and are base classes for all Data Providers. (including our own custom providers)
DataProvider classes: For each Data Provider, we have a unique DataProvider class. We do all operation related to a specific data source by its own DataProvider. Each and every Data Provider has its own DataConnection, Command, Adapter and Reader objects.
Let's examine a portion of a data access layer class. (This is a general ADO.NET example. In subsequent entries, I'll give more specific examples)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient; //This is for MSSQL operations.
public static void Insert_Customers(Guid? userID, string name, string surname, string tel_no)
{
SqlConnection conn = null; //for database connection
SqlCommand cmd = null;
// with this object we send necessary commands (sql queries)
// to database
SqlTransaction trans = null; //SqlTransaction is used for insert and delete
//when there is an exception, we can undo our operations
//with this object
try
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ImaletConnectionString"].ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
cmd = new SqlCommand("Insert_Customer",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier);
//add sql parameters with the same name in sql queries
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@Surname", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@Tel_No", SqlDbType.NVarChar, 20);
cmd.Parameters[1].Value = name;
cmd.Parameters[2].Value = surname;
cmd.Parameters[3].Value = tel_no;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception pr)
{
if (trans != null)
trans.Rollback();
//if there is an exception undo current operation
throw new Exception();
}
finally
{
if (conn != null)
conn.Close();
// with Dispose() we prompt C# compiler to delete our objects
cmd.Dispose();
trans.Dispose();
conn.Dispose();
}
}
