public static void Initialize(string file, string format)
{
if (string.IsNullOrEmpty((format)))
return;
try
{
Format = (SqlFormat)Enum.Parse(typeof(SqlFormat), format, true);
}
catch (Exception)
{
Program.PrintUsage("The SQL format " + format + " was not recognized.");
return;
}
File.Delete(file);
_file = new StreamWriter(file, true);
}
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);
}
/*
* 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.Write(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))
{
DalBase sourceHelper = action.dalHelper;
action.SetAopState(Aop.AopOp.CloseAll);
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation(); //事务由外部控制
}
action.dalHelper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql; //屏蔽SQL日志记录
if (keepid)
{
action.SetidentityInsertOn();
}
bool isGoOn = true;
if (IsTruncate)
{
if (dalTypeTo == DataBaseType.Txt || dalTypeTo == DataBaseType.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.Write(action.DebugInfo, LogType.DataBase);
}
}
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.Write(msg, LogType.DataBase);
break;
}
}
}
if (keepid)
{
action.SetidentityInsertOff();
}
if (_dalHelper == null)
{
action.EndTransation();
}
action.dalHelper.IsRecordDebugInfo = true; //恢复SQL日志记录
action.dalHelper = sourceHelper; //恢复原来，避免外来的链接被关闭。
}
return(result);
}
/// <summary>
/// 多个条件
/// </summary>
private static List <TFilter> GetTFilter(object whereObj, MDataColumn mdc, out List <TFilter> group2)
{
group2 = new List <TFilter>();
List <TFilter> tFilterList = new List <TFilter>();
string whereStr = SqlFormat.GetIFieldSql(whereObj);
whereStr = SqlCreate.FormatWhere(whereStr, mdc, DataBaseType.None, null);
string lowerWhere = whereStr.ToLower();
string andSign = " and ";
string orSign = " or ";
int andIndex = IndexOf(lowerWhere, andSign, 0);// lowerWhere.IndexOf(andSign);
int orIndex = IndexOf(lowerWhere, orSign, 0);
TFilter filter = null;
if (andIndex == -1 && orIndex == -1)//仅有一个条件
{
filter = GetSingleTFilter(whereStr, mdc);
if (filter != null)
{
tFilterList.Add(filter);
}
}
else if (orIndex == -1) // 只有and条件
{
int andStartIndex = 0;
while (andIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andStartIndex, andIndex - andStartIndex), mdc);
if (filter != null)
{
if (andStartIndex > 0)
{
filter._Ao = Ao.And;
}
tFilterList.Add(filter);
}
andStartIndex = andIndex + andSign.Length;
andIndex = IndexOf(lowerWhere, andSign, andStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(andStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.And;
tFilterList.Add(filter);
}
}
else if (andIndex == -1) //只有or 条件
{
int orStartIndex = 0;
while (orIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(orStartIndex, orIndex - orStartIndex), mdc);
if (filter != null)
{
if (orStartIndex > 0)
{
filter._Ao = Ao.Or;
}
tFilterList.Add(filter);
}
orStartIndex = orIndex + orSign.Length;
orIndex = IndexOf(lowerWhere, orSign, orStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(orStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.Or;
tFilterList.Add(filter);
}
}
else //有and 又有 or
{
bool isAnd = andIndex < orIndex;
bool lastAnd = isAnd;
int andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
int andOrStartIndex = 0;
bool needGroup2 = isAnd;//如果是and开头，则分成两组
while (andOrIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex, andOrIndex - andOrStartIndex), mdc);
if (filter != null)
{
if (andOrStartIndex > 0)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
}
tFilterList.Add(filter);
}
andOrStartIndex = andOrIndex + (isAnd ? andSign.Length : orSign.Length);
if (isAnd)
{
andIndex = IndexOf(lowerWhere, andSign, andOrStartIndex + 1);
}
else
{
orIndex = IndexOf(lowerWhere, orSign, andOrStartIndex + 1);
}
lastAnd = isAnd;
if (andIndex == -1)
{
isAnd = false;
andOrIndex = orIndex;
}
else if (orIndex == -1)
{
isAnd = true;
andOrIndex = andIndex;
}
else
{
isAnd = andIndex < orIndex;
andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
}
}
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex), mdc);
if (filter != null)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
tFilterList.Add(filter);
}
if (tFilterList.Count > 2 && needGroup2)
{
int okflag = -1;
for (int i = 0; i < tFilterList.Count; i++)
{
if (okflag == -1 && tFilterList[i]._Ao == Ao.Or)
{
i--;//返回上一个索引1,2,3
okflag = i;
}
if (okflag != -1)
{
group2.Add(tFilterList[i]);
}
}
tFilterList.RemoveRange(okflag, tFilterList.Count - okflag);
}
}
// string firstFilter=whereStr.su
return(tFilterList);
}
private void SetCommandText(string commandText, bool isProc)
{
if (OracleDal.clientType > 0)
{
Type t = _com.GetType();
System.Reflection.PropertyInfo pi = t.GetProperty("BindByName");
if (pi != null)
{
pi.SetValue(_com, true, null);
}
}
_com.CommandText = isProc ? commandText : SqlFormat.Compatible(commandText, dalType, false);
if (!isProc && dalType == DalType.SQLite && _com.CommandText.Contains("charindex"))
{
_com.CommandText += " COLLATE NOCASE";//忽略大小写
}
//else if (isProc && dalType == DalType.MySql)
//{
// _com.CommandText = "Call " + _com.CommandText;
//}
_com.CommandType = isProc ? CommandType.StoredProcedure : CommandType.Text;
if (isProc)
{
if (commandText.Contains("SelectBase") && !_com.Parameters.Contains("ReturnValue"))
{
AddReturnPara();
//检测是否存在分页存储过程，若不存在，则创建。
Tool.DBTool.CreateSelectBaseProc(dalType, conn);//内部分检测是否已创建过。
}
}
else
{
//取消多余的参数，新加的小贴心，过滤掉用户不小心写多的参数。
if (_com != null && _com.Parameters != null && _com.Parameters.Count > 0)
{
bool needToReplace = (dalType == DalType.Oracle || dalType == DalType.MySql) && _com.CommandText.Contains("@");
string paraName;
for (int i = 0; i < _com.Parameters.Count; i++)
{
paraName = _com.Parameters[i].ParameterName.TrimStart(Pre);//默认自带前缀的，取消再判断
if (needToReplace && _com.CommandText.IndexOf("@" + paraName) > -1)
{
//兼容多数据库的参数（虽然提供了=:?"为兼容语法，但还是贴心的再处理一下）
switch (dalType)
{
case DalType.Oracle:
case DalType.MySql:
_com.CommandText = _com.CommandText.Replace("@" + paraName, Pre + paraName);
break;
}
}
if (_com.CommandText.IndexOf(Pre + paraName, StringComparison.OrdinalIgnoreCase) == -1)
{
_com.Parameters.RemoveAt(i);
i--;
}
}
}
}
//else
//{
// string checkText = commandText.ToLower();
// //int index=
// //if (checkText.IndexOf("table") > -1 && (checkText.IndexOf("delete") > -1 || checkText.IndexOf("drop") > -1 || checkText.IndexOf("truncate") > -1))
// //{
// // Log.WriteLog(commandText);
// //}
//}
if (IsAllowRecordSql)
{
tempSql = GetParaInfo(_com.CommandText) + AppConst.BR + "execute time is: ";
}
}
/// <summary>
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
/// </summary>
/// <param name="flag">[0:转成标准值],[1:转成各数据库值]</param>
/// <returns></returns>
public static string FormatDefaultValue(DataBaseType dalType, object value, int flag, SqlDbType sqlDbType)
{
return(SqlFormat.FormatDefaultValue(dalType, value, flag, sqlDbType));
}
protected IEnumerable <string> GenerateInsertScripts(IExtDataRecord dataRow, EntityMetadata rowMetaData)
{
List <string> scripts = new List <string>();
FieldMetadata[] preDefFields = rowMetaData.PreDefinedFields;
FieldMetadata[] extFields = rowMetaData.ExtensionFields;
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(rowMetaData.SourceName).Append(" ( ");
bool addFlag = false;
for (int i = 0; i < preDefFields.Length; i++)
{
object value = null;
if (!dataRow.TryGetValue(preDefFields[i].Name, out value))
{
continue; //value not exists. Insert the least values
}
if (addFlag)
{
sb.Append(", ");
}
sb.Append(preDefFields[i].ColumnName);
addFlag = true;
}
sb.Append(") VALUES ( ");
addFlag = false;
for (int i = 0; i < preDefFields.Length; i++)
{
object value = null;
if (!dataRow.TryGetValue(preDefFields[i].Name, out value))
{
continue; //value not exists. Insert the least values
}
if (addFlag)
{
sb.Append(", ");
}
sb.Append(SqlFormat.ToSqlValueString(value));
addFlag = true;
}
sb.Append(")");
scripts.Add(sb.ToString());
if (extFields == null || extFields.Length == 0)
{
return(scripts);
}
string recordIdStringValue = SqlFormat.ToSqlValueString(dataRow.GetValue(rowMetaData.Key));
string extensionTable = string.IsNullOrEmpty(rowMetaData.ExtensionTable) ? Consts.DefExtensionTable : rowMetaData.ExtensionTable;
foreach (FieldMetadata extField in extFields)
{
object value = null;
if (!dataRow.TryGetValue(extField.Name, out value))
{
continue;
}
value = TypeConvert.ChangeType <string>(value);
sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(extensionTable).
Append("(RecordId, FieldId, Value) VALUES (").Append(recordIdStringValue).
Append(",").Append(SqlFormat.ToSqlValueString(extField.Id)).
Append(",").Append(SqlFormat.ToSqlValueString(value)).Append(")");
scripts.Add(sb.ToString());
}
return(scripts);
}
//private static List<string> flag = new List<string>(2);
//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.IsRecordDebugInfo = 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.Write(err, LogType.DataBase);
// }
//}
/// <summary>
/// 为字段或表名添加关键字标签：如[],''等符号
/// </summary>
/// <param name="name">表名或字段名</param>
/// <param name="dalType">数据类型</param>
/// <returns></returns>
public static string Keyword(string name, DataBaseType dalType)
{
return(SqlFormat.Keyword(name, dalType));
}
/// <summary>
/// 多个条件
/// </summary>
private static TFilter[] GetTFilter(object whereObj, MDataColumn mdc)
{
List <TFilter> tFilterList = new List <TFilter>();
string whereStr = SqlFormat.GetIFieldSql(whereObj);
whereStr = SqlCreate.FormatWhere(whereStr, mdc, DalType.None, null);
string lowerWhere = whereStr.ToLower();
string andSign = " and ";
string orSign = " or ";
int andIndex = IndexOf(lowerWhere, andSign, 0);// lowerWhere.IndexOf(andSign);
int orIndex = IndexOf(lowerWhere, orSign, 0);
TFilter filter = null;
if (andIndex == -1 && orIndex == -1)//仅有一个条件
{
filter = GetSingleTFilter(whereStr, mdc);
if (filter != null)
{
tFilterList.Add(filter);
}
}
else if (orIndex == -1) // 只有and条件
{
int andStartIndex = 0;
while (andIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andStartIndex, andIndex - andStartIndex), mdc);
if (filter != null)
{
if (andStartIndex > 0)
{
filter._Ao = Ao.And;
}
tFilterList.Add(filter);
}
andStartIndex = andIndex + andSign.Length;
andIndex = IndexOf(lowerWhere, andSign, andStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(andStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.And;
tFilterList.Add(filter);
}
}
else if (andIndex == -1) //只有or 条件
{
int orStartIndex = 0;
while (orIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(orStartIndex, orIndex - orStartIndex), mdc);
if (filter != null)
{
if (orStartIndex > 0)
{
filter._Ao = Ao.Or;
}
tFilterList.Add(filter);
}
orStartIndex = orIndex + orSign.Length;
orIndex = IndexOf(lowerWhere, orSign, orStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(orStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.Or;
tFilterList.Add(filter);
}
}
else //有and 又有 or
{
bool isAnd = andIndex < orIndex;
bool lastAnd = isAnd;
int andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
int andOrStartIndex = 0;
while (andOrIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex, andOrIndex - andOrStartIndex), mdc);
if (filter != null)
{
if (andOrStartIndex > 0)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
}
tFilterList.Add(filter);
}
andOrStartIndex = andOrIndex + (isAnd ? andSign.Length : orSign.Length);
if (isAnd)
{
andIndex = IndexOf(lowerWhere, andSign, andOrStartIndex + 1);
}
else
{
orIndex = IndexOf(lowerWhere, orSign, andOrStartIndex + 1);
}
lastAnd = isAnd;
if (andIndex == -1)
{
isAnd = false;
andOrIndex = orIndex;
}
else if (orIndex == -1)
{
isAnd = true;
andOrIndex = andIndex;
}
else
{
isAnd = andIndex < orIndex;
andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
}
}
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex), mdc);
if (filter != null)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
tFilterList.Add(filter);
}
}
// string firstFilter=whereStr.su
return(tFilterList.ToArray());
}
/// <summary>
/// 构造Where条件
/// </summary>
public virtual string GetWhere()
{
return(SqlFormat.BuildSQL(SqlFormat.SearchParaList));
}
internal bool OracleBulkCopyInsert()
{
ConnBean bean = ConnBean.Create(_Conn);
if (bean == null)
{
string err = "MDataTableBatchAction.OracleBulkCopyInsert ConnBean can't create by " + _Conn;
Log.Write(err, LogType.DataBase);
Error.Throw(err);
}
string conn = bean.ConnString;
CheckGUIDAndDateTime(DataBaseType.Oracle);
Assembly ass = OracleDal.GetAssembly();
object sbc = ass.CreateInstance("Oracle.DataAccess.Client.OracleBulkCopy", false, BindingFlags.CreateInstance, null, new object[] { conn }, null, null);
Type sbcType = sbc.GetType();
try
{
sbcType.GetProperty("BatchSize").SetValue(sbc, 100000, null);
sbcType.GetProperty("BulkCopyTimeout").SetValue(sbc, AppConfig.DB.CommandTimeout, null);
sbcType.GetProperty("DestinationTableName").SetValue(sbc, SqlFormat.Keyword(mdt.TableName, DataBaseType.Oracle), null);
PropertyInfo cInfo = sbcType.GetProperty("ColumnMappings");
object cObj = cInfo.GetValue(sbc, null);
MethodInfo addMethod = cInfo.PropertyType.GetMethods()[4];
foreach (MCellStruct column in mdt.Columns)
{
addMethod.Invoke(cObj, new object[] { column.ColumnName, column.ColumnName });
}
sbcType.GetMethods()[4].Invoke(sbc, new object[] { mdt });
return(true);
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.Write(err, LogType.DataBase);
return(false);
}
finally
{
sbcType.GetMethod("Dispose").Invoke(sbc, null);
}
//using (Oracle.DataAccess.Client.OracleBulkCopy sbc = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default))
//{
// sbc.BatchSize = 100000;
// sbc.DestinationTableName = mdt.TableName;
// foreach (MCellStruct column in mdt.Columns)
// {
// sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
// }
// sbc.WriteToServer(mdt);
//}
//return true;
}
/// <summary>
/// 将Xml获取到的SQL格式化
/// </summary>
/// <param name="sQL">sQL字段</param>
/// <param name="maxConditionsCount">最大条件数量</param>
/// <returns></returns>
private List <SqlFormat> SqlFormatAction(
string sQL
, int maxConditionsCount
, out bool isNotActiveConditionInsQL)
{
//最终输出产物 { [条件:0,SQL:...,IsWhere:...] ,[条件:1,SQL:...,IsWhere:...] ,...}
List <SqlFormat> sqlList = new List <SqlFormat>();
List <string> spliteCount = new List <string>();
List <CondiIndex> indexList = new List <CondiIndex>();
isNotActiveConditionInsQL = true;
#region 添加CondiIndex
IndexAdd("", CONDITION_TYPE.VALUE, sQL, maxConditionsCount, spliteCount, indexList, ref isNotActiveConditionInsQL);
IndexAdd("?", CONDITION_TYPE.WHERE, sQL, maxConditionsCount, spliteCount, indexList, ref isNotActiveConditionInsQL);
IndexAdd("!", CONDITION_TYPE.SET, sQL, maxConditionsCount, spliteCount, indexList, ref isNotActiveConditionInsQL);
#endregion
//让格式化后的条件按照位置，从小到大排序
indexList.Sort((x, y) => x.Index.CompareTo(y.Index));
//以 {0},{1},{2}... 作为分割符，分割SQL字段
List <string> sqlSplite = new List <string>(sQL.Split(spliteCount.ToArray(), StringSplitOptions.RemoveEmptyEntries));
//格式化逻辑字符，按正常顺序输出，并带上逻辑条件标记（{0},{1},{2}...）
if (!isNotActiveConditionInsQL)
{
for (var count = 0; count < sqlSplite.Count; count++)
{
SqlFormat sqlFormat = new SqlFormat
{
SQLText = sqlSplite[count]
};
//将最后一段SQL标记为-1
if (count < indexList.Count)
{
sqlFormat.CondiIndex = indexList[count].CondiCount;
sqlFormat.oNDITION_TYPE = indexList[count].cONDITION_TYPE;
}
else
{
sqlFormat.CondiIndex = -1;
}
sqlList.Add(sqlFormat);
}
}
else
{
//无任何逻辑字符直接输出
sqlList.Add(new SqlFormat()
{
CondiIndex = -1,
SQLText = sqlSplite[0]
});
}
return(sqlList);
}
public void TestDateTimeFormat()
{
string v = SqlFormat.DateTimeFormat(null);
AssertAreEqual(v, "NULL", "Date nulle");
v = SqlFormat.DateTimeFormat(new DateTime(2000, 10, 23, 12, 30, 00, 100));
AssertAreEqual(v, "'2000-10-23T12:30:00'", "Date 23/10/2000 12h30:00.100");
v = SqlFormat.DateTimeFormat(new DateTime(2000, 10, 23, 12, 30, 00, 100), true);
AssertAreEqual(v, "'2000-10-23T12:30:00'", "Date nullable 23/10/2000 12h30:00.100");
v = SqlFormat.DateTimeFormat(DateTime.MinValue);
AssertAreEqual(v, "GETDATE()", "Date MinValue not Null");
v = SqlFormat.DateTimeFormat(DateTime.MinValue, true);
AssertAreEqual(v, "NULL", "Date MinValue Null");
v = SqlFormat.DateTimeFormat(DateTime.MaxValue);
AssertAreEqual(v, "GETDATE()", "Date MaxValue not Null");
v = SqlFormat.DateTimeFormat(new DateTime(1600, 1, 5), true);
AssertAreEqual(v, "NULL", "Date 05/01/1600 inférieur à min SQL nullable");
v = SqlFormat.DateTimeFormat(new DateTime(9999, 12, 31, 23, 59, 59, 300), true);
AssertAreEqual(v, "NULL", "Date 31/12/9999 23h59:59.300 supérieur à max SQL nullable");
DateTime?dt = null;
v = SqlFormat.DateTimeFormat(dt);
AssertAreEqual(v, "NULL", "Date? Null");
dt = new DateTime(2000, 10, 23, 12, 30, 00);
v = SqlFormat.DateTimeFormat(dt);
AssertAreEqual(v, "'2000-10-23T12:30:00'", "Date 23/10/2000 12h30");
// DateTime2
dt = null;
v = SqlFormat.DateTime2Format(dt);
AssertAreEqual(v, "NULL", "DateTime2? Null");
dt = new DateTime(2000, 10, 23, 12, 30, 00, 100);
v = SqlFormat.DateTime2Format(dt);
AssertAreEqual(v, "'2000-10-23T12:30:00.100'", "Date Time2 23/10/2000 12h30:00.100");
v = SqlFormat.DateTime2Format(new DateTime(2000, 10, 23, 12, 30, 00, 100), true);
AssertAreEqual(v, "'2000-10-23T12:30:00.100'", "Date nullable Time2 23/10/2000 12h30:00.100");
// Heure Format
DateTime pivot = SqlFormat.DatePivotHeure;
AssertAreEqual(pivot, new DateTime(1900, 1, 1), "Heure Pivot base");
dt = null;
v = SqlFormat.HeureFormat(dt);
AssertAreEqual(v, "NULL", "Heure? dans datetime Null");
dt = new DateTime(2000, 10, 23, 12, 30, 00, 100);
v = SqlFormat.HeureFormat(dt);
AssertAreEqual(v, "'1900-01-01T12:30:00'", "Heure? dans datetime 23/10/2000 12h30:00.100");
v = SqlFormat.HeureFormat(new DateTime(2000, 10, 23, 12, 30, 00, 100), nullable: true);
AssertAreEqual(v, "'1900-01-01T12:30:00'", "Heure? dans datetime nullable 23/10/2000 12h30:00.100");
// Heure dans DateTime2
dt = null;
v = SqlFormat.HeureFormat(dt, SqlFormat.EFormatDate.DateTime2);
AssertAreEqual(v, "NULL", "Heure? dans datetime2 Null");
dt = new DateTime(2000, 10, 23, 12, 30, 00, 100);
v = SqlFormat.HeureFormat(dt, SqlFormat.EFormatDate.DateTime2);
AssertAreEqual(v, "'1900-01-01T12:30:00.100'", "Heure? dans datetime2 23/10/2000 12h30:00.100");
v = SqlFormat.HeureFormat(new DateTime(2000, 10, 23, 12, 30, 00, 100), SqlFormat.EFormatDate.DateTime2, true);
AssertAreEqual(v, "'1900-01-01T12:30:00.100'", "Heure? dans datetime2 nullable 23/10/2000 12h30:00.100");
// Heure dans time
dt = null;
v = SqlFormat.HeureFormat(dt, SqlFormat.EFormatDate.Time);
AssertAreEqual(v, "NULL", "Heure? dans time Null");
dt = new DateTime(2000, 10, 23, 12, 30, 00, 100);
v = SqlFormat.HeureFormat(dt, SqlFormat.EFormatDate.Time);
AssertAreEqual(v, "'12:30:00.100'", "Heure? dans time 12h30:00.100");
v = SqlFormat.HeureFormat(new DateTime(2000, 10, 23, 12, 30, 00, 100), SqlFormat.EFormatDate.Time, true);
AssertAreEqual(v, "'12:30:00.100'", "Heure? dans time nullable 23/10/2000 12h30:00.100");
// mise à jour de la date pivot
SqlFormat.DatePivotHeure = new DateTime(1753, 1, 1);
pivot = SqlFormat.DatePivotHeure;
AssertAreEqual(pivot, new DateTime(1753, 1, 1), "Date pivot non modifiée");
dt = new DateTime(2000, 10, 23, 12, 30, 00, 100);
v = SqlFormat.HeureFormat(dt);
AssertAreEqual(v, "'1753-01-01T12:30:00'", "Heure? dans datetime 23/10/2000 12h30:00.100");
SqlFormat.RestaureDatePivot();
pivot = SqlFormat.DatePivotHeure;
AssertAreEqual(pivot, new DateTime(1900, 1, 1), "Date pivot non restaurée");
}
internal bool LoadDataInsert(DataBaseType dalType, bool keepid)
{
bool fillGUID = CheckGUIDAndDateTime(dalType);
bool isNeedCreateDal = (_dalHelper == null);
if (isNeedCreateDal && dalType != DataBaseType.Oracle)
{
_dalHelper = DalCreate.CreateDal(_Conn);
_dalHelper.IsWriteLogOnError = false;
}
string path = MDataTableToFile(mdt, fillGUID ? true : keepid, dalType);
string formatSql = dalType == DataBaseType.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 == DataBaseType.Oracle)
{
string ctlPath = CreateCTL(sql, path);
sql = string.Format(SqlCreate.OracleSqlldr, "sa/123456@ORCL", ctlPath);//只能用进程处理
}
try
{
if (dalType == DataBaseType.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.Write(err, LogType.DataBase);
}
finally
{
if (isNeedCreateDal && _dalHelper != null)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
_dalHelper = null;
}
IOHelper.Delete(path);//删除文件。
}
return(false);
}
public abstract string Format(SqlFormat format);
public string Format(SqlFormat format)
{
return("DROP TABLE " + Name);
}
public override string Format(SqlFormat format)
{
return("WHERE " + Column + " LIKE " + Value);
}
/// <summary>
/// 取消字段或表名添加关键字标签：如[],''等符号
/// </summary>
/// <param name="name">表名或字段名</param>
public static string NotKeyword(string name)
{
return(SqlFormat.NotKeyword(name));
}
protected IEnumerable <string> GenerateUpdateScripts(IExtDataRecord dataRow, EntityMetadata rowMetaData, IEnumerable <string> dirtyFields)
{
List <string> scripts = new List <string>();
List <string> preDefDirtyFields = new List <string>();
List <string> extDirtyFields = new List <string>();
foreach (string dirtyField in dirtyFields)
{
if (!rowMetaData.ContainsField(dirtyField))
{
continue;
}
if (!rowMetaData[dirtyField].IsExtension)
{
preDefDirtyFields.Add(dirtyField);
}
else
{
extDirtyFields.Add(dirtyField);
}
}
//update predefined fields
if (preDefDirtyFields.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE ").Append(rowMetaData.SourceName).Append(" SET ");
for (int i = 0; i < preDefDirtyFields.Count; i++)
{
sb.Append(preDefDirtyFields[i]).Append(" = ").
Append(SqlFormat.ToSqlValueString(dataRow.GetValue(preDefDirtyFields[i])));
if (i != preDefDirtyFields.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(" WHERE ").Append(GetConditionClause(dataRow, rowMetaData.KeyFieldNames));
scripts.Add(sb.ToString());
}
//uppdate extension values
if (extDirtyFields.Count > 0)
{
string recordIdStringValue = SqlFormat.ToSqlValueString(dataRow.GetValue(rowMetaData.Key));
string extensionTable = string.IsNullOrEmpty(rowMetaData.ExtensionTable) ? Consts.DefExtensionTable : rowMetaData.ExtensionTable;
foreach (string extFieldName in extDirtyFields)
{
object value = dataRow.GetValue(extFieldName);
value = TypeConvert.ChangeType <string>(value);
StringBuilder sb = new StringBuilder();
sb.Append("DELETE FROM ").Append(extensionTable).
Append(" WHERE RecordId=").Append(recordIdStringValue).
Append(" AND FieldId=").Append(SqlFormat.ToSqlValueString(rowMetaData[extFieldName].Id));
scripts.Add(sb.ToString());
sb = new StringBuilder();
sb.Append("INSERT INTO ").Append(extensionTable).
Append("(RecordId, FieldId, Value ) VALUES (").Append(recordIdStringValue).
Append(",").Append(SqlFormat.ToSqlValueString(rowMetaData[extFieldName].Id)).
Append(",").Append(SqlFormat.ToSqlValueString(value)).Append(")");
scripts.Add(sb.ToString());
}
}
//updates extension fields
return(scripts);
}
