/// <summary>
/// 为字段或表名添加关键字标签：如[],''等符号
/// </summary>
/// <param name="name">表名或字段名</param>
/// <param name="dalType">数据类型</param>
/// <returns></returns>
public static string Keyword(string name, DalType dalType)
{
return(SqlFormat.Keyword(name, dalType));
}
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);//只能用进程处理
}
try
{
if (dalType == DalType.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.WriteLogToTxt(err);
}
finally
{
if (isNeedCreateDal && _dalHelper != null)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
_dalHelper = null;
}
IOHelper.Delete(path);//删除文件。
}
return(false);
}
/*
* internal bool SybaseBulkCopyInsert()
* {
*
* // string a, b, c;
* string conn = DalCreate.FormatConn(DalType.Sybase, AppConfig.GetConn(_Conn));
*
* using (Sybase.Data.AseClient.AseBulkCopy sbc = new Sybase.Data.AseClient.AseBulkCopy(conn, Sybase.Data.AseClient.AseBulkCopyOptions.KeepIdentity))
* {
* sbc.BatchSize = 100000;
* sbc.DestinationTableName = mdt.TableName;
* foreach (MCellStruct column in mdt.Columns)
* {
* Sybase.Data.AseClient.AseBulkCopyColumnMapping ac = new Sybase.Data.AseClient.AseBulkCopyColumnMapping();
* ac.SourceColumn = ac.DestinationColumn = column.ColumnName;
* sbc.ColumnMappings.Add(ac);
* }
* sbc.WriteToServer(mdt.ToDataTable());
* }
* return true;
*
*
* //Assembly ass = SybaseDal.GetAssembly();
*
* //object sbc = ass.CreateInstance("Sybase.Data.AseClient.AseBulkCopy", false, BindingFlags.CreateInstance, null, new object[] { conn }, null, null);
*
* //Type sbcType = sbc.GetType();
* //try
* //{
*
* // sbcType.GetProperty("BatchSize").SetValue(sbc, 100000, null);
* // sbcType.GetProperty("DestinationTableName").SetValue(sbc, SqlFormat.Keyword(mdt.TableName, DalType.Sybase), null);
* // PropertyInfo cInfo = sbcType.GetProperty("ColumnMappings");
* // object cObj = cInfo.GetValue(sbc, null);
* // MethodInfo addMethod = cInfo.PropertyType.GetMethods()[2];
* // foreach (MCellStruct column in mdt.Columns)
* // {
* // object columnMapping = ass.CreateInstance("Sybase.Data.AseClient.AseBulkCopyColumnMapping", false, BindingFlags.CreateInstance, null, new object[] { column.ColumnName, column.ColumnName }, null, null);
* // addMethod.Invoke(cObj, new object[] { columnMapping });
* // }
* // //Oracle.DataAccess.Client.OracleBulkCopy ttt = sbc as Oracle.DataAccess.Client.OracleBulkCopy;
* // //ttt.WriteToServer(mdt);
* // sbcType.GetMethods()[14].Invoke(sbc, new object[] { mdt.ToDataTable() });
* // return true;
* //}
* //catch (Exception err)
* //{
* // Log.WriteLogToTxt(err);
* // return false;
* //}
* //finally
* //{
* // sbcType.GetMethod("Dispose").Invoke(sbc, null);
* //}
* }
*/
#endregion
internal bool NomalInsert(bool keepID)
{
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
DbBase sourceHelper = action.dalHelper;
action.SetAopState(Aop.AopOp.CloseAll);
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation(); //事务由外部控制
}
action.dalHelper.IsAllowRecordSql = false; //屏蔽SQL日志记录
if (keepID)
{
action.SetIdentityInsertOn();
}
bool isGoOn = true;
if (IsTruncate)
{
if (dalTypeTo == DalType.Txt || dalTypeTo == DalType.Xml)
{
action.Delete("1=1");
}
else if (action.dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(action.TableName, dalTypeTo)), false) == -2)
{
isGoOn = false;
sourceTable.DynamicData = action.DebugInfo;
Log.WriteLogToTxt(action.DebugInfo);
}
}
if (isGoOn)
{
MDataRow row;
for (int i = 0; i < mdt.Rows.Count; i++)
{
row = mdt.Rows[i];
action.ResetTable(row, false);
action.Data.SetState(1, BreakOp.Null);
result = action.Insert(InsertOp.None);
sourceTable.RecordsAffected = i;
if (!result)
{
string msg = "Error On : MDataTable.AcceptChanges.Insert." + mdt.TableName + " : [" + row.PrimaryCell.Value + "] : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.WriteLogToTxt(msg);
break;
}
}
}
if (keepID)
{
action.SetIdentityInsertOff();
}
if (_dalHelper == null)
{
action.EndTransation();
}
action.dalHelper.IsAllowRecordSql = true; //恢复SQL日志记录
action.dalHelper = sourceHelper; //恢复原来，避免外来的链接被关闭。
}
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);//如果未开启，则开启，打开链接后，如果以前没执行过数据，事务对象为空，这时会产生事务对象
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);
}
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);
}