単純ファクトリ・モードでデータ・アクセス・レイヤを構築し、複数のデータベース間の切り替えを実現
1、新しいデータベースアクセスベースDBHandler
///
///
///
public abstract class DBHandler
{
public DBHandler() { }
#region
protected DbConnection dbConnection = null; //
protected DbTransaction dbTransaction = null; //
protected abstract DbCommand CreateCommand(); // DbCommand
protected abstract DbDataAdapter CreateAdapter(); // DbDataAdapter
protected abstract void BuilderCommand(DbDataAdapter adapter); // Update ， DbDataAdapter UpdateCommand/InsertCommand/DeleteCommand
protected abstract int GetTotalCount(); // ，
#endregion
#region
protected List parameters = new List();
protected bool IsInTransaction = false; //
// ， SQL
protected void CheckPageSQL()
{
this.CommandType = CommandType.Text;
if (!this.CommandText.StartsWith("select", true, null))
{
throw new Exception("sql select ");
}
if (IsInTransaction)
{
throw new Exception(" ");
}
}
#endregion
#region
///
/// sql
///
public string CommandText { get; set; }
///
/// SQL
///
public CommandType CommandType { get; set; }
///
/// ， List
///
///
///
public void AddParameter(string paraName, string paraValue)
{
this.parameters.Add(new Parameter(paraName,paraValue));
}
///
///
///
public void ClearParameter()
{
this.parameters.Clear();
}
///
/// ，
///
public int TotalCount
{
get
{
return this.GetTotalCount();
}
}
#endregion
#region
///
///
///
///
public object ExecuteScalar()
{
try
{
if (dbConnection.State != ConnectionState.Open)
{
dbConnection.Open();
}
DbCommand cmd = this.CreateCommand();
object r = cmd.ExecuteScalar();
if (!this.IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
this.dbConnection.Close();
throw new Exception(ex.Message);
}
}
///
/// Update/Delete/Insert
///
///
public int ExecuteNonQuery()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbCommand cmd = this.CreateCommand();
int r = cmd.ExecuteNonQuery();
if (!IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
dbConnection.Close();
throw new Exception(ex.Message);
}
}
///
/// DataTable
///
///
public DataTable ExecuteDataTable()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
DataTable dt = new DataTable();
adapter.FillSchema(dt, SchemaType.Mapped);
adapter.Fill(dt);
if (!IsInTransaction)
{
dbConnection.Close();
}
return dt;
}
catch (Exception ex)
{
dbConnection.Close();
throw new Exception(ex.Message);
}
}
///
/// ，
///
///
///
///
public abstract DataTable ExecuteDataTable(int pageSize,int currentPageIndex);
#endregion
#region DataTable
public int UpdateData(DataTable dt)
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
if (this.CommandType == CommandType.StoredProcedure)
{
this.CommandType = CommandType.Text;
}
this.BuilderCommand(adapter);
int r = adapter.Update(dt);
if (!IsInTransaction)
{
dbConnection.Close();
}
return r;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region
///
///
///
public void BegionTransaction()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
this.dbConnection.BeginTransaction();
this.IsInTransaction = true;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
///
///
///
public void RollbackTransaction()
{
try
{
this.dbTransaction.Rollback();
this.dbConnection.Close();
this.IsInTransaction = false;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
///
///
///
public void CommitTransaction()
{
try
{
this.dbTransaction.Commit();
this.dbConnection.Close();
this.IsInTransaction = false;
}
catch (Exception ex)
{
this.dbConnection.Close();
this.IsInTransaction = false;
throw ex;
}
}
#endregion
#region
#endregion
#region
///
/// ， oracle ， System_Sequence ， （Name(nvarchar,50),Value(int))
///
///
///
public abstract int GetSequenceValue(string sequenceName);
#endregion
} public class Parameter
{
public string Name = string.Empty;
public object Value = null;
public Parameter(string name,string value)
{
this.Name = name;
this.Value = value;
}
} internal class DBhANDlerSQLServer:DBHandler
{
public DBhANDlerSQLServer(string connectionString)
: base()
{
this.dbConnection = new SqlConnection(connectionString);
}
protected override DbCommand CreateCommand()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = (SqlConnection)this.dbConnection;
if (this.IsInTransaction)
{
cmd.Transaction = (SqlTransaction)this.dbTransaction;
}
if (this.CommandType == CommandType.TableDirect)
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format("select * from {0}", this.CommandText);
}
else
{
cmd.CommandType=CommandType;
cmd.CommandText = CommandText;
}
if (this.parameters.Count > 0)
{
foreach (Parameter p in parameters)
{
cmd.Parameters.AddWithValue(p.Name, p.Value);
}
}
return cmd;
}
protected override DbDataAdapter CreateAdapter()
{
SqlCommand cmd = (SqlCommand)this.CreateCommand();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
return adapter;
}
protected override void BuilderCommand(DbDataAdapter adapter)
{
new SqlCommandBuilder((SqlDataAdapter)adapter);
}
protected override int GetTotalCount()
{
this.CheckPageSQL();
string sql = this.CommandText; // SQL
string sqlWithOutOrderField = string.Empty; // SQL order by ，
int startIndex = sql.LastIndexOf("order by");
if (startIndex >= 0)
{
sqlWithOutOrderField = sql.Substring(0, startIndex);
}
else
{
sqlWithOutOrderField = sql;
}
this.CommandText = string.Format("select count(*）from ({0}) t1",sqlWithOutOrderField);
int r = int.Parse(this.ExecuteScalar().ToString());
this.CommandText = sql;
return r;
}
public override DataTable ExecuteDataTable(int pageSize, int currentPageIndex)
{
this.CheckPageSQL();
string sql = this.CommandText; // SQL
string orderBy = string.Empty; // order by
string sqlWithSelectAndOrder = sql.Substring(6); // select order by
int startIndex = sqlWithSelectAndOrder.ToLower().LastIndexOf("order by");
if (startIndex > 0)
{
orderBy = sqlWithSelectAndOrder.Substring(startIndex);
sqlWithSelectAndOrder = sqlWithSelectAndOrder.Substring(0, startIndex);
}
else
{
throw new Exception("sql order by");
}
if (pageSize == 0) //
{
this.CommandText = sql;
this.ExecuteDataTable();
}
DataTable dt = new DataTable();
if (currentPageIndex == 1) // 1
{
this.CommandText = string.Format("select top {0} {1} {2}", pageSize, sqlWithSelectAndOrder, orderBy);
dt = this.ExecuteDataTable();
}
else // sqlserver2005 ， order by
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from ");
sb.AppendFormat("(select Row_Number() over ({0}) as RowNum,{1})t1",orderBy,sqlWithSelectAndOrder);
sb.AppendFormat(" where RowNum between {0} and {1}",pageSize*(currentPageIndex-1),pageSize*currentPageIndex-1);
this.CommandText = sb.ToString();
dt = this.ExecuteDataTable();
}
this.CommandText = sql;
return dt;
}
public override int GetSequenceValue(string sequenceName)
{
//sqlserver System_Sequence
/* ：if not exists(select * from sysobjects where Name='System_Sequence')
create table System_Sequence
{
Name varchar(50),
Value int,
constraint "PK_SsystemSequence" primary key(Name)
};
insert into System_Sequence(Name,Value) values('Sequence_',0); */
this.CommandType = CommandType.Text;
this.BegionTransaction();
this.CommandText = string.Format("Update System_Sequence set Value=Value+1 where Name='{0}'", sequenceName);
this.ExecuteNonQuery();
this.CommandText = string.Format("select Value from System_Sequence where Name='{0}'", sequenceName);
int r = this.ExecuteNonQuery();
this.CommitTransaction();
return r;
}
} public enum DatabaseType
{
SqlServer = 1,
Oracle = 2,
ODBC = 3,
OLEDB = 4
}
public class DBHandlerFactory
{
//
private DBHandlerFactory() { }
///
/// webconfig ConnectionString
///
///
///
public static DBHandler GetHandler(string connStr)
{
ConnectionStringSettings ccs = ConfigurationManager.ConnectionStrings[connStr];
string providerName = ccs.ProviderName.ToLower();
DatabaseType dbType = DatabaseType.SqlServer;
switch (providerName)
{
case "":
case "sqlserver":
case "system.data.sqlclient":
dbType = DatabaseType.SqlServer;
break;
case "oracle":
case"system.data.oracleclient":
dbType = DatabaseType.Oracle;
break;
case "odbc":
case "system.data.odbc":
dbType = DatabaseType.ODBC;
break;
case "oledb":
case "system.data.oledb":
dbType = DatabaseType.OLEDB;
break;
default:
throw new Exception(" ProviderName ");
}
switch (dbType)
{
case DatabaseType.SqlServer:
return new DBhANDlerSQLServer(ccs.ConnectionString);
case DatabaseType.Oracle:
return null;
case DatabaseType.ODBC:
return null;
case DatabaseType.OLEDB:
return null;
default:
return null;
}
}
///
///
///
///
///
///
public static DBHandler GetHandler(string connStr,DatabaseType dbType)
{
switch (dbType)
{
case DatabaseType.SqlServer:
return new DBhANDlerSQLServer(connStr);
case DatabaseType.Oracle:
return null;
case DatabaseType.ODBC:
return null;
case DatabaseType.OLEDB:
return null;
default:
return null;
}
}
}