|
| Why Do I Need a Database Layer Anyway?
When
creating software for clients, even the beginner developer recognizes
the frequency of change. Requirements change, user interface pieces
change, platforms change, and databases changes. Determining ways to
minimize re-work in your web applications becomes a very important task
and by adding a distinct database layer to your C# ASP .NET application,
you can greatly increase its flexibility and adaptability to change.
In
addition to adaptability, a database layer can also provide an
on-the-fly means of changing connections strings and even the database
type itself. By changing just a few lines in your C# ASP .NET web.config
file, you can effectively change the type of database accessed by your
application from MSSQL to Oracle to MySQL - all without a single change
to the application code. |
The Simple Method
Many
developers begin by coding the user interface of a web application, as
this helps move the project visually. Because of this, it's not
surprising that database code for populating the fields is often mixed
directly in. In a C# ASP .NET application, this is similar to adding
calls to SQLConnection in your Page_Load() function, as shown below.
using System.Data.SqlClient;
protected void Page_Load(object sender, EventArgs e)
{
using (SqlConnection MyConnection = new SqlConnection(MyConnectionString))
{
using (SqlCommand MyCommand = new SqlCommand("SELECT * FROM Flowers", MyConnection))
{
using (SqlDataReader MyReader = MyCommand.ExecuteReader())
{
// read flowers and process ...
}
}
}
}
Notice
in the above code, we're referencing System.Data.SqlClient directly in
the user interface code. We're also making calls to the database. A
program will run just fine with this method. However, managing this code
becomes a problem.
The core problem with the above code is not
only that business logic may be mixed in with the user interface code
(business logic would appear inside the "read database information"
section), but that including database accessibility logic complicates
the readability of the program. Furthermore, what happens when you need
to change from using the SqlClient to using ODBC? You could change the
"using" statement and rename the lines from SqlConnection to
OdbcConnection throughout your code, but this may waste time and is also
prone to errors.
A much more clear picture of this application
could be made by leaving only user interface code inside the Page_Load()
function. Defer the decision on what type of database to use and
instead concentrate on user interface code. Any calls to database
routines would be handled by a call to a separate layer, as follows:
using MyStuff.Managers;
protected void Page_Load(object sender, EventArgs e)
{
Flowers = CommonManager.GetFlowers();
}
Notice
in the above code, the database functionality has been minimized to
just a single line. The details of the database logic are abstracted
away in a database layer. This greatly simplifies reading and managing
the code. Now that you can see the initial benefits to utilizing a
database layer in your C# ASP .NET web application, let's move on to
implementing it.
Putting the Web.Config to Good Use
Before
defining the actual database factory classes, we can use the C# ASP
.NET web application's web.config file to hold some important data about
how our database factory will be used. We will be storing information
such as the type of database provider (MSSQL, Oracle, MySQL), a list of
possible connection strings, as well as which connection string to use.
By storing this information in the web.config file, we provide the
flexibility to change the database provider or connection string in
real-time, without changing code in the application.
Our web.config file will look similar to the following:
<configuration>
<configSections>
<section name="DatabaseFactoryConfiguration"
type="MyStuff.Managers.DatabaseFactorySectionHandler, MyStuff.Managers,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</configSections>
<connectionStrings>
<clear/>
<add name="MyConnection1" providerName="Oracle.DataAccess.Client" connectionString="Your Connection String Here" />
<add name="MyConnection2" providerName="MSSQL" connectionString="Your Connection String Here" />
</connectionStrings>
<DatabaseFactoryConfiguration Name="MyStuff.Managers.OracleDatabase" ConnectionStringName="MyConnection1" />
</configuration>
In
the above web.config file, we have two connection strings defined. One
is for an Oracle database and another for an MSSQL database. You could
have any number of connection strings. Our database factory design uses
its own web.config section, defined in DatabaseFactoryConfiguration.
This allows us to tell the database factory which provider class to
instantiate (Oracle, MSSQL, etc) and which connection string to use. You
can see the flexibility this adds, by allowing us to change any of
these parts right in the web.config file.
The DatabaseFactory Web.Config Handler
Since
we've added our own custom section to the web.config file for the
database factory, we need to create a web.config custom section handler.
The section handler is defined as follows:
using System.Configuration;
public sealed class DatabaseFactorySectionHandler : ConfigurationSection
{
[ConfigurationProperty("Name")]
public string Name
{
get { return (string)base["Name"]; }
}
[ConfigurationProperty("ConnectionStringName")]
public string ConnectionStringName
{
get { return (string)base["ConnectionStringName"]; }
}
public string ConnectionString
{
get
{
try
{
return ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
}
catch (Exception excep)
{
throw new Exception("Connection string " + ConnectionStringName + " was
not found in web.config. " + excep.Message);
}
}
}
}
The
only important part to note about the section handler is that it
derives from the ConfigurationSection base class. When this class is
instantiated, it will automatically read the line from the web.config
file and populate with the values. Since we include a ConnectionString
property, it will also fetch the correct connection string, so that we
never have to access the ConfigurationManager.ConnectionStrings property
ourselves.
It All Starts With a Database
The
first part to creating a database factory design pattern is to
implement your generic Database object. This is the object you will
reference when calling database routines. Since this object is generic,
you don't have to decide what type of physical database to actually use
with it. This gives you the power to change database providers without
changing any of your code. The database object is designed as follows:
using System.Data;
public abstract class Database
{
public string connectionString;
#region Abstract Functions
public abstract IDbConnection CreateConnection();
public abstract IDbCommand CreateCommand();
public abstract IDbConnection CreateOpenConnection();
public abstract IDbCommand CreateCommand(string commandText, IDbConnection connection);
public abstract IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection);
public abstract IDataParameter CreateParameter(string parameterName, object parameterValue);
#endregion
}
It's
important to note that we're using .NET's own abstract database factory
interfaces. The interfaces can be used in exactly the same way as the
concrete database classes (SqlConnection, OdbcCommand, etc), without
forcing you to bind to one. This is the core piece to providing
flexibility to your database layer.
The Powerful Database Factory
With
the details out of the way, we can move on to the power behind the
database factory design pattern, which of course, is the factory class
itself. The database factory is the class we will use to instantiate the
concrete provider for our generic Database class. Remember, we defined
an abstract Database class which we can use without deciding on a
concrete database provider. Instead, we specify the concrete provider in
the web.config and let the database factory design pattern instantiate
it. Note, the database factory is able to instantiate any type of
concrete database by using C# .NET reflection.
The database factory is defined as follows:
using System.Reflection;
using System.Configuration;
public sealed class DatabaseFactory
{
public static DatabaseFactorySectionHandler sectionHandler =
(DatabaseFactorySectionHandler)ConfigurationManager.GetSection("DatabaseFactoryConfiguration");
private DatabaseFactory() { }
public static Database CreateDatabase()
{
// Verify a DatabaseFactoryConfiguration line exists in the web.config.
if (sectionHandler.Name.Length == 0)
{
throw new Exception("Database name not defined in DatabaseFactoryConfiguration section of web.config.");
}
try
{
// Find the class
Type database = Type.GetType(sectionHandler.Name);
// Get it's constructor
ConstructorInfo constructor = database.GetConstructor(new Type[] { });
// Invoke it's constructor, which returns an instance.
Database createdObject = (Database)constructor.Invoke(null);
// Initialize the connection string property for the database.
createdObject.connectionString = sectionHandler.ConnectionString;
// Pass back the instance as a Database
return createdObject;
}
catch (Exception excep)
{
throw new Exception("Error instantiating database " + sectionHandler.Name + ". " + excep.Message);
}
}
}
It's
important to note the use of .NET Reflection in the database factory.
While this adds the extensibility and power for our database layer, it
also adds a bit of overhead to processing. This overhead can be
minimized, as you'll see below, by utilizing a static variable so that
the number of times objects are instantiated by the factory is
minimized.
Putting the Database Factory to Use
We've
now implemented the C# ASP .NET database factory design pattern, using
the built-in .NET generic database interfaces. To actually use the
database factory, we'll create a data worker class to take care of
handling how the database factory and generic database are used.
Our
data worker class holds a static instance of the abstract Database
class. In its constructor, we instantiate the concrete database (defined
in the C# ASP .NET web applicaion's web.config file) by using our
database factory design pattern. The data worker is defined as follows:
public class DataWorker
{
private static Database _database = null;
static DataWorker()
{
try
{
_database = DatabaseFactory.CreateDatabase();
}
catch (Exception excep)
{
throw excep;
}
}
public static Database database
{
get { return _database; }
}
}
Notice
how, in the above code, there are no references to concrete database
providers. Nowhere do we reference MSSQL, Oracle, or other concrete
types. Everything is kept generic by using the abstract Database class
and leaving the details of the concrete type inside the web.config file
for the factory to access.
Now, whenever we create a manager class
to perform business logic related to the database, we simply inherit
from DataWorker. This gives the class instant access to the database
routines. For example, we can now create a manager class as follows:
class MyDatabaseLogic : DataWorker
{
public void LoadFlowers()
{
using (IDbConnection connection = database.CreateOpenConnection())
{
using (IDbCommand command = database.CreateCommand("SELECT * FROM FLOWERS", connection))
{
using (IDataReader reader = command.ExecuteReader())
{
// read flowers and process ...
}
}
}
}
}
How About a Concrete Database Class?
Up
until this point, we've created abstract and generic classes which
defer the decision on which database type to actually use. Now, it's
time to create a concrete implementation of the abstract Database
object. You will create a concrete Database class for the type of
database you plan on using (or may use in the future). If you end up
needing to change the database provider, you can change the web.config
to use a different concrete implementation.
For this example, we'll define an Oracle Database class as follows:
using Oracle.DataAccess.Client;
public class OracleDatabase : Database
{
public override IDbConnection CreateConnection()
{
return new OracleConnection(connectionString);
}
public override IDbCommand CreateCommand()
{
return new OracleCommand();
}
public override IDbConnection CreateOpenConnection()
{
OracleConnection connection = (OracleConnection)CreateConnection();
connection.Open();
return connection;
}
public override IDbCommand CreateCommand(string commandText, IDbConnection connection)
{
OracleCommand command = (OracleCommand)CreateCommand();
command.CommandText = commandText;
command.Connection = (OracleConnection)connection;
command.CommandType = CommandType.Text;
return command;
}
public override IDbCommand CreateStoredProcCommand(string procName, IDbConnection connection)
{
OracleCommand command = (OracleCommand)CreateCommand();
command.CommandText = procName;
command.Connection = (OracleConnection)connection;
command.CommandType = CommandType.StoredProcedure;
return command;
}
public override IDataParameter CreateParameter(string parameterName, object parameterValue)
{
return new OracleParameter(parameterName, parameterValue);
}
As
you can see in the above code, we fill out the body for each abstract
function defined in the Database class. You can customize the abstract
and concrete classes further to perform more functionality.
Putting It All Together
Now
that our C# ASP .NET database layer is complete, we can use the layer
to refactor our original "Simple" database access code listed above. An
example is shown below which replaces our Page_Load() database code with
usage of our Manager class (which, in turn, uses the Database and
factory classes).
class FlowerManager : DataWorker
{
public static void GetFlowers()
{
using (IDbConnection connection = database.CreateOpenConnection())
{
using (IDbCommand command = database.CreateCommand("SELECT * FROM FLOWERS", connection))
{
using (IDataReader reader = command.ExecuteReader())
{
// ...
}
}
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
Flowers = FlowerManager.GetFlowers();
// Populate the flowers in a C# ASP .NET droplist control.
MyDropListControl.DataSource = Flowers;
MyDropListControl.DataBind();
} |
| Conclusion
The
database layer is a powerful addition to the C# ASP .NET web
application. It provides advanced extensibility, flexibility, and
adaptation to change. By implementing the database layer with a database
factory design pattern, we can add enhanced power to our architecture
and better anticipate changes in database platforms and connections.
Separating database code from application level code is a key method for
providing better readability and maintenance of code in C# ASP .NET.
____________________________________________________________________________
http://www.primaryobjects.com/CMS/Article81.aspx
http://www.databasedev.co.uk/data-access-layer.html
http://www.c-sharpcorner.com/uploadfile/mbouck/genericdataaccesscompactivator09052005013346am/genericdataaccesscompactivator.aspx
http://www.codeproject.com/Articles/13695/Data-Access-Component-and-the-Factory-Design-Patte
_____________________________________________________________________________
Example of Code Project:-
using System;
using System.Data;
namespace DataAccessLayer
{
/// <summary>
/// Defines the DataAccessLayer implemented data provider types.
/// </summary>
public enum DataProviderType
{
Access,
Odbc,
OleDb,
Oracle,
Sql
}
/// <summary>
/// The DataAccessLayerBaseClass lists all the abstract methods that each data access layer provider (SQL Server, OleDb, etc.) must implement.
/// </summary>
public abstract class DataAccessLayerBaseClass
{
#region private data members, methods & constructors
// Private Members
private string strConnectionString;
private IDbConnection connection;
private IDbCommand command;
private IDbTransaction transaction;
// Properties
/// <summary>
/// Gets or sets the string used to open a database.
/// </summary>
public string ConnectionString
{
get
{
// make sure conection string is not empty
if (strConnectionString == string.Empty
|| strConnectionString.Length == 0)
throw new ArgumentException("Invalid database connection string.");
return strConnectionString;
}
set
{
strConnectionString = value;
}
}
// Since this is an abstract class, for better documentation and readability of source code,
// class is defined with an explicit protected constructor
protected DataAccessLayerBaseClass() {}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
private void PrepareCommand(CommandType commandType, string commandText, IDataParameter[] commandParameters)
{
// provide the specific data provider connection object, if the connection object is null
if (connection == null)
{
connection = GetDataProviderConnection();
connection.ConnectionString = this.ConnectionString;
}
// if the provided connection is not open, then open it
if (connection.State != ConnectionState.Open)
connection.Open();
// Provide the specific data provider command object, if the command object is null
if (command == null)
command = GeDataProviderCommand();
// associate the connection with the command
command.Connection = connection;
// set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
// set the command type
command.CommandType = commandType;
// if a transaction is provided, then assign it.
if (transaction != null)
command.Transaction = transaction;
// attach the command parameters if they are provided
if (commandParameters != null)
{
foreach (IDataParameter param in commandParameters)
command.Parameters.Add(param);
}
}
#endregion
#region Abstract Methods
/// <summary>
/// Data provider specific implementation for accessing relational databases.
/// </summary>
internal abstract IDbConnection GetDataProviderConnection();
/// <summary>
/// Data provider specific implementation for executing SQL statement while connected to a data source.
/// </summary>
internal abstract IDbCommand GeDataProviderCommand();
/// <summary>
/// Data provider specific implementation for filling the DataSet.
/// </summary>
internal abstract IDbDataAdapter GetDataProviderDataAdapter();
#endregion
// Generic methods implementation
#region Database Transaction
/// <summary>
/// Begins a database transaction.
/// </summary>
public void BeginTransaction()
{
if (transaction != null)
return;
try
{
// instantiate a connection object
connection = GetDataProviderConnection();
connection.ConnectionString = this.ConnectionString;
// open connection
connection.Open();
// begin a database transaction with a read committed isolation level
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
catch
{
connection.Close();
throw;
}
}
/// <summary>
/// Commits the database transaction.
/// </summary>
public void CommitTransaction()
{
if (transaction == null)
return;
try
{
// Commit transaction
transaction.Commit(); }
catch
{
// rollback transaction
RollbackTransaction();
throw; }
finally
{
connection.Close();
transaction = null;
}
}
/// <summary>
/// Rolls back a transaction from a pending state.
/// </summary>
public void RollbackTransaction()
{
if (transaction == null)
return;
try
{
transaction.Rollback();}
catch { }
finally
{
connection.Close();
transaction = null;
}
}
#endregion
#region ExecuteDataReader
/// <summary>
/// Executes the CommandText against the Connection and builds an IDataReader.
/// </summary>
public IDataReader ExecuteDataReader(string commandText)
{
return this.ExecuteDataReader(commandText, CommandType.Text, null);
}
/// <summary>
/// Executes the CommandText against the Connection and builds an IDataReader.
/// </summary>
public IDataReader ExecuteDataReader(string commandText, CommandType commandType)
{
return this.ExecuteDataReader(commandText, commandType, null);
}
/// <summary>
/// Executes a parameterized CommandText against the Connection and builds an IDataReader.
/// </summary>
public IDataReader ExecuteDataReader(string commandText, IDataParameter[] commandParameters)
{
return this.ExecuteDataReader(commandText, CommandType.Text, commandParameters);
}
/// <summary>
/// Executes a stored procedure against the Connection and builds an IDataReader.
/// </summary>
public IDataReader ExecuteDataReader(string commandText, CommandType commandType, IDataParameter[] commandParameters)
{
try
{
PrepareCommand(commandType, commandText, commandParameters);
IDataReader dr;
if (transaction == null)
// Generate the reader. CommandBehavior.CloseConnection causes the
// the connection to be closed when the reader object is closed
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
else
dr = command.ExecuteReader();
return dr;
}
catch
{
if (transaction == null)
{
connection.Close();
command.Dispose();
}
else
RollbackTransaction();
throw;
}
}
#endregion
#region ExecuteDataSet
/// <summary>
/// Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".
/// </summary>
public DataSet ExecuteDataSet(string commandText)
{
return this.ExecuteDataSet(commandText, CommandType.Text, null);
}
/// <summary>
/// Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".
/// </summary>
public DataSet ExecuteDataSet(string commandText, CommandType commandType)
{
return this.ExecuteDataSet(commandText, commandType, null);
}
/// <summary>
/// Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".
/// </summary>
public DataSet ExecuteDataSet(string commandText, IDataParameter[] commandParameters)
{
return this.ExecuteDataSet(commandText, CommandType.Text, commandParameters);
}
/// <summary>
/// Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named "Table".
/// </summary>
public DataSet ExecuteDataSet(string commandText, CommandType commandType, IDataParameter[] commandParameters)
{
try
{
PrepareCommand(commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
IDbDataAdapter da = GetDataProviderDataAdapter();
da.SelectCommand = command;
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
//return the dataset
return ds;
}
catch
{
if (transaction == null)
connection.Close();
else
RollbackTransaction();
throw;
}
}
#endregion
#region ExecuteQuery
/// <summary>
/// Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.
/// </summary>
public int ExecuteQuery(string commandText)
{
return this.ExecuteQuery(commandText, CommandType.Text, null);
}
/// <summary>
/// Executes an SQL statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.
/// </summary>
public int ExecuteQuery(string commandText, CommandType commandType)
{
return this.ExecuteQuery(commandText, commandType, null);
}
/// <summary>
/// Executes an SQL parameterized statement against the Connection object of a .NET Framework data provider, and returns the number of rows affected.
/// </summary>
public int ExecuteQuery(string commandText, IDataParameter[] commandParameters)
{
return this.ExecuteQuery(commandText, CommandType.Text, commandParameters);
}
/// <summary>
/// Executes a stored procedure against the Connection object of a .NET Framework data provider, and returns the number of rows affected.
/// </summary>
public int ExecuteQuery(string commandText, CommandType commandType, IDataParameter[] commandParameters)
{
try
{
PrepareCommand(commandType, commandText, commandParameters);
// execute command
int intAffectedRows = command.ExecuteNonQuery();
// return no of affected records
return intAffectedRows;
}
catch
{
if (transaction != null)
RollbackTransaction();
throw;
}
finally
{
if (transaction == null)
{
connection.Close();
command.Dispose();
}
}
}
#endregion
#region ExecuteScalar
/// <summary>
/// Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
/// </summary>
public object ExecuteScalar(string commandText)
{
return this.ExecuteScalar(commandText, CommandType.Text, null);
}
/// <summary>
/// Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
/// </summary>
public object ExecuteScalar(string commandText, CommandType commandType)
{
return this.ExecuteScalar(commandText, commandType, null);
}
/// <summary>
/// Executes a parameterized query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
/// </summary>
public object ExecuteScalar(string commandText, IDataParameter[] commandParameters)
{
return this.ExecuteScalar(commandText, CommandType.Text, commandParameters);
}
/// <summary>
/// Executes a stored procedure, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
/// </summary>
public object ExecuteScalar(string commandText, CommandType commandType, IDataParameter[] commandParameters)
{
try
{
PrepareCommand(commandType, commandText, commandParameters);
// execute command
object objValue = command.ExecuteScalar();
// check on value
if (objValue != DBNull.Value)
// return value
return objValue;
else
// return null instead of dbnull value
return null;
}
catch
{
if (transaction != null)
RollbackTransaction();
throw;
}
finally
{
if (transaction == null)
{
connection.Close();
command.Dispose();
}
}
}
#endregion
}
/// <summary>
/// Loads different data access layer provider depending on the configuration settings file or the caller defined data provider type.
/// </summary>
public sealed class DataAccessLayerFactory
{
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new DataAccessLayerFactory()"
private DataAccessLayerFactory() {}
/// <summary>
/// Constructs a data access layer data provider based on application configuration settings.
/// Application configuration file must contain two keys:
/// 1. "DataProviderType" key, with one of the DataProviderType enumerator.
/// 2. "ConnectionString" key, holds the database connection string.
/// </summary>
public static DataAccessLayerBaseClass GetDataAccessLayer()
{
// Make sure application configuration file contains required configuration keys
if (System.Configuration.ConfigurationSettings.AppSettings["DataProviderType"] == null
|| System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"] == null)
throw new ArgumentNullException("Please specify a 'DataProviderType' and 'ConnectionString' configuration keys in the application configuration file.");
DataProviderType dataProvider;
try
{
// try to parse the data provider type from configuration file
dataProvider =
(DataProviderType)System.Enum.Parse(typeof(DataProviderType),
System.Configuration.ConfigurationSettings.AppSettings["DataProviderType"].ToString(),
true);
}
catch
{
throw new ArgumentException("Invalid data access layer provider type.");
}
// return data access layer provider
return GetDataAccessLayer(
dataProvider,
System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
}
/// <summary>
/// Constructs a data access layer based on caller specific data provider.
/// Caller of this method must provide the database connection string through ConnectionString property.
/// </summary>
public static DataAccessLayerBaseClass GetDataAccessLayer(DataProviderType dataProviderType)
{
return GetDataAccessLayer(dataProviderType, null);
}
/// <summary>
/// Constructs a data access layer data provider.
/// </summary>
public static DataAccessLayerBaseClass GetDataAccessLayer(DataProviderType dataProviderType, string connectionString)
{
// construct specific data access provider class
switch (dataProviderType)
{
case DataProviderType.Access:
case DataProviderType.OleDb:
return new OleDbDataAccessLayer(connectionString);
case DataProviderType.Odbc:
return new OdbcDataAccessLayer(connectionString);
case DataProviderType.Oracle:
return new OracleDataAccessLayer(connectionString);
case DataProviderType.Sql:
return new SqlDataAccessLayer(connectionString);
default:
throw new ArgumentException("Invalid data access layer provider type.");
}
}
}
}
--------------------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessLayer
{
/// <summary>
/// The SQLDataAccessLayer contains the data access layer for Microsoft SQL Server.
/// This class implements the abstract methods in the DataAccessLayerBaseClass class.
/// </summary>
public class SqlDataAccessLayer : DataAccessLayerBaseClass
{
// Provide class constructors
public SqlDataAccessLayer() {}
public SqlDataAccessLayer(string connectionString) { this.ConnectionString = connectionString;}
// DataAccessLayerBaseClass Members
internal override IDbConnection GetDataProviderConnection()
{
return new SqlConnection();
}
internal override IDbCommand GeDataProviderCommand()
{
return new SqlCommand();
}
internal override IDbDataAdapter GetDataProviderDataAdapter()
{
return new SqlDataAdapter();
}
}
}
----------------------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OracleClient;
namespace DataAccessLayer
{
/// <summary>
/// The SQLDataAccessLayer contains the data access layer for Oracle data provider.
/// This class implements the abstract methods in the DataAccessLayerBaseClass class.
/// </summary>
public class OracleDataAccessLayer : DataAccessLayerBaseClass
{
// Provide class constructors
public OracleDataAccessLayer() {}
public OracleDataAccessLayer(string connectionString) { this.ConnectionString = connectionString;}
// DataAccessLayerBaseClass Members
internal override IDbConnection GetDataProviderConnection()
{
return new OracleConnection();
}
internal override IDbCommand GeDataProviderCommand()
{
return new OracleCommand();
}
internal override IDbDataAdapter GetDataProviderDataAdapter()
{
return new OracleDataAdapter();
}
}
}
------------------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OleDb;
namespace DataAccessLayer
{
/// <summary>
/// The OleDbDataAccessLayer contains the data access layer for OleDb data provider.
/// This class implements the abstract methods in the DataAccessLayerBaseClass class.
/// </summary>
public class OleDbDataAccessLayer : DataAccessLayerBaseClass
{
// Provide class constructors
public OleDbDataAccessLayer() {}
public OleDbDataAccessLayer(string connectionString) { this.ConnectionString = connectionString;}
// DataAccessLayerBaseClass Members
internal override IDbConnection GetDataProviderConnection()
{
return new OleDbConnection();
}
internal override IDbCommand GeDataProviderCommand()
{
return new OleDbCommand();
}
internal override IDbDataAdapter GetDataProviderDataAdapter()
{
return new OleDbDataAdapter();
}
}
}
--------------------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.Odbc;
namespace DataAccessLayer
{
/// <summary>
/// The SQLDataAccessLayer contains the data access layer for Odbc data provider.
/// This class implements the abstract methods in the DataAccessLayerBaseClass class.
/// </summary>
public class OdbcDataAccessLayer : DataAccessLayerBaseClass
{
// Provide class constructors
public OdbcDataAccessLayer() {}
public OdbcDataAccessLayer(string connectionString) { this.ConnectionString = connectionString;}
// DataAccessLayerBaseClass Members
internal override IDbConnection GetDataProviderConnection()
{
return new OdbcConnection();
}
internal override IDbCommand GeDataProviderCommand()
{
return new OdbcCommand();
}
internal override IDbDataAdapter GetDataProviderDataAdapter()
{
return new OdbcDataAdapter();
}
}
}
-----------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="DataProviderType" value = "sql" />
<add key="ConnectionString" value = "Server = (local); initial catalog = Northwind; user id = sa; password = ;" />
<!--add key="DataProviderType" value = "access" />
<add key="ConnectionString" value = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Development\Mine\DataAccessLayer\Northwind.mdb" /-->
</appSettings>
</configuration>
----------------------------------------------------------------------------------------------
Call method:-
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
try
{
Console.WriteLine("GoodDays...GoodHabits\nDataAccessLayerBaseClass Test");
DataAccessLayer.DataAccessLayerBaseClass dataAccess =
DataAccessLayer.DataAccessLayerFactory.GetDataAccessLayer();
Console.WriteLine("\nNorthwind customers count: ");
Console.WriteLine("Test DataAccessLayer methods with commandText");
// Test ExecuteScalar
Console.WriteLine("DataAccess.ExecuteScalar Method: "
+ dataAccess.ExecuteScalar("SELECT COUNT(*) FROM Customers"));
// Test ExecuteDataSet
DataSet ds = dataAccess.ExecuteDataSet("SELECT * FROM Customers");
Console.WriteLine("DataAccess.ExecuteDataSet Method: "
+ ds.Tables[0].Rows.Count);
// Test ExecuteDataReader
IDataReader dr = dataAccess.ExecuteDataReader("SELECT * FROM Customers");
int intCount = 0;
while (dr.Read())
intCount++;
Console.WriteLine("DataAccess.ExecuteDataReader Method: " + intCount);
dr.Close();
Console.WriteLine("\nGet [Ten Most Expensive Products]");
Console.WriteLine("Test DataAccessLayer methods calling stored procedures without parameters");
// Test ExecuteScalar
Console.WriteLine("DataAccess.ExecuteScalar Method: "
+ dataAccess.ExecuteScalar("[Ten Most Expensive Products]", CommandType.StoredProcedure));
// Test ExecuteDataSet
ds = dataAccess.ExecuteDataSet("[Ten Most Expensive Products]", CommandType.StoredProcedure);
Console.WriteLine("DataAccess.ExecuteDataSet Method: " + ds.Tables[0].Rows.Count);
// Test ExecuteDataReader
dr = dataAccess.ExecuteDataReader("[Ten Most Expensive Products]", CommandType.StoredProcedure);
intCount = 0;
while (dr.Read())
intCount++;
Console.WriteLine("DataAccess.ExecuteDataReader Method: " + intCount);
dr.Close();
// Test ExecuteQuery
Console.WriteLine("\nInsert new region in REGION table");
int intAffectedRecords =
dataAccess.ExecuteQuery("INSERT INTO REGION VALUES (5, 'DataAccess')");
Console.WriteLine("Number of affected records: " + intAffectedRecords);
Console.WriteLine("Delete newly added region from REGION table");
intAffectedRecords =
dataAccess.ExecuteQuery("DELETE FROM REGION WHERE RegionID = 5");
Console.WriteLine("Number of affected records: " + intAffectedRecords);
Console.WriteLine("\nTest Transaction");
Console.WriteLine("Begin Transaction");
dataAccess.BeginTransaction();
Console.WriteLine("Add new 5 regions in REGION table");
for (int i = 1; i <= 5; i++)
{
int intRegionId = (int)
dataAccess.ExecuteScalar("SELECT MAX(REGIONID) FROM REGION");
intRegionId++;
dataAccess.ExecuteQuery("INSERT INTO REGION VALUES(" + intRegionId
+ ", 'Region" + i + "')");
}
Console.WriteLine("Commit Transaction");
dataAccess.CommitTransaction();
// cleanup
Console.WriteLine("Delete newly added records from transaction from REGION table");
dataAccess.ExecuteQuery("DELETE FROM REGION WHERE REGIONID > 4");
// provide a specific call for MS sql data provider
if (dataAccess.GetType() == typeof(DataAccessLayer.SqlDataAccessLayer))
TestSqlParameters((DataAccessLayer.SqlDataAccessLayer)dataAccess);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
Console.WriteLine("Press enter to exit...");
Console.ReadLine();
}
private static void TestSqlParameters(DataAccessLayer.SqlDataAccessLayer dataAccess)
{
Console.WriteLine("Test a specific MS SQL server provider storedprocedure");
// create a stored procedure
try
{
dataAccess.ExecuteQuery(@"
CREATE PROCEDURE OrderSummary @MaxQuantity INT OUTPUT AS
-- SELECT to return a result set summarizing
-- employee sales.
SELECT Ord.EmployeeID, SummSales = SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
JOIN [Order Details] AS OrDet ON (Ord.OrderID = OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID
-- SELECT to fill the output parameter with the
-- maximum quantity from Order Details.
SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details]
-- Return the number of all items ordered.
RETURN (SELECT SUM(Quantity) FROM [Order Details])
");
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@MaxQuantity", SqlDbType.Int, 4),
new SqlParameter("@OrderSum", SqlDbType.Int) };
param[0].Direction = ParameterDirection.Output;
param[1].Direction = ParameterDirection.ReturnValue;
dataAccess.ExecuteQuery("OrderSummary", CommandType.StoredProcedure, param);
Console.WriteLine("The size of the largest single order was: " + param[0].Value.ToString());
Console.WriteLine("The sum of the quantities ordered was: " + param[1].Value.ToString());
}
}
|
No comments:
Post a Comment