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