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 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 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 void ShouldAddParams()
{
var p = new DbParams
{
{ "key", 1 }
};
p.Add("key2", 2);
Assert.Equal(1, p["key"]);
Assert.Equal(2, p["key2"]);
}
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);
}
}
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);
}
public async Task ExecuteNonQueryAsync(string connectionName, string procedureName, IEnumerable <IDbDataParameter> parameters)
{
using (var connection = _queryConnectionProvider.GetConnection(connectionName))
{
var paramsList = new DbParams();
foreach (var p in parameters)
{
paramsList.Add(p);
}
try
{
connection.Open();
await connection.ExecuteAsync(procedureName, paramsList, commandType : CommandType.StoredProcedure);
}
finally
{
connection.Close();
}
}
}
public CondCmpr(string field, string opt, object value, string paramName = "")
{
opt = opt.ToUpper();
// Empty field name or invalid opt makes an empty SQL.
if (string.IsNullOrEmpty(field))
{
return;
}
if (!(new[] { ">", ">=", "<", "<=", "=", "<>", "LIKE", "NOT LIKE", "IS", "IS NOT" }.Contains(opt)))
{
return;
}
// "IS" or "IS NOT" for NULL value only.
if (value == null)
{
if (opt == "IS" || opt == "IS NOT")
{
Sql = $"{field} {opt} NULL";
}
}
else
{
if (opt == "IS" || opt == "IS NOT")
{
return;
}
// get parameter name
var pName = string.IsNullOrEmpty(paramName)
? "@p" + StaticCounter.Next
: paramName;
// construct SQL and push parameter
Sql = $"{field} {opt} {pName}";
DbParams.Add(new TDbParam {
ParameterName = pName, Value = ConvertDbValue(value)
});
}
}