/// <summary>
/// 控件值给DataLink
/// </summary>
/// <param name="dbType"></param>
/// <param name="dbConn"></param>
/// <param name="txtHostName"></param>
/// <param name="txtUserName"></param>
/// <param name="txtPwd"></param>
/// <param name="txtPort"></param>
/// <param name="txtServerName"></param>
/// <param name="labServerName"></param>
/// <returns></returns>
public static Data_Source ControlsToData(string dbType, TextBox txtHostName, TextBox txtUserName, TextBox txtPwd,
TextBox txtPort, TextBox txtServerName, Label labServerName, bool isLink, TextBox txtLinkName)
{
var item = new Data_Source();
item.Type = dbType;
item.Host = txtHostName.Text.Trim();
item.UserName = txtUserName.Text.Trim();
item.PassWord = txtPwd.Text.Trim();
item.Port = txtPort.Text.Trim();
item.ServerName = txtServerName.Text.Trim();
if (isLink)
{
if (txtLinkName.Text.Trim() == "")
{
item.LinkName = GetLinkName(AppCache.GetLink);
}
else
{
item.LinkName = txtLinkName.Text.Trim();
}
}
return(item);
}
/// <summary>
/// 操作
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public IActionResult OnPostDataForm(Data_Source item)
{
var dbConn = AppCommon.GetConnStr(item);
if (!AppCommon.TestLink(item.Type, dbConn))
{
return(new JsonResult(new { success = false, msg = "连接失败" }));
}
var info = new WriteReturn();
using (var db = new DataContext(AppEtl.Db))
{
if (IFast.Query <Data_Source>(a => a.Id == item.Id).ToCount(db) == 0)
{
item.Id = Guid.NewGuid().ToString();
info = db.Add(item).writeReturn;
}
else
{
info = db.Update <Data_Source>(item, a => a.Id == item.Id).writeReturn;
}
if (info.IsSuccess)
{
return(new JsonResult(new { success = true, msg = "操作成功" }));
}
else
{
return(new JsonResult(new { success = false, msg = info.Message }));
}
}
}
/// <summary>
/// DataLink给控件值
/// </summary>
/// <param name="link"></param>
/// <param name="txtHostName"></param>
/// <param name="txtUserName"></param>
/// <param name="txtPwd"></param>
/// <param name="txtPort"></param>
/// <param name="txtServerName"></param>
/// <param name="labServerName"></param>
/// <param name="txtLinkName"></param>
public static void DataToControls(Data_Source link, ref TextBox txtHostName, ref TextBox txtUserName, ref TextBox txtPwd,
ref TextBox txtPort, ref TextBox txtServerName, ref Label labServerName, ref TextBox txtLinkName)
{
txtHostName.Text = link.Host;
txtUserName.Text = link.UserName;
txtPwd.Text = link.PassWord;
txtPort.Text = link.Port;
txtServerName.Text = link.ServerName;
txtLinkName.Text = link.LinkName;
if (link.Type == FastApp.DataDbType.Oracle)
{
labServerName.Content = "服务名：";
txtPort.Text = "1521";
}
else if (link.Type == FastApp.DataDbType.SqlServer)
{
labServerName.Content = "库名称：";
txtPort.Text = "1433";
}
else if (link.Type == FastApp.DataDbType.MySql)
{
txtPort.Text = "3306";
labServerName.Content = "库名称：";
}
}
/// <summary>
/// 加载
/// </summary>
/// <param name="id"></param>
public void OnGet(string id)
{
if (!string.IsNullOrEmpty(id))
{
using (var db = new DataContext(AppEtl.Db))
{
info = IFast.Query <Data_Source>(a => a.Id == id).ToItem <Data_Source>(db);
}
}
}
public double GetSOFastK(int offset)
{
var data = Data_Source.TakeLast(this.Rounds + offset).Take(this.Rounds).OrderBy(x => x.date_round).ToList();
var min = data.Min(x => x.minimun);
var max = data.Max(x => x.maximun);
if (min == max)
{
return(0);
}
return(100 * ((data.Last().closing - min) / (max - min)));
}
/// <summary>
/// 测试
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public IActionResult OnPostTest(Data_Source item)
{
var dbConn = AppCommon.GetConnStr(item);
if (AppCommon.TestLink(item.Type, dbConn))
{
return(new JsonResult(new { success = true, msg = "连接成功" }));
}
else
{
return(new JsonResult(new { success = false, msg = "连接失败" }));
}
}
public double GetEMA(int rounds, int offset)
{
double result = Data_Source.TakeLast((rounds * 2) + offset).Take(rounds).Average(x => x.closing);
var data = Data_Source.TakeLast(rounds + offset).Take(rounds);
foreach (Stock_Quote quote in data)
{
decimal Kdecimal = Decimal.Divide(2, rounds + 1);
double K = Double.Parse((Math.Truncate(100000000 * Kdecimal) / 100000000).ToString()); //set to 8 decimal places
result = (quote.closing * K) + (result * (1 - K));
}
return(result);
}
/// <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>
/// <param name="tableName"></param>
/// <returns></returns>
private static string ColumnSql(Data_Source item, string tableName)
{
var sql = "";
if (item.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
tableName = tableName.ToUpper();
sql = @"select a.column_name,data_type,data_length,b.comments,
(select count(0) from all_cons_columns aa, all_constraints bb
where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = '"
+ tableName + @"' and aa.column_name=a.column_name),(select count(0) from all_ind_columns t,all_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '"
+ tableName + @"' and t.column_name=a.column_name),nullable,data_precision,data_scale
from all_tab_columns a inner join all_col_comments b
on a.table_name='" + tableName +
"' and a.table_name=b.table_name and a.column_name=b.column_name order by a.column_id asc";
}
if (item.Type.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
sql = @"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where TABLE_SCHEMA='" + item.ServerName
+ "' and TABLE_NAME='" + tableName + @"' and constraint_name='PRIMARY' and c.column_name=a.column_name),
(SELECT count(0) from information_schema.statistics a where table_schema = '"
+ item.ServerName + "' and table_name = '" + tableName + @"' and c.column_name=a.column_name),
is_nullable,numeric_precision,numeric_scale,column_type from information_schema.columns c where table_name='"
+ tableName + "' order by ordinal_position asc";
}
if (item.Type.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
sql = @"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='"
+ tableName + @"' and COLUMN_NAME=a.name),
(SELECT count(0) FROM sysindexes aa JOIN sysindexkeys bb ON aa.id=bb.id AND aa.indid=bb.indid
JOIN sysobjects cc ON bb.id=cc.id JOIN syscolumns dd ON bb.id=dd.id AND bb.colid=dd.colid
WHERE aa.indid NOT IN(0,255) AND cc.name='" + tableName + @"' and dd.name=a.name),isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('" + tableName + "') order by a.colid asc";
}
return(sql);
}
/// <summary>
/// 表说明
/// </summary>
/// <returns></returns>
public static void InitTable(Data_Source item, bool IsLoad)
{
var key = string.Format(AppEtl.CacheKey.Table, item.Host);
if (RedisInfo.Exists(key, AppEtl.CacheDb) && IsLoad)
{
return;
}
var list = new List <CacheTable>();
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 = TableSql(item);
if (cmd.CommandText == "")
{
return;
}
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
}
foreach (DataRow row in dt.Rows)
{
var table = new CacheTable();
table.Comments = row.ItemArray[1] == DBNull.Value ? "" : row.ItemArray[1].ToString();
table.Name = row.ItemArray[0] == DBNull.Value ? "" : row.ItemArray[0].ToString();
list.Add(table);
Parallel.Invoke(() => {
InitColumn(item, IsLoad, table.Name);
});
}
RedisInfo.Set <List <CacheTable> >(key, list, 8640, AppEtl.CacheDb);
}
/// <summary>
/// 获取表语句
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private static string TableSql(Data_Source item)
{
var sql = "";
if (item.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
sql = "select a.table_name,comments from all_tables a inner join all_tab_comments b on a.TABLE_NAME=b.TABLE_NAME and a.TABLESPACE_NAME!='SYSAUX' and a.TABLESPACE_NAME!='SYSTEM'";
}
if (item.Type.ToLower() == AppEtl.DataDbType.MySql.ToLower())
{
sql = string.Format("select table_name, table_comment from information_schema.TABLES where table_schema='{0}' and table_type='BASE TABLE'", item.ServerName);
}
if (item.Type.ToLower() == AppEtl.DataDbType.SqlServer.ToLower())
{
sql = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type='U'";
}
return(sql);
}
/// <summary>
/// 获取连接字符串
/// </summary>
private static string GetConnStr(Data_Source link)
{
var connStr = "";
if (link.Type == FastApp.DataDbType.Oracle)
{
connStr = string.Format("User Id={0};Password={1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})));pooling=true;Min Pool Size=1;Max Pool Size=5;"
, link.UserName, link.PassWord, link.Host, link.Port, link.ServerName);
}
else if (FastApp.DataDbType.SqlServer == link.Type)
{
connStr = string.Format("Data Source={0},{1};Initial Catalog={2};User Id={3};Password={4};pooling=true;Min Pool Size=1;Max Pool Size=5;"
, link.Host, link.Port, link.ServerName, link.UserName, link.PassWord);
}
else if (FastApp.DataDbType.MySql == link.Type)
{
connStr = string.Format("server={0};port={1};Database={2};user id={3};password={4};pooling=true;Min Pool Size=1;Max Pool Size=5;CharSet=utf8;"
, link.Host, link.Port, link.ServerName, link.UserName, link.PassWord);
}
return(connStr);
}
/// <summary>
/// 获取连接字符串
/// </summary>
public static string GetConnStr(Data_Source item)
{
var connStr = "";
if (item.Type.ToLower() == AppEtl.DataDbType.Oracle.ToLower())
{
connStr = string.Format("User Id={0};Password={1};Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})));pooling=true;Min Pool Size=1;Max Pool Size=5;"
, item.UserName.Trim(), item.PassWord.Trim(), item.Host.Trim(), item.Port.Trim(), item.ServerName.Trim());
}
else if (AppEtl.DataDbType.SqlServer.ToLower() == item.Type.ToLower())
{
connStr = string.Format("Data Source={0},{1};Initial Catalog={2};User Id={3};Password={4};pooling=true;Min Pool Size=1;Max Pool Size=5;"
, item.Host.Trim(), item.Port.Trim(), item.ServerName.Trim(), item.UserName.Trim(), item.PassWord.Trim());
}
else if (AppEtl.DataDbType.MySql.ToLower() == item.Type.ToLower())
{
connStr = string.Format("server={0};port={1};Database={2};user id={3};password={4};pooling=true;Min Pool Size=1;Max Pool Size=5;CharSet=utf8;"
, item.Host.Trim(), item.Port.Trim(), item.ServerName.Trim(), item.UserName.Trim(), item.PassWord.Trim());
}
return(connStr);
}
public static void SetLink(Data_Source item)
{
CacheDb.Set <Data_Source>("Link", item);
}
/// <summary>
/// 获取字段类型
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private static string GetFieldType(Cache_Column item, ConfigModel config, Data_Source source)
{
if (config.DbType.ToLower() == DataDbType.Oracle.ToLower() && source.Type.ToLower() == 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() == DataDbType.Oracle.ToLower() && source.Type.ToLower() == 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() == DataDbType.SqlServer.ToLower() && source.Type.ToLower() == 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() == DataDbType.SqlServer.ToLower() && source.Type.ToLower() == 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() == DataDbType.MySql.ToLower() && source.Type.ToLower() == 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() == DataDbType.MySql.ToLower() && source.Type.ToLower() == 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 UpdateColumn(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource)
{
var sql = "";
if (db.config.DbType == DataDbType.Oracle)
{
sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource));
}
if (db.config.DbType == DataDbType.MySql)
{
sql = string.Format("alter table {0} modify {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource));
}
if (db.config.DbType == DataDbType.SqlServer)
{
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);
}
/// <summary>
/// 增加列
/// </summary>
/// <returns></returns>
public static bool AddColumn(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource)
{
return(db.ExecuteSql(string.Format("alter table {0} add {1} {2}", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource)), null, false).writeReturn.IsSuccess);
}
/// <summary>
/// 修改列备注
/// </summary>
/// <returns></returns>
public static bool UpdateColumnComment(DataContext db, Data_Business table, Data_Business_Details column, Cache_Column columnInfo, Data_Source dataSource)
{
var sql = "";
if (db.config.DbType == DataDbType.MySql)
{
sql = string.Format("alter table {0} modify {1} {2} comment '{3}'", table.TableName, column.FieldName, GetFieldType(columnInfo, db.config, dataSource), columnInfo.Comments);
}
if (db.config.DbType == DataDbType.Oracle)
{
sql = string.Format("Comment on column {0}.{1} is '{2}'", table.TableName, column.FieldName, columnInfo.Comments);
}
if (db.config.DbType == DataDbType.SqlServer)
{
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);
}
//列list
public static List <Cache_Column> GetColumnList(Data_Source link, string table)
{
return(CacheDb.Get <List <Cache_Column> >(GetColumnKey(link, table)) ?? new List <Cache_Column>());
}
/// <summary>
/// 加载数据源
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
public IActionResult OnPostLoadCache(Data_Source item)
{
Task.Run(() => { DataSchema.InitTable(item, true); });
return(new JsonResult(new { success = true, msg = "操作成功" }));
}
private static string GetTableKey(Data_Source link)
{
return(string.Format("tableList_{0}", link.Host));
}
//表list
public static List <Cache_Table> GetTableList(Data_Source link)
{
return(CacheDb.Get <List <Cache_Table> >(GetTableKey(link)) ?? new List <Cache_Table>());
}
public static void SetTableList(List <Cache_Table> item, Data_Source link)
{
CacheDb.Set <List <Cache_Table> >(GetTableKey(link), item);
}
public static bool ExistsColumn(Data_Source link, string table)
{
return(CacheDb.Exists(GetColumnKey(link, table)));
}
public static void SetColumnList(List <Cache_Column> item, Data_Source link, string table)
{
CacheDb.Set <List <Cache_Column> >(GetColumnKey(link, table), item);
}
/// <summary>
/// 连接名格式
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="userName">用户名</param>
/// <param name="serverValue">数据库名</param>
/// <returns></returns>
public static string GetLinkName(Data_Source item)
{
return(string.Format("{0}_{1}_{2}", item.Type, item.UserName, item.ServerName));
}
private static string GetColumnKey(Data_Source link, string table)
{
return(string.Format("columnList_{0}_{1}", link.Host, table));
}
/// <summary>
/// 获取列的信息
/// </summary>
/// <returns></returns>
private static void InitColumn(Data_Source link, bool IsLoad, string tableName)
{
if (AppCache.ExistsColumn(link, tableName) && IsLoad)
{
return;
}
var list = new List <Cache_Column>();
var dt = new DataTable();
//oracle 列信息
if (link.Type == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(BaseLink.GetConnStr(link)))
{
tableName = tableName.ToUpper();
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.column_name,data_type,data_length,b.comments,
(select count(0) from all_cons_columns aa, all_constraints bb
where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = '"
+ tableName + @"' and aa.column_name=a.column_name),(select count(0) from all_ind_columns t,all_indexes i
where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = '"
+ tableName + @"' and t.column_name=a.column_name),nullable,data_precision,data_scale
from all_tab_columns a inner join all_col_comments b
on a.table_name='" + tableName +
"' and a.table_name=b.table_name and a.column_name=b.column_name order by a.column_id asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.SqlServer)
{
#region sql server
using (var conn = new SqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='"
+ tableName + @"' and COLUMN_NAME=a.name),
(SELECT count(0) FROM sysindexes aa JOIN sysindexkeys bb ON aa.id=bb.id AND aa.indid=bb.indid
JOIN sysobjects cc ON bb.id=cc.id JOIN syscolumns dd ON bb.id=dd.id AND bb.colid=dd.colid
WHERE aa.indid NOT IN(0,255) AND cc.name='" + tableName + @"' and dd.name=a.name),isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('" + tableName + "') order by a.colid asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
if (link.Type == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where TABLE_SCHEMA='" + link.ServerName
+ "' and TABLE_NAME='" + tableName + @"' and constraint_name='PRIMARY' and c.column_name=a.column_name),
(SELECT count(0) from information_schema.statistics a where table_schema = '"
+ link.ServerName + "' and table_name = '" + tableName + @"' and c.column_name=a.column_name),
is_nullable,numeric_precision,numeric_scale,column_type from information_schema.columns c where table_name='"
+ tableName + "' order by ordinal_position asc";
var rd = cmd.ExecuteReader();
dt.Load(rd);
rd.Close();
conn.Close();
}
#endregion
}
foreach (DataRow item in dt.Rows)
{
var column = new Cache_Column();
column.Name = (item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString());
column.Type = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
column.Length = item.ItemArray[2] == DBNull.Value ? 0 : decimal.Parse(item.ItemArray[2].ToString());
column.Comments = item.ItemArray[3] == DBNull.Value ? "" : item.ItemArray[3].ToString();
column.Precision = item.ItemArray[7] == DBNull.Value ? 0 : int.Parse(item.ItemArray[7].ToString());
column.ShowName = string.Format("{0}({1})", column.Name, column.Comments);
list.Add(column);
}
AppCache.SetColumnList(list, link, tableName);
}
/// <summary>
/// 表说明
/// </summary>
/// <returns></returns>
public static void InitTable(Data_Source link, bool IsLoad)
{
if (AppCache.ExistsTable(link) && IsLoad)
{
return;
}
var list = new List <Cache_Table>();
var dt = new DataTable();
//oracle 表信息
if (link.Type == DataDbType.Oracle)
{
#region oracle
using (var conn = new OracleConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select a.table_name,comments from all_tables a inner join all_tab_comments b on a.TABLE_NAME=b.TABLE_NAME and a.TABLESPACE_NAME!='SYSAUX' and a.TABLESPACE_NAME!='SYSTEM'";
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
//sql server 表信息
if (link.Type == DataDbType.SqlServer)
{
#region sqlserver
using (var conn = new SqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type='U'";
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
//mysql 表信息
if (link.Type == DataDbType.MySql)
{
#region mysql
using (var conn = new MySqlConnection(BaseLink.GetConnStr(link)))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select table_name, table_comment from information_schema.TABLES where table_schema='{0}' and table_type='BASE TABLE'", link.ServerName);
var rd = cmd.ExecuteReader();
dt.Load(rd);
conn.Close();
}
#endregion
}
foreach (DataRow item in dt.Rows)
{
var table = new Cache_Table();
table.Comments = item.ItemArray[1] == DBNull.Value ? "" : item.ItemArray[1].ToString();
table.Name = item.ItemArray[0] == DBNull.Value ? "" : item.ItemArray[0].ToString();
list.Add(table);
Parallel.Invoke(() => {
InitColumn(link, IsLoad, table.Name);
});
}
AppCache.SetTableList(list, link);
}
public static bool ExistsTable(Data_Source link)
{
return(CacheDb.Exists(GetTableKey(link)));
}
