/// <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))); }