public DBTableDetailEx GetTableStructEx(string tableName)
{
DBTableDetail detail = this.GetTableStruct(tableName);
DBTableDetailEx detailEx = new DBTableDetailEx();
detailEx.TableName = tableName;
detailEx.TableDescription = tableName + "表没有描述。";
detailEx.dtColumns = DBTableStructParser.CreateDBTableColumnsStruct();
for (int i = 0; i < detail.Columns.Length; i++)
{
DataRow row = detailEx.dtColumns.NewRow();
row[0] = detail.Columns[i].ColumnName;
row[1] = detail.Columns[i].ColumnName;
row[2] = detail.Columns[i].Description;
row[3] = detail.Columns[i].ColumnType;
row[4] = detail.Columns[i].Length;
row[5] = detail.Columns[i].IsPkey;
row[6] = detail.Columns[i].IsAutoID;
row[7] = detail.Columns[i].DefaultValue;
detailEx.dtColumns.Rows.Add(row);
}
return(detailEx);
}
//使用系统存储过程
public DBTableDetail GetTableStruct(string tableName)
{
IADOBase adoBase = new SqlADOBase(this.connectionStr);
DataSet ds = adoBase.DoQuery("sp_columns " + tableName);
DataTable tb = ds.Tables[0];
DBTableDetail tableDetail = new DBTableDetail();
tableDetail.TableName = tableName;
tableDetail.Columns = new DBColumnInfo[tb.Rows.Count];
for (int i = 0; i < tb.Rows.Count; i++)
{
tableDetail.Columns[i] = new DBColumnInfo();
tableDetail.Columns[i].ColumnName = tb.Rows[i]["Column_Name"].ToString();
tableDetail.Columns[i].ColumnType = tb.Rows[i]["Type_Name"].ToString();
if (tableDetail.Columns[i].ColumnType == "int identity")
{
tableDetail.Columns[i].ColumnType = "int";
}
tableDetail.Columns[i].Length = int.Parse(tb.Rows[i]["Length"].ToString());
tableDetail.Columns[i].AllowNull = (tb.Rows[i]["Nullable"].ToString() == "1");
tableDetail.Columns[i].IsAutoID = (tb.Rows[i]["SS_DATA_TYPE"].ToString() == "56");
tableDetail.Columns[i].Description = tb.Rows[i]["Remarks"].ToString();
if ((tableDetail.Columns[i].Description == null) || (tableDetail.Columns[i].Description == ""))
{
tableDetail.Columns[i].Description = tableDetail.Columns[i].ColumnName;
}
string dv = tb.Rows[i]["Column_def"].ToString();
if (dv == "")
{
tableDetail.Columns[i].DefaultValue = "";
}
else
{
string[] str = dv.Split(new char[] { '(', ')' });
string[] parts = str[1].Split('\'');
if (parts.Length >= 2)
{
tableDetail.Columns[i].DefaultValue = parts[1];
}
else
{
if (str.Length > 0)
{
tableDetail.Columns[i].DefaultValue = str[1];
}
}
}
}
this.SetPKeys(tableDetail, tableName);
return(tableDetail);
}
/// <summary>
/// 在数据库中创建指定表
/// </summary>
public void CreateTable(string connStr, DBTableDetail tableInfo)
{
//形成SQL语句
StringBuilder strBuilder = new StringBuilder();
string str_create = string.Format("Create Table {0}", tableInfo.TableName);
strBuilder.Append(str_create);
strBuilder.Append(" (");
int start = 0;
if (tableInfo.Columns[0].ColumnType == "int")
{
if (tableInfo.Columns[0].IsAutoID)
{
strBuilder.Append(string.Format("{0} int PRIMARY KEY IDENTITY ,", tableInfo.Columns[0].ColumnName));
start = 1;
}
}
for (int i = start; i < tableInfo.Columns.Length; i++)
{
bool length_fixed = DBSystemHelper.DBType_lengthFixed(tableInfo.Columns[i].ColumnType);
string item;
if (length_fixed)
{
item = string.Format("{0} {1} ", tableInfo.Columns[i].ColumnName, tableInfo.Columns[i].ColumnType);
}
else
{
item = string.Format("{0} {1}({2}) ", tableInfo.Columns[i].ColumnName, tableInfo.Columns[i].ColumnType);
}
if ((tableInfo.Columns[i].DefaultValue != null) && (tableInfo.Columns[i].DefaultValue != ""))
{
item += string.Format("DEFAULT {0}", tableInfo.Columns[i].DefaultValue);
}
if (tableInfo.Columns[i].IsPkey)
{
item += "PRIMARY KEY";
}
if (i != tableInfo.Columns.Length - 1)
{
item += " , ";
}
strBuilder.Append(item);
}
strBuilder.Append(" )");
//插入数据库
IADOBase adoBase = new SqlADOBase(connStr);
adoBase.DoCommand(strBuilder.ToString());
}
private void SetPKeys(DBTableDetail tableDetail, string tableName)
{
IADOBase adoBase = new SqlADOBase(this.connectionStr);
DataSet ds = adoBase.DoQuery("sp_pkeys " + tableName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
foreach (DBColumnInfo colInfo in tableDetail.Columns)
{
if (colInfo.ColumnName == ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString())
{
colInfo.IsPkey = true;
break;
}
}
}
}
//使用ADO.NET的GetSchemaTable方法
public DBTableDetail GetTableStruct2(string tableName)
{
DataSet ds = new DataSet();
IDbCommand cmd = this.dbEleFactory.GetCommand();
cmd.Connection = this.dbEleFactory.GetConnection(this.connectionStr);
cmd.CommandText = string.Format("Select * from {0}", tableName);
cmd.Connection.Open();
IDataReader read = cmd.ExecuteReader();
DataTable tb = read.GetSchemaTable(); //注意这句话，得到表的架构信息
read.Close();
cmd.Connection.Close();
DBTableDetail tableDetail = new DBTableDetail();
tableDetail.TableName = tableName;
tableDetail.Columns = new DBColumnInfo[tb.Rows.Count];
for (int i = 0; i < tb.Rows.Count; i++)
{
tableDetail.Columns[i] = new DBColumnInfo();
tableDetail.Columns[i].ColumnName = tb.Rows[i]["ColumnName"].ToString();
tableDetail.Columns[i].ColumnType = DBTypeConverter.ConvertCsTypeToOriginDBType(tb.Rows[i]["DataType"].ToString());
if (tableDetail.Columns[i].ColumnType.ToString() == "image")
{
tableDetail.Columns[i].Length = 16;
}
else
{
tableDetail.Columns[i].Length = int.Parse(tb.Rows[i]["ColumnSize"].ToString());
}
tableDetail.Columns[i].AllowNull = bool.Parse(tb.Rows[i]["AllowDBNull"].ToString());
tableDetail.Columns[i].IsAutoID = bool.Parse(tb.Rows[i]["IsAutoIncrement"].ToString());
tableDetail.Columns[i].Description = "";
tableDetail.Columns[i].DefaultValue = "";
}
this.SetPKeys(tableDetail, tableName);
return(tableDetail);
}
