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