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