/// <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); }
public override void Dispose() { if (baseDB is SqlBase) { baseDB.Dispose(); } }
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> /// 获取所有表(表名+表说明)【链接错误时,抛异常】 /// </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); }
/// <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); }
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); }
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); }
/// <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 MsSqlBulkCopyInsert(bool keepID) { SqlTransaction sqlTran = null; SqlConnection con = null; bool isCreateDal = false; try { CheckGUIDAndDateTime(DalType.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.WriteLogToTxt(_dalHelper.debugInfo.ToString()); } } 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, DalType.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.WriteLogToTxt(err); } finally { if (_dalHelper == null) { con.Close(); con = null; } else if (isCreateDal) { _dalHelper.EndTransaction(); _dalHelper.Dispose(); } } return(false); }
public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper) { tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName)); DalType dalType = dbHelper.dalType; tableName = SqlFormat.Keyword(tableName, dbHelper.dalType); string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType); if (CacheManage.LocalInstance.Contains(key))//缓存里获取 { return(CacheManage.LocalInstance.Get <MDataColumn>(key).Clone()); } switch (dalType) { case DalType.SQLite: case DalType.MySql: tableName = SqlFormat.NotKeyword(tableName); break; case DalType.Txt: case DalType.Xml: tableName = Path.GetFileNameWithoutExtension(tableName); //视图表,带“.“的,会出问题 string fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml"); MDataColumn mdc = MDataColumn.CreateFrom(fileName); mdc.dalType = dalType; return(mdc); } MDataColumn mdcs = new MDataColumn(); mdcs.dalType = dbHelper.dalType; //如果table和helper不在同一个库 DbBase helper = dbHelper.ResetDbBase(tableName); helper.IsAllowRecordSql = false;//内部系统,不记录SQL表结构语句。 try { bool isView = tableName.Contains(" ");//是否视图。 if (!isView) { isView = Exists("V", tableName, ref helper); } 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 DalType.MsSql: case DalType.Oracle: case DalType.MySql: case DalType.Sybase: #region Sql string sql = string.Empty; if (dalType == DalType.MsSql) { string dbName = null; if (!helper.Version.StartsWith("08")) { //先获取同义词,检测是否跨库 string realTableName = Convert.ToString(helper.ExeScalar(string.Format(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.DataBase); } else if (dalType == DalType.MySql) { sql = GetMySqlColumns(helper.DataBase); } else if (dalType == DalType.Oracle) { sql = GetOracleColumns(); } else if (dalType == DalType.Sybase) { tableName = SqlFormat.NotKeyword(tableName); sql = GetSybaseColumns(); } helper.AddParameters("TableName", 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.dalType); 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 DalType.MsSql: case DalType.MySql: case DalType.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 == DalType.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 DalType.SQLite: #region SQlite if (helper.Con.State != ConnectionState.Open) { helper.Con.Open(); } DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, 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 = 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 DalType.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; } } helper.ClearParameters(); } catch (Exception err) { helper.debugInfo.Append(err.Message); } finally { helper.IsAllowRecordSql = true;//恢复记录SQL表结构语句。 if (helper != dbHelper) { helper.Dispose(); } } 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); } } } if (!CacheManage.LocalInstance.Contains(key)) { CacheManage.LocalInstance.Add(key, mdcs.Clone()); } return(mdcs); }