/// <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>
/// 获取表的约束条件
/// 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);
}
}
