Software/C#.Net

ADO.NET1

charom 2011. 8. 27. 01:06
The Two faces of ADO.NET
Connected
Disconnected 

Understanding ADO.NET Data Provider
can access any unique features of a particular DBMS
Be able to directly connect to the underlying engine of the DBMS

Core Object of an ADO.NET Data Provider
 Object  Base Class   Implemented Interfaces  
 Connection DbConnection  IDbConnection  Provides connect / disconnect object and a related transaction object 
 Command DbCommand  IDbCommand  Represent a SQL query or a stored procedure 
 DataReader DbDataReader  IDataReader
IDataRecord 		 Provides forward-only,read-only, access to data using a sever-side cursor 
DataAdapter DbDataAdapter IDataAdapter,IDbDataAdapter Transfers DataSets between the caller and the data store. Data adapters contain a connection an a set of four internal command objects used to select, insert, update, and delete information from the data store.
Parameter DbParameter IDataParameter,IDbDataParameter Represents a named parameter within a parameterized query.
Transaction DbTransaction IDbTransaction Encapsulates a database transaction.

  Types of the System.Data Namespace
  • The lowest common denominator
  • Contains types that are shared among all ADO.NET data providers
  • Include a number of database ( NonullAllowedException, MissingPrimaryKeyException)
  • Contains various database primitives types(Tables, Rows, Columns,Constraints, etc)
  
Microsoft ADO.NET Data Providers 
Data Provider  Namespace  Assembly
OLE DB System.Data.OleDb  System.Data.dll 
Microsoft SQL Server System.Data.SqlClient   System.Data.dll 
Microsoft SQL Server Mobile System.Data.SqlServerCe System.Data.SqlServerCe.dll
ODBC System.Data.Odbc System.Data.dll
Oracle System.Data.OracleClient  System.Data.OracleClient.dll

The Roles of Interfaces with Provider
◎ IDbConnection Interfaces
  • Be implemented by a data provider's connection object
  • To configure a connection to a specific data store
  • To obtain the data provider's transaction object
public interface IDbConnection : IDisposable
{
    string ConnectionString { get; set; }
    int ConnectionTimeout { get; }
    string Database { get; }
    ConnectionState State { get; }
    IDbTransaction BeginTransaction();
    IDbTransaction BeginTransaction(IsolationLevel il);
    void ChangeDatabase(string databaseName);
    void Close();
    IDbCommand CreateCommand();
    void Open();
}

◎ IDbTransaction Interface  
  • To Programmatically interact with a transactional session 
     
public interface IDbTransaction : IDisposable
{
    IDbConnection Connection { get; }
    IsolationLevel IsolationLevel { get; }
    void Commit();
    void Rollback();
}

◎ IDbCommand Interface
  • Be implemented by a data provider's command object
  • Command objects allow programmatic manipulation of SQL statements ( stored procedures, and parameterized queries )
  • Provide access to the data provider's data reader type via the overloaded ExecuteReader() method

public interface IDbCommand : IDisposable
{
    string CommandText { get; set; }
    int CommandTimeout { get; set; }
    CommandType CommandType { get; set; }
    IDbConnection Connection { get; set; }
    IDataParameterCollection Parameters { get; }
    IDbTransaction Transaction { get; set; }
    UpdateRowSource UpdatedRowSource { get; set; }
    void Cancel();
    IDbDataParameter CreateParameter();
    int ExecuteNonQuery();
    IDataReader ExecuteReader();
    IDataReader ExecuteReader(CommandBehavior behavior);
    object ExecuteScalar();
    void Prepare();
}


◎ The Role of the IDbDataParameter and IDataParameter Interfaces  
public interface IDbDataParameter : IDataParameter
{
    byte Precision { get; set; }
    byte Scale { get; set; }
    int Size { get; set; }
}
public interface IDataParameter
{
    DbType DbType { get; set; }
    ParameterDirection Direction { get; set; }
    bool IsNullable { get; }
    string ParameterName { get; set; }
    string SourceColumn { get; set; }
    DataRowVersion SourceVersion { get; set; }
    object Value { get; set; }
}

◎ IDbDataAdapter and IDataAdapter Interfaces
  • To push and pull DataSet to and from a given data store
  • Defines a set of properties to maintain the SQL statements (Select, Insert, Update, and Delete)
public interface IDbDataAdapter : IDataAdapter
{
    IDbCommand DeleteCommand { get; set; }
    IDbCommand InsertCommand { get; set; }
    IDbCommand SelectCommand { get; set; }
    IDbCommand UpdateCommand { get; set; }
}
public interface IDataAdapter
{
    MissingMappingAction MissingMappingAction { get; set; }
    MissingSchemaAction MissingSchemaAction { get; set; }
    ITableMappingCollection TableMappings { get; }
    int Fill(System.Data.DataSet dataSet);
    DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType);
    IDataParameter[] GetFillParameters();
    int Update(DataSet dataSet);
}

◎ The Role of the IDataReader and IDataRecord Interfaces
  
public interface IDataReader : IDisposable, IDataRecord
{
    int Depth { get; }
    bool IsClosed { get; }
    int RecordsAffected { get; }
    void Close();
    DataTable GetSchemaTable();
    bool NextResult();
    bool Read();
}
public interface IDataRecord
{
    int FieldCount { get; }
    object this[ string name ] { get; }
    object this[ int i ] { get; }
    bool GetBoolean(int i);
    byte GetByte(int i);
    char GetChar(int i);
    DateTime GetDateTime(int i);
    Decimal GetDecimal(int i);
    float GetFloat(int i);
    short GetInt16(int i);
    int GetInt32(int i);
    long GetInt64(int i);
    ...
    bool IsDBNull(int i);
}

◎ Increasing Flexibility Using Application Configuration Files
  • namespace : System.Configuration
    *.config file
    use key/value pairs within the <appSettings>
    can be programmatically obtained

example 1.configfile 2.load and connect program

    
        
        
     


    // Read the provider key.
    string dataProvString = ConfigurationManager.AppSettings["provider"];
    // Transform string to enum.
    DataProvider dp = DataProvider.None;
    if(Enum.IsDefined(typeof(DataProvider), dataProvString))
        dp = (DataProvider)Enum.Parse(typeof(DataProvider), dataProvString);
    else
        Console.WriteLine("Sorry, no provider exists!");
    // Get a specific connection.
    IDbConnection myCn = GetConnection(dp);
    if(myCn != null)
        Console.WriteLine("Your connection is a {0}", myCn.GetType().Name);
    // Open, use, and close connection...

◎ ADO.NET Data Provider Factory Model
  • can obtain the associated provider-specific data objects (connection, commands, data readers) when you have obtained the factory for your data provider
  • To obtain the DbProviderFactory : DbProviderFactories::GetFactory()
  • DbProviderFactory sqlFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common   Source
 ▷DbCommand: Abstract base class for all command objects
▷DbConnection: Abstract base class for all connection objects
▷DbDataAdapter: Abstract base class for all data adapter objects
▷DbDataReader: Abstract base class for all data reader objects
▷DbParameter: Abstract base class for all parameter objects
▷DbTransaction: Abstract base class for all transaction objects 		 
public abstract class DbProviderFactory
{
    ...
    public virtual DbCommand CreateCommand();
    public virtual DbCommandBuilder CreateCommandBuilder();
    public virtual DbConnection CreateConnection();
    public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
    public virtual DbDataAdapter CreateDataAdapter();
    public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
    public virtual DbParameter CreateParameter();
}

◎ Registered Data Provider Factories
  • DbProviderFactories type is able to fetch factories for only a subset of all possible data providers
  • The valid provider factories is recorded with in the <DbProviderFactories> element within the machine.config
  • A complete Data Provier Factory Example
  • configuration

    
        
            
            
            
            
        
    

    // Get Connection string/provider from *.config.
    string dp =ConfigurationManager.AppSettings["provider"];
    string cnStr =ConfigurationManager.AppSettings["cnStr"];
    // Get the factory provider.
    DbProviderFactory df = DbProviderFactories.GetFactory(dp);
    // Now make connection object.
    DbConnection cn = df.CreateConnection();
    Console.WriteLine("Your connection object is a: {0}", cn.GetType() cn.ConnectionString = cnStr;
    cn.Open();
    // Make command object.
    DbCommand cmd = df.CreateCommand();
    Console.WriteLine("Your command object is a: {0}", cmd.GetType().F
    cmd.Connection = cn;
    cmd.CommandText = "Select * From Inventory";
    // Print out data with data reader.
    // Because we specified CommandBehavior.CloseConnection, we
    // don't need to explicitly call Close() on the connection.
    DbDataReader dr =
    cmd.ExecuteReader(CommandBehavior.CloseConnection);
    Console.WriteLine("Your data reader object is a: {0}", dr.GetType(
    Console.WriteLine("\n***** Current Inventory *****");
    while (dr.Read())
        Console.WriteLine("-> Car #{0} is a {1}.",  dr["CarID"], dr["Make"].ToString().Trim());
    dr.Close();

◎ The <connectionStrings> element
 
  • can define any number of name/value pairs
  • it can programmatically read into memory
  • To read, configuration Manager.ConnectionStrings indexer
  • can define multiple connection strings for a single application


String cnStr=configurationManager.ConnectionString["AutoLotSqlProvider"].ConnectionString;
  ◎Connected Layer of ADO.NET Interact with a database using the connection, command, and data reader objects Steps to perform
  1. Allocate, configure, and open connection object
  2. Allocate and configure a command object, specifying the connection object as a constructor argument or via the connection property
  3. Call ExecuteReader() on the confiuged command object
  4. Process each record using the Read() method of the data Reader
◎Connection Objects 
SqlConnection cn=new SqlConnection();
cn.ConnectionString = @"Data Source==(local)\....//identified the name of the machine
Integrated Security=SSIP; //Database name, attempting to establish a session with
connect Timeout=30
Initial Catalog=AutoLot
//Data Source:identifies the name of the name of the machine that maintains the database
//Close() :specifiy the current local machine
//SSPI: uses the current Windows account crendentials for user authentication
◎Members of the DbConnection Type
 Member  Meaning
 BeginTransaction()  This method is used to begin a database transaction
 ChangeDatabase()  This method changes the database on an open connection
 ConnectionTimeout  This read-only property returns the amount of time to wait while establishing a connection before terminating and generating an error (the default value is 15 seconds). If you wish to change the default, specify a “Connect Timeout” segment in the connection string (e.g., Connect Timeout=30).
 Database This property gets the name of the database maintained by the connection object 
 DataSource This property gets the location of the database maintained by the connection object. 
 GetSchema() This method returns a DataSet that contains schema information from the data source. 
 State This property sets the current state of the connection, represented by the ConnectionState enumeration. 

◎ ConnectionStringBuilder Objects
 
  • support name/value pairs
    •     // Create a connection string via the builder object.
    SqlConnectionStringBuilder cnStrBuilder =   new SqlConnectionStringBuilder();
    cnStrBuilder.InitialCatalog = "AutoLot";
    cnStrBuilder.DataSource = @"(local)\SQLEXPRESS";
    cnStrBuilder.ConnectTimeout = 30;
    cnStrBuilder.IntegratedSecurity = true;
    SqlConnection cn = new SqlConnection();
    cn.ConnectionString = cnStrBuilder.ConnectionString;
    cn.Open();
◎ Command Object
  • Command Type Property
    • public enum commandType = {StoredProcedure, TableDirect, Text}; //Text is default
  • Can establish the SQL query by a constructor parameter or directly via the commandText Property 
    //1.a constructor parameter
string strSQL = "Select * from Inventory";
SqlCommand myCommand=new SqlCommand(strSQL, ConStr);

//2.directly via the Command Text Property
SqlCommand testCommand = new SqlCommand();
testCommand.Connection = cn;
testCommand.CommandText = strSQL;
◎ Members of the DbCommand Object
 Member  Meaning
 CommandTimeout  Gets or sets the time to wait while executing the command before terminating the attempt and generating an error. The default is 30 seconds.
 Connection  Gets or sets the DbConnection used by this instance of the DbCommand
 Parameters  Gets the collection of DbParameter types used for a parameterized query
 Cancel()  Cancels the execution of a command.
 ExecuteReader() Returns the data provider’s DbDataReader object, which provides forward-only, read-only access to the underlying data. 
 ExecuteNonQuery()  Issues the command text to the data store where no results are expected or desired
 ExecuteScalar()  A lightweight version of the ExecuteNonQuery()method, designed specifically for singleton queries (such as obtaining a record count).
 ExecuteXmlReader()  Microsoft SQL Server (2000 and higher) is capable of returning result sets as XML. As you might suspect, this method returns a System.Xml.XmlReader that allows you to process the incoming stream of XML.
 Prepare()  Creates a prepared (or compiled) version of the command on the data source. As you may know, a prepared query executes slightly faster and is useful when you wish to execute the same query multiple times.

◎ Data Readers
 
  • Obtain queried information from DbDataReader(the simplest and fastest way)
  • Data Readers represent a read-only, forward-only stream of data
  • Returned one record at a time
  • Only useful when submiting SQL selection statements to the underlying data store
  • Useful to iterate over large amounts of data very quickly and no need to maintain on in-memory representation on
  • Connection Oriented way
  • Be obtained from the command object via a call to ExecuteReader()
    • 	// Obtain a data reader via ExecuteReader().
	SqlDataReader myDataReader;
	myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
	// Loop over the results.
	while (myDataReader.Read())
	{
		Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
		myDataReader["Make"].ToString().Trim(),
		myDataReader["PetName"].ToString().Trim(),
		myDataReader["Color"].ToString().Trim());
	}
	myDataReader.Close();
◎Obtaining Multiple Result Sets
  • Can use both SQL select statements using a semicolon delimiter.
  • Use NextResult() to iterate over each result set

     
◎A Reusable Data Access Library 
using System;
using System.Collections.Generic;
using System.Text;
// We will make use of the SQL server
// provider; however, it would also be
// permissible to make use of the ADO.NET
// factory pattern for greater flexibility.
using System.Data;
using System.Data.SqlClient;

namespace AutoLotConnectedLayer
{

	public class InventoryDAL
	{
		// This member will be used by all methods.
		private SqlConnection sqlCn = new SqlConnection();
		
		public void OpenConnection(string connectionString)
		{
			sqlCn.ConnectionString = connectionString;
			sqlCn.Open();
		}
		public void CloseConnection()
		{
			sqlCn.Close();
		}
		public void InsertAuto(int id, string color, string make, string petName)
		{
			// Format and execute SQL statement.
			string sql = string.Format("Insert Into Inventory" +
			"(CarID, Make, Color, PetName) Values" +
			"('{0}', '{1}', '{2}', '{3}')", id, make, color, petName);
			// Execute using our connection.
			using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
			{
				cmd.ExecuteNonQuery();
			}//using
		}//Insert
		public void InsertAutoParam(int id, string color, string make, string petName)
		{
			// Note the "placeholders" in the SQL query.
			string sql = string.Format("Insert Into Inventory" +
			"(CarID, Make, Color, PetName) Values" +
			"(@CarID, @Make, @Color, @PetName)");
			// This command will have internal parameters.
			using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
			{
				// Fill params collection.
				SqlParameter param = new SqlParameter();
				param.ParameterName = "@CarID";
				param.Value = id;				
				param.SqlDbType = SqlDbType.Int;
				cmd.Parameters.Add(param);
				
				param = new SqlParameter();
				param.ParameterName = "@Make";
				param.Value = make;
				param.SqlDbType = SqlDbType.Char;
				param.Size = 10;
				cmd.Parameters.Add(param);
				
				param = new SqlParameter();
				param.ParameterName = "@Color";
				param.Value = color;
				param.SqlDbType = SqlDbType.Char;
				param.Size = 10;
				cmd.Parameters.Add(param);
				
				param = new SqlParameter();
				param.ParameterName = "@PetName";
				param.Value = petName;
				param.SqlDbType = SqlDbType.Char;
				param.Size = 10;
				cmd.Parameters.Add(param);
				
				cmd.ExecuteNonQuery();
			}
		}
		public void DeleteCar(int id)
		{
			// Get ID of car to delete, then do so.
			string sql = string.Format("Delete from Inventory where CarID = '{0}'",	id);
			using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
			{
				try
				{
					cmd.ExecuteNonQuery();
				}
				catch(SqlException ex)
				{
					Exception error = new Exception("Sorry! That car is on order!", ex);
					throw error;
				}
			}
		}//DeleteCar
		public void UpdateCarPetName(int id, string newPetName)
		{
			// Get ID of car to modify and new pet name.
			string sql = string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
										newPetName, id);
			using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
			{
				cmd.ExecuteNonQuery();
			}
		}//UpdateCarPetName
		public DataTable GetAllInventory()
		{
			// This will hold the records.
			DataTable inv = new DataTable();
			// Prep command object.
			string sql = "Select * From Inventory";
			using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
			{
				SqlDataReader dr = cmd.ExecuteReader();
				// Fill the DataTable with data from the reader and clean up.
				inv.Load(dr);
				dr.Close();
			}
			return inv;
		}//GetAllInventory
		public string LookUpPetName(int carID)
		{
			string carPetName = string.Empty;
			// Establish name of stored proc.
			using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn))
			{
				cmd.CommandType = CommandType.StoredProcedure;
				
				// Input param.
				SqlParameter param = new SqlParameter();
				param.ParameterName = "@carID";
				param.SqlDbType = SqlDbType.Int;
				param.Value = carID;
				param.Direction = ParameterDirection.Input;
				cmd.Parameters.Add(param);
				
				// Output param.
				param = new SqlParameter();
				param.ParameterName = "@petName";
				param.SqlDbType = SqlDbType.Char;
				param.Size = 10;
				param.Direction = ParameterDirection.Output;
				cmd.Parameters.Add(param);
				// Execute the stored proc.
				cmd.ExecuteNonQuery();
				
				// Return output param.
				carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();
			}
			return carPetName;
		}
	}//InventtoryDAL
}//namespace