/// <summary>
/// 获取某个表的表结构
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public Table GetTable(Connection conn, string tableName)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT A.TABLE_NAME,");
builder.Append(" A.COLUMN_NAME,");
builder.Append(" A.DATA_TYPE,");
builder.Append(" A.DATA_LENGTH,");
builder.Append(" A.DATA_PRECISION,");
builder.Append(" A.NULLABLE,");
builder.Append(" A.DATA_DEFAULT,");
builder.Append(" A.CHAR_LENGTH,");
builder.Append(" A.DATA_SCALE,");
builder.Append(" B.COMMENTS,");
builder.Append(" C.COMMENTS TABLECOMMENTS");
builder.Append(" FROM USER_TAB_COLUMNS A");
builder.Append(" LEFT OUTER JOIN user_col_comments B");
builder.Append(" ON A.table_name = B.table_name");
builder.Append(" and A.column_name = B.column_name");
builder.Append(" LEFT OUTER JOIN user_tab_comments C");
builder.Append(" ON A.table_name = C.table_name");
builder.Append(" WHERE A.TABLE_NAME = '");
builder.Append(tableName);
builder.Append("'");
builder.Append(" ORDER BY A.TABLE_NAME, A.COLUMN_ID");
DataTable dataTable = new DataTable();
using (OracleConnection connection = new OracleConnection(conn.ToString()))
{
try
{
connection.Open();
new OracleDataAdapter(builder.ToString(), connection).Fill(dataTable);
}
catch (Exception exception)
{
connection.Close();
connection.Dispose();
throw exception;
}
finally
{
connection.Close();
connection.Dispose();
}
}
if (dataTable.Rows.Count == 0)
{
return(null);
}
Table table2 = new Table();
table2.Name = dataTable.Rows[0]["TABLE_NAME"].ToString();
table2.Comments = dataTable.Rows[0]["TABLECOMMENTS"].ToString();
table2.Database = conn.Database;
string str = string.Empty;
string str2 = string.Empty;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ColumnSchema item = new ColumnSchema();
item.Name = dataTable.Rows[i]["COLUMN_NAME"].ToString();
item.Property = dataTable.Rows[i]["COLUMN_NAME"].ToString().Replace("_", "");
item.DataType = dataTable.Rows[i]["DATA_TYPE"].ToString();
if (item.DataType.StartsWith("NUMBER") || item.DataType.StartsWith("TIMESTAMP"))
{
str = (dataTable.Rows[i]["DATA_PRECISION"].ToString() == "0") ? "" : dataTable.Rows[i]["DATA_PRECISION"].ToString();
str2 = (dataTable.Rows[i]["DATA_SCALE"].ToString() == "0") ? "" : dataTable.Rows[i]["DATA_SCALE"].ToString();
if (string.IsNullOrEmpty(str) && string.IsNullOrEmpty(str2))
{
item.Length = 0;
}
else
{
item.Length = Convert.ToInt32(str + str2);
}
}
else
{
item.Length = Convert.ToInt32(dataTable.Rows[i]["CHAR_LENGTH"].ToString());
}
item.IsNullAble = dataTable.Rows[i]["NULLABLE"].ToString() == "Y";
item.DefaultValue = dataTable.Rows[i]["DATA_DEFAULT"].ToString();
item.Comments = dataTable.Rows[i]["COMMENTS"].ToString();
table2.Columns.Add(item);
}
return(table2);
}
public Table GetTable(Connection conn, string tableName)
{
StringBuilder commandText = new StringBuilder();
commandText.Append("SELECT ");
commandText.Append(" TableName = case when a.colorder=1 then d.name else '' end, ");
commandText.Append(" TableDescription = case when a.colorder=1 then isnull(f.value,'') else '' end, ");
commandText.Append(" [Index] = a.colorder, ");
commandText.Append(" [Name] = a.name, ");
commandText.Append(" IsIdentity = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, ");
commandText.Append(" IsPrimaryKey = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( ");
commandText.Append(" SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, ");
commandText.Append(" DataType = b.name, ");
commandText.Append(" Length= a.length, ");
commandText.Append(" [Size] = COLUMNPROPERTY(a.id,a.name,'PRECISION'), ");
commandText.Append(" DecimalDigits = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), ");
commandText.Append(" IsNullAble = case when a.isnullable=1 then '√'else '' end, ");
commandText.Append(" DefaultValue = isnull(e.text,''), ");
commandText.Append(" Description = isnull(g.[value],'') ");
commandText.Append("FROM ");
commandText.Append(" syscolumns a ");
commandText.Append("left join ");
commandText.Append(" systypes b ");
commandText.Append("on ");
commandText.Append(" a.xusertype=b.xusertype ");
commandText.Append("inner join ");
commandText.Append(" sysobjects d ");
commandText.Append("on ");
commandText.Append(" a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
commandText.Append("left join ");
commandText.Append(" syscomments e ");
commandText.Append("on ");
commandText.Append(" a.cdefault=e.id ");
commandText.Append("left join ");
commandText.Append("sys.extended_properties g ");
commandText.Append("on ");
commandText.Append(" a.id=G.major_id and a.colid=g.minor_id ");
commandText.Append("left join ");
commandText.Append("sys.extended_properties f ");
commandText.Append("on ");
commandText.Append(" d.id=f.major_id and f.minor_id=0 ");
commandText.Append("where ");
commandText.Append(" d.name='").Append(tableName).Append("'");//只查询指定表
commandText.Append("order by ");
commandText.Append(" a.id,a.colorder");
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection(conn.ToString()))
{
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(commandText.ToString(), connection);
command.Fill(dataTable);
}
catch (System.Data.SqlClient.SqlException ex)
{
connection.Close();
connection.Dispose();
throw ex;
}
finally
{
connection.Close();
connection.Dispose();
}
}
if (dataTable.Rows.Count == 0)
{
return(null);
}
Table table = new Table();
table.Name = dataTable.Rows[0]["TableName"].ToString();
table.Comments = dataTable.Rows[0]["TableDescription"].ToString();
table.Database = conn.Database;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ColumnSchema column = new ColumnSchema();
column.IsIdentity = dataTable.Rows[i]["IsIdentity"].ToString() == "√" ? true : false;
column.IsPrimaryKey = dataTable.Rows[i]["IsPrimaryKey"].ToString() == "√" ? true : false;
column.Name = dataTable.Rows[i]["Name"].ToString();
column.Property = dataTable.Rows[i]["Name"].ToString().Replace("_", "");
column.DataType = dataTable.Rows[i]["DataType"].ToString();
column.Length = Convert.ToInt64(dataTable.Rows[i]["Length"].ToString());
column.Size = Convert.ToInt32(dataTable.Rows[i]["Size"].ToString());
column.DecimalDigits = Convert.ToInt32(dataTable.Rows[i]["DecimalDigits"].ToString());
column.IsNullAble = dataTable.Rows[i]["IsNullAble"].ToString() == "√" ? true : false;
column.DefaultValue = dataTable.Rows[i]["DefaultValue"].ToString();
column.Comments = dataTable.Rows[i]["Description"].ToString();
table.Columns.Add(column);
}
return(table);
}
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public Table GetTable(Connection conn, string tableName)
{
StringBuilder sb = new StringBuilder(524);
sb.AppendLine(@"SELECT C.TABLE_NAME,");
sb.AppendLine(@" C.COLUMN_NAME,");
sb.AppendLine(@" C.DATA_TYPE,");
sb.AppendLine(@" C.CHARACTER_MAXIMUM_LENGTH DATA_LENGTH,");
sb.AppendLine(@" C.NUMERIC_PRECISION DATA_PRECISION,");
sb.AppendLine(@" C.IS_NULLABLE NULLABLE,");
sb.AppendLine(@" C.COLUMN_DEFAULT DATA_DEFAULT,");
sb.AppendLine(@" C.COLUMN_TYPE,");
sb.AppendLine(@" C.COLUMN_COMMENT,");
sb.AppendLine(@" T.TABLE_COMMENT");
sb.AppendLine(@" FROM INFORMATION_SCHEMA.COLUMNS C");
sb.AppendLine(@" LEFT JOIN INFORMATION_SCHEMA.TABLES T");
sb.AppendLine(@" ON C.TABLE_NAME = T.TABLE_NAME");
sb.AppendLine(@" WHERE C.TABLE_NAME = ");
sb.AppendFormat("'{0}'", tableName);
sb.AppendLine(string.Format(@" AND C.TABLE_SCHEMA = '{0}' ", conn.Database));
sb.AppendLine(string.Format(@" AND T.TABLE_SCHEMA = '{0}'", conn.Database));
sb.AppendLine(@" ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION");
DataTable dataTable = new DataTable();
using (MySqlConnection connection = new MySqlConnection(conn.ToString()))
{
try
{
connection.Open();
MySqlDataAdapter command = new MySqlDataAdapter(sb.ToString(), connection);
command.Fill(dataTable);
}
catch (System.Data.SqlClient.SqlException ex)
{
connection.Close();
connection.Dispose();
throw ex;
}
finally
{
connection.Close();
connection.Dispose();
}
}
if (dataTable.Rows.Count == 0)
{
return(null);
}
Table table = new Table();
table.Name = dataTable.Rows[0]["TABLE_NAME"].ToString();
table.Comments = dataTable.Rows[0]["TABLE_COMMENT"].ToString();
table.Database = conn.Database;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
ColumnSchema column = new ColumnSchema();
column.Name = dataTable.Rows[i]["COLUMN_NAME"].ToString();
column.Property = dataTable.Rows[i]["COLUMN_NAME"].ToString().Replace("_", "");
column.DataType = dataTable.Rows[i]["DATA_TYPE"].ToString();
if (column.DataType.Equals("varchar") || column.DataType.Equals("char"))
{
column.DataLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
column.CharLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
}
else if (column.DataType.Equals("int"))
{
string columnType = dataTable.Rows[i]["COLUMN_TYPE"].ToString();
int leftBracket = columnType.IndexOf("(");
if (leftBracket > 0)
{
string columnLeft = columnType.Substring(leftBracket);
columnLeft = columnLeft.TrimStart(new char[] { '(', ')' }).TrimEnd(new char[] { '(', ')' });
int iColumnLen = int.Parse(columnLeft);
if (iColumnLen > 8)
{
column.DataType = "bigint";
}
column.DataLength = iColumnLen;
}
}
//column.DataLength = Convert.ToInt32(dataTable.Rows[i]["DATA_LENGTH"].ToString());
//column.CharLength = Convert.ToInt32(dataTable.Rows[i]["CHAR_LENGTH"].ToString());
column.IsNullAble = dataTable.Rows[i]["NULLABLE"].ToString() == "YES" ? true : false;
column.DefaultValue = dataTable.Rows[i]["DATA_DEFAULT"].ToString() == "NULL" ? "" : dataTable.Rows[i]["DATA_DEFAULT"].ToString();
column.Comments = dataTable.Rows[i]["COLUMN_COMMENT"].ToString();
table.Columns.Add(column);
}
return(table);
}
