MSSQL获取表结构通用方法

 1 #region 通用方法
 2 
 3         ///<summary>
 4         ///读取错误信息
 5         ///</summary>
 6         public string ErrorMessage { get; private set; }
 7 
 8         /// <summary>
 9         /// 获取数据库中的表清单
10         /// </summary>
11         private DataTable GetTableNameList
12         {
13             get { return SqlDatabase.OperateData("SELECT [Name] FROM SysObjects Where XType='U' and name <> N'sysdiagrams'  ORDER BY [Name]"); }
14         }
15 
16         /// <summary>
17         /// 获取数据库中的表清单和字段清单
18         /// </summary>
19         private DataTable GetTableNameAndColumnList
20         {
21             get
22             {
23                 //频繁进行字符串拼接操作的时候推荐使用StringBuilder，因为它提供更优秀的性能
24                 var strSql = new StringBuilder();
25                 strSql.Append("SELECT     [Name],STUFF((SELECT ',' + CAST( '['+COLUMN_NAME+']' as varchar(50)) ");
26                 strSql.Append("FROM         INFORMATION_SCHEMA.COLUMNS  where (TABLE_NAME = [Name])  FOR XML PATH('')),1,1,'') as ColumnList ");
27                 strSql.Append("FROM         SysObjects ");
28                 strSql.Append("Where XType='U' and name <> N'sysdiagrams' ORDER BY [Name] ");
29                 return SqlDatabase.OperateData(strSql.ToString());
30             }
31         }
32 
33         /// <summary>
34         /// 获取数据库中的表清单和字段清单
35         /// </summary>
36         /// <param name="strTableList">表清单</param>
37         /// <returns></returns>
38         private DataTable GetTableNameAndColumnsToInsert(string strTableList)
39         {
40 
41                 //频繁进行字符串拼接操作的时候推荐使用StringBuilder，因为它提供更优秀的性能
42                 var strSql = new StringBuilder();
43                 strSql.Append("SELECT     [Name],STUFF((SELECT ',' + CAST( '['+COLUMN_NAME+']' as varchar(200)) ");
44                 strSql.Append("FROM         INFORMATION_SCHEMA.COLUMNS  where (TABLE_NAME = [Name])  FOR XML PATH('')),1,1,'') as ColumnList ");
45                 strSql.Append("FROM         SysObjects ");
46                 strSql.Append(string.Format("Where XType='U' and [Name] <> N'sysdiagrams' and ([Name] in ({0}) ) ORDER BY [Name] ", strTableList));
47                 return SqlDatabase.OperateData(strSql.ToString());
48             
49         }
50 
51         /// <summary>
52         /// 获取数据库中的表清单和字段清单
53         /// </summary>
54         /// <param name="strTableList">表清单</param>
55         /// <returns></returns>
56         private DataTable GetTableNameAndColumnsToUpdate(string strTableList)
57         {
58 
59             //频繁进行字符串拼接操作的时候推荐使用StringBuilder，因为它提供更优秀的性能
60             var strSql = new StringBuilder();
61             strSql.Append("SELECT     [Name],STUFF((SELECT ',' + CAST( '['+COLUMN_NAME+']' + '=SourceTable_1.'+'['+COLUMN_NAME+']'  as varchar(200)) "); 
62             strSql.Append("FROM         INFORMATION_SCHEMA.COLUMNS  where (TABLE_NAME = [Name]) and COLUMN_NAME <> 'Id'  FOR XML PATH('')),1,1,'') as ColumnList "); 
63             strSql.Append("FROM         SysObjects ");
64             strSql.Append(string.Format("Where XType='U' and [Name] <> N'sysdiagrams' and ([Name] in ({0}) ) ORDER BY [Name] ", strTableList));
65             return SqlDatabase.OperateData(strSql.ToString());
66 
67         }
68 
69         /// <summary>
70         /// 获取指定表的列信息
71         /// </summary>
72         /// <param name="strTableName">表名</param>
73         /// <returns></returns>
74         private DataTable GetTableColumns(string strTableName)
75         {
76             //频繁进行字符串拼接操作的时候推荐使用StringBuilder，因为它提供更优秀的性能
77             var strSql = new StringBuilder();
78             strSql.Append("SELECT     COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH ");
79             strSql.Append("FROM         INFORMATION_SCHEMA.COLUMNS ");
80             strSql.Append("WHERE     (TABLE_NAME = @TableName) ");
81             var htCols = new Hashtable
82                              {
83                                  //表名称
84                                  {"TableName", strTableName}
85                              };
86             return SqlDatabase.OperateData(CommandType.Text, strSql.ToString(), htCols);
87 
88         }
89 
90         #endregion