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
