458 lines
17 KiB
C#
458 lines
17 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Text.RegularExpressions;
|
||
using System.Reflection;
|
||
|
||
namespace MJTop.Data
|
||
{
|
||
public partial class DBExtend
|
||
{
|
||
|
||
#region 将集合实体转为DataTable
|
||
|
||
/// <summary>
|
||
/// 将集合实体转为DataTable
|
||
/// </summary>
|
||
/// <typeparam name="T">实体类型</typeparam>
|
||
/// <param name="lstEntity">集合</param>
|
||
/// <returns></returns>
|
||
public static DataTable EntityToDataTable<T>(IEnumerable<T> lstEntity)
|
||
where T:class,new()
|
||
{
|
||
return EntityToDataTable(lstEntity, null);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将集合实体转为DataTable
|
||
/// </summary>
|
||
/// <typeparam name="T">实体类型</typeparam>
|
||
/// <param name="lstEntity">集合</param>
|
||
/// <param name="tableName">DataTable表名</param>
|
||
/// <returns></returns>
|
||
public static DataTable EntityToDataTable<T>(IEnumerable<T> lstEntity, string tableName)
|
||
where T : class, new()
|
||
{
|
||
DataTable data = new DataTable();
|
||
Type ty = typeof(T);
|
||
if (!string.IsNullOrWhiteSpace(tableName))
|
||
{
|
||
data.TableName = tableName;
|
||
}
|
||
else
|
||
{
|
||
data.TableName = ty.Name;
|
||
}
|
||
var props = ty.GetProperties();
|
||
|
||
foreach (var prop in props)
|
||
{
|
||
data.Columns.Add(prop.Name, prop.PropertyType);
|
||
}
|
||
foreach (var entity in lstEntity)
|
||
{
|
||
DataRow dr = data.NewRow();
|
||
foreach (DataColumn dc in data.Columns)
|
||
{
|
||
var prop = ty.GetProperty(dc.ColumnName);
|
||
var value = prop.GetValue(entity, null);
|
||
dr[dc.ColumnName] = value;
|
||
}
|
||
data.Rows.Add(dr);
|
||
}
|
||
return data;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region DataTable =》 GetCreateSqlScript
|
||
|
||
/// <summary>
|
||
/// 根据DataTable获取创建Sql语句
|
||
/// </summary>
|
||
/// <param name="data">DataTable</param>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="DBType">数据库类型</param>
|
||
/// <returns>创建表的Sql脚本</returns>
|
||
public static string GetCreateSqlScript(DataTable data, string tableName, DBType DBType = DBType.SqlServer)
|
||
{
|
||
if (data == null)
|
||
{
|
||
throw new ArgumentException("数据表不能为null！", "data");
|
||
}
|
||
data.TableName = tableName;
|
||
return GetCreateSqlScript(data, DBType);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据集合实体得到 Sql创建表脚本
|
||
/// </summary>
|
||
/// <typeparam name="T">实体类型</typeparam>
|
||
/// <param name="lstEntity">集合</param>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="DBType">数据库类型</param>
|
||
/// <returns></returns>
|
||
public static string GetCreateSqlScript<T>(IEnumerable<T> lstEntity, string tableName, DBType DBType = DBType.SqlServer)
|
||
where T : class, new()
|
||
{
|
||
DataTable data = EntityToDataTable(lstEntity, tableName);
|
||
return GetCreateSqlScript(data, DBType);
|
||
}
|
||
|
||
#region private
|
||
/// <summary>
|
||
/// 获取列的值的最大小数点占用位数
|
||
/// </summary>
|
||
/// <param name="data">DataTable</param>
|
||
/// <returns></returns>
|
||
private static Dictionary<string, int> GetColDecimals(DataTable data)
|
||
{
|
||
Dictionary<string, int> dictDecimals = new Dictionary<string, int>();
|
||
List<Type> lstTy = new List<Type>()
|
||
{
|
||
typeof(double),
|
||
typeof(decimal),
|
||
typeof(float)
|
||
};
|
||
var rowColl = data.AsEnumerable();
|
||
var lstCol = data.Columns.ToArray().Where(t => lstTy.Contains(t.DataType));//获取小数类型的列
|
||
foreach (DataColumn dc in lstCol)
|
||
{
|
||
long lngTemp;
|
||
var currArr = rowColl.Select(t => t[dc.ColumnName].ToString()); //得到当前列的 ToString()
|
||
currArr = currArr.Where(t => t.Length > 0 && !long.TryParse(t, out lngTemp));//不是空字符串，并且Parse失败则才算是真正的小数，过滤 小数点后都是0的数值
|
||
if (currArr.Any())
|
||
{
|
||
var currArrInt = currArr.Select(t => (Regex.Replace(t, @"(\d+)\.(\d+)", "$2", RegexOptions.Compiled)).Length);
|
||
int decimals = currArrInt.Max();
|
||
dictDecimals[dc.ColumnName] = decimals;
|
||
}
|
||
}
|
||
return dictDecimals;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据DataTable获取创建Sql语句
|
||
/// </summary>
|
||
/// <param name="data">DataTable</param>
|
||
/// <param name="DBType">数据库类型</param>
|
||
/// <returns>创建表的Sql脚本</returns>
|
||
private static string GetCreateSqlScript(DataTable data, DBType DBType = DBType.SqlServer)
|
||
{
|
||
if (data == null)
|
||
{
|
||
throw new ArgumentException("数据表不能为null！", "data");
|
||
}
|
||
if (string.IsNullOrWhiteSpace(data.TableName))
|
||
{
|
||
throw new ArgumentException("表名不能为空！");
|
||
}
|
||
return GetCreateSqlScript(data, data.Columns, DBType);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据 表名，DataColumn[]获取创建Sql语句
|
||
/// </summary>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="dcs">所有列</param>
|
||
/// <param name="DBType">数据库类型</param>
|
||
/// <returns>创建表的Sql脚本</returns>
|
||
private static string GetCreateSqlScript(DataTable data, DataColumnCollection dcs, DBType DBType = DBType.SqlServer)
|
||
{
|
||
//获取主键
|
||
DataColumn[] primaryKey = data.PrimaryKey;
|
||
if (dcs == null)
|
||
{
|
||
throw new ArgumentException("列集合不能为null！");
|
||
}
|
||
if (dcs.Count <= 0)
|
||
{
|
||
throw new ArgumentException("列集合个数必须大于0！");
|
||
}
|
||
Dictionary<string, int> dictDecimals = GetColDecimals(data);
|
||
|
||
StringBuilder sbSql = new StringBuilder();
|
||
sbSql.AppendFormat("create table {0}", data.TableName);
|
||
sbSql.Append("(");
|
||
for (int j = 0; j < dcs.Count; j++)
|
||
{
|
||
var dc = dcs[j];
|
||
string colSqlType = GetColSqlType(dc, DBType, dictDecimals);
|
||
if (dc.AutoIncrement) //是自增列
|
||
{
|
||
colSqlType += " identity(" + dc.AutoIncrementSeed + "," + dc.AutoIncrementStep + ") ";
|
||
}
|
||
if (primaryKey.Contains(dc))
|
||
{
|
||
colSqlType += " primary key ";
|
||
}
|
||
colSqlType = colSqlType + ((j != dcs.Count - 1) ? "," : "");
|
||
sbSql.Append(colSqlType);
|
||
}
|
||
sbSql.Append(")");
|
||
string sql = sbSql.ToString();
|
||
return sql;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 返回Sql列类型
|
||
/// </summary>
|
||
/// <param name="dc">列</param>
|
||
/// <param name="DBType">数据库类型</param>
|
||
/// <param name="dictDecimals">列的值的最大小数点占用位数</param>
|
||
/// <returns>Sql列类型</returns>
|
||
private static string GetColSqlType(DataColumn dc, DBType DBType, Dictionary<string, int> dictDecimals)
|
||
{
|
||
string res = string.Empty;
|
||
string columnName = dc.ColumnName;
|
||
Type typecol = Nullable.GetUnderlyingType(dc.DataType) ?? dc.DataType;
|
||
switch (typecol.FullName)
|
||
{
|
||
case "System.Guid":
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " uniqueidentifier";
|
||
}
|
||
else
|
||
{
|
||
res = columnName + " char(36)";
|
||
}
|
||
break;
|
||
case "System.Boolean":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
res = columnName + " bit";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " number(1,0)";
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " integer";
|
||
}
|
||
break;
|
||
case "System.Int32":
|
||
case "System.Int64":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
res = columnName + " bigint";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " number(38,0)";
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " integer";
|
||
}
|
||
break;
|
||
case "System.Decimal":
|
||
case "System.Double":
|
||
case "System.Single":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
if (dictDecimals != null && dictDecimals.ContainsKey(dc.ColumnName))
|
||
{
|
||
res = columnName + " decimal(38," + dictDecimals[dc.ColumnName] + ")";
|
||
}
|
||
else
|
||
{
|
||
res = columnName + " bigint";
|
||
}
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
if (dictDecimals != null && dictDecimals.ContainsKey(dc.ColumnName))
|
||
{
|
||
res = columnName + " number(38," + dictDecimals[dc.ColumnName] + ")";
|
||
}
|
||
else
|
||
{
|
||
res = columnName + " number(38,0)";
|
||
}
|
||
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
if (dictDecimals != null && dictDecimals.ContainsKey(dc.ColumnName))
|
||
{
|
||
res = columnName + " real(38," + dictDecimals[dc.ColumnName] + ")";
|
||
}
|
||
else
|
||
{
|
||
res = columnName + " integer";
|
||
}
|
||
}
|
||
break;
|
||
case "System.DateTime":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " datetime";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " date";
|
||
}
|
||
break;
|
||
case "System.Byte[]":
|
||
//先把数据存进去，使用max
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " varbinary(max)";
|
||
}
|
||
else if (DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " blob";
|
||
}
|
||
break;
|
||
default:
|
||
//先把数据存进去，使用max
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " nvarchar(max)";
|
||
}
|
||
else if (DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " text";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " nclob";
|
||
}
|
||
break;
|
||
}
|
||
|
||
return res;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#endregion
|
||
|
||
#region 反射对象类型的属性生成创建脚本
|
||
public static string GetCreateSqlScript(Type ty, DBType DBType = DBType.SqlServer)
|
||
{
|
||
var props = ty.GetProperties();
|
||
StringBuilder sbSql = new StringBuilder();
|
||
sbSql.AppendFormat("create table {0}", ty.Name);
|
||
sbSql.Append("(");
|
||
for (int j = 0; j < props.Length; j++)
|
||
{
|
||
PropertyInfo pInfo = props[j];
|
||
string colType = GetColTypeByProperty(pInfo, DBType);
|
||
sbSql.Append(colType + " " + ((j == props.Length - 1) ? "" : ","));
|
||
}
|
||
sbSql.Append(")");
|
||
return sbSql.ToString();
|
||
}
|
||
private static string GetColTypeByProperty(PropertyInfo pInfo, DBType DBType)
|
||
{
|
||
string res = string.Empty;
|
||
string columnName = pInfo.Name;
|
||
Type typecol = Nullable.GetUnderlyingType(pInfo.PropertyType) ?? pInfo.PropertyType;
|
||
switch (typecol.FullName)
|
||
{
|
||
case "System.Guid":
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " uniqueidentifier";
|
||
}
|
||
else
|
||
{
|
||
res = columnName + " char(36)";
|
||
}
|
||
break;
|
||
case "System.Boolean":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
res = columnName + " bit";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " number(1,0)";
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " integer";
|
||
}
|
||
break;
|
||
case "System.Int32":
|
||
case "System.Int64":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
res = columnName + " bigint";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " number(38,0)";
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " integer";
|
||
}
|
||
break;
|
||
case "System.Decimal":
|
||
case "System.Double":
|
||
case "System.Single":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql)
|
||
{
|
||
res = columnName + " decimal(38," + 2 + ")";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " number(38,," + 2 + ")";
|
||
|
||
}
|
||
else if (DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " real(38," + 2 + ")";
|
||
}
|
||
break;
|
||
case "System.DateTime":
|
||
if (DBType == DBType.SqlServer || DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " datetime";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " date";
|
||
}
|
||
break;
|
||
case "System.Byte[]":
|
||
//先把数据存进去，使用max
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " varbinary(max)";
|
||
}
|
||
else if (DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " blob";
|
||
}
|
||
break;
|
||
default:
|
||
//先把数据存进去，使用max
|
||
if (DBType == DBType.SqlServer)
|
||
{
|
||
res = columnName + " nvarchar(max)";
|
||
}
|
||
else if (DBType == DBType.MySql || DBType == DBType.SQLite)
|
||
{
|
||
res = columnName + " text";
|
||
}
|
||
else if (DBType == DBType.OracleDDTek || DBType == DBType.Oracle)
|
||
{
|
||
res = columnName + " nclob";
|
||
}
|
||
break;
|
||
}
|
||
|
||
return res;
|
||
}
|
||
#endregion
|
||
|
||
}
|
||
}
|