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