コード例 #1
0
ファイル: CondIn.cs プロジェクト: fkdl/OpenLIS
        public CondIn(string field, ICollection <object> valueList)
        {
            // Any of the following situations makes an empty SQL:

            if (string.IsNullOrEmpty(field) || // 1) field not specified
                valueList == null ||           // 2) value list is null
                valueList.Count < 1 ||         // 3) no elements in value list
                valueList.All(m => m == null)) // 4) all elements in value list are null
            {
                return;
            }

            // Combine all elements into IN clause
            var sbSql = new StringBuilder();

            foreach (var v in valueList.Where(m => m != null))
            {
                // append SQL
                if (sbSql.Length > 0)
                {
                    sbSql.Append(", ");                   // seperator
                }
                var pName = "@p" + StaticCounter.Next;
                sbSql.Append(pName);

                // push parameters
                DbParams.Add(new TDbParam {
                    ParameterName = pName, Value = ConvertDbValue(v)
                });
            }

            Sql = $"{field} IN ({sbSql})";
        }
コード例 #2
0
        public CondGroup <TDbParam> Add(Cond <TDbParam> cond)
        {
            // Empty cond makes an empty SQL.
            var cSql = (cond == null ? string.Empty : cond.FetchSql());

            if (string.IsNullOrEmpty(cSql))
            {
                return(this);
            }

            // append SQL
            if (string.IsNullOrEmpty(Sql))
            {
                Sql = $"({cSql})";
            }
            else
            {
                Sql += $" {CondJoin} ({cSql})";
            }

            // push parameters
            Debug.Assert(cond != null, ""); // Never asserts
            DbParams.AddRange(cond.FetchDbParams());

            return(this);
        }
コード例 #3
0
 /// <summary>
 /// Change parameters.
 /// </summary>
 /// <param name="values">New values.</param>
 /// <returns>BuildingSql after change.</returns>
 public BuildedSql ChangeParams(Dictionary <string, object> values)
 => new BuildedSql(Text, DbParams.ToDictionary(e => e.Key, e =>
 {
     object val;
     return(values.TryGetValue(e.Key, out val) ?
            e.Value.ChangeValue(val) : e.Value);
 }));
コード例 #4
0
ファイル: CondBtwn.cs プロジェクト: fkdl/OpenLIS
        public CondBtwn(string field, object from, object to, string paramNameFrom = "", string paramNameTo = "")
        {
            // Empty field makes an empty SQL.
            if (string.IsNullOrEmpty(field))
            {
                return;
            }

            // Null "from" or "to" makes an empty SQL.
            var fromStr = (from == null ? string.Empty : ConvertDbValue(from));
            var toStr   = (to == null ? string.Empty : ConvertDbValue(to));

            if (string.IsNullOrEmpty(fromStr) || string.IsNullOrEmpty(toStr))
            {
                return;
            }

            // get parameter names
            var pNameFrom = string.IsNullOrEmpty(paramNameFrom)
                ? "@p" + StaticCounter.Next
                : paramNameFrom;

            var pNameTo = string.IsNullOrEmpty(paramNameTo)
                ? "@p" + StaticCounter.Next
                : paramNameTo;

            // construct SQL and push parameters
            Sql = $"{field} BETWEEN {pNameFrom} AND {pNameTo}";
            DbParams.Add(new TDbParam {
                ParameterName = pNameFrom, Value = fromStr
            });
            DbParams.Add(new TDbParam {
                ParameterName = pNameTo, Value = toStr
            });
        }
コード例 #5
0
        public IQueryable <Order> GetOrders(DbParams dbParams)
        {
            var query = @"SELECT order_id
                              ,o.customer_id
                              ,order_status
                              ,order_date
                              ,required_date
                              ,shipped_date
                              ,store_id
                              ,staff_id
	                          ,first_name
                              ,last_name
                              ,phone
                              ,email
                              ,street
                              ,city
                              ,state
                              ,zip_code
                          FROM orders o
                          inner join customers c on c.customer_id=o.customer_id";
            //var orders = _db.GetQueryableResult<Order>(query, out _, filter: dbParams.DbFilter, top: dbParams.Top);
            var orders = _db.GetQueryableResult <Order, Customer>(query, (x, y) =>
            {
                x.Customer = y;
                return(x);
            }, "first_name", out _, filter: dbParams.Filter, top: dbParams.Top, skip: dbParams.Skip, take: dbParams.Take, orderBy: dbParams.OrderBy);

            return(orders);
        }
コード例 #6
0
        public T update(T model)
        {
            if (!policy.checkUpdate(model.ID))
            {
                throw new UnauthorizedException("You do not have permission to update this resource.");
            }

            // adding update restriciton
            var searchParams = new DbParams(new[] {
                new DbParam("id", model.ID, this.transformer.getParamType("id")),
            });

            // adding timestamps
            this.beforeSave(model);
            var valueParams = this.transformer.getDbParams(model);

            valueParams.remove("created_at");
            valueParams.add(new DbParam("updated_at", DateTime.Now.ToString(CultureInfo.InvariantCulture), SqlDbType.DateTime));

            // updating
            DB.update(this.tableName, this.transformer.getDbParams(model), searchParams);
            var saved = this.one(model.ID);

            this.afterSave(model, saved);

            return(saved);
        }
コード例 #7
0
        public async Task <IQueryReader> ExecuteAsync(string connectionName, string procedureName, IEnumerable <IDbDataParameter> parameters)
        {
            var connection = _queryConnectionProvider.GetConnection(connectionName);
            var paramsList = new DbParams();

            if (parameters != null)
            {
                foreach (var p in parameters)
                {
                    paramsList.Add(p);
                }
            }

            SqlMapper.GridReader reader;

            try
            {
                connection.Open();
                reader = await connection.QueryMultipleAsync(procedureName, paramsList, commandType : CommandType.StoredProcedure);
            }
            catch
            {
                connection.Close();
                throw;
            }
            return(new QueryReader(reader, connection));
        }
コード例 #8
0
        public List <T> all(List <QueryParam> queryParams, bool withIncludes = true)
        {
            if (!policy.checkList(typeof(T)))
            {
                throw new UnauthorizedException("You do not have permission to list requested resources.");
            }

            // transform queryParams to DbParams
            var dbParams = new DbParams();

            foreach (var param in queryParams)
            {
                dbParams.add(new DbParam(param.Name, param.Value, this.transformer.getParamType(param.Name), param.Operator, param.BooleanOpr));
            }

            // read data
            var reader = DB.all(this.tableName, manipulateParams(dbParams));
            var result = new List <T>();

            while (reader.Read())
            {
                var transformed = this.transformer.transform(reader);
                transformed = withIncludes ? this.addIncludes(transformed) : transformed;
                result.Add(transformed);
            }

            reader.Close();
            return(result);
        }
コード例 #9
0
 public FluentQuery(string tableName, Transformer <T> transformer, BaseRepository <T> repository)
 {
     this.tableName   = tableName;
     this.transformer = transformer;
     this.repository  = repository;
     this.query       = new DbParams();
     this.includes    = false;
 }
コード例 #10
0
        public SqlCommand process(string tableName, DbParams searchParams)
        {
            var query = constructBaseQuery(tableName);

            lastQuery = query + constructSearchParams(searchParams);

            return(constructCommand(lastQuery, searchParams));
        }
コード例 #11
0
 public void Clear()
 {
     SelectFields.Clear();
     Sql.Clear();
     DbParams.Clear();
     _dicTableName.Clear();
     _queueEnglishWords = new Queue <string>(SListEnglishWords);
 }
コード例 #12
0
        public SqlCommand process(string tableName, DbParams valueParams)
        {
            var query       = constructBaseQuery(tableName) + constructParamNames(valueParams);
            var queryValues = constructParamValues(valueParams);

            lastQuery = query + " OUTPUT INSERTED.ID VALUES " + queryValues;

            return(constructCommand(lastQuery, valueParams));
        }
コード例 #13
0
ファイル: DB.cs プロジェクト: aleksa-sukovic/data-bunch
        public static void delete(string tableName, DbParams searchParams)
        {
            var queryProcessor = new DeleteQueryProcessor();
            var command        = queryProcessor.process(tableName, searchParams);

            Log.info("Deleting with query: " + queryProcessor.getLastQuery());
            command.ExecuteNonQuery();
            Log.success("Successfully executed query.");
        }
コード例 #14
0
ファイル: DB.cs プロジェクト: aleksa-sukovic/data-bunch
        public static int save(string tableName, DbParams valueParams, DbParams searchParams = null)
        {
            if (searchParams == null)
            {
                return(create(tableName, valueParams));
            }

            return(update(tableName, valueParams, searchParams));
        }
コード例 #15
0
        public void CanScalar()
        {
            var expected = _testDb.GenerateRandomString();
            var sql      = "SELECT @description";
            var param    = new DbParams("description", expected);
            var result   = _db.Scalar(sql, param);

            Assert.Equal(expected, Convert.ToString(result) ?? "");
        }
コード例 #16
0
        public void CanQuerySingle()
        {
            var expected = _testDb.GenerateRandomString();
            var sql      = "SELECT @description AS description";
            var param    = new DbParams("description", expected);
            var result   = _db.QuerySingle(sql, param, rd => rd.GetString("description"));

            Assert.Equal(expected, result);
        }
コード例 #17
0
        public void CanExec()
        {
            var descripton = _testDb.GenerateRandomString();
            var sql        = "INSERT INTO test_values (description) VALUES (@description);";
            var param      = new DbParams("description", descripton);

            var rowsAffected = _db.Exec(sql, param);

            Assert.Equal(1, rowsAffected);
        }
コード例 #18
0
ファイル: DB.cs プロジェクト: aleksa-sukovic/data-bunch
        public static int update(string tableName, DbParams valueParams, DbParams searchParams)
        {
            var queryProcessor = new UpdateQueryProcessor();
            var command        = queryProcessor.process(tableName, valueParams, searchParams);

            Log.info("Updating with query: " + queryProcessor.getLastQuery());
            command.ExecuteNonQuery();
            Log.success("Successfully executed query.");

            return(0);
        }
コード例 #19
0
        private string constructParamValues(DbParams valueParams)
        {
            var query = "(";

            foreach (var pair in valueParams.get())
            {
                query += "@" + pair.ID + ", ";
            }

            return(query.Substring(0, query.Length - 2) + ")");
        }
コード例 #20
0
        public void ShouldAddParams()
        {
            var p = new DbParams
            {
                { "key", 1 }
            };

            p.Add("key2", 2);
            Assert.Equal(1, p["key"]);
            Assert.Equal(2, p["key2"]);
        }
コード例 #21
0
ファイル: DbBatchAsyncTests.cs プロジェクト: Zaid-Ajaj/Spiffy
        public async Task CanQuerySingleAsync()
        {
            var expected = _testDb.GenerateRandomString();
            var sql      = "SELECT @description AS description";
            var param    = new DbParams("description", expected);

            var batch  = _db.NewBatch();
            var result = await batch.QuerySingleAsync(sql, param, rd => rd.GetString("description"));

            batch.Commit();
            Assert.Equal(expected, result);
        }
コード例 #22
0
ファイル: DB.cs プロジェクト: aleksa-sukovic/data-bunch
        public static int create(string tableName, DbParams valueParams)
        {
            var queryProcessor = new InsertQueryProcessor();
            var command        = queryProcessor.process(tableName, valueParams);

            Log.info("Inserting with query: " + queryProcessor.getLastQuery());
            var affectedId = (int)command.ExecuteScalar();

            Log.success("Successfully executed query. Inserted id => " + affectedId);

            return(affectedId);
        }
コード例 #23
0
ファイル: DbBatchAsyncTests.cs プロジェクト: Zaid-Ajaj/Spiffy
        public async Task CanExecAsync()
        {
            var descripton = _testDb.GenerateRandomString();
            var sql        = "INSERT INTO test_values (description) VALUES (@description);";
            var param      = new DbParams("description", descripton);

            var batch        = _db.NewBatch();
            var rowsAffected = await batch.ExecAsync(sql, param);

            batch.Commit();
            Assert.Equal(1, rowsAffected);
        }
コード例 #24
0
ファイル: DB.cs プロジェクト: aleksa-sukovic/data-bunch
        public static SqlDataReader all(string tableName, DbParams searchParams)
        {
            var queryProcessor = new SearchQueryProcessor();
            var command        = queryProcessor.process(tableName, searchParams);

            Log.info("Searching with query: " + queryProcessor.getLastQuery());
            var reader = command.ExecuteReader();

            Log.success("Successfully executed query.");

            return(reader);
        }
コード例 #25
0
 public void AddDbParameter(object parameterValue)
 {
     if (parameterValue == null || parameterValue == DBNull.Value)
     {
         Sql.Append(" null");
     }
     else
     {
         var name = DbParamPrefix + "param" + DbParams.Count;
         DbParams.Add(name, parameterValue);
         Sql.Append(" " + name);
     }
 }
コード例 #26
0
ファイル: DbBatchAsyncTests.cs プロジェクト: Zaid-Ajaj/Spiffy
        public async Task CanScalarAsync()
        {
            var expected = _testDb.GenerateRandomString();
            var sql      = "SELECT @description";
            var param    = new DbParams("description", expected);

            var batch  = _db.NewBatch();
            var result = await batch.ScalarAsync(sql, param);

            batch.Commit();

            Assert.Equal(expected, Convert.ToString(result) ?? "");
        }
コード例 #27
0
 public override void Iteration()
 {
     try
     {
         // If throw here (A)
         DbParams.Set();     //EXCEPTION IS THROWN INSIDE
     }
     catch (Exception exception)
     {
         // I'll catch here (A)
         throw new Exception("Oops!", exception);
     }
 }
コード例 #28
0
        public void CanQuery()
        {
            var expected = _testDb.GenerateRandomString();
            var sql      = "SELECT @description AS description";
            var param    = new DbParams("description", expected);

            var batch = _db.NewBatch();

            var result = batch.Query(sql, param, rd => rd.GetString("description"));

            batch.Commit();

            Assert.Equal(expected, result.First());
        }
コード例 #29
0
ファイル: CondRaw.cs プロジェクト: fkdl/OpenLIS
        public CondRaw(string raw, IEnumerable <TDbParam> paramList = null)
        {
            // Null or empty raw string makes an empty SQL:
            if (string.IsNullOrEmpty(raw))
            {
                return;
            }

            Sql = raw;
            if (paramList != null)
            {
                DbParams.AddRange(paramList);
            }
        }
コード例 #30
0
ファイル: UpdateBuilder.cs プロジェクト: hankuikuide/Fm
        protected override void Build()
        {
            List <IColumn> dbCol = Table.DbColumns;

            if (usedProperies != null && usedProperies.Count != 0)
            {
                dbCol = dbCol.Where(col => usedProperies.Contains(col.PropertyName) || col.IsPrimaryKey).ToList();
#if DEBUG
                if (usedProperies != null)
                {
                    var cols = dbCol.Where(col => !usedProperies.Contains(col.PropertyName)).ToList();
                    for (int i = 0; i < cols.Count; i++)
                    {
                        if (cols[i].IsAutoInsert || cols[i].IsSqlGenColumn)
                        {
                            cols.RemoveAt(i);
                        }
                    }

                    string message = cols.Aggregate <IColumn, string>(null, (current, col) => current + (col.ColumnName + ","));
                    if (message != null)
                    {
                        throw new System.Exception("insert有不包含列的属性" + message);
                    }
                }
#endif
            }

            var sbKeys  = new StringBuilder();
            var sbWhere = new StringBuilder();
            Ensure.That(dbCol.Exists(col => col.IsPrimaryKey), "Entity must contain a primary key").IsTrue();
            foreach (IColumn col in dbCol)
            {
                if (!col.IsPrimaryKey)
                {
                    sbKeys.AppendFormat("{0} = {1}{2}, \r\n", EncodeName(col.ColumnName), SqlDialect.DbParameterConstant, col.PropertyName);
                    DbParams.Add(col.PropertyName, Reflection.GetPropertyValue(domain, col.PropertyName));
                }
                else
                {
                    sbWhere.AppendFormat(" {0} = {1}{2} \r\n and", EncodeName(col.ColumnName), SqlDialect.DbParameterConstant, col.PropertyName);
                    DbParams.Add(col.PropertyName, Reflection.GetPropertyValue(domain, col.PropertyName));
                }
            }
            string keys = sbKeys.ToString().Substring(0, sbKeys.Length - 4); //去掉, \r\n
            string where = sbWhere.ToString().Substring(0, sbWhere.Length - 4);
            string tsql = string.Format(this.updateTemplate, this.EncodeName(this.Table.TableName), keys, where);

            this.sql.Append(tsql);
        }