/// <summary> /// 将用于查询的拉曼达表达式转换为SQL查询片段 /// </summary> /// <param name="expr"></param> /// <param name="dbTranslator"></param> /// <param name="dpc"></param> /// <returns></returns> public string ToSQL(Expression expr, IDbTranslator dbTranslator, DataParameterCollection dpc) { if (dpc == null) { dpc = new DataParameterCollection(); } StringBuilder sBuilder = new StringBuilder(); if (expr is LambdaExpression) { LambdaExpression lamb = expr as LambdaExpression; this.parse(lamb.Body, dbTranslator, sBuilder, dpc); } else { this.parse(expr, dbTranslator, sBuilder, dpc); } return(sBuilder.ToString()); }
public override SqlStatement GetPagedSelectSqlStatement(SelectStatementBuilder ssb, List <string> queryRequiredFields) { if (ssb.Order == null || ssb.Keys.Count == 0) { throw PagedMustHaveOrder; } const string posName = "__rownumber__"; var dpc = new DataParameterCollection(); string sqlString = string.Format( "SELECT {7} FROM (SELECT {0}, ROW_NUMBER() OVER ({3}) AS {6} FROM {1} {2}) AS T WHERE T.{6} >= {4} AND T.{6} <= {5} ORDER BY T.{6}", ssb.GetColumns(this), ssb.From.ToSqlText(dpc, this), ssb.Where.ToSqlText(dpc, this, queryRequiredFields), ssb.Order.ToSqlText(dpc, this), ssb.Range.StartIndex, ssb.Range.EndIndex, posName, ssb.GetColumns(this, false, true) ); return(new TimeConsumingSqlStatement(CommandType.Text, sqlString, dpc)); }
public override string ToSqlText(DataParameterCollection dpc, DbDialect dd, List <string> queryRequiredFields) { if (queryRequiredFields != null && dpc.FindQueryRequiedFieldOrId == false) { if (KV.Key == "Id" || queryRequiredFields.Contains(KV.Key)) { dpc.FindQueryRequiedFieldOrId = true; } } string dpStr = GetValueString(dpc, dd, KV); string dkStr = dd.QuoteForColumnName(KV.Key); switch (function) { case ColumnFunction.ToLower: dkStr = string.Format("LOWER({0})", dkStr); break; case ColumnFunction.ToUpper: dkStr = string.Format("UPPER({0})", dkStr); break; } return(string.Format("{0} {2} {1}", dkStr, dpStr, Comp)); }
public void CanPassDataTable_AsTableValuedParameter() { var reader = new Mock <IDataReader>(); reader.SetupGet(r => r.FieldCount).Returns(0); reader.Setup(r => r.Read()).Returns(false); var parms = new DataParameterCollection(); var cmd = new Mock <IDbCommand>(); cmd.SetupAllProperties(); cmd.Setup(c => c.ExecuteReader()).Returns(reader.Object); cmd.SetupGet(c => c.Parameters).Returns(parms); cmd.Setup(c => c.CreateParameter()).Returns(new SqlParameter()); var ctx = new Mock <IDbConnection>(); ctx.Setup(c => c.CreateCommand()).Returns(cmd.Object); var dt = new DataTable(); dt.TableName = "[dbo].[Person]"; dt.Columns.Add("FirstName"); dt.Rows.Add("Foo"); dt.Rows.Add("Bar"); dynamic toTest = new DynamicStoredProcedure(ctx.Object, transformers, CancellationToken.None, TEST_TIMEOUT, DynamicExecutionMode.Synchronous, true); toTest.usp_AddPeople(people: dt); var p = parms.OfType <SqlParameter>().Single(); p.ParameterName.Should().Be("people", "because that was the argument name"); p.SqlDbType.Should().Be(SqlDbType.Structured, "because it is a table-valued parameter"); p.TypeName.Should().Be("[dbo].[Person]", "because that is the name of the TableName set on the DataTable being passed as a TVP"); }
public abstract string ToSqlText(DataParameterCollection dpc, DbDialect dd, List <string> queryRequiredFields);
public MockCommand(MockDb db) { _db = db; Parameters = new DataParameterCollection(); }
public void Init(System.Collections.Specialized.NameValueCollection Tags) { for (int i = 0; i < Tags.Count; i++) { switch (Tags.Keys[i]) { case "Connection": _dataConnection = Tags[i]; break; case "Command": _command = Tags[i]; break; case "Procedure": _procedureName = Tags[i]; break; case "Parameter": _parameter = Tags[i]; break; case "DefaultParameter": _defaultParameter = Tags[i]; break; } } if (_dataParameterCollection == null) _dataParameterCollection = new DataParameterCollection(); }
/// <summary> /// 执行翻译工作 /// </summary> /// <param name="expr"></param> /// <param name="dbTranslator"></param> /// <param name="sBuilder"></param> /// <param name="dpc"></param> private void parse(Expression expr, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { if (expr is BinaryExpression) { //翻译二元运算符表达式 this.parseBinaryExpression((BinaryExpression)expr, dbTranslator, sBuilder, dpc); } else if (expr is UnaryExpression) { //翻译一元运算符的表达式 this.parseUnaryExpression((UnaryExpression)expr, dbTranslator, sBuilder, dpc); } else if (expr is MethodCallExpression) { //翻译对静态方法或实例方法的调用 this.parseMethodCallExpression((MethodCallExpression)expr, dbTranslator, sBuilder, dpc); } else if (expr is System.Linq.Expressions.ConstantExpression) { System.Linq.Expressions.ConstantExpression cExp = expr as System.Linq.Expressions.ConstantExpression; bool cExpValue = false; if (cExp.Value != null) { if (!bool.TryParse(cExp.Value.ToString(), out cExpValue)) { cExpValue = true; } } if (cExpValue) { sBuilder.Append("(1=1)"); } else { sBuilder.Append("(1<>1)"); } } else { if (isBooleanFieldOrProperty(expr)) { IPropertyMapper propertyMapper = this.getPropertyMapper(((MemberExpression)expr).Member.Name); //生成SQL片段和参数 this.buildSqlAndDataParameter(propertyMapper, ColumnFunction.None, CompareOpration.Equal, true, dbTranslator, sBuilder, dpc); } else { throw new MapleException("该操作不支持!" + expr.Type.FullName); } } }
/// <summary> /// 生成SQL片段和参数 /// </summary> /// <param name="propertyMapper"></param> /// <param name="function"></param> /// <param name="co"></param> /// <param name="value"></param> /// <param name="dbTranslator"></param> /// <param name="sBuilder"></param> /// <param name="dpc"></param> private void buildSqlAndDataParameter(IPropertyMapper propertyMapper, ColumnFunction function, CompareOpration co, object value, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { //获取参数名称 string strKey = dbTranslator.Quote(string.Format("{0}_{1}", propertyMapper.ColumnName, dpc.Count)); //拼接SQL 1 switch (function) { //处理函数 case ColumnFunction.ToLower: sBuilder.Append(string.Format("LOWER({0})", propertyMapper.ColumnName)); break; case ColumnFunction.ToUpper: sBuilder.Append(string.Format("UPPER({0})", propertyMapper.ColumnName)); break; default: sBuilder.Append(propertyMapper.ColumnName); break; } //拼接SQL 2 if (value == null) { if (co == CompareOpration.Equal) { sBuilder.Append(" IS NULL "); } else if (co == CompareOpration.NotEqual) { sBuilder.Append(" IS NOT NULL "); } else { throw new MapleException("该操作不支持!NULL value only supported Equal and NotEqual!"); } } else { switch (co) { case CompareOpration.Equal: sBuilder.Append(" = "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; case CompareOpration.GreatOrEqual: sBuilder.Append(" >= "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; case CompareOpration.GreatThan: sBuilder.Append(" > "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; case CompareOpration.LessOrEqual: sBuilder.Append(" <= "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; case CompareOpration.LessThan: sBuilder.Append(" < "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; case CompareOpration.Like: sBuilder.Append(" LIKE "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); value = string.Format("%{0}%", value); break; case CompareOpration.StartsWith: sBuilder.Append(" LIKE "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); value = string.Format("{0}%", value); break; case CompareOpration.EndsWith: sBuilder.Append(" LIKE "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); value = string.Format("%{0}", value); break; case CompareOpration.NotEqual: sBuilder.Append(" <> "); sBuilder.Append(dbTranslator.QuoteParameter(strKey)); break; default: throw new MapleException("该操作不支持!CompareOpration = " + co); } //添加查询参数 dpc.Add(new DataParameter(strKey, value, propertyMapper.DbType, propertyMapper.Size, ParameterDirection.Input)); } }
private void parseClause(BinaryExpression e, CompareOpration co, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { ColumnFunction function; MemberExpression left; //获得左侧的属性字段信息 IPropertyMapper propertyMapper = this.getPropertyMapper(e.Left, out function, out left); //if (e.Right.NodeType == ExpressionType.MemberAccess) //{ // var right = (MemberExpression)e.Right; // if (right.Expression != null && right.Expression.ToString() == left.Expression.ToString()) // throw new MapleException("该操作不支持!Right.NodeType == ExpressionType.MemberAccess"); //} //获取右侧的值 object value = this.getValue(e.Right); //生成SQL片段和参数 this.buildSqlAndDataParameter(propertyMapper, function, co, value, dbTranslator, sBuilder, dpc); }
private static int DuplicateShow(UpdateQueryBatch batch, int curShowObjid, int parentElmObjid, int parentShowObjid, int objidBase, int hgbstShowTableID) { var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT * FROM table_hgbst_show WHERE objid = {0}" }; sqlHelper.Parameters.Add("curShowObjid", curShowObjid); var newObjid = GetNextObjid(batch, hgbstShowTableID, objidBase); var showSet = sqlHelper.ExecuteDataSet(); if (showSet == null || showSet.Tables.Count != 1 || showSet.Tables[0].Rows.Count != 1) { throw new ApplicationException("Show with objid " + curShowObjid + " not found, or more than one row with that objid exists"); } var showRow = showSet.Tables[0].Rows[0]; var cmdParams = new DataParameterCollection { {batch.GetUniqueParamName("objid"), newObjid}, {batch.GetUniqueParamName("title"), showRow["title"]}, {batch.GetUniqueParamName("def_val"), showRow["def_val"]}, {batch.GetUniqueParamName("chld_prnt2hgbst_show"), parentShowObjid} }; batch.AddStatement(String.Format( @" INSERT INTO table_hgbst_show (objid, last_mod_time, title, def_val, dev, chld_prnt2hgbst_show) VALUES ({{0}}, {0}, {{1}}, {{2}}, NULL, {{3}})", batch.Provider.GetDateStatement()), cmdParams); // Now relate it to the new parent cmdParams = new DataParameterCollection { {batch.GetUniqueParamName("parentElmObjid"), parentElmObjid}, {batch.GetUniqueParamName("newShowObjid"), newObjid} }; batch.AddStatement(@"INSERT INTO mtm_hgbst_elm0_hgbst_show1 (hgbst_elm2hgbst_show, hgbst_show2hgbst_elm) VALUES ({0}, {1})", cmdParams); return newObjid; }
/// <summary> /// 获取IDbCommand 接口 /// </summary> /// <param name="commandType"></param> /// <param name="commandText"></param> /// <param name="dps"></param> /// <param name="sqlTimeOut"></param> /// <param name="needLog"></param> /// <returns></returns> public virtual IDbCommand GetDbCommand(CommandType commandType, string commandText, DataParameterCollection dps, int sqlTimeOut = 10, bool needLog = false) { if (this._disposed) { throw new MapleException("IDatabaseContext is Disposed!"); } IDbCommand command = this.DbTranslator.GetDbProviderFactory().CreateCommand(); command.CommandType = commandType; command.CommandText = commandText; command.Connection = this._dbConnection; if (this.IsInTransaction) { command.Transaction = this.Transaction; } if (needLog) { Console.WriteLine(commandText); } //设置超时时间 this.setCommandTimeOut(command, sqlTimeOut); //设置参数 this.fillDbParameters(command, dps); return(command); }
private void parseInCall(Expression eProperty, Expression eList, bool notIn, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { ColumnFunction function; MemberExpression member; IPropertyMapper propertyMapper = this.getPropertyMapper(eProperty, out function, out member); var list = this.getValue(eList); if (list is IEnumerable) { sBuilder.Append(propertyMapper.ColumnName); if (notIn) { sBuilder.Append(" NOT"); } sBuilder.Append(" IN ("); IEnumerable items = (IEnumerable)list; string prefix = ""; bool? isVarChar = null; bool? isEumn = null; foreach (var obj in (IEnumerable)items) { if (prefix.Length == 0) { prefix = ","; } else { sBuilder.Append(prefix); } if (!isVarChar.HasValue || !isEumn.HasValue) { Type type = obj.GetType(); isEumn = type.IsEnum; isVarChar = !isEumn.Value && !type.IsNumeric(); } if (isVarChar.Value) { sBuilder.Append("'"); } if (isEumn.Value) { sBuilder.Append((int)obj); } else { sBuilder.Append(obj); } if (isVarChar.Value) { sBuilder.Append("'"); } } sBuilder.Append(")"); } else { CompareOpration co = notIn ? CompareOpration.NotEqual : CompareOpration.Equal; this.buildSqlAndDataParameter(propertyMapper, ColumnFunction.None, co, list, dbTranslator, sBuilder, dpc); } }
private void parseNull(MethodCallExpression e, bool isNull, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { IPropertyMapper propertyMapper = this.getPropertyMapper(e.Arguments[0], out _, out _); CompareOpration co = isNull ? CompareOpration.Equal : CompareOpration.NotEqual; this.buildSqlAndDataParameter(propertyMapper, ColumnFunction.None, co, null, dbTranslator, sBuilder, dpc); }
/// <summary> /// 翻译对静态方法或实例方法的调用 /// </summary> /// <param name="e"></param> /// <param name="dbTranslator"></param> /// <param name="sBuilder"></param> /// <param name="dpc"></param> private void parseMethodCallExpression(MethodCallExpression e, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { switch (e.Method.Name) { case "StartsWith": this.parseLikeCall(e, CompareOpration.StartsWith, dbTranslator, sBuilder, dpc); break; case "EndsWith": this.parseLikeCall(e, CompareOpration.EndsWith, dbTranslator, sBuilder, dpc); break; case "Contains": if (e.Object == null) { this.parseInCall(e.Arguments[1], e.Arguments[0], false, dbTranslator, sBuilder, dpc); } else { this.parseLikeCall(e, CompareOpration.Like, dbTranslator, sBuilder, dpc); } //e.Method.ReflectedType break; case "In": case "InStatement": this.parseInCall(e.Arguments[0], e.Arguments[1], false, dbTranslator, sBuilder, dpc); break; case "NotIn": case "NotInStatement": this.parseInCall(e.Arguments[0], e.Arguments[1], true, dbTranslator, sBuilder, dpc); break; case "IsNull": this.parseNull(e, true, dbTranslator, sBuilder, dpc); break; case "IsNotNull": this.parseNull(e, false, dbTranslator, sBuilder, dpc); break; default: throw new MapleException("无法处理的函数:" + e.Method.Name); } }
/// <summary> /// 翻译二元运算符表达式 /// </summary> /// <param name="e"></param> /// <param name="dbTranslator"></param> /// <param name="sBuilder"></param> /// <param name="dpc"></param> private void parseBinaryExpression(BinaryExpression e, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { switch (e.NodeType) { case ExpressionType.Equal: this.parseClause(e, CompareOpration.Equal, dbTranslator, sBuilder, dpc); break; case ExpressionType.GreaterThan: this.parseClause(e, CompareOpration.GreatThan, dbTranslator, sBuilder, dpc); break; case ExpressionType.GreaterThanOrEqual: this.parseClause(e, CompareOpration.GreatOrEqual, dbTranslator, sBuilder, dpc); break; case ExpressionType.LessThan: this.parseClause(e, CompareOpration.LessThan, dbTranslator, sBuilder, dpc); break; case ExpressionType.LessThanOrEqual: this.parseClause(e, CompareOpration.LessOrEqual, dbTranslator, sBuilder, dpc); break; case ExpressionType.NotEqual: this.parseClause(e, CompareOpration.NotEqual, dbTranslator, sBuilder, dpc); break; case ExpressionType.AndAlso: { sBuilder.Append("("); this.parse(e.Left, dbTranslator, sBuilder, dpc); sBuilder.Append(") AND ("); this.parse(e.Right, dbTranslator, sBuilder, dpc); sBuilder.Append(")"); break; } case ExpressionType.OrElse: { sBuilder.Append("("); this.parse(e.Left, dbTranslator, sBuilder, dpc); sBuilder.Append(") OR ("); this.parse(e.Right, dbTranslator, sBuilder, dpc); sBuilder.Append(")"); break; } default: throw new MapleException("该操作不支持!" + e.NodeType); } }
/// <summary> /// 翻译一元运算符的表达式 /// </summary> /// <param name="expr"></param> /// <param name="dbTranslator"></param> /// <param name="sBuilder"></param> /// <param name="dpc"></param> private void parseUnaryExpression(UnaryExpression expr, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { switch (expr.NodeType) { case ExpressionType.Not: { sBuilder.Append("( NOT ("); this.parse(expr.Operand, dbTranslator, sBuilder, dpc); sBuilder.Append(") )"); break; } default: throw new MapleException("该操作不支持!" + expr.Type.FullName); } }
private static void CopyAndReparentElement(UpdateQueryBatch batch, int elmObjid, int childShowObjid, int newParentShowObjid) { log.LogDebug("CopyAndReparentElement called. Elmenet: {0}, Child Show: {1}, New Parent Show: {2}", elmObjid, childShowObjid, newParentShowObjid); // Get table ID's for hgbst_elm and hgbst_show var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT type_id, type_name FROM adp_tbl_name_map WHERE type_name = 'hgbst_elm' OR type_name = 'hgbst_show'" }; var hgbstElmTableID = 0; var hgbstShowTableID = 0; using (var dataReader = sqlHelper.ExecuteReader()) { while (dataReader.Read()) { if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_elm", true) == 0) { hgbstElmTableID = Convert.ToInt32(dataReader["type_id"]); } else if (String.Compare(Convert.ToString(dataReader["type_name"]), "hgbst_show", true) == 0) { hgbstShowTableID = Convert.ToInt32(dataReader["type_id"]); } } } sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = "SELECT site_id FROM adp_db_header" }; var siteId = Convert.ToInt32(sqlHelper.ExecuteScalar()); var objidBase = siteId*Convert.ToInt32(Math.Pow(2, 28)); //this.objidBase = 0; // Duplicate the element var dupeElmObjid = DuplicateElement(batch, elmObjid, newParentShowObjid, objidBase, hgbstElmTableID); log.LogDebug("Duplicated element. New Objid: {0}", dupeElmObjid); // Duplicate the child show and link it to the new element var dupeChildShowObjid = DuplicateShow(batch, childShowObjid, dupeElmObjid, newParentShowObjid, objidBase, hgbstShowTableID); log.LogDebug("Duplicated child show. New Objid: {0}", dupeChildShowObjid); // Get the whole child hierarchy for this element starting from its childShowObjid and duplicate it log.LogDebug("Duplicating child hierarchy..."); DuplicateChildHierarchy(batch, elmObjid, childShowObjid, dupeChildShowObjid, objidBase, hgbstElmTableID, hgbstShowTableID); // Unlink the old elm from the new parent show (if it was linked - a la the Best Buy double-linked problem) log.LogDebug("Unlinking original element (if necessary) from new parent to complete the separation"); var commandParameters = new DataParameterCollection {{"elmObjid", elmObjid}, {"newParent", newParentShowObjid}}; batch.AddStatement(@"DELETE FROM mtm_hgbst_elm0_hgbst_show1 WHERE hgbst_elm2hgbst_show = {0} and hgbst_show2hgbst_elm = {1}", commandParameters); }
private void parseLikeCall(MethodCallExpression e, CompareOpration co, IDbTranslator dbTranslator, StringBuilder sBuilder, DataParameterCollection dpc) { ColumnFunction function; MemberExpression member; if (e.Arguments.Count == 1) { IPropertyMapper propertyMapper = this.getPropertyMapper(e.Object, out function, out member); object value = this.getValue(e.Arguments[0]); if (value != null && value is string) { this.buildSqlAndDataParameter(propertyMapper, function, co, value, dbTranslator, sBuilder, dpc); } else { throw new MapleException("'Like' clause only supported one Parameter and the Parameter should be string and not allow NULL.!"); } } //else if (e.Arguments.Count == 2) //{ // throw new MapleException("'Like' clause only supported one Parameter and the Parameter should be string and not allow NULL.!"); //} else { throw new MapleException("'Like' clause only supported one Parameter and the Parameter should be string and not allow NULL.!"); } }
private static int DuplicateElement(UpdateQueryBatch batch, int curElmObjid, int newParentShowObjid, int objidBase, int hgbstElmTableID) { var sqlHelper = new SqlHelper(batch.Provider) { Transaction = batch.Transaction, CommandText = @"SELECT * FROM table_hgbst_elm WHERE objid = {0}" }; sqlHelper.Parameters.Add("curElmObjid", curElmObjid); var newObjid = GetNextObjid(batch, hgbstElmTableID, objidBase); var elmSet = sqlHelper.ExecuteDataSet(); if (elmSet == null || elmSet.Tables.Count != 1 || elmSet.Tables[0].Rows.Count != 1) { throw new ApplicationException("Element with objid " + curElmObjid + " not found, or more than one row with that objid exists"); } var elementRow = elmSet.Tables[0].Rows[0]; var commandParameters = new DataParameterCollection { {batch.GetUniqueParamName("objid"), newObjid}, {batch.GetUniqueParamName("title"), elementRow["title"]}, {batch.GetUniqueParamName("rank"), elementRow["rank"]}, {batch.GetUniqueParamName("state"), elementRow["state"]}, {batch.GetUniqueParamName("intval1"), elementRow["intval1"]} }; batch.AddStatement(@"INSERT INTO table_hgbst_elm (objid, title, rank, state, dev, intval1) VALUES ({0}, {1}, {2}, {3}, NULL, {4})", commandParameters); // Now relate it to the new parent commandParameters = new DataParameterCollection { {batch.GetUniqueParamName("newObjid"), newObjid}, {batch.GetUniqueParamName("newParentShowObjid"), newParentShowObjid} }; batch.AddStatement( @"INSERT INTO mtm_hgbst_elm0_hgbst_show1 (hgbst_elm2hgbst_show, hgbst_show2hgbst_elm) VALUES ({0}, {1})", commandParameters); return newObjid; }