internal static void CheckAndCreateOracleSequence(string seqName, string conn, string primaryKey, string tableName) { seqName = seqName.ToUpper(); using (DbBase db = DalCreate.CreateDal(conn)) { object o = db.ExeScalar(string.Format(TableSchema.ExistOracleSequence, seqName), false); if (o == null || Convert.ToString(o) == "0") { int startWith = 1; if (!string.IsNullOrEmpty(primaryKey)) { o = db.ExeScalar(string.Format(TableSchema.GetOracleMaxID, primaryKey, tableName), false); if (!int.TryParse(Convert.ToString(o), out startWith) || startWith < 1) { startWith = 1; } else { startWith++; } } db.ExeNonQuery(string.Format(TableSchema.CreateOracleSequence, seqName, startWith), false); } } }
internal static void CheckAndCreateOracleSequence(string seqName, string conn, string primaryKey, string tableName) { seqName = seqName.ToUpper(); using (DalBase db = DalCreate.CreateDal(conn)) { object o = db.ExeScalar(string.Format(ExistOracleSequence, seqName), false); if (db.RecordsAffected != -2 && (o == null || Convert.ToString(o) == "0")) { int startWith = 1; if (!string.IsNullOrEmpty(primaryKey)) { o = db.ExeScalar(string.Format(GetOracleMaxID, primaryKey, tableName), false); if (db.RecordsAffected != -2) { if (!int.TryParse(Convert.ToString(o), out startWith) || startWith < 1) { startWith = 1; } else { startWith++; } } } db.ExeNonQuery(string.Format(CreateOracleSequence, seqName, startWith), false); } if (db.RecordsAffected == -2) { _ErrorMsg.AppendLine("CheckAndCreateOracleSequence:" + db.DebugInfo.ToString()); } } }
private void GetProcs() { if (_Procs.Count == 0) { lock (lockProcObj) { if (_Procs.Count == 0) { using (DalBase dal = DalCreate.CreateDal(ConnString)) { Dictionary <string, string> procs = dal.GetProcs(); if (procs != null && procs.Count > 0) { Dictionary <string, TableInfo> dic = new Dictionary <string, TableInfo>(); foreach (KeyValuePair <string, string> item in procs) { string hash = TableInfo.GetHashKey(item.Key); if (!dic.ContainsKey(hash)) { dic.Add(hash, new TableInfo(item.Key, "P", item.Value, this)); } } _Procs = dic; } } } } } }
/// <summary> /// 获取表列架构(链接错误时,抛异常) /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="errInfo">出错时的错误信息</param> /// <returns></returns> public static MDataColumn GetColumns(string tableName, string conn, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn()) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + tableName; } helper.Dispose(); Error.Throw(errInfo); return(null); } if (!tableName.Contains(" ")) { tableName = GetMapTableName(conn, tableName); } MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper); helper.Dispose(); return(mdc); }
/// <summary> /// 移除一张表 /// <param name="conn">数据库链接</param> /// </summary> public static bool DropTable(string tableName, string conn) { bool result = false; string key = string.Empty; using (DbBase helper = DalCreate.CreateDal(conn)) { key = TableSchema.GetTableCacheKey(helper); DalType dalType = helper.dalType; switch (dalType) { case DalType.Txt: case DalType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName); string path = folder + ".ts"; try { if (File.Exists(path)) { result = IOHelper.Delete(path); } path = folder + (dalType == DalType.Txt ? ".txt" : ".xml"); if (File.Exists(path)) { result = IOHelper.Delete(path); } } catch { } break; default: result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2; if (result) { //处理表相关的元数据和数据缓存。 RemoveCache(tableName, helper.DataBase, dalType); } break; } if (helper.recordsAffected == -2) { _ErrorMsg.AppendLine(helper.debugInfo.ToString()); } } if (result) { //处理数据库表字典缓存 if (TableSchema.tableCache.ContainsKey(key)) { Dictionary <string, string> tableDic = TableSchema.tableCache[key]; if (tableDic.ContainsKey(tableName)) { tableDic.Remove(tableName); } } } return(result); }
private static DBInfo GetSchemaDic(string conn) { DBInfo info = new DBInfo(); using (DalBase dal = DalCreate.CreateDal(conn)) { info.ConnName = dal.ConnObj.Master.ConnName; info.ConnString = dal.ConnObj.Master.ConnString; info.DataBaseName = dal.DataBaseName; info.DataBaseType = dal.DataBaseType; info.DataBaseVersion = dal.Version; Dictionary <string, string> tables = dal.GetTables(); if (tables != null && tables.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in tables) { int hash = TableInfo.GetHashCode(item.Key); if (!dic.ContainsKey(hash)) { dic.Add(hash, new TableInfo(item.Key, "U", item.Value, info)); } } info.Tables = dic; } Dictionary <string, string> views = dal.GetViews(); if (views != null && views.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in views) { int hash = TableInfo.GetHashCode(item.Key); if (!dic.ContainsKey(hash)) { dic.Add(hash, new TableInfo(item.Key, "V", item.Value, info)); } } info.Views = dic; } Dictionary <string, string> procs = dal.GetProcs(); if (procs != null && procs.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in procs) { int hash = TableInfo.GetHashCode(item.Key); if (!dic.ContainsKey(hash)) { dic.Add(hash, new TableInfo(item.Key, "P", item.Value, info)); } } info.Procs = dic; } } return(info); }
internal static void CreateSelectBaseProc(DalType dal, string conn) { try { switch (dal) { //case DalType.Oracle: // if (!flag.Contains("oracle")) // { // flag.Add("oracle"); // using (DbBase db = DalCreate.CreateDal(conn)) // { // db.AllowRecordSql = false; // object o = db.ExeScalar(string.Format(ExistOracle.Replace("TABLE", "PROCEDURE"), "MyPackage.SelectBase"), false); // if (o != null && Convert.ToInt32(o) < 1) // { // db.ExeNonQuery(SqlPager.GetPackageHeadForOracle(), false); // db.ExeNonQuery(SqlPager.GetPackageBodyForOracle(), false); // } // } // } // break; case DalType.MsSql: if (!flag.Contains("sql")) { flag.Add("sql"); //考虑到一个应用不太可能同时使用mssql的不同版本,只使用一个标识。 using (DbBase db = DalCreate.CreateDal(conn)) { db.IsAllowRecordSql = false; object o = null; if (!db.Version.StartsWith("08")) { // o = db.ExeScalar(string.Format(Exist2000.Replace("U", "P"), "SelectBase"), false); // if (o != null && Convert.ToInt32(o) < 1) // { // db.ExeNonQuery(SqlPager.GetSelectBaseForSql2000(), false); // } //} //else //{ o = db.ExeScalar(string.Format(TableSchema.Exist2005, "SelectBase", "P"), false); if (o != null && Convert.ToInt32(o) < 1) { db.ExeNonQuery(SqlCreateForPager.GetSelectBaseForSql2005(), false); } } } } break; } } catch (Exception err) { Log.WriteLogToTxt(err); } }
private void GetVersion() { if (string.IsNullOrEmpty(_DataBaseVersion)) { using (DalBase dal = DalCreate.CreateDal(ConnString)) { _DataBaseVersion = dal.Version; } } }
internal bool LoadDataInsert(DalType dalType, bool keepID) { bool fillGUID = CheckGUIDAndDateTime(dalType); bool isNeedCreateDal = (_dalHelper == null); if (isNeedCreateDal && dalType != DalType.Oracle) { _dalHelper = DalCreate.CreateDal(_Conn); _dalHelper.isAllowInterWriteLog = false; } string path = MDataTableToFile(mdt, fillGUID ? true : keepID, dalType); string formatSql = dalType == DalType.MySql ? SqlCreate.MySqlBulkCopySql : SqlCreate.OracleBulkCopySql; string sql = string.Format(formatSql, path, SqlFormat.Keyword(mdt.TableName, dalType), AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepID, dalType)); if (dalType == DalType.Oracle) { string ctlPath = CreateCTL(sql, path); sql = string.Format(SqlCreate.OracleSqlIDR, "sa/123456@ORCL", ctlPath, ctlPath.Replace(".ctl", ".out"));//只能用进程处理 } try { if (dalType == DalType.Oracle) { return(ExeSqlLoader(sql)); } else { if (_dalHelper.ExeNonQuery(sql, false) != -2) { return(true); } } } catch (Exception err) { if (err.InnerException != null) { err = err.InnerException; } sourceTable.DynamicData = err; Log.WriteLogToTxt(err); } finally { if (isNeedCreateDal && _dalHelper != null) { _dalHelper.Dispose(); _dalHelper = null; } // File.Delete(path); } return(false); }
/// <summary> /// 移除一张表 /// <param name="conn">数据库链接</param> /// </summary> public static bool DropTable(string tableName, string conn) { bool result = false; string key = string.Empty; using (DalBase helper = DalCreate.CreateDal(conn)) { DataBaseType dalType = helper.DataBaseType; switch (dalType) { case DataBaseType.Txt: case DataBaseType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName); string path = folder + ".ts"; try { if (File.Exists(path)) { result = IOHelper.Delete(path); } path = folder + (dalType == DataBaseType.Txt ? ".txt" : ".xml"); if (File.Exists(path)) { result = IOHelper.Delete(path); } } catch { } break; default: result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2; if (result) { //处理表相关的元数据和数据缓存。 RemoveCache(tableName, conn); } break; } if (helper.RecordsAffected == -2) { _ErrorMsg.AppendLine(helper.DebugInfo.ToString()); } } if (result) { //处理数据库表字典缓存 CrossDB.Remove(tableName, "U", conn); } return(result); }
/// <summary> /// 获得表的描述 /// </summary> internal static string GetTableDescription(string conn, string tableName) { using (DbBase dbBase = DalCreate.CreateDal(conn)) { string key = GetTableCacheKey(dbBase); if (tableCache.ContainsKey(key)) { if (tableCache[key].ContainsKey(tableName)) { return(tableCache[key][tableName]); } } return(string.Empty); } }
/// <summary> /// 检测表是否存在 /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="dalType">数据库类型</param> public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database) { dalType = DalType.None; database = string.Empty; if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")")) { return(false); } using (DalBase helper = DalCreate.CreateDal(conn)) { dalType = helper.DataBaseType; database = helper.DataBase; } return(TableSchema.Exists(tableName, "U", conn)); }
/// <summary> /// 获取表架构 /// </summary> /// <param name="conn">数据库链接</param> /// <param name="connectionName">指定要返回架构的名称</param> /// <param name="restrictionValues">为指定的架构返回一组限制值</param> public static DataTable GetSchema(string conn, string connectionName, string[] restrictionValues) { DbBase helper = DalCreate.CreateDal(conn); if (!helper.TestConn()) { return(null); } helper.Con.Open(); DataTable dt = helper.Con.GetSchema(connectionName, restrictionValues); helper.Con.Close(); helper.Dispose(); return(dt); }
/// <summary> /// 检测表是否存在 /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="dalType">数据库类型</param> public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database) { dalType = DalType.None; database = string.Empty; if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")")) { return(false); } DbBase helper = DalCreate.CreateDal(conn); dalType = helper.dalType; database = helper.DataBase; bool result = TableSchema.Exists("U", tableName, ref helper); helper.Dispose(); return(result); }
private static DBInfo GetSchemaDic(string conn) { DalBase dal = DalCreate.CreateDal(conn); DBInfo info = new DBInfo(); info.ConnName = dal.ConnObj.Master.ConnName; info.ConnString = dal.ConnObj.Master.ConnString; info.DataBaseName = dal.DataBase; Dictionary <string, string> tables = TableSchema.GetTables(conn); if (tables != null && tables.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in tables) { dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "U", item.Value, info)); } info.Tables = dic; } Dictionary <string, string> views = TableSchema.GetViews(conn); if (views != null && views.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in views) { dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "V", item.Value, info)); } info.Views = dic; } Dictionary <string, string> procs = TableSchema.GetProcs(conn); if (procs != null && procs.Count > 0) { Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>(); foreach (KeyValuePair <string, string> item in procs) { dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "P", item.Value, info)); } info.Procs = dic; } return(info); }
/// <summary> /// 获取所有表(表名+表说明)【链接错误时,抛异常】 /// </summary> /// <param name="conn">数据库链接</param> /// <param name="dbName">返回指定链接的数据库名称</param> /// <param name="errInfo">链接错误时的信息信息</param> public static Dictionary <string, string> GetTables(string conn, out string dbName, out string errInfo) { errInfo = string.Empty; using (DalBase helper = DalCreate.CreateDal(conn)) { dbName = helper.DataBase; if (!helper.TestConn(AllowConnLevel.MaterBackupSlave)) { errInfo = helper.DebugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = helper.DataBaseType + " Open database fail : " + dbName; } return(null); } } return(TableSchema.GetTables(conn));//内部有缓存 }
internal bool MySqlBulkCopyInsert(bool keepID) { bool fillGUID = CheckGUIDAndDateTime(DalType.MySql); string conn = DalCreate.FormatConn(DalType.MySql, AppConfig.GetConn(_Conn)); bool isNeedCreateDal = (_dalHelper == null); if (isNeedCreateDal) { _dalHelper = DalCreate.CreateDal(conn); _dalHelper.isAllowInterWriteLog = false; } string path = MDataTableToFile(mdt, fillGUID ? true : keepID); string sql = string.Format(SqlCreate.MySqlBulkCopySql, path, SqlFormat.Keyword(mdt.TableName, DalType.MySql), AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepID, DalType.MySql)); try { if (_dalHelper.ExeNonQuery(sql, false) != -2) { return(true); } } catch (Exception err) { if (err.InnerException != null) { err = err.InnerException; } sourceTable.DynamicData = err; Log.WriteLogToTxt(err); } finally { if (isNeedCreateDal) { _dalHelper.Dispose(); _dalHelper = null; } // File.Delete(path); } return(false); }
/// <summary> /// 移除一张表 /// <param name="conn">数据库链接</param> /// </summary> public static bool DropTable(string tableName, string conn) { bool result = false; using (DbBase helper = DalCreate.CreateDal(conn)) { DalType dalType = helper.dalType; switch (dalType) { case DalType.Txt: case DalType.Xml: string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName); string path = folder + ".ts"; try { if (File.Exists(path)) { result = IOHelper.Delete(path); } path = folder + (dalType == DalType.Txt ? ".txt" : ".xml"); if (File.Exists(path)) { result = IOHelper.Delete(path); } } catch { } break; default: result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2; if (result) { RemoveCache(tableName, helper.DataBase, dalType); } break; } } return(result); }
/// <summary> /// 获取所有表(表名+表说明)【链接错误时,抛异常】 /// </summary> /// <param name="conn">数据库链接</param> /// <param name="dbName">返回指定链接的数据库名称</param> /// <param name="errInfo">链接错误时的信息信息</param> public static Dictionary <string, string> GetTables(string conn, out string dbName, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); helper.IsAllowRecordSql = false; dbName = helper.DataBase; if (!helper.TestConn()) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + dbName; } helper.Dispose(); return(null); } Dictionary <string, string> tables = TableSchema.GetTables(ref helper);//内部有缓存 helper.Dispose(); return(tables); }
/// <summary> /// 获取表列架构(链接错误时,抛异常) /// </summary> /// <param name="tableName">表名</param> /// <param name="conn">数据库链接</param> /// <param name="errInfo">出错时的错误信息</param> /// <returns></returns> public static MDataColumn GetColumns(string tableName, string conn, out string errInfo) { errInfo = string.Empty; DbBase helper = DalCreate.CreateDal(conn); DbResetResult result = helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 switch (result) { case DbResetResult.No_DBNoExists: helper.Dispose(); return(null); case DbResetResult.No_SaveDbName: case DbResetResult.Yes: tableName = SqlFormat.NotKeyword(tableName); //same database no need database.tablename break; } if (!helper.TestConn(AllowConnLevel.MaterBackupSlave)) { errInfo = helper.debugInfo.ToString(); if (string.IsNullOrEmpty(errInfo)) { errInfo = "Open database fail : " + tableName; } helper.Dispose(); Error.Throw(errInfo); return(null); } if (!tableName.Contains(" "))// { tableName = GetMapTableName(conn, tableName); } MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper); helper.Dispose(); return(mdc); }
public static bool TestConn(string conn, out string msg) { bool result = false; try { DbBase helper = DalCreate.CreateDal(conn); result = helper.TestConn(); if (result) { msg = helper.Version; } else { msg = helper.debugInfo.ToString(); } helper.Dispose(); } catch (Exception err) { msg = err.Message; } return(result); }
public static MDataColumn GetColumns(string tableName, string conn) { string key = GetSchemaKey(tableName, conn); #region 缓存检测 if (_ColumnCache.ContainsKey(key)) { return(_ColumnCache[key].Clone()); } if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts"; if (System.IO.File.Exists(fullPath)) { MDataColumn columns = MDataColumn.CreateFrom(fullPath); if (columns.Count > 0) { CacheManage.LocalInstance.Set(key, columns.Clone(), 1440); return(columns); } } } #endregion string fixName; conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn); tableName = fixName; if (conn == null) { return(null); } MDataColumn mdcs = null; using (DalBase dbHelper = DalCreate.CreateDal(conn)) { DataBaseType dalType = dbHelper.DataBaseType; if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml) { #region 文本数据库处理。 if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1 { tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName; tableName = Path.GetFileNameWithoutExtension(tableName); //视图表,带“.“的,会出问题 string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml"); mdcs = MDataColumn.CreateFrom(fileName); mdcs.DataBaseType = dalType; mdcs.Conn = conn; } #endregion } else { #region 其它数据库 mdcs = new MDataColumn(); mdcs.Conn = conn; mdcs.TableName = tableName; mdcs.DataBaseType = dalType; tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字:引号 //如果table和helper不在同一个库 DalBase helper = dbHelper.ResetDalBase(tableName); helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统,不记录SQL表结构语句。 try { bool isView = tableName.Contains(" ");//是否视图。 if (!isView) { isView = CrossDB.Exists(tableName, "V", conn); } if (!isView) { TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn); if (info != null) { mdcs.Description = info.Description; } } MCellStruct mStruct = null; SqlDbType sqlType = SqlDbType.NVarChar; if (isView) { string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName); mdcs = GetViewColumns(sqlText, ref helper); } else { mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName)); switch (dalType) { case DataBaseType.MsSql: case DataBaseType.Oracle: case DataBaseType.MySql: case DataBaseType.Sybase: case DataBaseType.PostgreSQL: #region Sql string sql = string.Empty; if (dalType == DataBaseType.MsSql) { #region Mssql string dbName = null; if (!helper.Version.StartsWith("08")) { //先获取同义词,检测是否跨库 string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_SynonymsName, SqlFormat.NotKeyword(tableName)), false)); if (!string.IsNullOrEmpty(realTableName)) { string[] items = realTableName.Split('.'); tableName = realTableName; if (items.Length > 0) //跨库了 { dbName = realTableName.Split('.')[0]; } } } sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBaseName); #endregion } else if (dalType == DataBaseType.MySql) { sql = GetMySqlColumns(helper.DataBaseName); } else if (dalType == DataBaseType.Oracle) { tableName = tableName.ToUpper(); //Oracle转大写。 //先获取同义词,不检测是否跨库 string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false)); if (!string.IsNullOrEmpty(realTableName)) { tableName = realTableName; } sql = GetOracleColumns(); } else if (dalType == DataBaseType.Sybase) { tableName = SqlFormat.NotKeyword(tableName); sql = GetSybaseColumns(); } else if (dalType == DataBaseType.PostgreSQL) { sql = GetPostgreColumns(); } helper.AddParameters("TableName", SqlFormat.NotKeyword(tableName), DbType.String, 150, ParameterDirection.Input); DbDataReader sdr = helper.ExeDataReader(sql, false); if (sdr != null) { long maxLength; bool isAutoIncrement = false; short scale = 0; string sqlTypeName = string.Empty; while (sdr.Read()) { short.TryParse(Convert.ToString(sdr["Scale"]), out scale); if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength)) //mysql的长度可能大于int.MaxValue { maxLength = -1; } else if (maxLength > int.MaxValue) { maxLength = int.MaxValue; } sqlTypeName = Convert.ToString(sdr["SqlType"]); sqlType = DataType.GetSqlType(sqlTypeName); isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]); mStruct = new MCellStruct(mdcs.DataBaseType); mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim(); mStruct.OldName = mStruct.ColumnName; mStruct.SqlType = sqlType; mStruct.IsAutoIncrement = isAutoIncrement; mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]); mStruct.MaxSize = (int)maxLength; mStruct.Scale = scale; mStruct.Description = Convert.ToString(sdr["Description"]); mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType); mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1"; switch (dalType) { case DataBaseType.MsSql: case DataBaseType.MySql: case DataBaseType.Oracle: mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1"; mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1"; mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]); break; } mStruct.SqlTypeName = sqlTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); mdcs.Add(mStruct); } sdr.Close(); if (dalType == DataBaseType.Oracle && mdcs.Count > 0) //默认没有自增概念,只能根据情况判断。 { MCellStruct firstColumn = mdcs[0]; if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1) { firstColumn.IsAutoIncrement = true; } } } #endregion break; case DataBaseType.SQLite: #region SQlite if (helper.Con.State != ConnectionState.Open) { helper.Con.Open(); } DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(tableName) }); if (!helper.IsOpenTrans) { helper.Con.Close(); } int size; short sizeScale; string dataTypeName = string.Empty; foreach (DataRow row in sqliteDt.Rows) { object len = row["NUMERIC_PRECISION"]; if (len == null || len == DBNull.Value) { len = row["CHARACTER_MAXIMUM_LENGTH"]; } short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale); if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于int.MaxValue { size = -1; } dataTypeName = Convert.ToString(row["DATA_TYPE"]); if (dataTypeName == "text" && size > 0) { sqlType = DataType.GetSqlType("varchar"); } else { sqlType = DataType.GetSqlType(dataTypeName); } //COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size); mStruct.Scale = sizeScale; mStruct.Description = Convert.ToString(row["DESCRIPTION"]); mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType); //"COLUMN_DEFAULT" mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]); mStruct.SqlTypeName = dataTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); mdcs.Add(mStruct); } #endregion break; case DataBaseType.Access: #region Access DataTable keyDt, valueDt; string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName); OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString); OleDbCommand com = new OleDbCommand(sqlText, con); con.Open(); keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable(); valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) }); con.Close(); con.Dispose(); if (keyDt != null && valueDt != null) { string columnName = string.Empty, sqlTypeName = string.Empty; bool isKey = false, isCanNull = true, isAutoIncrement = false; int maxSize = -1; short maxSizeScale = 0; SqlDbType sqlDbType; foreach (DataRow row in keyDt.Rows) { columnName = row["ColumnName"].ToString(); isKey = Convert.ToBoolean(row["IsKey"]); //IsKey isCanNull = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]); sqlTypeName = Convert.ToString(row["DataType"]); sqlDbType = DataType.GetSqlType(sqlTypeName); short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale); if (Convert.ToInt32(row["NumericPrecision"]) > 0) //NumericPrecision { maxSize = Convert.ToInt32(row["NumericPrecision"]); } else { long len = Convert.ToInt64(row["ColumnSize"]); if (len > int.MaxValue) { maxSize = int.MaxValue; } else { maxSize = (int)len; } } mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize); mStruct.Scale = maxSizeScale; mStruct.IsPrimaryKey = isKey; mStruct.SqlTypeName = sqlTypeName; mStruct.TableName = SqlFormat.NotKeyword(tableName); foreach (DataRow item in valueDt.Rows) { if (columnName == item[3].ToString()) //COLUMN_NAME { if (item[8].ToString() != "") { mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType); //"COLUMN_DEFAULT" } break; } } mdcs.Add(mStruct); } } #endregion break; } } } catch (Exception err) { Log.Write(err, LogType.DataBase); //helper.DebugInfo.Append(err.Message); } #endregion } } if (mdcs.Count > 0) { //移除被标志的列: string[] fields = AppConfig.DB.HiddenFields.Split(','); foreach (string item in fields) { string field = item.Trim(); if (!string.IsNullOrEmpty(field) & mdcs.Contains(field)) { mdcs.Remove(field); } } } #region 缓存设置 if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0) { _ColumnCache.Add(key, mdcs.Clone()); if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath; if (!System.IO.Directory.Exists(folderPath)) { System.IO.Directory.CreateDirectory(folderPath); } mdcs.WriteSchema(folderPath + key + ".ts"); } } #endregion return(mdcs); }
/// <summary> /// 获取指定的表架构生成的SQL(Alter Table)语句 /// </summary> public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn) { List <string> sql = new List <string>(); DbBase helper = DalCreate.CreateDal(conn); helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn()) { helper.Dispose(); return(sql); } DalType dalType = helper.dalType; string version = helper.Version; MDataColumn dbColumn = TableSchema.GetColumns(tableName, ref helper);//获取数据库的列结构 helper.Dispose(); //开始比较异同 List <MCellStruct> primaryKeyList = new List <MCellStruct>(); string tbName = SqlFormat.Keyword(tableName, dalType); string alterTable = "alter table " + tbName; foreach (MCellStruct ms in columns)//遍历新的结构 { string cName = SqlFormat.Keyword(ms.ColumnName, dalType); if (ms.AlterOp != AlterOp.None) { bool isContains = dbColumn.Contains(ms.ColumnName); if (ms.AlterOp == AlterOp.Drop) { #region MyRegion if (isContains) { switch (dalType) { case DalType.MsSql: case DalType.Access: case DalType.MySql: case DalType.Oracle: if (dalType == DalType.MsSql) { sql.Add(@"declare @name varchar(50) select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault where a.id = object_id('" + tableName + "') and a.name ='" + ms.ColumnName + "'if(@name!='') begin EXEC('alter table Base_Button drop constraint '+ @name) end"); } sql.Add(alterTable + " drop column " + cName); break; case DalType.Sybase: sql.Add(alterTable + " drop " + cName); break; } } #endregion } else if (ms.AlterOp == AlterOp.Rename) { #region MyRegion if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains) { string oName = SqlFormat.Keyword(ms.OldName, dalType); switch (dalType) { case DalType.MsSql: sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'"); break; case DalType.Sybase: sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName); break; case DalType.MySql: sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(',')); break; case DalType.Oracle: sql.Add(alterTable + " rename column " + oName + " to " + cName); break; } } #endregion } else if (ms.AlterOp == AlterOp.AddOrModify) { string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version); //智能判断 if (isContains) // 存在,则修改 { //检测是否相同 MCellStruct dbStruct = dbColumn[ms.ColumnName]; if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale) { string modify = ""; switch (dalType) { case DalType.Oracle: case DalType.Sybase: modify = " modify "; break; case DalType.MySql: modify = " change " + cName + " "; break; case DalType.MsSql: case DalType.Access: modify = " alter column "; break; } if (ms.IsCanNull != dbStruct.IsCanNull) { alterSql += (ms.IsCanNull ? " NULL" : " NOT NULL"); } sql.Add(alterTable + modify + alterSql); } } else //不在,则添加 { sql.Add(alterTable + " add " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(',')); if (!string.IsNullOrEmpty(ms.Description)) { string description = SqlCreateForSchema.GetTableDescriptionSql(tableName, ms, dalType, true); if (!string.IsNullOrEmpty(description)) { sql.Add(description); } } } } } } return(sql); }
internal bool LoadDataInsert(DataBaseType dalType, bool keepid) { bool fillGUID = CheckGUIDAndDateTime(dalType); bool isNeedCreateDal = (_dalHelper == null); if (isNeedCreateDal && dalType != DataBaseType.Oracle) { _dalHelper = DalCreate.CreateDal(_Conn); _dalHelper.IsWriteLogOnError = false; } string path = MDataTableToFile(mdt, fillGUID ? true : keepid, dalType); string formatSql = dalType == DataBaseType.MySql ? SqlCreate.MySqlBulkCopySql : SqlCreate.OracleBulkCopySql; string sql = string.Format(formatSql, path, SqlFormat.Keyword(mdt.TableName, dalType), AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepid, dalType)); if (dalType == DataBaseType.Oracle) { string ctlPath = CreateCTL(sql, path); sql = string.Format(SqlCreate.OracleSqlidR, "sa/123456@ORCL", ctlPath);//只能用进程处理 } try { if (dalType == DataBaseType.Oracle) { return(ExeSqlLoader(sql)); } else { bool isGoOn = true; if (IsTruncate) { _dalHelper.IsOpenTrans = true;//开启事务 isGoOn = _dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) != -2; } if (isGoOn && _dalHelper.ExeNonQuery(sql, false) != -2) { return(true); } } } catch (Exception err) { if (err.InnerException != null) { err = err.InnerException; } sourceTable.DynamicData = err; Log.Write(err, LogType.DataBase); } finally { if (isNeedCreateDal && _dalHelper != null) { _dalHelper.EndTransaction(); _dalHelper.Dispose(); _dalHelper = null; } IOHelper.Delete(path);//删除文件。 } return(false); }
internal bool MsSqlBulkCopyInsert(bool keepid) { SqlTransaction sqlTran = null; SqlConnection con = null; bool isCreateDal = false; try { CheckGUIDAndDateTime(DataBaseType.MsSql); string conn = AppConfig.GetConn(_Conn); if (_dalHelper == null) { if (IsTruncate) { isCreateDal = true; _dalHelper = DalCreate.CreateDal(conn); } else { con = new SqlConnection(conn); con.Open(); } } bool isGoOn = true; if (_dalHelper != null) { if (IsTruncate) { _dalHelper.IsOpenTrans = true; if (_dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) == -2) { isGoOn = false; sourceTable.DynamicData = _dalHelper.DebugInfo; Log.Write(_dalHelper.DebugInfo.ToString(), LogType.DataBase); } } if (isGoOn) { con = _dalHelper.Con as SqlConnection; _dalHelper.OpenCon(null, AllowConnLevel.MasterBackup);//如果未开启,则开启,打开链接后,如果以前没执行过数据,事务对象为空,这时会产生事务对象 sqlTran = _dalHelper._tran as SqlTransaction; } } if (isGoOn) { using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepid ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran)) { sbc.BatchSize = 100000; sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DataBaseType.MsSql); sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout; foreach (MCellStruct column in mdt.Columns) { sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName); } if (AppConfig.IsAspNetCore) { sbc.WriteToServer(mdt.ToDataTable()); } else { sbc.WriteToServer(mdt); } } } return(true); } catch (Exception err) { sourceTable.DynamicData = err; Log.Write(err, LogType.DataBase); } finally { if (_dalHelper == null) { con.Close(); con = null; } else if (isCreateDal) { _dalHelper.EndTransaction(); _dalHelper.Dispose(); } } return(false); }
internal static Dictionary <string, string> GetSchemas(string conn, string type) { ConnBean connBean = ConnBean.Create(conn); int hash = connBean.GetHashCode(); Dictionary <int, Dictionary <string, string> > schemaDic = null; switch (type) { case "U": schemaDic = _TableCache; break; case "V": schemaDic = _ViewCache; break; case "P": schemaDic = _ProcCache; break; } if (schemaDic.ContainsKey(hash)) { return(schemaDic[hash]); } Dictionary <string, string> tables = null; #region 查看有没有表架构缓存 if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + hash + ".ts"; if (System.IO.File.Exists(fullPath)) { tables = JsonHelper.Split(IOHelper.ReadAllText(fullPath)); } } #endregion if (tables == null) { lock (ot) { if (!schemaDic.ContainsKey(hash)) { #region 从数据库读 using (DalBase helper = DalCreate.CreateDal(connBean.ConnName)) { helper.IsRecordDebugInfo = false; switch (type) { case "U": tables = helper.GetTables(); break; case "V": tables = helper.GetViews(); break; case "P": tables = helper.GetProcs(); break; } } } else { return(schemaDic[hash]); } } #endregion } #region 写入表结构映射 if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath)) { string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + hash + ".ts"; IOHelper.Save(fullPath, JsonHelper.ToJson(tables), false, true); } #endregion #region 写入缓存 if (!schemaDic.ContainsKey(hash) && tables != null && tables.Count > 0)//读不到表不缓存。 { schemaDic.Add(hash, tables); } #endregion return(tables); }