private bool RunSqlCommands(List <string> commands)
{
bool success = true;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
foreach (string command in commands)
{
using (DbCommand dbCmd = db.GetSqlStringCommand(command))
{
db.ExecuteNonQuery(dbCmd, transaction);
}
}
transaction.Commit();
}
catch (Exception ex)
{
success = false;
Trace.WriteLine(ex.Message);
// Rollback transaction
transaction.Rollback();
}
connection.Close();
}
return(success);
}
/// <summary>
/// 数据库删除（使用微软企业库）
/// </summary>
/// <param name="db"></param>
/// <param name="pdata"></param>
/// <returns></returns>
public static int DeleteById(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object pdata)
{
//var db = DBFactory.CreateDefault();
int n = 0; //影响的行数
PropertyInfo[] pro = pdata.GetType().GetProperties(); //获取传来的对象的属性
List <PropertyInfo> idlist = GetIdProperty(pro); //找到主键属性
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
string table = FindPropertyInfoValue(pdata, "TableName").ToString();
string sqltext = string.Empty;
sql.AppendFormat("delete from {0} where", table);
foreach (PropertyInfo item in idlist)
{
sql.Append(" " + item.Name + " = '" + item.GetValue(pdata, null) + "'");
}
sqltext = sql.ToString();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
n = cmd.ExecuteNonQuery();
}
}
return(n);
}
public void BeginTransaction()
{
_db = new SqlDatabase(_strConnection);
_dbConn = _db.CreateConnection();
_dbConn.Open();
_dbTrans = _dbConn.BeginTransaction();
}
public DbTransaction BeginTransaction()
{
_db = DatabaseFactory.CreateDatabase();
_connection = _db.CreateConnection();
_connection.Open();
_transaction = _connection.BeginTransaction();
return _transaction;
}
/// <summary>
/// 获取指定表名的架构信息
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataTable GetSchema(Database db, string tableName)
{
DbDataAdapter ap = db.GetDataAdapter();
DbCommand cmd = db.GetSqlStringCommand(string.Format("SELECT * FROM {0}", tableName));
cmd.Connection = db.CreateConnection();
ap.SelectCommand = cmd;
DataTable t = new DataTable();
ap.FillSchema(t, SchemaType.Mapped);
return t;
}
public object ExecuteScalar(string query, CommandType commandType, List <IDbDataParameter> parameters)
{
DbConnection _connection;
_connection = _database.CreateConnection();
_connection.Open();
var cmd = commandType == CommandType.StoredProcedure ? _database.GetStoredProcCommand(query) : _database.GetSqlStringCommand(query);
cmd.CommandTimeout = 300;
cmd.Connection = _connection;
using (cmd)
{
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
return(_database.ExecuteScalar(cmd));
}
}
public void OpenConnection()
{
if (Connection == null)
{
Connection = _database.CreateConnection();
}
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
}
public void TestInitialize()
{
EnvironmentHelper.AssertOracleClientIsInstalled();
DatabaseProviderFactory factory = new DatabaseProviderFactory(OracleTestConfigurationSource.CreateConfigurationSource());
db = factory.Create("OracleTest");
storedProcedure = db.GetStoredProcCommand("NWND_CustOrdersOrders");
connection = db.CreateConnection();
connection.Open();
storedProcedure.Connection = connection;
cache = new ParameterCache();
baseFixture = new ParameterDiscoveryFixture(storedProcedure);
}
/// <summary>
/// 数据库主键查询（使用微软企业库）
/// </summary>
/// <param name="pdata"></param>
/// <returns></returns>
public static object GetById(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object pdata)
{
//var db = DBFactory.CreateDefault();
PropertyInfo[] pro = pdata.GetType().GetProperties();//获取传来的对象的属性
List <PropertyInfo> idlist = GetIdProperty(pro);
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
string table = FindPropertyInfoValue(pdata, "TableName").ToString();
string sqltext = string.Empty;
sql.AppendFormat("select * from {0} where", table);
foreach (PropertyInfo item in idlist)
{
if (item.Name == "TableName")
{
continue;
}
else
{
sql.Append(" " + item.Name + " = '" + item.GetValue(pdata, null) + "'");
}
}
sqltext = sql.ToString();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
DbDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
dr.Read();
foreach (PropertyInfo item in pro)
{
if (item.Name == "TableName")
{
continue;
}
else
{
if (string.IsNullOrEmpty(dr[item.Name].ToString()))
{
continue;
}
item.SetValue(pdata, dr[item.Name], null);
}
}
}
}
return(pdata);
}
/// <summary>
/// 数据库更新方法（使用微软企业库）
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static int Update(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object obj)
{
//var db = DBFactory.CreateDefault();
int n = 0;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
//获取对象的属性数组
PropertyInfo[] pro = obj.GetType().GetProperties();
//主键属性数组
List <PropertyInfo> idlist = GetIdProperty(pro);
//要更新的数据表
string table = FindPropertyInfoValue(obj, "TableName").ToString();
//执行的sql语句
string sqltext = string.Empty;
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 and 列2=某值
sql.Append("UPDATE " + table + " set");
sqlend.Append("WHERE");
//拼接sql语句主体
foreach (PropertyInfo item in pro)
{
if (item.Name == "TableName")
{
continue;
}
else
{
sql.Append(" " + item.Name + "= " + item.GetValue(obj, null));
}
}
//根据主键增加定位条件
foreach (PropertyInfo item in idlist)
{
sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + ", and ");
}
string start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
string end = sqlend.ToString();
end = end.Substring(0, end.Length - 5) + " ";
sqltext = start + end;
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
n = cmd.ExecuteNonQuery();
}
}
return(n);
}
public DbConnection OpenAndGetConnection(string ConfigConnectionDB)
{
#region
try
{
USEDB = DatabaseFactory.CreateDatabase(ConfigConnectionDB);
ConnectionDB = ConfigConnectionDB;
Conn = USEDB.CreateConnection();
Conn.Open();
return Conn;
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
/// <summary>
/// 通用保存（带前缀、限制_isIDENTITY_INSERT_OFF的单表获取）
/// 示例（所有表保存）：DAOHelp.UniversalizationSave(CommonApp.DBFactory.CreateDefault(), res, "[SLMSDB_I].[dbo].", "", true, "ID");
/// 示例（指定表保存）：DAOHelp.UniversalizationSave(CommonApp.DBFactory.CreateDefault(), res, "[SLMSDB_I].[dbo].", "SampleInfo", true, "ID");
/// </summary>
/// <param name="db">Database db = CommonApp.DBFactory.CreateDefault();</param>
/// <param name="_model">model载具</param>
/// <param name="_tablenameprefix">如果数据库表有限定开头(如[Common].[table1])，则必须加上前缀；没有前缀则留空（注意本处留空不能让所有表保存）</param>
/// <param name="_tablenameNoprefix">指定表保存(注意这里是数据集的table，无前缀无[]),注意本处留空则所有表保存</param>
/// <param name="_isIDENTITY_INSERT_OFF">是否关闭主键列插入功能（插入使用，一般为ID）</param>
/// <param name="_identity">显式指定主键名称（_isIDENTITY_INSERT_OFF为true时，与其一起使用）</param>
/// <returns></returns>
public static int UniversalizationSave(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object _model,
string _tablenameprefix, string _tablenameNoprefix, bool _isIDENTITY_INSERT_OFF, string _identity)
{
using (var conn = db.CreateConnection())
{
conn.Open();
var trans = conn.BeginTransaction();
try
{
if (string.IsNullOrEmpty(_tablenameNoprefix)) //_table留空，保存全部
{
var tables = _model.GetType().GetProperties(); //获取表集合
foreach (var t in tables)
{
var eltype = t.ToString().Split(' ')[0];
if (!eltype.Contains("DataTable"))
{
continue;
}
if (eltype.Substring(eltype.Length - 9) != "DataTable")
{
continue;
}
var table = t.GetValue(_model, null) as DataTable;//获取集合中的元素
if (table == null)
{
continue;
}
for (var i = 0; i < table.Rows.Count; i++)
{
//逐行执行
if (table.Rows[i].RowState == DataRowState.Added)
{
UniversalizationInsert(db, table.Rows[i], _tablenameprefix + t.Name,
_isIDENTITY_INSERT_OFF, _identity);
continue;
}
if (table.Rows[i].RowState == DataRowState.Modified)
{
UniversalizationUpdate(db, table.Rows[i], _tablenameprefix + t.Name, _identity);
continue;
}
if (table.Rows[i].RowState == DataRowState.Deleted)
{
UniversalizationDelete(db, table.Rows[i], _tablenameprefix + t.Name, _identity);
continue;
}
//trans.Commit();
}
}
}
else //_table不留空保存指定表
{
var tables = _model.GetType().GetProperties();//获取集合
DataTable table = null;
foreach (var info in tables)
{
if (info.Name == _tablenameNoprefix)
{
table = info.GetValue(_model, null) as DataTable;//获取集合中的元素
}
}
if (table == null)
{
return(0);
}
for (var i = 0; i < table.Rows.Count; i++)
{
//逐行执行
if (table.Rows[i].RowState == DataRowState.Added)
{
UniversalizationInsert(db, table.Rows[i], _tablenameprefix + _tablenameNoprefix,
_isIDENTITY_INSERT_OFF, _identity);
continue;
}
if (table.Rows[i].RowState == DataRowState.Modified)
{
UniversalizationUpdate(db, table.Rows[i], _tablenameprefix + _tablenameNoprefix, _identity);
continue;
}
if (table.Rows[i].RowState == DataRowState.Deleted)
{
UniversalizationDelete(db, table.Rows[i], _tablenameprefix + _tablenameNoprefix, _identity);
continue;
}
//trans.Commit();
}
}
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return(0);
}
public DAReportPurchase()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public DAReceivingGoods()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
DbCommand mDbCommand = null; //定义Command
#endregion
#region 构造函数
public h(string configuration_connectionstring_node_name)
{
mDatabase = DatabaseFactory.CreateDatabase(configuration_connectionstring_node_name);
Connection = mDatabase.CreateConnection();
}
/// <summary>
/// 执行SQL语句事务，返回影响的记录数
/// </summary>
/// <param name="dc">查询语句</param>
/// <param name="db">操作目标数据库</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlTrans(DbCommand dc, Database db)
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
int rows = 0;
try
{
PrepareCommand(ref dc, db);
rows = db.ExecuteNonQuery(dc);
transaction.Commit();
}
catch (System.Exception e)
{
transaction.Rollback();
throw new Exception(e.Message);
}
connection.Close();
return rows;
}
}
}
public DAOutputGoods()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public static DataSet UpdateDataTable(
Database db,
DataSet ds,
string tablename,
string insert_sp_name, string[] insert_columns,
string update_sp_name, string[] update_columns,
string delete_sp_name, string[] delete_columns,
RowStatus rowstate)
{
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = SqlHelper.CreateCommand((SqlConnection)db.CreateConnection(), insert_sp_name, insert_columns);
da.UpdateCommand = SqlHelper.CreateCommand((SqlConnection)db.CreateConnection(), update_sp_name, update_columns);
da.DeleteCommand = SqlHelper.CreateCommand((SqlConnection)db.CreateConnection(), delete_sp_name, delete_columns);
if (rowstate == RowStatus.None)
da.Update(ds.Tables[tablename]);
else
if (ds.Tables[tablename].Select("", "", (DataViewRowState)Convert.ToInt32(rowstate)).Length > 0)
{
da.Update(ds.Tables[tablename].Select("", "", (DataViewRowState)Convert.ToInt32(rowstate)));
}
return ds;
}
/// <summary>
/// Opens the connection.
/// </summary>
protected void OpenConnection()
{
db = DatabaseFactory.CreateDatabase("MyGym");
connection = db.CreateConnection();
connection.Open();
}
public SQLWrapper()
{
db = DatabaseFactory.CreateDatabase(ConfigReader.ActiveConnectionStringKey);
conn = db.CreateConnection();
trans = null;
}
/// <summary>
/// 创建并返回与当前 <see cref="DbProviderFactory"/> 关联的 <see cref="DbConnection"/> 对象。
/// </summary>
/// <param name="database">表示当前 <see cref="Microsoft.Practices.EnterpriseLibrary.Data.Database"/> 对象。</param>
/// <returns>与当前 <see cref="DbProviderFactory"/> 关联的 <see cref="DbConnection"/> 对象。</returns>
public static DbConnection CreateConnection(Database database)
{
return database.CreateConnection();
}
public DAWarehouse()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public DAManagement()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
/// <summary>
/// 执行SQL语句事务，返回查询结果(object)。
/// </summary>
/// <param name="dc">查询语句</param>
/// <param name="db">操作目标数据库</param>
/// <returns>查询结果(object)</returns>
public static object GetSingleBySqlTrans(DbCommand dc, Database db)
{
object obj;
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
try
{
PrepareCommand(ref dc, db);
obj = db.ExecuteScalar(dc);
transaction.Commit();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
obj = null;
}
}
catch (System.Exception e)
{
transaction.Rollback();
throw new Exception(e.Message);
}
connection.Close();
}
}
return obj;
}
public DATransferGoods()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public void SetConnection()
{
_db = DatabaseFactory.CreateDatabase();
_connection = _db.CreateConnection();
}
public DAUbigeo()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
/// <summary>
/// 构造
/// </summary>
/// <param name="db">数据库</param>
public DataTransaction(Database db)
{
_db = db;
IDbConnection dbConn = db.CreateConnection();
SetConnectionMode(dbConn);
dbConn.Dispose();
}
public DAStockInventory()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public DASales()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
odb = factory.Create("PS");
ocn = odb.CreateConnection();
}
public DASecurity()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public DAModelArticle()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
odb = factory.Create("PS");
ocn = odb.CreateConnection();
}
public DAGenerics()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
/// <summary>
/// DAAB Database에서 새로운 Connection을 생성하고 Open 합니다.
/// </summary>
/// <param name="db"></param>
/// <param name="tryCount"></param>
/// <returns></returns>
private static DbConnection OpenConnection(Database db, int tryCount = 5) {
DbConnection connection = null;
for(var i = 0; i < tryCount; i++) {
try {
connection = db.CreateConnection();
connection.Open();
if(connection.State == ConnectionState.Open)
return connection;
}
catch(Exception ex) {
if(log.IsErrorEnabled) {
log.Error("Database Connection 생성 및 Open 수행 시에 예외가 발생했습니다. 시도횟수=[{0}], ConnectionString=[{1}]", i + 1,
db.ConnectionString);
log.Error(ex);
}
Thread.Sleep(5 * (i + 1));
}
}
Guard.Assert(connection != null && connection.State != ConnectionState.Closed, "Connection을 생성하지 못했습니다!!!");
return connection;
}
public DAReportPurchase()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
odb = factory.Create("PS");
ocn = odb.CreateConnection();
}
/// <summary>
/// 数据库的插入或更新的方法（使用微软企业库）
/// </summary>
/// <param name="obj">要存入数据库的对象</param>
/// <returns></returns>
public static int Insert(Microsoft.Practices.EnterpriseLibrary.Data.Database db, object obj)
{
//var db = DBFactory.CreateDefault();
var n = 0;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
//获取对象的属性数组
PropertyInfo[] pro = obj.GetType().GetProperties();
//主键属性数组
List <PropertyInfo> idlist = GetIdProperty(pro);
//要更新的数据表
string table = FindPropertyInfoValue(obj, "TableName").ToString();
//执行的sql语句
string sqltext = string.Empty;
//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
sql.Append("INSERT INTO " + table + "(");
sqlend.Append(" VALUES (");
foreach (PropertyInfo item in pro)
{//拼接sql语句主体
if (item.Name == "TableName")
{
continue;
}
else
{
string columnValue = item.GetValue(obj, null) + "";
if (string.IsNullOrEmpty(columnValue))
{//去掉空属性
continue;
}
if (item.PropertyType == typeof(DateTime))
{//时间属性初始化时未赋值会变为默认最小值
DateTime dt;
DateTime.TryParse(columnValue, out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + item.Name + ",");
sqlend.Append(" '" + columnValue + "',");
}
}
string start = sql.ToString();
start = start.Substring(0, start.Length - 1) + ")";
string end = sqlend.ToString();
end = end.Substring(0, end.Length - 1) + ")";
sqltext = start + end;
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqltext;
n = cmd.ExecuteNonQuery();
}
}
return(n);
}
public DAReceivingGoods()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
odb = factory.Create("PS");
ocn = odb.CreateConnection();
}
public DAModelArticle()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
public DATypeReason()
{
odb = DatabaseFactory.CreateDatabase("PS");
ocn = odb.CreateConnection();
}
