private void AutoProcess(DbTransaction transaction, DataTable dataTable, ExistingItemProcessType processType)
{
if (string.IsNullOrEmpty(dataTable.TableName))
{
throw new CustomDataException("表名为空");
}
if (dataTable.Rows.Count == 0)
{
return;
}
string sql = string.Empty;
bool owerConnection = transaction == null;
DbConnection conn = null;
DbTransaction tran = null;
try
{
ConstraintCollection ucList = GetSqlTableConstraint(dataTable.TableName);
if (ucList.Count == 0)
{
AutoInsert(null, dataTable);
return;
}
if (owerConnection)
{
conn = CreateConnection();
conn.Open();
tran = conn.BeginTransaction();
}
else
{
conn = transaction.Connection;
tran = transaction;
}
//sql = "select * from " + dt.TableName;
//DataSet ds = GetDataSet(tran, sql);
sql = "select count(*) from " + dataTable.TableName + " where ";
Dictionary <string, bool> keyColumnList = new Dictionary <string, bool>();
foreach (UniqueConstraint uc in ucList)
{
if (uc == null || uc.Columns.Length == 0)
{
continue;
}
foreach (DataColumn dc in uc.Columns)
{
keyColumnList[dc.ColumnName] = true;
}
}
int idx = 0;
foreach (string strColumnName in keyColumnList.Keys)
{
if (idx > 0)
{
sql += " and ";
}
sql += strColumnName + "= @" + strColumnName;
++idx;
}
DataSet dsTable = GetSqlTableInfo(dataTable.TableName);
DataTable dtInsert = dataTable.Clone();
DataTable dtExist = dataTable.Clone();
foreach (DataRow dr in dataTable.Rows)
{
DbParameter[] pList = new DbParameter[keyColumnList.Keys.Count];
idx = 0;
foreach (DataRow dr2 in dsTable.Tables[0].Rows)
{
if (keyColumnList.ContainsKey(dr2["cname"].ToString()))
{
pList[idx] = DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + dr2["cname"].ToString(), SqlServerDbTypeWrapper.WrapSqlDbType(dr2["coltype"].ToString()), Convert.ToInt32(dr2["width"]), dr[dr2["cname"].ToString()]);
++idx;
}
}
bool bContain = Convert.ToInt32(ExecuteScalar(tran, CommandType.Text, sql, pList)) > 0;
if (!bContain)
{
dtInsert.ImportRow(dr);
}
else
{
dtExist.ImportRow(dr);
}
}
if (dtInsert.Rows.Count > 0)
{
AutoInsert(tran, dtInsert);
}
if (processType == ExistingItemProcessType.Update && dtExist.Rows.Count > 0)
{
AutoUpdate(tran, dtExist);
}
if (owerConnection)
{
tran.Commit();
}
}
catch (System.Exception ex)
{
if (owerConnection)
{
tran.Rollback();
}
throw new CustomDataException(ex, sql);
}
finally
{
if (owerConnection && conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// 自动更新表
/// </summary>
/// <param name="transaction"></param>
/// <param name="dataTable"></param>
public void AutoUpdate(DbTransaction transaction, DataTable dataTable)
{
DataSet dsTable = GetSqlTableInfo(dataTable.TableName);
if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0)
{
throw new CustomDataException("未找到 " + dataTable.TableName + " 表的信息");
}
ConstraintCollection ucList = GetSqlTableConstraint(dataTable.TableName);
if (ucList.Count == 0)
{
throw new CustomDataException("表 " + dataTable.TableName + " 没有主键，无法自动更新");
}
Dictionary <string, bool> keyTable = new Dictionary <string, bool>();
foreach (UniqueConstraint uc in ucList)
{
if (uc == null || uc.Columns.Length == 0)
{
continue;
}
foreach (DataColumn dc in uc.Columns)
{
keyTable[dc.ColumnName] = true;
}
}
StringBuilder sb = new StringBuilder();
sb.Append("update ");
sb.Append(dataTable.TableName);
int idx = 0;
foreach (DataRow drTable1 in dsTable.Tables[0].Rows)
{
string cName = drTable1["cname"].ToString();
if (dataTable.Columns.Contains(cName) && !keyTable.ContainsKey(cName))
{
if (idx == 0)
{
sb.Append(" set ");
}
else
{
sb.Append(", ");
}
sb.Append(cName);
sb.Append(" = @");
sb.Append(cName);
++idx;
}
}
if (idx == 0)
{
return;
}
idx = 0;
foreach (UniqueConstraint uc in ucList)
{
if (uc == null || uc.Columns.Length == 0)
{
continue;
}
foreach (DataColumn dc in uc.Columns)
{
if (idx == 0)
{
sb.Append(" where ");
}
else
{
sb.Append(" and ");
}
sb.Append(dc.ColumnName);
sb.Append(" = @");
sb.Append(dc.ColumnName);
++idx;
}
}
string sql = sb.ToString();
DbParameter[] parameters = null;
try
{
foreach (DataRow dr in dataTable.Rows)
{
List <DbParameter> paramList = new List <DbParameter>();
foreach (DataRow drTable in dsTable.Tables[0].Rows)
{
string cName2 = drTable["cname"].ToString();
if (!dataTable.Columns.Contains(cName2))
{
continue;
}
DbType dbt = SqlServerDbTypeWrapper.WrapSqlDbType(drTable["coltype"].ToString());
object objValue = dr.IsNull(cName2) ? null : dr[cName2];
paramList.Add(DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + cName2, dbt, Convert.ToInt32(drTable["width"]), objValue));
}
parameters = paramList.ToArray();
IDbOperation db = new SqlServerExecute();
if (transaction == null)
{
db.ExecuteNonQuery(sql, parameters);
}
else
{
db.ExecuteNonQuery(transaction, sql, parameters);
}
}
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql, parameters);
}
}
/// <summary>
/// 将源表中的数据插入到表中(含事务)
/// </summary>
/// <param name="transaction">事务</param>
/// <param name="dt">表名</param>
public void AutoInsert(DbTransaction transaction, DataTable dt)
{
DataSet dataSet = GetSqlTableInfo(dt.TableName);
if (null == dataSet || dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
{
throw new CustomDataException("未找到 " + dt.TableName + " 表的信息");
}
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
foreach (DataRow drTable1 in dataSet.Tables[0].Rows)
{
string cName = drTable1["cname"].ToString();
if (dt.Columns.Contains(cName))
{
if (sb1.Length > 0)
{
sb1.Append(", ");
sb2.Append(", ");
}
sb1.Append(cName);
sb2.Append("@" + cName);
}
}
if (sb1.Capacity == 0)
{
return;
}
string sql = "insert into " + dt.TableName + " (" + sb1.ToString() + ") values (" + sb2.ToString() + ")";
DbParameter[] parameters = null;
try
{
foreach (DataRow dr in dt.Rows)
{
List <DbParameter> paramList = new List <DbParameter>();
foreach (DataRow drTable in dataSet.Tables[0].Rows)
{
string cName2 = drTable["cname"].ToString();
if (!dt.Columns.Contains(cName2))
{
continue;
}
DbType dbt = SqlServerDbTypeWrapper.WrapSqlDbType(drTable["coltype"].ToString());
object objValue = dr.IsNull(cName2) ? null : dr[cName2];
paramList.Add(DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + cName2, dbt, Convert.ToInt32(drTable["width"]), objValue));
}
parameters = paramList.ToArray();
IDbOperation db = new SqlServerExecute();
if (transaction == null)
{
db.ExecuteNonQuery(sql, parameters);
}
else
{
db.ExecuteNonQuery(transaction, sql, parameters);
}
}
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql, parameters);
}
}