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})";
}
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);
}
/// <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);
}));
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
});
}
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);
}
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);
}
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));
}
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);
}
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;
}
public SqlCommand process(string tableName, DbParams searchParams)
{
var query = constructBaseQuery(tableName);
lastQuery = query + constructSearchParams(searchParams);
return(constructCommand(lastQuery, searchParams));
}
public void Clear()
{
SelectFields.Clear();
Sql.Clear();
DbParams.Clear();
_dicTableName.Clear();
_queueEnglishWords = new Queue <string>(SListEnglishWords);
}
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));
}
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.");
}
public static int save(string tableName, DbParams valueParams, DbParams searchParams = null)
{
if (searchParams == null)
{
return(create(tableName, valueParams));
}
return(update(tableName, valueParams, searchParams));
}
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) ?? "");
}
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);
}
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);
}
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);
}
private string constructParamValues(DbParams valueParams)
{
var query = "(";
foreach (var pair in valueParams.get())
{
query += "@" + pair.ID + ", ";
}
return(query.Substring(0, query.Length - 2) + ")");
}
public void ShouldAddParams()
{
var p = new DbParams
{
{ "key", 1 }
};
p.Add("key2", 2);
Assert.Equal(1, p["key"]);
Assert.Equal(2, p["key2"]);
}
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);
}
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);
}
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);
}
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);
}
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);
}
}
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) ?? "");
}
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);
}
}
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());
}
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);
}
}
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);
}
