public void AutoUpdate(DbTransaction transaction, DataTable dataTable)
{
DataSet dsTable = GetTableInfo(dataTable.TableName);
if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0)
{
throw new Exception("未找到 " + dataTable.TableName + " 表的信息");
}
ConstraintCollection ucList = GetTableConstraint(dataTable.TableName);
if (ucList.Count == 0)
{
throw new Exception("表 " + 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 dataRow in dsTable.Tables[0].Rows)
{
string cName = dataRow["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 dataRow 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 = OracleDbTypeWrapper.WrapDbType(drTable["coltype"].ToString());
object o = dataRow.IsNull(cName2) ? null : dataRow[cName2];
paramList.Add(DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":" + cName2, dbt, Convert.ToInt32(drTable["WIDTH"]), o));
}
parameters = paramList.ToArray();
IDbOperation db = new OracleExecute();
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="srcTableName">源表</param>
/// <param name="destTableName">新表名称，必须是不存在的</param>
public void CopyTableStruct(string srcTableName, string destTableName)
{
if (srcTableName == destTableName)
{
throw new Exception("源表和目标表名称冲突");
}
string sql = "";
try
{
ConstraintCollection ucList = GetTableConstraint(srcTableName);
sql = "create table " + destTableName + " as select * from " + srcTableName + " where 1=2";
IDbOperation db = new OracleExecute();
db.ExecuteNonQuery(sql);
// 添加主键、唯一键约束
foreach (UniqueConstraint uc in ucList)
{
if (uc == null || uc.Columns.Length <= 0)
{
continue;
}
string newConstraintName = uc.ConstraintName;
if (newConstraintName.IndexOf(srcTableName) > -1)
{
newConstraintName = newConstraintName.Replace(srcTableName, destTableName);
}
else
{
newConstraintName += "_" + destTableName;
}
StringBuilder sb = new StringBuilder("alter table ");
sb.Append(destTableName);
sb.Append(" add constraint ");
sb.Append(newConstraintName);
if (uc.IsPrimaryKey)
{
sb.Append(" primary key (");
}
else
{
sb.Append(" unique (");
}
for (int i = 0; i < uc.Columns.Length; ++i)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append(uc.Columns[i].ColumnName);
}
sb.Append(")");
sql = sb.ToString();
IDbOperation dbOperation = new OracleExecute();
dbOperation.ExecuteNonQuery(sql);
}
}
catch (CustomDataException exdb)
{
throw exdb;
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql);
}
}
public void AutoInsert(DbTransaction transaction, DataTable dataTable)
{
DataSet dsTable = GetTableInfo(dataTable.TableName);
if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0)
{
throw new Exception("未找到 " + dataTable.TableName + " 表的信息");
}
StringBuilder sb1 = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
foreach (DataRow drTable1 in dsTable.Tables[0].Rows)
{
string cName = drTable1["CNAME"].ToString();
if (dataTable.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 " + dataTable.TableName + " (" + sb1.ToString() + ") values (" + sb2.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 = OracleDbTypeWrapper.WrapDbType(drTable["coltype"].ToString());
object o = dr.IsNull(cName2) ? null : dr[cName2];
paramList.Add(DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":" + cName2, dbt, Convert.ToInt32(drTable["width"]), o));
}
parameters = paramList.ToArray();
IDbOperation db = new OracleExecute();
if (transaction == null)
{
db.ExecuteNonQuery(sql, parameters);
}
else
{
db.ExecuteNonQuery(transaction, sql, parameters);
}
}
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql, parameters);
}
}
