public Cache(int blocks, int words, Memory memory)
{
this.blocks = blocks;
this.words = words;
columns = new CacheColumn <T> [blocks];
for (int i = 0; i < blocks; i++)
{
columns[i] = new CacheColumn <T>(words);
}
mainMemory = memory;
}
/// <summary>
/// 获取列的信息
/// </summary>
/// <returns></returns>
public static void InitColumn(Data_Source item, bool IsLoad, string tableName)
{
var key = string.Format(AppEtl.CacheKey.Column, item.Host, tableName);
if (RedisInfo.Exists(key, AppEtl.CacheDb) && IsLoad)
{
return;
}
var list = new List <CacheColumn>();
var dt = new DataTable();
using (var conn = DbProviderFactories.GetFactory(item.Type).CreateConnection())
{
conn.ConnectionString = AppCommon.GetConnStr(item);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = ColumnSql(item, tableName);
if (cmd.CommandText == "")
{
return;
}
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
}
foreach (DataRow row in dt.Rows)
{
var column = new CacheColumn();
column.Name = (row.ItemArray[0] == DBNull.Value ? "" : row.ItemArray[0].ToString());
column.Type = row.ItemArray[1] == DBNull.Value ? "" : row.ItemArray[1].ToString();
column.Length = row.ItemArray[2] == DBNull.Value ? 0 : int.Parse(row.ItemArray[2].ToString());
column.Comments = row.ItemArray[3] == DBNull.Value ? "" : row.ItemArray[3].ToString();
column.IsKey = row.ItemArray[4].ToString() != "0" ? true : false;
column.Precision = row.ItemArray[7] == DBNull.Value ? 0 : int.Parse(row.ItemArray[7].ToString());
column.ShowName = string.Format("{0}({1})", column.Name, column.Comments);
list.Add(column);
}
RedisInfo.Set <List <CacheColumn> >(key, list, 8640, AppEtl.CacheDb);
}
/// <summary>
/// 获取字段类型
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private static string GetFieldType(CacheColumn item, ConfigModel config, Data_Source source)
{
if (config.DbType.ToLower() == AppEtl.DataDbType.Oracle.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
#region sqlserver to oracle
if (string.IsNullOrEmpty(item.Type))
{
return("nvarchar2(255)");
}
switch (item.Type.ToLower())
{
case "bit":
case "int":
case "smallint":
case "tinyint":
return("integer");
case "datetime":
case "smalldatetime":
return("date");
case "decimal":
case "numeric":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "money":
case "smallmoney":
case "real":
return("real");
case "uniqueidentifier":
return(string.Format("char({0})", item.Length));
case "nchar":
case "nvarchar":
{
if (item.Length > 4000)
{
return("clob");
}
else
{
return(string.Format("nvarchar2({0})", item.Precision / 2));
}
}
case "varchar":
case "char":
{
if (item.Length > 4000)
{
return("clob");
}
else
{
return(string.Format("varchar2({0})", item.Precision));
}
}
case "text":
case "ntext":
return("clob");
case "binary":
case "varbinary":
case "image":
return("blob");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == AppEtl.DataDbType.Oracle.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
#region mysql to oracle
if (string.IsNullOrEmpty(item.Type))
{
return("nvarchar2(255)");
}
switch (item.Type.ToLower())
{
case "bigint":
return("number(19,0)");
case "bit":
case "tinyblob":
return("raw");
case "blob":
return("blob");
case "char":
return(string.Format("char{0}", item.Length));
case "date":
case "datetime":
case "time":
case "timestamp":
return("date");
case "deciaml":
case "double":
case "real":
case "double precision":
return("float(24)");
case "float":
return("float");
case "int":
case "integer":
return("number(10, 0)");
case "longblob":
case "mediumblog":
return("blob");
case "longtext":
case "mediumtext":
case "text":
case "tinytext":
case "varchar":
return("clob");
case "mediumint":
return("number(7,0)");
case "numeric":
case "year":
return("number");
case "smallint":
return("number(5,0)");
case "tinyint":
return("number(3,0)");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == AppEtl.DataDbType.SqlServer.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
#region oracle to sqlserver
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "char":
case "varchar2":
return(string.Format("varchar({0})", item.Length));
case "nchar":
case "nvarchar2":
return(string.Format("nvarchar({0})", item.Length / 2));
case "date":
return("datetime");
case "long":
return("text");
case "bfile":
case "blob":
case "long raw":
case "raw":
case "nrowid":
case "binary":
return("image");
case "rowid":
return("uniqueidentifier");
case "number":
case "decimal":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "integer":
return("int");
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == AppEtl.DataDbType.SqlServer.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
#region MySql to sqlserver
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "tinyint":
return("bit");
case "text":
return("ntext");
case "varchar":
return(string.Format("nvarchar({0})", item.Length));
case "char":
return(string.Format("nchar({0})", item.Length));
case "decimal":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == AppEtl.DataDbType.MySql.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
#region Oracle to MySql
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "colb":
return("text");
case "date":
return("datetime");
case "varchar2":
return(string.Format("varchar({0})", item.Length));
case "number":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
else if (config.DbType.ToLower() == AppEtl.DataDbType.MySql.ToLower() && source.Type.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
#region SqlServer to MySql
if (string.IsNullOrEmpty(item.Type))
{
return("varchar(255)");
}
switch (item.Type.ToLower())
{
case "datetime2":
case "datetimeoffset":
case "smalldatetime":
return("datetime");
case "uniqueidentifier":
return("varchar(40)");
case "bit":
return("bigint");
case "money":
case "real":
case "smallmoney":
return("float");
case "xml":
case "ntext":
return("text");
case "decimal":
case "numeric":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("decimal({0},{1})", item.Precision, item.Scale));
}
case "char":
return(string.Format("char({0})", item.Length));
case "nchar":
return(string.Format("char({0})", item.Length / 2));
case "varchar":
return(string.Format("varchar({0})", item.Length));
case "nvarchar":
return(string.Format("varchar({0})", item.Length / 2));
default:
return(item.Type);
}
#endregion
}
else
{
#region default
switch (item.Type.ToLower())
{
case "char":
case "varchar":
case "varchar2":
return(string.Format("{0}({1})", item.Type, item.Length == -1 ? "max" : item.Length.ToString()));
case "nchar":
case "nvarchar":
case "nvarchar2":
return(string.Format("{0}({1})", item.Type, item.Length == -1 ? "max" : (item.Length / 2).ToString()));
case "decimal":
case "numeric":
case "number":
if (item.Precision == 0 && item.Scale == 0)
{
return(item.Type);
}
else
{
return(string.Format("{0}({1},{2})", item.Type, item.Precision, item.Scale));
}
default:
return(item.Type);
}
#endregion
}
}
/// <summary>
/// 修改列备注
/// </summary>
/// <returns></returns>
public static bool UpdateColumnComment(DataContext db, Data_Business table, Data_Business_Details column, CacheColumn columnInfo, Data_Source dataSource)
{
columnInfo.Comments = columnInfo.Comments.Replace("'", "");
var sql = "";
if (db.config.DbType.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
sql = string.Format("alter table {0} modify {1} {2} comment '{3}'", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource), columnInfo);
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
sql = string.Format("Comment on column {0}.{1} is '{2}'", table.TableName, column.FieldName, columnInfo.Comments);
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
sql = string.Format("select count(0) from syscolumns where id = object_id('{0}') and name='{1}' and exists(select 1 from sys.extended_properties where object_id('{0}') = major_id and colid = minor_id", table.TableName, column.FieldName);
var count = db.ExecuteSql(sql, null, false).DicList[0]["count"].ToStr().ToInt(0);
if (count >= 1)
{
sql = string.Format("exec sys.sp_updateextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}',N'column',N'{2}'", columnInfo.Comments, table.TableName, column.FieldName);
}
else
{
sql = string.Format("exec sys.sp_addextendedproperty N'MS_Description',N'{0}',N'SCHEMA', N'dbo', N'TABLE',N'{1}',N'column',N'{2}'", columnInfo.Comments, table.TableName, column.FieldName);
}
}
return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess);
}
/// <summary>
/// 增加列
/// </summary>
/// <returns></returns>
public static WriteReturn AddColumn(DataContext db, Data_Business table, Data_Business_Details column, CacheColumn columnInfo, Data_Source dataSource, bool IsCheckKey = true)
{
if (columnInfo.IsKey && IsCheckKey)
{
return(db.ExecuteSql(string.Format("alter table {0} add {1} {2} primary key", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn);
}
else
{
return(db.ExecuteSql(string.Format("alter table {0} add {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn);
}
}
/// <summary>
/// 修改列
/// </summary>
/// <returns></returns>
public static bool UpdateColumn(DataContext db, Data_Business table, Data_Business_Details column, CacheColumn columnInfo, Data_Source dataSource)
{
var sql = "";
if (db.config.DbType.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource));
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource));
}
if (db.config.DbType.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
sql = string.Format("alter table {0} alter column {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource));
}
return(db.ExecuteSql(sql, null, false).writeReturn.IsSuccess);
}
