/// <summary>
/// 获取表结构<不包含数据>
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
private static DataSet GetEmptyTable(string tableName)
{
string sql = "select * from " + tableName + " where 0 = 1";
try
{
IDbOperation db = new SqlServerExecute();
return(db.FillDataset(sql));
}
catch (Exception ex)
{
throw new CustomDataException(ex, sql);
}
}
/// <summary>
/// 获取某个表的信息--SqlServer
/// cname - 字段名
/// coltype - 字段类型
/// width - 字段长度
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>数据集</returns>
public DataSet GetSqlTableInfo(string tableName)
{
tableName = tableName.ToUpper();
string sql = "select col.name as cname, (select name from sys.types where system_type_id= col.system_type_id) as coltype,col.max_length as width"
+ " from sys.columns col where object_id in (select object_id from sys.tables t where t.name = @tname)";
DbParameter[] dbParams =
{
DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@tname", SqlDbType.Char, 30, tableName), //此外的","不能省略
};
IDbOperation db = new SqlServerExecute();
DataSet dataSet = db.FillDataset(sql, dbParams);
return(dataSet);
}
/// <summary>
/// 向表中增加记录
/// </summary>
/// <param name="descObject">实体类</param>
/// <param name="tableName">表名称</param>
/// <param name="keyName">主键名</param>
/// <param name="isAutoIncCol">主键是否是自动增长列</param>
public void InsertTableRec(object descObject, string tableName, string keyName, bool isAutoIncCol)
{
string sqlStatement = string.Empty;
string sqlStatement2 = "insert into [" + tableName + "](";
string sqlStatement3 = " values(";
Type type = descObject.GetType();
foreach (PropertyInfo info in type.GetProperties())
{
if (isAutoIncCol)
{
if (info.Name.ToLower().Equals(keyName))
{
continue;
}
}
sqlStatement2 = sqlStatement2 + info.Name + ",";
if (info.PropertyType.ToString().Equals("System.String"))
{
sqlStatement3 = sqlStatement3 + "'" + (type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null) as string).Replace("'", "''") + "',";
}
else if (info.PropertyType.ToString().Equals("System.Boolean"))
{
if ((bool)type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null))
{
sqlStatement3 = sqlStatement3 + "1,";
}
else
{
sqlStatement3 = sqlStatement3 + "0,";
}
}
else if (info.PropertyType.ToString().Equals("System.Int32"))
{
sqlStatement3 = sqlStatement3 + ((int)type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) + ",";
}
else if (info.PropertyType.ToString().Equals("System.Int64"))
{
sqlStatement3 = sqlStatement3 + ((long)type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) + ",";
}
else if (info.PropertyType.ToString().Equals("System.Double"))
{
sqlStatement3 = sqlStatement3 + ((double)type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) + ",";
}
else if (info.PropertyType.ToString().Equals("System.Decimal"))
{
sqlStatement3 = sqlStatement3 + ((decimal)type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) + ",";
}
else if (info.PropertyType.ToString().Equals("System.DateTime"))
{
if (Convert.ToDateTime(type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) == DateTime.MinValue)
{
sqlStatement3 = sqlStatement3 + "null,";
}
else
{
sqlStatement3 = sqlStatement3 + "cast('" + Convert.ToString(type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null)) + "' as datetime),";
}
}
else
{
sqlStatement3 = sqlStatement3 + (type.InvokeMember(info.Name, BindingFlags.GetProperty, null, descObject, null) as string) + ",";
}
}
sqlStatement = sqlStatement2.Substring(0, sqlStatement2.Length - 1) + ") " + sqlStatement3.Substring(0, sqlStatement3.Length - 1) + ")";
IDbOperation db = new SqlServerExecute();
db.ExecuteNonQuery(sqlStatement);
}
/// <summary>
/// 复制表结构及主键、唯一键
/// </summary>
/// <param name="srcTableName">源表</param>
/// <param name="destTableName">新表名称，必须是不存在的</param>
public void CopyTableStruct(string srcTableName, string destTableName)
{
if (srcTableName == destTableName)
{
throw new CustomDataException("源表和目标表名称冲突");
}
string sql = string.Empty;
try
{
ConstraintCollection ucList = GetSqlTableConstraint(srcTableName);
//sql = "create table " + destTableName + " as select * from " + srcTableName + " where 1=2";
//上面语句为Oracle数据库中的复制表结构的Sql语句
//下面语句为SqlServer数据库中复制表结构的Sql语句
sql = "select * into " + destTableName + " from " + srcTableName + " where 1 = 2";
IDbOperation db = new SqlServerExecute();
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 SqlServerExecute();
dbOperation.ExecuteNonQuery(sql);;
}
}
catch (CustomDataException exdb)
{
throw exdb;
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql);
}
}
/// <summary>
/// 获取表的约束条件
/// constraint_name - 约束名称
/// column_name - 约束的字段
/// constraint_type - 约束类型，PK: 主键，U: 唯一键，R: 外键
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public ConstraintCollection GetSqlTableConstraint(string tableName)
{
tableName = tableName.ToUpper();
//此处取SqlServer2008中表的主键列信息，注意建表时主键列排第一列,此处通过列ID来检索
string sql = "select col.name as column_name,con.name as constraint_name,con.type as constraint_type"
+ " from sys.columns col, sys.key_constraints con "
+ " where col.object_id = con.parent_object_id and col.column_id = 1 and col.is_nullable = 0"
+ " and col.object_id in (select object_id from sys.tables where name = @table_name)";
try
{
DbParameter[] dbParams =
{
DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@table_name", SqlDbType.VarChar, 30, tableName),
};
IDbOperation db = new SqlServerExecute();
DataSet dataSet = db.FillDataset(sql, dbParams);
DataSet dsEmtpy = GetEmptyTable(tableName);
string prevType = string.Empty;
string prevColumnName = string.Empty;
List <DataColumn> cols = null;
foreach (DataRow dr in dataSet.Tables[0].Rows)
{
string constraintName = dr["constraint_name"].ToString();
string columnName = dr["column_name"].ToString();
string constraintType = dr["constraint_type"].ToString();
if (constraintType != prevType || constraintName != prevColumnName)
{
if (prevType.Length > 0)
{
UniqueConstraint uc = new UniqueConstraint(prevColumnName, cols.ToArray(), prevType == "PK");
dsEmtpy.Tables[0].Constraints.Add(uc);
}
cols = new List <DataColumn>();
}
cols.Add(dsEmtpy.Tables[0].Columns[columnName]);
prevType = constraintType;
prevColumnName = constraintName;
}
if (prevType.Length > 0)
{
UniqueConstraint uc = new UniqueConstraint(prevColumnName, cols.ToArray(), prevType == "PK");
dsEmtpy.Tables[0].Constraints.Add(uc);
}
return(dsEmtpy.Tables[0].Constraints);
}
catch (CustomDataException exdb)
{
throw exdb;
}
catch (System.Exception ex)
{
throw new CustomDataException(ex, sql);
}
}
/// <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);
}
}
