Jay Nathan's Weblog
Thursday, June 03, 2004
 
Base Class for Creating Table Data Gateway Classes for .NET
One of the more interesting challenges of moving to .NET from VB6 is deciding to do with all of the object oriented features of a CLS-compliant language such as C# or VB.NET. When it comes to defining the architecture of your next .NET application, there are many things that must be considered, not the least of which is how you are going access relational data in a clean, consistent manner. Here is a generic SQL data provider that I have built to be subclassed by specific data providers that you may need within your application. For instance I could create an OrderDataProvider class that implements all of the necessary database interaction features that an Order class may need to use. Depending on the size of your application, you could even create one data provider class for all of your data interaction.

I used the SqlHelper Application Block from Microsoft within this class. A nice thing to be able to do would be to switch out the data provider on the back end of the domain specific data access classes (to facilitate changing the back-end database). I've also built an OracleDataProvider and an OleDbDataProvider base class that are basically exactly the same as the SqlDataProvider class.

Any and all feedback as to this method is more than welcome, so feel free to leave your comments on this. Here's the code, enjoy!


using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace JNathan.Data.Sql
{
///
/// Summary description for DataProvider.
///

public abstract class SqlDataProvider : IDisposable
{
public SqlDataProvider()
{
this.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectString"];
}


private string connectionString = String.Empty;
protected string ConnectionString
{
set{this.connectionString = value;}
get{return this.connectionString;}
}


#region Common

protected SqlConnection Connection = null;
public SqlConnection Open()
{
try
{
Connection = new SqlConnection(this.ConnectionString);
Connection.Open();

return Connection;
}
catch(Exception exp)
{
throw exp;
}
}


SqlTransaction Transaction = null;
public SqlTransaction BeginTransaction()
{
if(Connection == null)
this.Connection = new SqlConnection(this.ConnectionString);

if(Connection.State != ConnectionState.Open)
this.Connection.Open();

try
{
this.Transaction = Connection.BeginTransaction();
}
catch(Exception e)
{
throw e;
}

return Transaction;
}


public void CommitTransaction()
{
if(this.Transaction != null)
this.Transaction.Commit();
}


public void RollbackTransaction()
{
if(this.Transaction != null)
this.Transaction.Rollback();
}


public void Close()
{
this.CleanUp();
}


protected void CleanUp()
{
if(this.Connection != null)
{
if(this.Connection.State != ConnectionState.Closed)
this.Connection.Close();

this.Connection.Dispose();
}
}


#endregion


#region ExecutionMethods

protected DataSet ExecuteDataSet(string sql)
{
if(this.Transaction != null)
return SqlHelper.ExecuteDataset(this.Transaction, CommandType.Text, sql);
else if(this.Connection != null)
return SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql);
else
return SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sql);
}


protected SqlDataReader ExecuteReader(string sql)
{
if(this.Transaction != null)
return SqlHelper.ExecuteReader(this.Transaction, CommandType.Text, sql);
else if(this.Connection != null)
return SqlHelper.ExecuteReader(this.Connection, CommandType.Text, sql);
else
return SqlHelper.ExecuteReader(this.connectionString, CommandType.Text, sql);
}


protected SqlDataReader ExecuteReader(string commandText, CommandType commandType, SqlParameter[] parameters)
{
if(this.Transaction != null)
return SqlHelper.ExecuteReader(this.Transaction, commandType, commandText, parameters);
else if(this.Connection != null)
return SqlHelper.ExecuteReader(this.Connection, commandType, commandText, parameters);
else
return SqlHelper.ExecuteReader(this.connectionString, commandType, commandText, parameters);
}


protected int ExecuteNonQuery(string sql)
{
if(this.Transaction != null)
return SqlHelper.ExecuteNonQuery(this.Transaction, CommandType.Text, sql);
else if(this.Connection != null)
return SqlHelper.ExecuteNonQuery(this.Connection, CommandType.Text, sql);
else
return SqlHelper.ExecuteNonQuery(this.connectionString, CommandType.Text, sql);
}


protected int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters)
{
if(this.Transaction != null)
return SqlHelper.ExecuteNonQuery(this.Transaction, commandType, commandText, parameters);
else if(this.Connection != null)
return SqlHelper.ExecuteNonQuery(this.Connection, commandType, commandText, parameters);
else
return SqlHelper.ExecuteNonQuery(this.connectionString, commandType, commandText, parameters);
}


protected object ExecuteScalar(string sql)
{
if(this.Transaction != null)
return SqlHelper.ExecuteScalar(this.Transaction, CommandType.Text, sql);
else if(this.Connection != null)
return SqlHelper.ExecuteScalar(this.Connection, CommandType.Text, sql);
else
return SqlHelper.ExecuteScalar(this.ConnectionString, CommandType.Text, sql);
}


protected object ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] parameters)
{
if(this.Transaction != null)
return SqlHelper.ExecuteScalar(this.Transaction, commandType, commandText, parameters);
else if(this.Connection != null)
return SqlHelper.ExecuteScalar(this.Connection, commandType, commandText, parameters);
else
return SqlHelper.ExecuteScalar(this.ConnectionString, commandType, commandText, parameters);
}


#endregion

#region IDisposable Members

public void Dispose()
{
System.GC.SuppressFinalize(this);
this.Close();
}


#endregion
}
}


Comments:
Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
 
Post a Comment

<< Home

Powered by Blogger