001.<#+
002.public class DbHelper
003.{
004.#region GetDbTables
005.
006.public static List<DbTable> GetDbTables(string connectionString, string database, string tables = null)
007.{
008.
009.if (!string.IsNullOrEmpty(tables))
010.{
011.tables = string.Format(" and obj.name in ('{0}')", tables.Replace(",", "','"));
012.}
013.#region SQL
014.string sql = string.Format(@"SELECT
015.obj.name tablename,
016.schem.name schemname,
017.idx.rows,
018.CAST
019.(
020.CASE
021.WHEN (SELECT COUNT(1) FROM sys.indexes WHERE object_id= obj.OBJECT_ID AND is_primary_key=1) >=1 THEN 1
022.ELSE 0
023.END
024.AS BIT) HasPrimaryKey
025.from {0}.sys.objects obj
026.inner join {0}.dbo.sysindexes idx on obj.object_id=idx.id and idx.indid<=1
027.INNER JOIN {0}.sys.schemas schem ON obj.schema_id=schem.schema_id
028.where type='U' {1}
029.order by obj.name", database, tables);
030.#endregion
031.DataTable dt = GetDataTable(connectionString, sql);
032.return dt.Rows.Cast<DataRow>().Select(row => new DbTable
033.{
034.TableName = row.Field<string>("tablename"),
035.SchemaName = row.Field<string>("schemname"),
036.Rows = row.Field<int>("rows"),
037.HasPrimaryKey = row.Field<bool>("HasPrimaryKey")
038.}).ToList();
039.}
040.#endregion
041.
042.#region GetDbColumns
043.
044.public static List<DbColumn> GetDbColumns(string connectionString, string database, string tableName, string schema = "dbo")
045.{
046.#region SQL
047.string sql = string.Format(@"
048.WITH indexCTE AS
049.(
050.SELECT
051.ic.column_id,
052.ic.index_column_id,
053.ic.object_id
054.FROM {0}.sys.indexes idx
055.INNER JOIN {0}.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id
056.WHERE idx.object_id =OBJECT_ID(@tableName) AND idx.is_primary_key=1
057.)
058.select
059.colm.column_id ColumnID,
060.CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey,
061.colm.name ColumnName,
062.systype.name ColumnType,
063.colm.is_identity IsIdentity,
064.colm.is_nullable IsNullable,
065.cast(colm.max_length as int) ByteLength,
066.(
067.case
068.when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2
069.when systype.name='nchar' and colm.max_length>0 then colm.max_length/2
070.when systype.name='ntext' and colm.max_length>0 then colm.max_length/2
071.else colm.max_length
072.end
073.) CharLength,
074.cast(colm.precision as int) Precision,
075.cast(colm.scale as int) Scale,
076.prop.value Remark
077.from {0}.sys.columns colm
078.inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
079.left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
080.LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id
081.where colm.object_id=OBJECT_ID(@tableName)
082.order by colm.column_id", database);
083.#endregion
084.SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) };
085.DataTable dt = GetDataTable(connectionString, sql, param);
086.return dt.Rows.Cast<DataRow>().Select(row => new DbColumn()
087.{
088.ColumnID = row.Field<int>("ColumnID"),
089.IsPrimaryKey = row.Field<bool>("IsPrimaryKey"),
090.ColumnName = row.Field<string>("ColumnName"),
091.ColumnType = row.Field<string>("ColumnType"),
092.IsIdentity = row.Field<bool>("IsIdentity"),
093.IsNullable = row.Field<bool>("IsNullable"),
094.ByteLength = row.Field<int>("ByteLength"),
095.CharLength = row.Field<int>("CharLength"),
096.Scale = row.Field<int>("Scale"),
097.Remark = row["Remark"].ToString()
098.}).ToList();
099.}
100.
101.#endregion
102.
103.
104.#region GetDataTable
105.
106.public static DataTable GetDataTable(string connectionString, string commandText, params SqlParameter[] parms)
107.{
108.using (SqlConnection connection = new SqlConnection(connectionString))
109.{
110.SqlCommand command = connection.CreateCommand();
111.command.CommandText = commandText;
112.command.Parameters.AddRange(parms);
113.SqlDataAdapter adapter = new SqlDataAdapter(command);
114.
115.DataTable dt = new DataTable();
116.adapter.Fill(dt);
117.
118.return dt;
119.}
120.}
121.
122.#endregion
123.}
124.
125.#region DbTable
126./// <summary>
127./// 表结构
128./// </summary>
129.public sealed class DbTable
130.{
131./// <summary>
132./// 表名称
133./// </summary>
134.public string TableName { get; set; }
135./// <summary>
136./// 表的架构
137./// </summary>
138.public string SchemaName { get; set; }
139./// <summary>
140./// 表的记录数
141./// </summary>
142.public int Rows { get; set; }
143.
144./// <summary>
145./// 是否含有主键
146./// </summary>
147.public bool HasPrimaryKey { get; set; }
148.}
149.#endregion
150.
151.#region DbColumn
152./// <summary>
153./// 表字段结构
154./// </summary>
155.public sealed class DbColumn
156.{
157./// <summary>
158./// 字段ID
159./// </summary>
160.public int ColumnID { get; set; }
161.
162./// <summary>
163./// 是否主键
164./// </summary>
165.public bool IsPrimaryKey { get; set; }
166.
167./// <summary>
168./// 字段名称
169./// </summary>
170.public string ColumnName { get; set; }
171.
172./// <summary>
173./// 字段类型
174./// </summary>
175.public string ColumnType { get; set; }
176.
177./// <summary>
178./// 数据库类型对应的C#类型
179./// </summary>
180.public string CSharpType
181.{
182.get
183.{
184.return SqlServerDbTypeMap.MapCsharpType(ColumnType);
185.}
186.}
187.
188./// <summary>
189.///
190./// </summary>
191.public Type CommonType
192.{
193.get
194.{
195.return SqlServerDbTypeMap.MapCommonType(ColumnType);
196.}
197.}
198.
199./// <summary>
200./// 字节长度
201./// </summary>
202.public int ByteLength { get; set; }
203.
204./// <summary>
205./// 字符长度
206./// </summary>
207.public int CharLength { get; set; }
208.
209./// <summary>
210./// 小数位
211./// </summary>
212.public int Scale { get; set; }
213.
214./// <summary>
215./// 是否自增列
216./// </summary>
217.public bool IsIdentity { get; set; }
218.
219./// <summary>
220./// 是否允许空
221./// </summary>
222.public bool IsNullable { get; set; }
223.
224./// <summary>
225./// 描述
226./// </summary>
227.public string Remark { get; set; }
228.}
229.#endregion
230.
231.#region SqlServerDbTypeMap
232.
233.public class SqlServerDbTypeMap
234.{
235.public static string MapCsharpType(string dbtype)
236.{
237.if (string.IsNullOrEmpty(dbtype)) return dbtype;
238.dbtype = dbtype.ToLower();
239.string csharpType = "object";
240.switch (dbtype)
241.{
242.case "bigint": csharpType = "long"; break;
243.case "binary": csharpType = "byte[]"; break;
244.case "bit": csharpType = "bool"; break;
245.case "char": csharpType = "string"; break;
246.case "date": csharpType = "DateTime"; break;
247.case "datetime": csharpType = "DateTime"; break;
248.case "datetime2": csharpType = "DateTime"; break;
249.case "datetimeoffset": csharpType = "DateTimeOffset"; break;
250.case "decimal": csharpType = "decimal"; break;
251.case "float": csharpType = "double"; break;
252.case "image": csharpType = "byte[]"; break;
253.case "int": csharpType = "int"; break;
254.case "money": csharpType = "decimal"; break;
255.case "nchar": csharpType = "string"; break;
256.case "ntext": csharpType = "string"; break;
257.case "numeric": csharpType = "decimal"; break;
258.case "nvarchar": csharpType = "string"; break;
259.case "real": csharpType = "Single"; break;
260.case "smalldatetime": csharpType = "DateTime"; break;
261.case "smallint": csharpType = "short"; break;
262.case "smallmoney": csharpType = "decimal"; break;
263.case "sql_variant": csharpType = "object"; break;
264.case "sysname": csharpType = "object"; break;
265.case "text": csharpType = "string"; break;
266.case "time": csharpType = "TimeSpan"; break;
267.case "timestamp": csharpType = "byte[]"; break;
268.case "tinyint": csharpType = "byte"; break;
269.case "uniqueidentifier": csharpType = "Guid"; break;
270.case "varbinary": csharpType = "byte[]"; break;
271.case "varchar": csharpType = "string"; break;
272.case "xml": csharpType = "string"; break;
273.default: csharpType = "object"; break;
274.}
275.return csharpType;
276.}
277.
278.public static Type MapCommonType(string dbtype)
279.{
280.if (string.IsNullOrEmpty(dbtype)) return Type.Missing.GetType();
281.dbtype = dbtype.ToLower();
282.Type commonType = typeof(object);
283.switch (dbtype)
284.{
285.case "bigint": commonType = typeof(long); break;
286.case "binary": commonType = typeof(byte[]); break;
287.case "bit": commonType = typeof(bool); break;
288.case "char": commonType = typeof(string); break;
289.case "date": commonType = typeof(DateTime); break;
290.case "datetime": commonType = typeof(DateTime); break;
291.case "datetime2": commonType = typeof(DateTime); break;
292.case "datetimeoffset": commonType = typeof(DateTimeOffset); break;
293.case "decimal": commonType = typeof(decimal); break;
294.case "float": commonType = typeof(double); break;
295.case "image": commonType = typeof(byte[]); break;
296.case "int": commonType = typeof(int); break;
297.case "money": commonType = typeof(decimal); break;
298.case "nchar": commonType = typeof(string); break;
299.case "ntext": commonType = typeof(string); break;
300.case "numeric": commonType = typeof(decimal); break;
301.case "nvarchar": commonType = typeof(string); break;
302.case "real": commonType = typeof(Single); break;
303.case "smalldatetime": commonType = typeof(DateTime); break;
304.case "smallint": commonType = typeof(short); break;
305.case "smallmoney": commonType = typeof(decimal); break;
306.case "sql_variant": commonType = typeof(object); break;
307.case "sysname": commonType = typeof(object); break;
308.case "text": commonType = typeof(string); break;
309.case "time": commonType = typeof(TimeSpan); break;
310.case "timestamp": commonType = typeof(byte[]); break;
311.case "tinyint": commonType = typeof(byte); break;
312.case "uniqueidentifier": commonType = typeof(Guid); break;
313.case "varbinary": commonType = typeof(byte[]); break;
314.case "varchar": commonType = typeof(string); break;
315.case "xml": commonType = typeof(string); break;
316.default: commonType = typeof(object); break;
317.}
318.return commonType;
319.}
320.}
321.#endregion
322.
323.
324.#>
