public static string ToText(this DataTable dt, bool needTitle = true) { if (dt == null || dt.Rows.Count <= 0) { return(string.Empty); } var sbTxt = new StringBuilder(); if (needTitle && dt.Columns.Count > 0) { foreach (DataColumn column in dt.Columns) { sbTxt.Append(column.ColumnName).Append(StringConst.HT); } sbTxt.RemoveLastChar(CharConst.HT); sbTxt.Append(Environment.NewLine); } //遍历数据行,将行数据存入 实体对象中,并添加到 泛型集合中list foreach (DataRow row in dt.Rows) { foreach (DataColumn column in dt.Columns) { sbTxt.Append(row[column.ColumnName]).Append(StringConst.HT); } sbTxt.RemoveLastChar(CharConst.HT); sbTxt.Append(Environment.NewLine); } var txt = sbTxt.RemoveLastChar(Environment.NewLine).ToString(); return(txt); }
/// <summary> /// 通过DataTable获得CSV格式数据 /// </summary> /// <param name="dataTable">数据表</param> /// <param name="c">char</param> /// <returns>CSV字符串数据</returns> public static string ToCSV(this DataTable dataTable, char c = ',') { if (dataTable.IsNull()) { return(string.Empty); } StringBuilder sb = new StringBuilder(); // 写出表头 foreach (DataColumn DataColumn in dataTable.Columns) { sb.AppendFormat("{0}{1}", DataColumn.ColumnName.ToString(), c); } sb.RemoveLastChar(c.ToString()); sb.Append("\n"); // 写出数据 foreach (DataRowView dataRowView in dataTable.DefaultView) { foreach (DataColumn DataColumn in dataTable.Columns) { sb.AppendFormat("{0}{1}", dataRowView[DataColumn.ColumnName].ToString(), c); } sb.RemoveLastChar(c.ToString()); sb.Append("\n"); } return(sb.ToString()); }
internal static SQLiteCommand InsertCommand <T>(string tableName, T model) { var toReturn = new SQLiteCommand(); var command = new StringBuilder($"INSERT INTO {tableName} ("); var filteredProperties = GetFilteredProperties <T>(); var modelType = model.GetType(); var columnNames = new StringBuilder(); var columnValues = new StringBuilder("VALUES ("); foreach (var prop in filteredProperties) { columnNames.Append(prop.Name + ","); columnValues.Append("$" + prop.Name + ","); var propInfo = modelType.GetProperty(prop.Name); var value = propInfo.GetValue(model); toReturn.Parameters.AddWithValue("$" + prop.Name, value); } columnNames.RemoveLastChar(); columnValues.RemoveLastChar(); columnNames.Append(")"); columnValues.Append(");"); command.Append(columnNames.ToString()); command.Append(columnValues.ToString()); toReturn.CommandText = command.ToString(); return(toReturn); }
/// <summary> /// Get a delegate object and use it to generate a entity class. /// </summary> /// <param name="type"></param> /// <param name="typeList"></param> /// <returns></returns> public static Func <object[], object> BuildDeletgateCreateInstance(Type type, Type[] typeList) { ConstructorInfo constructor = type.GetConstructor(typeList); if (constructor == null) { if (typeList == null || typeList.Length == 0) { throw new Exception($@"未找到类'{type.Name}({type.FullName})'的无参数构造器."); } else { StringBuilder sb = new StringBuilder(); foreach (var item in typeList) { sb.Append($@"{item.Name}({item.FullName}),"); } sb.RemoveLastChar(','); var msg = $@"未找到类'{type.Name}({type.FullName})'的构造器:参数个数:{typeList.Length},参数类型:{sb}."; throw new Exception(msg); } } ParameterExpression paramExp = Expression.Parameter(typeof(object[]), "args_"); Expression[] expList = GetExpressionArray(typeList, paramExp); NewExpression newExp = Expression.New(constructor, expList); Expression <Func <object[], object> > expObj = Expression.Lambda <Func <object[], object> >(newExp, paramExp); return(expObj.Compile()); }
public void RemoveLastCharTest() { var s = new StringBuilder(); s.Append("hello"); s.RemoveLastChar(); Assert.AreEqual("hell", s.ToString()); s.Clear(); Assert.AreEqual("", s.ToString()); s.RemoveLastChar(); Assert.AreEqual("", s.ToString()); s.Append('a'); s.RemoveLastChar(); Assert.AreEqual("", s.ToString()); }
/// <summary> /// Hashtable数据转URL字符串 /// </summary> /// <param name="parameters">Hashtable</param> /// <returns></returns> public static string ToUrlEncode(this Hashtable parameters) { if (parameters.IsNull() || parameters.Count == 0) return string.Empty; StringBuilder sb = new StringBuilder(); foreach (string k in parameters.Keys) sb.AppendFormat("{0}={1}&", k.UrlEncode(), parameters[k].ToString().UrlEncode()); sb.RemoveLastChar("&"); return sb.ToString(); }
public override string ToJson() { var sb = new StringBuilder(); sb.Append("{"); foreach (var keyVP in props) { sb.Append(keyVP.Key.AsJavaScript() + ":"); if (keyVP.Value is JsBaseObject) sb.Append(((JsBaseObject)keyVP.Value).ToJson() + ","); else sb.Append(Json.Encode(keyVP.Value) + ","); } foreach (var keyVP in rawProps) { sb.Append(keyVP.Key.AsJavaScript() + ":"); sb.Append(keyVP.Value + ","); } if (sb.Length > 1) sb.RemoveLastChar(); sb.Append("}"); return sb.ToString(); }
internal static SQLiteCommand UpdateCommand <T>(string tableName, T model) { var toReturn = new SQLiteCommand(); var command = new StringBuilder($"UPDATE {tableName} SET "); var filteredProperties = GetFilteredProperties <T>(); var modelType = model.GetType(); foreach (var prop in filteredProperties) { var propInfo = modelType.GetProperty(prop.Name); var value = propInfo.GetValue(model); command.Append(prop.Name + "="); command.Append("$" + prop.Name + ","); toReturn.Parameters.AddWithValue("$" + prop.Name, value); } command.RemoveLastChar(); var primaryKey = GetPrimaryKeyProperty <T>(); var pkInfo = modelType.GetProperty(primaryKey.Name); var pkValue = pkInfo.GetValue(model); command.Append($" WHERE {primaryKey.Name} = ${primaryKey.Name}"); toReturn.Parameters.AddWithValue("$" + primaryKey.Name, pkValue); toReturn.CommandText = command.ToString(); return(toReturn); }
[M(O.AggressiveInlining)] private static void ReplaceLastCharIfNotEqual(this StringBuilder sb, char ch) { if (sb[sb.Length - 1] != ch) { sb.RemoveLastChar().Append(ch); } }
private void GenerateConfigurationCode_Click(object sender, EventArgs e) { TryRun(() => { string filePath = filePath1.Text.Trim().移除路径前后引号(); if (string.IsNullOrEmpty(filePath)) { MessageBox.Show("路径不能为空"); return; } var dataConfigInfo = new List <ExcelDataConfigInfo>(); for (int i = 0; i < dgv1.Rows.Count; i++) { dataConfigInfo.Add(new ExcelDataConfigInfo() { WorkSheetIndex = i + 1, TitleLine = Convert.ToInt32(dgv1.Rows[i].Cells[2].Value), TitleColumn = Convert.ToInt32(dgv1.Rows[i].Cells[3].Value) }); } string fileOutDirectoryName = Path.GetDirectoryName(Path.GetFullPath(filePath)); var defaultConfigList = EPPlusHelper.FillExcelDefaultConfig(filePath, fileOutDirectoryName, dataConfigInfo); var haveConfig = defaultConfigList.Find(a => a.ClassPropertyList.Count > 0) != null; if (!haveConfig) { MessageBox.Show("未检测到配置信息"); return; } //将字符串写入文件 StringBuilder errMsg = new StringBuilder(); var filePathPrefix = $@"{fileOutDirectoryName}\{Path.GetFileNameWithoutExtension(filePath)}_Result"; foreach (var item in defaultConfigList) { if (item.ClassPropertyList.Count > 0) { File.WriteAllText($@"{filePathPrefix}_{nameof(item.CrateDataTableSnippe)}_{item.WorkSheetName}.txt", item.CrateDataTableSnippe); File.WriteAllText($@"{filePathPrefix}_{nameof(item.CrateClassSnippe)}_{item.WorkSheetName}.txt", item.CrateClassSnippe); } else { errMsg.Append(item.WorkSheetName + "、"); } } errMsg.RemoveLastChar('、'); if (errMsg.Length > 0) { MessageBox.Show($@"下列工作簿未生成配置项:{errMsg}"); } if (!filePath.GetDirectoryName().Contains(@"\Desktop\")) { WinFormHelper.OpenFilePath(filePath.GetDirectoryName()); } }); }
public static StringBuilder RemoveLastChar(this StringBuilder value, char c) { if (value == null) { throw new System.ArgumentNullException(nameof(value)); } return(value.Length <= 0 ? value : value[value.Length - 1] == c?value.RemoveLastChar() : value); }
//#endregion /// <summary> /// 遍历 get/post 数据 /// </summary> /// <returns>遍历 get/post 数据</returns> public static string GetRequest() { StringBuilder sb = new StringBuilder(); sb.Append("form:"); for (int i = 0; i < HttpContext.Current.Request.Form.Count; i++) { sb.Append(HttpContext.Current.Request.Form.Keys[i].ToString() + "=" + HttpContext.Current.Request.Form[i].ToString() + "&"); } sb.RemoveLastChar("&"); sb.Append("querystring:"); for (int i = 0; i < HttpContext.Current.Request.QueryString.Count; i++) { sb.Append(HttpContext.Current.Request.QueryString.Keys[i].ToString() + "=" + HttpContext.Current.Request.QueryString[i].ToString() + "&"); } sb.RemoveLastChar("&"); return(sb.ToString()); }
/// <summary> /// LIST参数转为URL参数 /// </summary> /// <param name="param">LIST参数</param> /// <returns>URL参数</returns> public static string ToUrlEncode(this IList <UrlParameter> param) { StringBuilder ParameString = new StringBuilder(); foreach (UrlParameter par in param) { ParameString.AppendFormat("{0}={1}&", par.ParameterName, par.ParameterValue.UrlEncode()); } ParameString.RemoveLastChar("&"); return(ParameString.ToString()); }
/// <summary> /// 分页SQL调用方法 只允许主键排序 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示数量</param> /// <param name="tableName">表名称</param> /// <param name="pk">主键</param> /// <param name="fieldList">字段列表</param> /// <param name="where">where条件 and or 开始</param> /// <param name="groupBy">分组条件</param> /// <param name="orderBy">排序条件</param> /// <returns>分页SQL</returns> public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") { PagerSql sql = new PagerSql(); StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.AppendFormat("count({0}) as total ", pk); if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName); if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where); if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy); sql.CountSql = strSql.ToString(); //select * from Student //where Id in ( //select top 10 Id //from(select top 3200010 Id from Student order by Id)t //order by Id desc) //order by Id //SELECT * FROM ( //SELECT TOP 页面容量 * FROM ( //SELECT TOP 页面容量*当前页码 * FROM //表 WHERE 条件 ORDER BY 字段A ASC //)AS TEMPTABLE1 ORDER BY 字段A DESC //) AS TEMPTABLE2 ORDER BY 字段A ASC StringBuilder orderByExt = new StringBuilder(); foreach (string order in orderBy.Split(',')) { string order2 = order.Trim(); if (order2.EndsWith(" desc", true, null)) orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc"); else orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc"); } orderByExt.RemoveLastChar(","); strSql.Clear(); strSql.Append("select "); //if (distinct) strSql.Append("distinct "); if (pageSize == 1) { strSql.AppendFormat("top {0} ", pageSize); strSql.AppendFormat("{0} ", fieldList); if (!tableName.IsNullEmpty()) strSql.AppendFormat("from {0} ", tableName); if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where); if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy); if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy); } else { if (!tableName.IsNullEmpty()) strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex); if (!where.IsNullEmpty()) strSql.AppendFormat("where {0} ", where); if (!groupBy.IsNullEmpty()) strSql.AppendFormat("group by {0} ", groupBy); if (!orderBy.IsNullEmpty()) strSql.AppendFormat("order by {0} ", orderBy); strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy); } sql.DataSql = strSql.ToString(); return sql; }
public MvcHtmlString ToHtmlStringOnlyMessages() { if (IsEmpty) return MvcHtmlString.Empty; var sb = new StringBuilder(); foreach (var error in errorList) { sb.Append("<span>" + error.ErrorMessage.AsHtml() + "</span><br>"); } sb.RemoveLastChar(4); return new MvcHtmlString(sb.ToString()); }
public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent) { if (GetJoinView().GetNativeTableRole() != null) { var sb = new StringBuilder(); sb.Append("[" + GetJoinView().Name + "]("); foreach (var col in GetJoinView().GetColumns()) //if (col is SchemaQuerySelectColumn) sb.Append("[" + col.GetFullAlias() + "],"); sb.Append("[__ID__],"); sb.RemoveLastChar(); sb.Append(") AS"); sb.AppendLine("("); sb.Append(GetJoinView().GetNativeTableRole().GetSqlText(withCTE, null)); sb.AppendLine("),"); withCTE.Add(sb.ToString()); } else if (GetJoinView().GetNativeQuery() != null) { var sb = new StringBuilder(); sb.Append("[" + GetJoinView().Name + "]("); foreach (var col in GetJoinView().GetColumns()) if (col is SchemaQuerySelectColumn) sb.Append("[" + col.GetFullAlias() + "],"); sb.Append("[__ID__],"); sb.RemoveLastChar(); sb.Append(") AS"); sb.AppendLine("("); sb.Append(GetJoinView().GetNativeQuery().GetSqlText(withCTE)); sb.AppendLine("),"); withCTE.Add(sb.ToString()); } sql.AppendLine(indent + "LEFT JOIN " + GetJoinView().Get4PartsTableName() + " AS [" + GetJoinTableFillAlias() + "]"); foreach (var col in Columns) col.EmitJoinSql(sql, withCTE, indent + " "); var keyFieldName = "__ID__"; if (GetJoinView().GetNativeTable() != null) keyFieldName = GetJoinView().GetNativeTable().GetPrimaryKeyColumn().Name; sql.AppendLine(indent + "ON [" + GetJoinTableFillAlias() + "].[" + keyFieldName + "] = [" + ParentColumn.GetJoinTableFillAlias() + "].[" + Name + "]"); }
/// <summary> /// IDictionary数据转URL字符串 Join("=","&") /// </summary> /// <param name="parameters">IDictionary</param> /// <param name="split1">分隔符1</param> /// <param name="split2">分隔符2</param> /// <returns></returns> public static string Join(this IDictionary parameters, string split1 = "=", string split2 = "&") { if (parameters.IsNull() || parameters.Count == 0) { return(string.Empty); } StringBuilder sb = new StringBuilder(); foreach (string k in parameters.Keys) { sb.AppendFormat("{0}{2}{1}{3}", k, parameters[k].ToString(), split1, split2); } sb.RemoveLastChar(split2); return(sb.ToString()); }
/// <summary> /// IDictionary数据转URL字符串 /// </summary> /// <param name="parameters">IDictionary</param> /// <returns></returns> public static string ToUrlEncode(this IDictionary parameters) { if (parameters.IsNull() || parameters.Count == 0) { return(string.Empty); } StringBuilder sb = new StringBuilder(); foreach (string k in parameters.Keys) { sb.AppendFormat("{0}={1}&", k.UrlEncode(), parameters[k].ToString().UrlEncode()); } sb.RemoveLastChar("&"); return(sb.ToString()); }
public MvcHtmlString ToHtmlString() { if (IsEmpty) return MvcHtmlString.Empty; var sb = new StringBuilder(); foreach (var error in errorList) { if (string.IsNullOrWhiteSpace(error.ErrorObjectName)) sb.Append(@"<span>" + error.ErrorMessage.AsHtml() + "</span><br>"); else sb.Append(@"<span>""" + error.ErrorObjectName.AsHtml() + @""": " + error.ErrorMessage.AsHtml() + "</span><br>"); } sb.RemoveLastChar(4); return new MvcHtmlString(sb.ToString()); }
private string getParameterStringValue(string name, object obj, bool afterIn = false) { var temp = obj; foreach (var item in name.Splite1WithCount(".")) { var property = JasmineReflectionCache.Instance.GetItem(obj.GetType()).Properties.GetItemByName(item); if (property == null) { throw new ParameterNotFoundException($"parameter @{name} can not be found by given isntance {obj} "); } temp = property.GetValue(temp); } if (afterIn) { var type = temp.GetType(); if (type.CanConvertTo(typeof(IEnumerable)) && type.GetElementType().IsBaseType()) { var elementType = type.GetElementType(); var builder = new StringBuilder(); builder.Append("("); foreach (var item in (IEnumerable)temp) { builder.Append(DefaultBaseTypeConvertor.Instance.ConvertToSqlString(elementType, item)) .Append(","); } builder.RemoveLastChar(); builder.Append(")"); return(builder.ToString()); } else { throw new Exception("parameter after keyword 'in' , must implement interface IEnumerable and element type is basetype "); } } return(DefaultBaseTypeConvertor.Instance.ConvertToSqlString(temp.GetType(), temp)); }
public static string MakeInsertLeft(string table, params string[] columns) { var builder = new StringBuilder(); builder.Append($"Insert Into {table}("); foreach (var item in columns) { builder.Append(item.Replace(".", "_")).Append(","); } builder.RemoveLastChar(); builder.Append(") Values "); return(builder.ToString()); }
internal static SQLiteCommand CreateTableCommant <T>(string tableName) { var toReturn = new SQLiteCommand(); var command = new StringBuilder($"CREATE TABLE IF NOT EXISTS {tableName}("); var properties = typeof(T).GetProperties(); foreach (var prop in properties) { command.Append(prop.Name + " "); command.Append(prop.GetSQLiteType() + ","); } command.RemoveLastChar(); command.Append(");"); toReturn.CommandText = command.ToString(); return(toReturn); }
/// <summary> /// 根据 id, Name , parentId 3个字段生成额外字段Depth 和 用于报表排序的Sort字段 /// </summary> /// <param name="tblName"></param> /// <param name="rootItemWhere"></param> /// <param name="nameFieldName"></param> /// <param name="idFiledName"></param> /// <param name="parentIdName"></param> /// <param name="eachSortFieldLength">每个Depth的长度,默认2. </param> /// <param name="reportSortFileTotallength">报表排序字段的总长度,默认为12如果真的要设置,level * Max(Len(主键))</param> /// <param name="rearChat">报表排序字段 / 每个Depth字段 小于 指定长度时填充的字符是什么</param> /// <param name="otherFiledName"></param> /// <returns></returns> public static string GetTreeTableReportSql(string tblName, string rootItemWhere, string nameFieldName = "Name", string idFiledName = "Id", string parentIdName = "ParentId", int eachSortFieldLength = 2, int reportSortFileTotallength = 12, char rearChat = ' ', params string[] otherFiledName) { //该方法基本与GetTreeTableIncludeLevelFieldSql()一样 string comma = " ,"; string dot = "."; StringBuilder sb1 = new StringBuilder(); //定位成员的字段 sb1.Append(idFiledName).Append(comma) .Append(nameFieldName).Append(comma) .Append(parentIdName); StringBuilder sb2 = new StringBuilder(); //递归成员的字段 sb2.Append(tblName).Append(dot).Append(idFiledName).Append(comma) .Append(tblName).Append(dot).Append(nameFieldName).Append(comma) .Append(tblName).Append(dot).Append(parentIdName); string char1 = Enumerable.Repeat(rearChat.ToString(), eachSortFieldLength).Aggregate((current, next) => next + current); string char2 = Enumerable.Repeat(rearChat.ToString(), reportSortFileTotallength).Aggregate((current, next) => next + current); if (otherFiledName != null && otherFiledName.Length > 0) { foreach (var item in otherFiledName) { sb1.Append(item).Append(comma); sb2.Append(tblName).Append(dot).Append(item).Append(comma); } sb1.RemoveLastChar(comma.Length); sb2.RemoveLastChar(comma.Length); } string sql = $@" with cte as( SELECT {sb1} , 1 as Level , CAST( LEFT(LTRIM({idFiledName})+'{char1}',{eachSortFieldLength}) AS VARCHAR(10)) AS 'Depth' FROM {tblName} WHERE {rootItemWhere} UNION ALL SELECT {sb2} , cte.Level+1 as Level , CAST(LTRIM(cte.Depth) + LEFT(LTRIM({tblName}.{idFiledName}) +'{char1}',{eachSortFieldLength})AS VARCHAR(10)) AS 'Depth' FROM cte, {tblName} where cte.{idFiledName} = {tblName}.{parentIdName} ) SELECT {sb1} , Level,LEFT(LTRIM(cte.Depth)+'{char2}',{reportSortFileTotallength}) AS 'sort' FROM cte ORDER BY sort ,cte.Level"; return(sql); }
public override string ToJson() { var sb = new StringBuilder(); sb.Append("["); foreach (var obj in props) { if (obj.isRaw) sb.Append(obj.value.ToString() + ","); else if (obj.value is JsBaseObject) sb.Append(((JsBaseObject)obj.value).ToJson() + ","); else sb.Append(Json.Encode(obj.value) + ","); } if (sb.Length > 1) sb.RemoveLastChar(); sb.Append("]"); return sb.ToString(); }
/// <summary> /// 原本的树形表结构是没有Level字段的,通过该方法可以生成level字段 /// </summary> /// <param name="tblName"></param> /// <param name="rootItemWhere"></param> /// <param name="nameFieldName"></param> /// <param name="idFiledName"></param> /// <param name="parentIdName"></param> /// <param name="otherFiledName"></param> /// <returns></returns> public static string GetTreeTableIncludeLevelFieldSql(string tblName, string rootItemWhere, string nameFieldName = "Name", string idFiledName = "Id", string parentIdName = "ParentId", params string[] otherFiledName) { string comma = " ,"; string dot = "."; StringBuilder sb1 = new StringBuilder(); //定位成员的字段 sb1.Append(idFiledName).Append(comma) .Append(nameFieldName).Append(comma) .Append(parentIdName); StringBuilder sb2 = new StringBuilder(); //递归成员的字段 sb2.Append(tblName).Append(dot).Append(idFiledName).Append(comma) .Append(tblName).Append(dot).Append(nameFieldName).Append(comma) .Append(tblName).Append(dot).Append(parentIdName); if (otherFiledName != null && otherFiledName.Length > 0) { foreach (var item in otherFiledName) { sb1.Append(item).Append(comma); sb2.Append(tblName).Append(dot).Append(item).Append(comma); } sb1.RemoveLastChar(comma.Length); sb2.RemoveLastChar(comma.Length); } string sql = $@" with cte as( SELECT {sb1} , 1 as Level FROM {tblName} WHERE {rootItemWhere} UNION ALL SELECT {sb2} , cte.Level+1 as Level from cte, {tblName} where cte.{idFiledName} = {tblName}.{parentIdName} ) SELECT {sb1} , Level FROM cte ORDER BY cte.Level"; return(sql); }
public static void SaveDictionaryList <TKey, TValue>(this IDictionary <TKey, List <TValue> > dictList, string file) { var sw = new StreamWriter(file); var sb = new StringBuilder(255); foreach (var key in dictList.Keys) { sb.Append('"'); sb.Append(key); sb.Append("\","); foreach (var val in dictList[key]) { sb.Append('"'); sb.Append(val); sb.Append("\","); } sb.RemoveLastChar(); sw.WriteLine(sb.ToString()); sb.Clear(); } sw.Close(); }
protected override IEnumerator <KeyValuePair <string, T> > CreateEnumerator() { Stack <TernarySearchTreeNode <T> > stack = new Stack <TernarySearchTreeNode <T> >(); StringBuilder stringBuilder = new StringBuilder(32); TernarySearchTreeNode <T> node = root; while (true) { while (node != null) { stack.Push(node); node = node.Left; } if (stack.IsEmpty) { yield break; } node = stack.Peek(); if (node != null) { stringBuilder.Append(node.Char); if (node.IsEos) { yield return(new KeyValuePair <string, T>(stringBuilder.ToString(), node.Value)); } stack.Push(null); node = node.Equal; } else { stringBuilder.RemoveLastChar(); stack.Pop(); node = stack.Pop().Right; } } }
/// <summary> /// LIST参数转为URL参数 /// </summary> /// <param name="param">LIST参数</param> /// <returns>URL参数</returns> public static string GetUrlParameter(List<UrlParameter> param) { StringBuilder ParameString = new StringBuilder(); foreach (UrlParameter par in param) ParameString.AppendFormat("{0}={1}&", par.ParameterName, par.ParameterValue); ParameString.RemoveLastChar("&"); return ParameString.ToString(); }
public void EmitSaveToLogSql(StringBuilder sql, string id_variable, SchemaTable masterTable, string indent) { sql.Append(indent + "INSERT [" + GetLogTable().Name + "] ("); foreach (var col in GetLogTable().Columns) { if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue; sql.Append(" [" + col.Name + "],"); } sql.RemoveLastChar(1); sql.AppendLine(")"); sql.Append(indent + "SELECT "); foreach (var col in GetLogTable().Columns) { if (col.Name == "__lockuser__" || col.Name == "__locksession__" || col.Name == "__locktime__") continue; if (col.Name == "__mastertimestamp__") sql.Append(" @master_timestamp,"); //sql.Append(" ( SELECT __timestamp__ FROM [" + masterTable.Name + "] WHERE ID=[" + Name + "].[" + GetMasterColumn().Name + "]),"); else sql.Append(" [" + col.Name + "],"); } sql.RemoveLastChar(1); sql.AppendLine(" FROM [" + Name + "] WHERE ID=" + id_variable); int detNum = App.Random.Next(1, int.MaxValue / 2); foreach (var detail in Details) { detNum++; var detailTable = detail.GetDetailTable(); sql.AppendLine(indent + "DECLARE @detid" + detNum + " uniqueidentifier"); sql.AppendLine(indent + "DECLARE curs" + detNum + " CURSOR LOCAL STATIC FOR"); sql.AppendLine(indent + "SELECT ID FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + id_variable); sql.AppendLine(indent + "OPEN curs" + detNum); sql.AppendLine(indent + "FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum); sql.AppendLine(indent + "WHILE @@FETCH_STATUS = 0"); sql.AppendLine(indent + "BEGIN"); detailTable.EmitSaveToLogSql(sql, "@detid" + detNum, this, indent + " "); sql.AppendLine(indent + " FETCH NEXT FROM curs" + detNum + " INTO @detid" + detNum); sql.AppendLine(indent + "END"); sql.AppendLine(indent + "CLOSE curs" + detNum); sql.AppendLine(indent + "DEALLOCATE curs" + detNum); } }
public static string Clean(string name, char wordsSeparator = '.') { if (name.IsNullOrEmpty()) { return(name); } var array = name.Split(PUNCTUATION_CHARS, StringSplitOptions.RemoveEmptyEntries); #region [.phase #1.] var firstWholeNumbersWord = default(string); var words = new List <(string word, bool isOnlyLetters)>(array.Length); foreach (var s in array) { if (EXCLUDES_WORDS_SET.Contains(s)) { continue; } if (int.TryParse(s, out var _)) { if (firstWholeNumbersWord == null) { firstWholeNumbersWord = s; } continue; } words.Add((s, s.IsOnlyLetters())); } if (!words.Any()) { var s = (firstWholeNumbersWord ?? "---"); words.Add((s, s.IsOnlyLetters())); } #endregion #region [.phase #2.] const char SPACE = ' '; var sb = new StringBuilder(name.Length); var prev_word_isOnlyLetters = true; foreach (var t in words) { if (t.word.TryParseSE(out var se)) { if (!sb.IsEmpty()) { sb.ReplaceLastCharIfNotEqual(wordsSeparator); } sb.Append(se).Append(wordsSeparator); break; } if (prev_word_isOnlyLetters) //&& t.isOnlyLetters ) { sb.AppendFirstCharToUpper(t.word).Append((t.isOnlyLetters ? SPACE : wordsSeparator)); } else { sb.Append(t.word).Append(wordsSeparator); } prev_word_isOnlyLetters = t.isOnlyLetters; } sb.RemoveLastChar(); #endregion return(sb.ToString()); }
public void SaveData() { CheckParams(); if (CrudMode == CrudMode.View) { throw new Exception("Запись только на чтение в таблице '" + Table.Name + "', ID='" + ID + "'"); } else if (CrudMode == CrudMode.Add) { if (MasterTableRow != null) { var masterColumn = Table.GetMasterColumn(); if (masterColumn == null) throw new Exception("'В таблице '" + Table.Name + "' отсутствует колонка с ролью '^ВложеннаяТаблица.Мастер'."); this[masterColumn.Name] = MasterTableRow.ID; } if (NewBussinesOperID != null) { var bussinesOperColumn = Table.GetColumnByRole(RoleConst.ВложеннаяТаблица_БизнесОперация); if (bussinesOperColumn == null) throw new Exception("'В таблице '" + Table.Name + "' отсутствует колонка с ролью '^ВложеннаяТаблица.БизнесОперация'."); this[bussinesOperColumn.Name] = NewBussinesOperID; } if (!NewValues.ContainsKey(Table.GetPrimaryKeyColumn().Name)) NewValues.Add(Table.GetPrimaryKeyColumn().Name, ID); if (!NewValues.ContainsKey("__changeuser__")) NewValues.Add("__changeuser__", App.UserID); else NewValues["__changeuser__"] = App.UserID; if (!NewValues.ContainsKey("__changetime__")) NewValues.Add("__changetime__", DateTime.Now); if (NewValues.Values.Count > 0) { var sql = new StringBuilder(); sql.AppendLine("BEGIN TRAN"); sql.AppendLine("INSERT " + Table.Get4PartsTableName() + " ("); foreach (var col in Table.Columns) { if (NewValues.ContainsKey(col.Name)) sql.AppendLine(" [" + col.Name + "],"); } sql.RemoveLastChar(3); sql.AppendLine(")"); sql.AppendLine("VALUES ("); foreach (var col in Table.Columns) { if (NewValues.ContainsKey(col.Name)) if (col.Name == "__changetime__") sql.AppendLine("GetDate(),"); else sql.AppendLine(NewValues[col.Name].AsSQL() + ","); } sql.RemoveLastChar(3); sql.AppendLine(")"); //// __undo__ //if (MasterTableRow != null) //{ // var undo_sql = "DELETE FROM [" + Table.Name + "] WHERE ID=" + ID.AsSQL(); // sql.AppendLine("INSERT [__EditSession__]([SessionID],[ParentSessionID],[TableID],[RecordID],[UserID],[UndoSql])"); // sql.AppendLine("VALUES (" + EditSessionID.AsSQL() + "," + MasterTableRow.EditSessionID.AsSQL() + "," + Table.ID.AsSQL() + "," + // ID.AsSQL() + "," + App.UserID.AsSQL() + "," + undo_sql.AsSQL() + ")"); //} //else //{ // foreach (var detailTable in App.Schema.GetAllDetailTablesSamples()) // { // sql.AppendLine("DELETE FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + ID.Reverse().AsSQL()); // } // sql.Append("EXEC __EditSession__Commit "); // sql.Append("@sessionID=" + EditSessionID.AsSQL() + ","); // sql.AppendLine("@parentSessionID=NULL"); //} sql.AppendLine("COMMIT"); if (MasterTableRow != null && MasterTableRow.BeforeSaveChanges != null) MasterTableRow.BeforeSaveChanges(this); if (BeforeSaveChanges != null) BeforeSaveChanges(this); using (var db = App.Schema.SqlDB.GetDbManager()) { int updatedRows = db.SetCommand(sql.ToString()).ExecuteNonQuery(); if (updatedRows == 0) throw new Exception("Ошибка добавления записи в таблицу '" + Table.Name + "', ID='" + ID + "'"); } CrudMode = Buhta.CrudMode.Edit; foreach (var columnName in NewValues.Keys) { if (Values.ContainsKey(columnName)) Values[columnName] = NewValues[columnName]; else Values.Add(columnName, NewValues[columnName]); } NewValues.Clear(); } } else if (CrudMode == CrudMode.Edit) { if (NewValues.ContainsKey(Table.GetPrimaryKeyColumn().Name)) throw new Exception("Запрещено изменять ID в таблице '" + Table.Name + "', ID='" + ID + "'"); //var log = new StringBuilder(); //var log_col = new StringBuilder(); //var log_sel = new StringBuilder(); //var logTable = Table.GetLogTable(); var sql = new StringBuilder(); //if (logTable != null) //{ // sql.AppendLine("BEGIN TRAN"); //} if (!NewValues.ContainsKey("__changeuser__")) NewValues.Add("__changeuser__", App.UserID); else NewValues["__changeuser__"] = App.UserID; if (!NewValues.ContainsKey("__changetime__")) NewValues.Add("__changetime__", DateTime.Now); sql.AppendLine("BEGIN TRAN"); var updateSql = new StringBuilder(); bool needUpdate = false; if (NewValues.Values.Count > 0) { updateSql.AppendLine("UPDATE " + Table.Get4PartsTableName() + " SET"); foreach (var col in Table.Columns) { if (col.Name != Table.GetPrimaryKeyColumn().Name && NewValues.ContainsKey(col.Name)) { updateSql.Append(" [" + col.Name + "] = "); needUpdate = true; if (col.Name == "__changetime__") updateSql.Append("GetDate()"); else { if (NewValues[col.Name] == null) updateSql.Append("NULL"); else updateSql.Append(NewValues[col.Name].AsSQL()); } updateSql.AppendLine(","); //if (logTable!=null) //{ // log_col.Append("[" + col.Name + "],"); // log_sel.Append("[" + col.Name + "],"); //} } } updateSql.RemoveLastChar(3); updateSql.AppendLine(); updateSql.AppendLine("WHERE [" + Table.GetPrimaryKeyColumn().Name + "]=" + ID.AsSQL()); } if (needUpdate) sql.Append(updateSql); //if (MasterTableRow != null) //{ // var undo_sql = new StringBuilder(); // undo_sql.AppendLine("UPDATE [" + Table.Name + "] SET"); // foreach (var col in Table.Columns) // { // if (col.Name != "ID" && NewValues.ContainsKey(col.Name)) // { // undo_sql.Append(" [" + col.Name + "] = "); // if (Values[col.Name] == null) // undo_sql.Append("NULL"); // else // undo_sql.Append(Values[col.Name].AsSQL()); // undo_sql.AppendLine(","); // } // } // undo_sql.RemoveLastChar(3); // undo_sql.AppendLine(); // undo_sql.AppendLine("WHERE ID=" + ID.AsSQL()); // sql.AppendLine("INSERT [__EditSession__]([SessionID],[ParentSessionID],[TableID],[RecordID],[UserID],[UndoSql])"); // sql.AppendLine("VALUES (" + EditSessionID.AsSQL() + "," + MasterTableRow.EditSessionID.AsSQL() + "," + Table.ID.AsSQL() + "," + // ID.AsSQL() + "," + App.UserID.AsSQL() + "," + undo_sql.AsSQL() + ")"); //} //else //{ // foreach (var detailTable in App.Schema.GetAllDetailTablesSamples()) // { // sql.AppendLine("DELETE FROM [" + detailTable.Name + "] WHERE [" + detailTable.GetMasterColumn().Name + "]=" + ID.Reverse().AsSQL()); // } // sql.Append("EXEC __EditSession__Commit "); // sql.Append("@sessionID=" + EditSessionID.AsSQL() + ","); // sql.AppendLine("@parentSessionID=NULL"); //} sql.AppendLine("COMMIT"); //if (logTable != null) //{ // log_col.Append("["+SchemaTable.logPrefix+""+"]"); // log.AppendLine("INSERT [" + logTable.Name + "](" + log_col.ToString() + ")"); // log.AppendLine("SELECT " + log_sel.ToString() + " FROM [" + Table.Name + "] WHERE ID=" + ID.AsSQL()); // sql.Insert(0, log.ToString()); // sql.AppendLine("COMMIT"); //} if (MasterTableRow != null && MasterTableRow.BeforeSaveChanges != null) MasterTableRow.BeforeSaveChanges(this); if (BeforeSaveChanges != null) BeforeSaveChanges(this); using (var db = App.Schema.SqlDB.GetDbManager()) { int updatedRows = db.SetCommand(sql.ToString()).ExecuteNonQuery(); if (updatedRows == 0) throw new Exception("Ошибка изменения записи в таблице '" + Table.Name + "', ID='" + ID + "'"); } if (BeforeSaveChanges != null) BeforeSaveChanges(this); foreach (var columnName in NewValues.Keys) { if (Values.ContainsKey(columnName)) Values[columnName] = NewValues[columnName]; else Values.Add(columnName, NewValues[columnName]); } NewValues.Clear(); } if (Table.GetIsProvodkaGenerationExists()) Table.ExecGenStoredProc(ID); if (OnSaveData != null) OnSaveData(this, null); }
public void EmitOrderBySql(StringBuilder sql) { var cols = GetAllColumns() .Where(col => col is SchemaQuerySelectColumn && (col as SchemaQuerySelectColumn).OrderBy != QueryOrderBy.None) .OrderBy(col => (col as SchemaQuerySelectColumn).OrderBy).ToList(); if (cols.Count > 0) { sql.AppendLine("ORDER BY"); foreach (var col in cols) { if ((col as SchemaQuerySelectColumn).OrderBy.ToString().StartsWith("Desc")) sql.AppendLine(" [" + col.GetFullAlias() + "] DESC,"); else sql.AppendLine(" [" + col.GetFullAlias() + "],"); } sql.RemoveLastChar(3); sql.AppendLine(); if (!string.IsNullOrWhiteSpace(RuntimeOffsetRows)) { sql.AppendLine("OFFSET " + RuntimeOffsetRows + " ROWS"); } if (!string.IsNullOrWhiteSpace(RuntimeFetchRows)) { if (string.IsNullOrWhiteSpace(RuntimeOffsetRows)) throw new Exception("Запрос "+Name.AsSQL()+": не заполнен RuntimeOffsetRow."); sql.AppendLine("FETCH NEXT " + RuntimeFetchRows + " ROWS ONLY"); } } else { if (!string.IsNullOrWhiteSpace(RuntimeOffsetRows) || !string.IsNullOrWhiteSpace(RuntimeFetchRows)) throw new Exception("Запрос " + Name.AsSQL() + ": нельзя использовать RuntimeOffsetRows и/или RuntimeFetchRows без указания сортировки в запросе."); } }
private void CheckTemplateConfiguration_Click(object sender, EventArgs e) { TryRun(() => { var ws1Path = this.filePath1.Text.Trim().移除路径前后引号(); var ws2Path = this.filePath2.Text.Trim().移除路径前后引号(); if (string.IsNullOrEmpty(ws1Path)) { MessageBox.Show("路径1不能为空"); return; } if (string.IsNullOrEmpty(ws2Path)) { MessageBox.Show("路径2不能为空"); return; } if (ws1Path == ws2Path) { MessageBox.Show("比较文件路径一致,无法比较"); return; } var ws1Index_string = this.wsNameOrIndex1.Text.Trim(); var ws2Index_string = this.wsNameOrIndex2.Text.Trim(); var ws1TitleLine = Convert.ToInt32(this.TitleLine1.Text.Trim()); var ws2TitleLine = Convert.ToInt32(this.TitleLine2.Text.Trim()); var ws1TitleCol = Convert.ToInt32(this.TitleCol1.Text.Trim()); var ws2TitleCol = Convert.ToInt32(this.TitleCol2.Text.Trim()); using (var fs1 = new FileStream(ws1Path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) using (var fs2 = new FileStream(ws2Path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) using (var excelPackage1 = new ExcelPackage(fs1)) using (var excelPackage2 = new ExcelPackage(fs2)) { var ws1 = GetWorkSheet(excelPackage1, ws1Index_string); var ws2 = GetWorkSheet(excelPackage2, ws2Index_string); var ws1Props = EPPlusHelper.FillExcelDefaultConfig(ws1, ws1TitleLine, ws1TitleCol).ClassPropertyList; var ws2Props = EPPlusHelper.FillExcelDefaultConfig(ws2, ws2TitleLine, ws2TitleCol).ClassPropertyList; { StringBuilder sb = new StringBuilder(); AppendCols(ws1Props, ws2Props, sb); if (sb.Length > 1) { MessageBox.Show($@"A与B比较:B未提供列:{sb.RemoveLastChar()}"); return; } } { StringBuilder sb = new StringBuilder(); AppendCols(ws2Props, ws1Props, sb); if (sb.Length > 1) { MessageBox.Show($@"A与B比较:B多提供列:{sb.RemoveLastChar()}"); return; } } MessageBox.Show("A与B比较:内容一致"); } }); }
public string GetAttrs() { var sb = new StringBuilder(); if (classes.Count > 0 || classes.Count > 0) { sb.Append(@"class="""); foreach (var cls in classes) { if (!classes.Contains(cls)) sb.Append(cls + " "); } foreach (var cls in classes) sb.Append(cls + " "); sb.RemoveLastChar(); sb.Append(@""" "); } if (styles.Keys.Count > 0 || styles.Keys.Count > 0) { sb.Append(@"style="""); foreach (var stl in styles) { if (!styles.ContainsKey(stl.Key)) sb.Append(stl.Key + ":" + stl.Value + ";"); } foreach (var stl in styles) sb.Append(stl.Key + ":" + stl.Value + ";"); sb.Append(@""" "); } foreach (var attr in attrs) { if (!attrs.ContainsKey(attr.Key)) sb.Append(attr.Key + "=" + attr.Value.AsJavaScript() + ";"); } foreach (var attr in attrs) { sb.Append(attr.Key + "=" + attr.Value.AsJavaScript() + ";"); } return sb.ToString(); }
void EmitGenerateProvodkaDbKrPartToTempTable(StringBuilder sql, string indent, SchemaTableProvodka prov, DbKrSaldo dbKr, SchemaTable registr, ObservableCollection<SchemaTableProvodkaField> registrFields) { var masterTable = this; var detailTable = prov.OwnerTableOper.TableDetail.GetDetailTable(); var operID = prov.OwnerTableOper.ID; sql.AppendLine(indent + "-- " + dbKr + ", регистр: " + registr.Name.AsSQL() + ", деталь: " + prov.OwnerTableOper.TableDetail.Name.AsSQL() + ", операция: '[" + prov.OwnerTableOper.Num + "] " + prov.OwnerTableOper.Name + "', проводка: " + prov.Name.AsSQL()); sql.AppendLine(indent + "INSERT " + registr.GetRegistrTempTableName() + "("); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигДеталь).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигБизнесОперация).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_КонфигПроводка).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_Мастер).Name + "],"); sql.AppendLine(indent + " [" + registr.GetColumnByRole(RoleConst.Регистр_Деталь).Name + "],"); foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null)) { sql.AppendLine(indent + " [" + field.RegistrFieldName + "],"); } sql.RemoveLastChar(3); sql.AppendLine(); sql.AppendLine(indent + ")"); sql.AppendLine(indent + "SELECT"); sql.AppendLine(indent + " NewID(),"); sql.AppendLine(indent + " '" + dbKr.ToString()[0] + "',"); sql.AppendLine(indent + " " + prov.OwnerTableOper.TableDetail.ID.AsSQL() + ", -- деталь: " + prov.OwnerTableOper.TableDetail.Name); sql.AppendLine(indent + " " + prov.OwnerTableOper.ID.AsSQL() + ", -- бизнес-операция: " + prov.OwnerTableOper.Name); sql.AppendLine(indent + " " + prov.ID.AsSQL() + ", -- проводка: " + prov.Name); sql.AppendLine(indent + " @masterID,"); sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.Таблица_Ключ).Name + "],"); foreach (var field in registrFields.Where(field => registr.GetColumnByName(field.RegistrFieldName) != null)) { if (field.DataFieldName == "<пусто>") sql.AppendLine(indent + " NULL,"); else { if (dbKr == DbKrSaldo.Кредит && registr.GetColumnByName(field.RegistrFieldName).ColumnRoles.Contains(RoleConst.Регистр_Мера)) sql.AppendLine(indent + " -" + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],"); else sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + field.DataFieldName + "],"); } } sql.RemoveLastChar(3); sql.AppendLine(); sql.AppendLine(indent + "FROM " + detailTable.Get4PartsTableName()); sql.AppendLine(indent + "WHERE " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_БизнесОперация).Name + "]=" + operID.AsSQL() + " AND "); sql.AppendLine(indent + " " + detailTable.Get4PartsTableName() + ".[" + detailTable.GetColumnByRole(RoleConst.ВложеннаяТаблица_Мастер).Name + "]=@masterID"); }
void EmitSaveRegistrFromTempTable(StringBuilder sql, string indent, SchemaTable registrTable) { sql.Append(indent + "INSERT " + registrTable.Get4PartsTableName() + "("); foreach (var col in registrTable.Columns) sql.Append("[" + col.Name + "],"); sql.RemoveLastChar(1); sql.Append(") SELECT "); foreach (var col in registrTable.Columns) sql.Append("[" + col.Name + "],"); sql.RemoveLastChar(1); sql.AppendLine(" FROM " + registrTable.GetRegistrTempTableName()); sql.Append(indent + "INSERT " + GetProvodkasTable().Get4PartsTableName() + "(tableRecordID,provodkaRecordID,registrID)"); sql.AppendLine(" SELECT @masterID,[" + registrTable.GetPrimaryKeyColumn().Name + "]," + registrTable.ID.AsSQL() + " FROM " + registrTable.GetRegistrTempTableName()); }
void EmitCreateRegisterTempTable(StringBuilder sql, string indent, SchemaTable registrTable) { sql.AppendLine(indent + "CREATE TABLE " + registrTable.GetRegistrTempTableName() + "("); foreach (var col in registrTable.Columns) { sql.AppendLine(indent + " [" + col.Name + "] " + col.DataType.GetDeclareSql() + ","); } sql.RemoveLastChar(3); sql.AppendLine(); sql.AppendLine(indent + ")"); }
public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent) { if (GetJoinView() is SchemaVirtualTable) { GetJoinView().EmitJoinSql(sql, withCTE, indent); } else { if (GetJoinView().GetNativeTableRole() != null) { var sb = new StringBuilder(); sb.Append("[" + GetJoinView().Name + "]("); foreach (var col in GetJoinView().GetColumns()) //if (col is SchemaQuerySelectColumn) sb.Append("[" + col.GetFullAlias() + "],"); sb.Append("[__ID__],"); sb.RemoveLastChar(); sb.Append(") AS"); sb.AppendLine("("); sb.Append(GetJoinView().GetNativeTableRole().GetSqlText(withCTE, ParentQuery.RuntimeRecordIDFilter)); sb.AppendLine("),"); withCTE.Add(sb.ToString()); } if (GetJoinView().GetNativeQuery() != null) { var sb = new StringBuilder(); sb.Append("[" + GetJoinView().Name + "]("); foreach (var col in GetJoinView().GetColumns()) if (col is SchemaQuerySelectColumn) sb.Append("[" + col.GetFullAlias() + "],"); sb.Append("[__ID__],"); sb.RemoveLastChar(); sb.Append(") AS"); sb.AppendLine("("); sb.Append(GetJoinView().GetNativeQuery().GetSqlText(withCTE)); sb.AppendLine("),"); withCTE.Add(sb.ToString()); } sql.AppendLine("FROM"); //sql.AppendLine(" [" + GetJoinView().Name + "] AS [" + GetJoinTableFillAlias() + "]"); sql.AppendLine(" " + GetJoinView().Get4PartsTableName() + " AS [" + GetJoinTableFillAlias() + "]"); } foreach (var col in Columns) col.EmitJoinSql(sql, withCTE, indent + " "); }
//#endregion /// <summary> /// 遍历 get/post 数据 /// </summary> /// <returns>遍历 get/post 数据</returns> public static string GetRequest() { StringBuilder sb = new StringBuilder(); sb.Append("form:"); for (int i = 0; i < HttpContext.Current.Request.Form.Count; i++) sb.Append(HttpContext.Current.Request.Form.Keys[i].ToString() + "=" + HttpContext.Current.Request.Form[i].ToString() + "&"); sb.RemoveLastChar("&"); sb.Append("querystring:"); for (int i = 0; i < HttpContext.Current.Request.QueryString.Count; i++) sb.Append(HttpContext.Current.Request.QueryString.Keys[i].ToString() + "=" + HttpContext.Current.Request.QueryString[i].ToString() + "&"); sb.RemoveLastChar("&"); return sb.ToString(); }
public string GetSqlText(List<string> withCTE) { bool cteMode = withCTE != null; //режим вложенного sql (Common Table Expression) if (!cteMode) withCTE = new List<string>(); var withCTESql = new StringBuilder(); var commentSql = new StringBuilder(); var selectSql = new StringBuilder(); var fromSql = new StringBuilder(); var joinSql = new StringBuilder(); var whereSql = new StringBuilder(); var orderbySql = new StringBuilder(); whereSql.AppendLine("WHERE"); commentSql.Append("-- запрос: " + Name + ", "); if (!cteMode) commentSql.Append("компьютер: " + System.Environment.MachineName); commentSql.AppendLine(); if (RuntimeTop != null) selectSql.AppendLine("SELECT TOP " + RuntimeTop); else selectSql.AppendLine("SELECT"); rootColumn.EmitSelectSql(selectSql, " "); if (!string.IsNullOrWhiteSpace(RuntimeIntoTableName)) { selectSql.RemoveLastChar(3); selectSql.AppendLine(); selectSql.AppendLine("INTO [" + RuntimeIntoTableName + "] "); // последний пробел не удалять! } rootColumn.EmitJoinSql(joinSql, withCTE, ""); selectSql.RemoveLastChar(3); selectSql.AppendLine(); if (!cteMode && withCTE.Count > 0) { withCTESql.AppendLine("WITH"); List<string> duplicates = new List<string>(); foreach (string cteStr in withCTE) { if (!duplicates.Contains(cteStr)) { withCTESql.AppendLine(cteStr); duplicates.Add(cteStr); } } withCTESql.RemoveLastChar(5); withCTESql.AppendLine(); } if (MasterTableRow != null) // фильтруем detail-записи { var masterColumn = GetRootNativeTable().GetMasterColumn(); if (masterColumn == null) throw new Exception("Запрос '" + Name + "': в таблице " + GetRootNativeTable().Name + " отсутствует колонка с типом '" + new MasterDataType().GetNameDisplay + "'."); whereSql.AppendLine(" [" + GetFullAlias() + "].[" + masterColumn.Name + "]=" + MasterTableRow.ID.AsSQL() + " AND"); } if (!string.IsNullOrWhiteSpace(RuntimeLookupSearchFilterString)) { var searchWords = RuntimeLookupSearchFilterString.Split(' '); foreach (var word in searchWords) { whereSql.Append("("); foreach (var col in GetAllColumns().Where(col => col is SchemaQuerySelectColumn)) { whereSql.Append("[" + col.ParentColumn.GetJoinTableFillAlias() + "]."); whereSql.Append("[" + col.Name + "]"); whereSql.Append(" LIKE '%" + word.AsLIKE() + "%'"); whereSql.Append(" OR "); } whereSql.RemoveLastChar(4); whereSql.AppendLine(") AND"); } } if (RuntimeRecordIDFilter != null) { if (RootColumn.GetJoinView().GetNativeTable() != null) whereSql.AppendLine("[" + RootColumn.GetJoinTableFillAlias() + "].[" + RootColumn.GetJoinView().GetNativeTable().GetPrimaryKeyColumn().Name + "]=" + RuntimeRecordIDFilter.AsSQL() + " AND"); else whereSql.AppendLine("[" + RootColumn.GetJoinTableFillAlias() + "].[__ID__]=" + RuntimeRecordIDFilter.AsSQL() + " AND"); } if (whereSql.Length > "WHERE\n\n".Length) whereSql.RemoveLastChar(5); // убираем 'AND' с переносом строки else whereSql.Clear(); EmitOrderBySql(orderbySql); var retSql = commentSql.ToString() + withCTESql.ToString() + selectSql.ToString() + fromSql.ToString() + joinSql.ToString() + whereSql.ToString() + orderbySql.ToString(); #if DEBUG File.WriteAllText(@"c:\$\запрос-" + ID.ToString() + "-" + Name.ToString().TranslateToCorrectFileName() + ".txt", retSql); #endif return retSql; }
public void LoadData() { CheckParams(); if (CrudMode == CrudMode.View || CrudMode == CrudMode.Edit) { var sql = new StringBuilder(); sql.AppendLine("SELECT "); foreach (var col in Table.Columns) { //if (col.Name != Table.GetPrimaryKeyColumn().Name) sql.AppendLine(" [" + col.Name + "],"); } sql.RemoveLastChar(3); sql.AppendLine(); sql.AppendLine("FROM " + Table.Get4PartsTableName()); sql.AppendLine("WHERE [" + Table.GetPrimaryKeyColumn().Name + "]=" + ID.AsSQL()); using (var db = App.Schema.SqlDB.GetDbManager()) { var dataTable = db.SetCommand(sql.ToString()).ExecuteDataTable(); if (dataTable.Rows.Count == 0) throw new Exception("Запись не найдена в таблице " + Table.Get4PartsTableName() + ", " + Table.GetPrimaryKeyColumn().Name + "='" + ID + "'"); foreach (var col in Table.Columns) { if (col.Name != Table.GetPrimaryKeyColumn().Name) Values.Add(col.Name, dataTable.Rows[0][col.Name]); } } } if (OnLoadData != null) OnLoadData(this, null); }
/// <summary> /// 分页SQL调用方法 只允许主键排序 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页显示数量</param> /// <param name="tableName">表名称</param> /// <param name="pk">主键</param> /// <param name="fieldList">字段列表</param> /// <param name="where">where条件 and or 开始</param> /// <param name="groupBy">分组条件</param> /// <param name="orderBy">排序条件</param> /// <returns>分页SQL</returns> public PagerSql GetSQL(int pageIndex, int pageSize, string tableName, string pk = "*", string fieldList = "*", string where = "", string groupBy = "", string orderBy = "") { PagerSql sql = new PagerSql(); StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.AppendFormat("count({0}) as total ", pk); if (!tableName.IsNullEmpty()) { strSql.AppendFormat("from {0} ", tableName); } if (!where.IsNullEmpty()) { strSql.AppendFormat("where {0} ", where); } if (!groupBy.IsNullEmpty()) { strSql.AppendFormat("group by {0} ", groupBy); } sql.CountSql = strSql.ToString(); //select * from Student //where Id in ( //select top 10 Id //from(select top 3200010 Id from Student order by Id)t //order by Id desc) //order by Id //SELECT * FROM ( //SELECT TOP 页面容量 * FROM ( //SELECT TOP 页面容量*当前页码 * FROM //表 WHERE 条件 ORDER BY 字段A ASC //)AS TEMPTABLE1 ORDER BY 字段A DESC //) AS TEMPTABLE2 ORDER BY 字段A ASC StringBuilder orderByExt = new StringBuilder(); foreach (string order in orderBy.Split(',')) { string order2 = order.Trim(); if (order2.EndsWith(" desc", true, null)) { orderByExt.AppendFormat("{0} {1},", order2.Left(order2.Length - 5), "asc"); } else { orderByExt.AppendFormat("{0} {1},", order2.EndsWith(" asc", true, null) ? order2.Left(order2.Length - 4) : order2, "desc"); } } orderByExt.RemoveLastChar(","); strSql.Clear(); strSql.Append("select "); //if (distinct) strSql.Append("distinct "); if (pageSize == 1) { strSql.AppendFormat("top {0} ", pageSize); strSql.AppendFormat("{0} ", fieldList); if (!tableName.IsNullEmpty()) { strSql.AppendFormat("from {0} ", tableName); } if (!where.IsNullEmpty()) { strSql.AppendFormat("where {0} ", where); } if (!groupBy.IsNullEmpty()) { strSql.AppendFormat("group by {0} ", groupBy); } if (!orderBy.IsNullEmpty()) { strSql.AppendFormat("order by {0} ", orderBy); } } else { if (!tableName.IsNullEmpty()) { strSql.AppendFormat("{1} from (select top {0} {1} from (select top {3} {1} from {2} ", pageSize, fieldList, tableName, pageSize * pageIndex); } if (!where.IsNullEmpty()) { strSql.AppendFormat("where {0} ", where); } if (!groupBy.IsNullEmpty()) { strSql.AppendFormat("group by {0} ", groupBy); } if (!orderBy.IsNullEmpty()) { strSql.AppendFormat("order by {0} ", orderBy); } strSql.AppendFormat(") as Top1 {0}) as Top2 {1} ", "order by " + orderByExt, "order by " + orderBy); } sql.DataSql = strSql.ToString(); return(sql); }
public static string GetRolesDisplayText(IList<Guid> rolesList) { if (rolesList == null || rolesList.Count == 0) return ""; var list = new List<SchemaBaseRole>(); string errorStr = ""; ; foreach (var roleID in rolesList) { if (SchemaBaseRole.Roles.ContainsKey(roleID) && SchemaBaseRole.Roles[roleID] is SchemaBaseRole) list.Add(SchemaBaseRole.Roles[roleID] as SchemaBaseRole); else errorStr += ", ?ошибка"; } var sb = new StringBuilder(); foreach (var tableRole in from role in list orderby role.Position, role.Name select role) { sb.Append(tableRole.Name + ", "); } sb.RemoveLastChar(2); sb.Append(errorStr); return sb.ToString(); }
public override void EmitJoinSql(StringBuilder sql, List<string> withCTE, string indent) { var props = Properties as OborotkaProperties; // props.BegDate = new DateTime(2014, 1, 1); // props.EndDate = new DateTime(2014, 1, 31); if (props.Registers.Count == 0) { throw new Exception("Не указан список регистров для оборотной ведомости."); return; } var cte_sql = new StringBuilder(); cte_sql.AppendLine("[__obortka_temp__]("); cte_sql.AppendLine(" [Регистр],"); var table = App.Schema.GetSampleObject<SchemaTable>(props.Registers[0]); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { //if (props.ВхСальдо) // cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__ВхСальдо],"); if (props.Дебет) cte_sql.AppendLine(" [" + registrCol.Name + "__Дебет],"); if (props.Кредит) cte_sql.AppendLine(" [" + registrCol.Name + "__Кредит],"); if (props.ИсхСальдо) cte_sql.AppendLine(" [" + registrCol.Name + "__ИсхСальдо],"); cte_sql.AppendLine(" [" + registrCol.Name + "__before_today],"); cte_sql.AppendLine(" [" + registrCol.Name + "__today],"); } } // cte_sql.AppendLine("__ID__) "); cte_sql.RemoveLastChar(3); cte_sql.AppendLine(")"); cte_sql.AppendLine("AS "); cte_sql.AppendLine("( "); int counter = 0; foreach (var registerID in props.Registers) { table = App.Schema.GetSampleObject<SchemaTable>(registerID); // первый select - остаток на сегодня (не EndDate), включает все проводки, что есть у данного регистра cte_sql.AppendLine("SELECT"); counter++; cte_sql.AppendLine(" " + table.Name.AsSQL() + " AS [Регистр],"); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "] AS [" + registrCol.Name + "],"); } foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { //if (props.ВхСальдо) // cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__ВхСальдо],"); if (props.Дебет) cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__Дебет],"); if (props.Кредит) cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__Кредит],"); if (props.ИсхСальдо) cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__ИсхСальдо],"); cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__before_today],"); cte_sql.AppendLine(" SUM([" + registrCol.Name + "]) AS [" + registrCol.Name + "__today],"); } } cte_sql.RemoveLastChar(3); cte_sql.AppendLine(); cte_sql.AppendLine("FROM " + table.Get4PartsTableName()); cte_sql.AppendLine("WHERE [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "] IN ('Д','К')"); cte_sql.AppendLine("GROUP BY "); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } cte_sql.RemoveLastChar(3); cte_sql.AppendLine(); } foreach (var registerID in props.Registers) { table = App.Schema.GetSampleObject<SchemaTable>(registerID); if (counter != 0) cte_sql.AppendLine("UNION ALL"); else counter++; cte_sql.AppendLine("SELECT"); cte_sql.AppendLine(" " + table.Name.AsSQL() + " AS [Регистр],"); // измерения foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { //if (props.ВхСальдо) // cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__ВхСальдо],"); if (props.Дебет) cte_sql.AppendLine(" CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "]='Д' AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] BETWEEN " + props.BegDate.AsSQL() + " AND " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END AS [" + registrCol.Name + "__Дебет],"); if (props.Кредит) cte_sql.AppendLine(" CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "]='К' AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] BETWEEN " + props.BegDate.AsSQL() + " AND " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END AS [" + registrCol.Name + "__Кредит],"); if (props.ИсхСальдо) cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__ИсхСальдо],"); cte_sql.AppendLine(" CASE WHEN [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] > " + props.EndDate.AsSQL() + " THEN [" + registrCol.Name + "] ELSE 0 END AS [" + registrCol.Name + "__before_today],"); cte_sql.AppendLine(" 0 AS [" + registrCol.Name + "__today],"); // cte_sql.AppendLine(" " + table.Get4PartsTableName() + ".[" + registrCol.Name + "],"); } } cte_sql.RemoveLastChar(3); cte_sql.AppendLine(); cte_sql.AppendLine("FROM "+table.Get4PartsTableName()); cte_sql.AppendLine("WHERE [" + table.GetColumnByRole(RoleConst.Регистр_ДбКр).Name + "] IN ('Д','К') AND [" + table.GetColumnByRole(RoleConst.Регистр_Дата).Name + "] >= " + props.BegDate.AsSQL()); // cte_sql.AppendLine("WHERE [" + table.GetPrimaryKeyColumn().Name + "]=" + recordID.AsSQL()); } cte_sql.AppendLine("),"); // группировка cte_sql.AppendLine("[$Оборотка]("); table = App.Schema.GetSampleObject<SchemaTable>(props.Registers[0]); cte_sql.AppendLine(" [Регистр],"); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { if (props.ВхСальдо) cte_sql.AppendLine(" [" + registrCol.Name + " ВхСальдо],"); if (props.Дебет) cte_sql.AppendLine(" [" + registrCol.Name + " Дебет],"); if (props.Кредит) cte_sql.AppendLine(" [" + registrCol.Name + " Кредит],"); if (props.ИсхСальдо) cte_sql.AppendLine(" [" + registrCol.Name + " ИсхСальдо],"); } } cte_sql.AppendLine(" __ID__"); cte_sql.AppendLine(") "); cte_sql.AppendLine("AS"); cte_sql.AppendLine("("); cte_sql.AppendLine("SELECT"); cte_sql.AppendLine(" [Регистр],"); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { if (props.ВхСальдо) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]-[" + registrCol.Name + "__Дебет]-[" + registrCol.Name + "__Кредит]) AS [" + registrCol.Name + ": ВхСальдо],"); if (props.Дебет) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__Дебет]) AS [" + registrCol.Name + ": Дебет],"); if (props.Кредит) cte_sql.AppendLine(" -SUM([" + registrCol.Name + "__Кредит]) AS [" + registrCol.Name + ": Кредит],"); if (props.ИсхСальдо) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]) AS [" + registrCol.Name + ": ИсхСальдо],"); } } cte_sql.AppendLine(" NEWID() AS __ID__"); //cte_sql.RemoveLastChar(3); //cte_sql.AppendLine(); cte_sql.AppendLine("FROM [__obortka_temp__]"); cte_sql.AppendLine("GROUP BY"); cte_sql.AppendLine(" [Регистр],"); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Измерение)) cte_sql.AppendLine(" [" + registrCol.Name + "],"); } cte_sql.RemoveLastChar(3); cte_sql.AppendLine(); cte_sql.AppendLine("HAVING"); foreach (var registrCol in table.Columns) { if (registrCol.ColumnRoles.Contains(RoleConst.Регистр_Мера)) { if (props.ВхСальдо) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today]-[" + registrCol.Name + "__Дебет]+[" + registrCol.Name + "__Кредит])<>0 OR"); if (props.Дебет) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__Дебет])<>0 OR"); if (props.Кредит) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__Кредит])<>0 OR"); if (props.ИсхСальдо) cte_sql.AppendLine(" SUM([" + registrCol.Name + "__today]-[" + registrCol.Name + "__before_today])<>0 OR"); } } cte_sql.RemoveLastChar(5); cte_sql.AppendLine("),"); withCTE.Add(cte_sql.ToString()); sql.AppendLine("FROM [$Оборотка]"); }