/// <summary> /// 事务回滚 /// </summary> public static bool RollBack(string conn) { string key = StaticTool.GetTransationKey(conn); TransationKeys.Remove(key); DalBase dal = DalCreate.Get(key); if (dal != null && dal.RollBack()) { dal.Dispose(); return(DalCreate.Remove(key)); } return(false); }
/// <summary> /// 提交事务 /// </summary> public static bool EndTransation(string conn) { string key = StaticTool.GetTransationKey(conn); TransationKeys.Remove(key); DalBase dal = DalCreate.Get(key); if (dal != null && dal.EndTransaction())//如果事务回滚了, { dal.Dispose(); return(DalCreate.Remove(key)); } return(false); }
/// <summary> /// 获取表架构 /// </summary> /// <param name="conn">数据库链接</param> /// <param name="connectionName">指定要返回架构的名称</param> /// <param name="restrictionValues">为指定的架构返回一组限制值</param> public static DataTable GetSchema(string conn, string connectionName, string[] restrictionValues) { DalBase helper = DalCreate.CreateDal(conn); if (!helper.TestConn(AllowConnLevel.MaterBackupSlave)) { return(null); } helper.Con.Open(); DataTable dt = helper.Con.GetSchema(connectionName, restrictionValues); helper.Con.Close(); helper.Dispose(); return(dt); }
public static bool TestConn(string conn, out string msg) { bool result = false; try { DalBase helper = DalCreate.CreateDal(conn); result = helper.TestConn(AllowConnLevel.Master); if (result) { msg = helper.Version; } else { msg = helper.DebugInfo.ToString(); } helper.Dispose(); } catch (Exception err) { msg = err.Message; } return(result); }
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); }
/// <summary> /// 获取指定的表架构生成的SQL(Alter Table)语句 /// </summary> public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn) { List <string> sql = new List <string>(); string version = null; DataBaseType dalType; using (DalBase helper = DalCreate.CreateDal(conn)) { helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况 if (!helper.TestConn(AllowConnLevel.Master)) { helper.Dispose(); return(sql); } dalType = helper.DataBaseType; version = helper.Version; } MDataColumn dbColumn = TableSchema.GetColumns(tableName, conn);//获取数据库的列结构 if (dbColumn == null || dbColumn.Count == 0) { return(sql); } //开始比较异同 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); AlterOp op = ms.AlterOp; if ((op & AlterOp.Rename) != 0) { op = (AlterOp)(op - AlterOp.Rename); #region MyRegion Rename if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains) { string oName = SqlFormat.Keyword(ms.OldName, dalType); switch (dalType) { case DataBaseType.MsSql: sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'"); break; case DataBaseType.Sybase: sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName); break; case DataBaseType.MySql: sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(',')); break; case DataBaseType.Oracle: sql.Add(alterTable + " rename column " + oName + " to " + cName); break; } isContains = isContains || dbColumn.Contains(ms.OldName); } #endregion } if (op == AlterOp.Drop) { #region MyRegion if (isContains) { switch (dalType) { case DataBaseType.MsSql: case DataBaseType.Access: case DataBaseType.MySql: case DataBaseType.Oracle: if (dalType == DataBaseType.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 " + tableName + " drop constraint '+ @name) end"); } sql.Add(alterTable + " drop column " + cName); break; case DataBaseType.Sybase: sql.Add(alterTable + " drop " + cName); break; } } #endregion } //else if (ms.AlterOp == AlterOp.Rename) //{ //} else if (op == AlterOp.AddOrModify) { //智能判断 if (isContains) // 存在,则修改 { string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version); //检测是否相同 MCellStruct dbStruct = dbColumn[ms.ColumnName] ?? dbColumn[ms.OldName]; if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale) { string modify = ""; switch (dalType) { case DataBaseType.Oracle: case DataBaseType.Sybase: modify = " modify "; break; case DataBaseType.MySql: modify = " change " + cName + " "; break; case DataBaseType.MsSql: case DataBaseType.Access: case DataBaseType.PostgreSQL: 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); }