1。获得SQL server 表结构的方法
/// <summary>
/// 获得SQL server 数据库表的表结构
/// </summary>
/// <param name="connString">连接字符串</param>
/// <param name="tableName">表名</param>
/// <returns>返回包含列名、类型、长度、是否为空的表</returns>
public DataTable GetColumnsInfo( string connString , string tableName )
{
DataTable dt = new DataTable();
dt.Columns.Add( "ColumsName" );
dt.Columns.Add( "Type" );
dt.Columns.Add( "Length" );
dt.Columns.Add( "Nullable" );
DataRow dr = dt.NewRow();
DataSet ds = new DataSet();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( ConnString );
System.Data.SqlClient.SqlDataAdapter cmd = new System.Data.SqlClient.SqlDataAdapter();
try
{
conn.Open();
cmd.SelectCommand = new System.Data.SqlClient.SqlCommand();
cmd.SelectCommand.CommandText = "sp_help " + tableName;
cmd.SelectCommand.CommandType = CommandType.Text;
cmd.SelectCommand.Connection = conn;
cmd.Fill( ds );
for ( int i = 0 ; i < ds.Tables[1].Rows.Count ; i ++ )
{
dr[0] = ds.Tables[1].Rows[i]["Column_name"];
dr[1] = ds.Tables[1].Rows[i]["Type"];
dr[2] = ds.Tables[1].Rows[i]["Length"];
dr[3] = ds.Tables[1].Rows[i]["Nullable"];
dt.Rows.Add( dr );
}
return dt;
}
catch( Exception ex )
{
throw ex;
}
finally
{
conn.Close();
cmd.Dispose();
}