// 转换为SqlDbType
public static string ToSqlDbType(DbTableColumn dr)
{
switch (dr.type)
{
case "tinyint":
return(" SqlDbType.TinyInt");
case "int":
return(" SqlDbType.Int");
case "bigint":
return(" SqlDbType.BigInt");
case "char":
return(" SqlDbType.Char");
case "varchar":
return(" SqlDbType.VarChar");
case "nvarchar":
return(" SqlDbType.NVarChar");
case "datetime":
return(" SqlDbType.DateTime");
case "bit":
return(" SqlDbType.Bit");
case "decimal":
return(" SqlDbType.Decimal");
default:
throw new Exception("Type not known " + dr.type);
}
}
// 转换为C#变量类型
public static string ToCSharpType(DbTableColumn dr)
{
bool isNullable = dr.isNullAble == "1" ? true : false;
switch (dr.type)
{
case "int":
return(isNullable ? "int?" : "int");
case "bigint":
return(isNullable ? "long?" : "long");
case "decimal":
return(isNullable ? "decimal?" : "decimal");
case "varchar":
return("string");
case "char":
return("string");
case "nvarchar":
return("string");
case "datetime":
return(isNullable ? "DateTime?" : "DateTime");
case "bit":
return(isNullable ? "bool?" : "bool");
default:
throw new Exception("Type not known:" + dr.type);
}
}
public static string ToProcType(DbTableColumn dr)
{
switch (dr.type)
{
case "varchar":
return("varchar(" + ((dr.size == 2147483647 || dr.size == -1) ? "max" : dr.size.ToString()) + ")");
case "nvarchar":
return("nvarchar(" + ((dr.size == 2147483647 || dr.size == -1) ? "max" : dr.size.ToString()) + ")");
default:
return(dr.type);
}
}
private void GetColumns(SqlConnection cn, DbTable dt)
{
string sql = @"select sys.columns.name as ColumnName, sys.types.name as DataType, sys.columns.max_length as ColumnSize, sys.columns.is_nullable as AllowDBNull,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as IsKey ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as Description
from sys.columns, sys.tables, sys.types where sys.types.name<>'sysname' and sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name='" + dt.name + "' order by sys.columns.column_id";
SqlCommand cmd = new SqlCommand(sql, cn);
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql, cn);
sda.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DbTableColumn col = new DbTableColumn();
col.name = dr["ColumnName"].ToString();
col.type = dr["DataType"].ToString();
col.size = Int32.Parse(dr["ColumnSize"].ToString());
col.isNullAble = dr["AllowDBNull"].ToString();
col.isKey = dr["IsKey"].ToString();
col.description = dr["Description"].ToString();
dt.columns.Add(col);
}
}
