static void AreEqual(BuildedSql info, IDbConnection con, string expected, Dictionary <string, DbParam> args)
{
if (con.GetType().Name == "OracleConnection")
{
expected = expected.Replace("@", ":");
args = args.ToDictionary(e => e.Key.Replace("@", ":"), e => e.Value);
}
Assert.AreEqual(expected, info.Text);
var dbParams = info.GetParams();
Assert.AreEqual(args.Count, dbParams.Count);
for (int i = 0; i < dbParams.Count; i++)
{
DbParam paramExprected;
Assert.IsTrue(args.TryGetValue(dbParams.Keys.ToArray()[i], out paramExprected));
var paramActural = dbParams.Values.ToArray()[i];
Assert.AreEqual(paramExprected.Value, paramActural.Value);
Assert.AreEqual(paramExprected.DbType, paramActural.DbType);
Assert.AreEqual(paramExprected.Direction, paramActural.Direction);
Assert.AreEqual(paramExprected.SourceColumn, paramActural.SourceColumn);
Assert.AreEqual(paramExprected.SourceVersion, paramActural.SourceVersion);
Assert.AreEqual(paramExprected.Precision, paramActural.Precision);
Assert.AreEqual(paramExprected.Scale, paramActural.Scale);
Assert.AreEqual(paramExprected.Size, paramActural.Size);
}
}
static void TestFormatAttribute()
{
Console.ReadKey();
Console.WriteLine("Start");
BuildedSql info = null;
var times = new List <double>();
var watch = new Stopwatch();
int a = 0;
int b = 1;
for (int i = 0; i < 10000; i++)
{
watch.Start();
var query = Db <Data> .Sql(db =>
Values(0, a, 2, b)
);
info = query.Build(typeof(SqlConnection));
watch.Stop();
if (i != 0)
{
times.Add(watch.Elapsed.TotalMilliseconds);
}
watch.Reset();
}
times = times.Skip(1).ToList();
times.Select(e => e.ToString()).ToList().ForEach(e => Console.WriteLine(e));
Console.WriteLine(times.Average().ToString());
Console.ReadKey();
}
static int ExecuteForTest(IDbConnection cnn, BuildedSql info)
{
bool openNow = false;
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
openNow = true;
}
try
{
using (var com = cnn.CreateCommand())
{
com.CommandText = info.Text;
com.Connection = cnn;
foreach (var obj in info.GetParams().Select(e => CreateParameter(com, e.Key, e.Value)).ToArray())
{
com.Parameters.Add(obj);
}
return(com.ExecuteNonQuery());
}
}
finally
{
if (openNow)
{
cnn.Close();
}
}
}
/// <summary>
/// Executes a query, returning the data typed as per T.
/// </summary>
/// <typeparam name="T">Result type.</typeparam>
/// <param name="dbContext">DbContext object.</param>
/// <param name="sql">Sql.</param>
/// <returns>
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is
/// queried then the data from the first column in assumed, otherwise an instance
/// is created per row, and a direct column-name===member-name mapping is assumed
/// (case insensitive).
/// </returns>
public static IEnumerable <T> Query <T>(this IDisposable dbContext, BuildedSql sql)
{
var cnn = EFWrapper.GetGetConnection(dbContext)(dbContext);
//debug.
Debug(sql);
object[] args;
using (var com = cnn.CreateCommand())
{
args = sql.GetParams().Select(e => CreateParameter(com, e.Key, e.Value)).ToArray();
try
{
var ret = EFWrapper <T> .GetSqlQuery(dbContext)(dbContext, sql.Text, args);
ResultLog?.Invoke(ret);
return(ret);
}
catch (Exception e)
{
throw GetCoreException(e);
}
}
}
static void Debug(BuildedSql info)
{
if (Log == null)
{
return;
}
Log(info.Text);
Log(string.Join(",", info.GetParamValues()));
Log(string.Empty);
}
/// <summary>
/// Executes a query, returning the data typed as per T.
/// </summary>
/// <typeparam name="T">Result type.</typeparam>
/// <param name="cnn">Connection.</param>
/// <param name="sql">Sql.</param>
/// <returns>
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is
/// queried then the data from the first column in assumed, otherwise an instance
/// is created per row, and a direct column-name===member-name mapping is assumed
/// (case insensitive).
/// </returns>
public static List <T> Query <T>(this IDisposable cnn, BuildedSql sql)
{
//debug.
Debug(sql);
try
{
return(SQLiteNetPclWrapper <T> .GetQuery(cnn)(cnn, sql.Text, sql.GetParamValues()));
}
catch (Exception e)
{
throw GetCoreException(e);
}
}
/// <summary>
/// Execute parameterized SQL.
/// </summary>
/// <param name="cnn">Connection.</param>
/// <param name="sql">Sql.</param>
/// <returns>Number of rows affected.</returns>
public static int Execute(this IDisposable cnn, BuildedSql sql)
{
//debug.
Debug(sql);
try
{
return(SQLiteNetPclWrapper.GetExecute(cnn)(cnn, sql.Text, sql.GetParamValues()));
}
catch (Exception e)
{
throw GetCoreException(e);
}
}
static void Debug(BuildedSql info)
{
BuildedSqlLog?.Invoke(info);
if (Log == null)
{
return;
}
Log(info.Text);
foreach (var e in info.GetParams())
{
Log(e.Key + " = " + (e.Value.Value == null ? string.Empty : e.Value.Value.ToString()));
}
Log(string.Empty);
}
static void Debug(BuildedSql info)
{
BuildedSqlLog?.Invoke(info);
if (Log == null)
{
return;
}
var list = new List <string>();
list.Add(info.Text);
foreach (var e in info.GetParams())
{
list.Add(e.Key + " = " + (e.Value.Value == null ? string.Empty : e.Value.Value.ToString()));
}
list.Add(string.Empty);
Log(string.Join(Environment.NewLine, list.ToArray()));
}
static void TestBuild()
{
Console.ReadKey();
Console.WriteLine("Start");
BuildedSql info = null;
var times = new List <double>();
var watch = new Stopwatch();
var query = Db <Data> .Sql(db =>
Select(new SelectedData()
{
name = db.tbl_staff.name,
payment_date = db.tbl_remuneration.payment_date,
money = db.tbl_remuneration.money,
}).
From(db.tbl_remuneration).
Join(db.tbl_staff, db.tbl_remuneration.staff_id == db.tbl_staff.id).
Where(3000 < db.tbl_remuneration.money && db.tbl_remuneration.money < 4000));
for (int i = 0; i < 10000; i++)
{
watch.Start();
info = query.Build(typeof(SqlConnection));
watch.Stop();
if (i != 0)
{
times.Add(watch.Elapsed.TotalMilliseconds);
}
watch.Reset();
}
times = times.Skip(1).ToList();
times.Select(e => e.ToString()).ToList().ForEach(e => Console.WriteLine(e));
Console.WriteLine(times.Average().ToString());
Console.ReadKey();
}
/// <summary>
/// Execute parameterized SQL.
/// For details, refer to the document of Dapper.
/// </summary>
/// <param name="cnn">Connection.</param>
/// <param name="sql">Sql.</param>
/// <param name="transaction">Transactions to be executed at the data source.</param>
/// <param name="commandTimeout">Command timeout.</param>
/// <param name="commandType">Command type.</param>
/// <returns>Number of rows affected.</returns>
public static Task <int> ExecuteAsync(this IDbConnection cnn, BuildedSql sql, IDbTransaction transaction = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
//for testing.
if (DapperAdapterTestPlugin.Execute != null)
{
return(Task.Factory.StartNew(() => DapperAdapterTestPlugin.Execute(cnn, sql)));
}
//debug.
Debug(sql);
try
{
var ret = DapperWrapperAsync.Execute(cnn, sql.Text, CreateDynamicParam(sql.GetParams()), transaction, commandTimeout, commandType);
ResultLog?.Invoke(ret);
return(ret);
}
catch (Exception e)
{
throw GetCoreException(e);
}
}
/// <summary>
/// Executes a query, returning the data typed as per T.
/// For details, refer to the document of Dapper.
/// </summary>
/// <typeparam name="T">Result type.</typeparam>
/// <param name="cnn">Connection.</param>
/// <param name="sql">Sql.</param>
/// <param name="transaction">Transactions to be executed at the data source.</param>
/// <param name="buffered">Is buffered,</param>
/// <param name="commandTimeout">Command timeout.</param>
/// <param name="commandType">Command type.</param>
/// <returns>
/// A sequence of data of the supplied type; if a basic type (int, string, etc) is
/// queried then the data from the first column in assumed, otherwise an instance
/// is created per row, and a direct column-name===member-name mapping is assumed
/// (case insensitive).
/// </returns>
public static IEnumerable <T> Query <T>(this IDbConnection cnn, BuildedSql sql, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
//for testing.
if (DapperAdapterTestPlugin.Query != null)
{
return(new T[DapperAdapterTestPlugin.Query(cnn, sql)]);
}
//debug.
Debug(sql);
try
{
var ret = DapperWrapper <T> .Query(cnn, sql.Text, CreateDynamicParam(sql.GetParams()), transaction, buffered, commandTimeout, commandType);
ResultLog?.Invoke(ret);
return(ret);
}
catch (Exception e)
{
throw GetCoreException(e);
}
}
static int QueryForTest(IDbConnection cnn, BuildedSql info)
{
bool openNow = false;
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
openNow = true;
}
try
{
using (var com = cnn.CreateCommand())
{
com.CommandText = info.Text;
com.Connection = cnn;
foreach (var obj in info.GetParams().Select(e => CreateParameter(com, e.Key, e.Value)).ToArray())
{
com.Parameters.Add(obj);
}
int count = 0;
using (var sdr = com.ExecuteReader())
{
while (sdr.Read())
{
count++;
}
return count;
}
}
}
finally
{
if (openNow)
{
cnn.Close();
}
}
}
/// <summary> /// Executes a query, returning the data typed as per T. /// For details, refer to the document of Dapper. /// </summary> /// <typeparam name="T">Result type.</typeparam> /// <param name="cnn">Connection.</param> /// <param name="sql">Sql.</param> /// <param name="transaction">Transactions to be executed at the data source.</param> /// <param name="commandTimeout">Command timeout.</param> /// <param name="commandType">Command type.</param> /// <returns> /// A sequence of data of the supplied type; if a basic type (int, string, etc) is /// queried then the data from the first column in assumed, otherwise an instance /// is created per row, and a direct column-name===member-name mapping is assumed /// (case insensitive). /// </returns> public static Task <IEnumerable <T> > QueryAsync <T>(this IDbConnection cnn, BuildedSql <T> sql, IDbTransaction transaction = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) => QueryAsync <T>(cnn, (BuildedSql)sql, transaction, commandTimeout, commandType);
/// <summary> /// Executes a query, returning the data typed as per T. /// </summary> /// <typeparam name="T">Result type.</typeparam> /// <param name="dbContext">DbContext object.</param> /// <param name="sql">Sql.</param> /// <returns> /// A sequence of data of the supplied type; if a basic type (int, string, etc) is /// queried then the data from the first column in assumed, otherwise an instance /// is created per row, and a direct column-name===member-name mapping is assumed /// (case insensitive). /// </returns> public static IEnumerable <T> Query <T>(this IDisposable dbContext, BuildedSql <T> sql) => Query <T>(dbContext, (BuildedSql)sql);
public static void AreEqual(BuildedSql info, IDbConnection con, string expected, Dictionary <string, object> args)
=> AreEqual(info, con, expected, args.ToDictionary(e => e.Key, e => new DbParam {
Value = e.Value
}));
public static void AreEqual(BuildedSql info, IDbConnection con, string expected, Params args) => AreEqual(info, con, expected, (Dictionary <string, object>)args);
/// <summary> /// Executes a query, returning the data typed as per T. /// </summary> /// <typeparam name="T">Result type.</typeparam> /// <param name="cnn">Connection.</param> /// <param name="sql">Sql.</param> /// <returns> /// A sequence of data of the supplied type; if a basic type (int, string, etc) is /// queried then the data from the first column in assumed, otherwise an instance /// is created per row, and a direct column-name===member-name mapping is assumed /// (case insensitive). /// </returns> public static List <T> Query <T>(this IDisposable cnn, BuildedSql <T> sql) => Query <T>(cnn, (BuildedSql)sql);
/// <summary> /// Executes a query, returning the data typed as per T. /// For details, refer to the document of Dapper. /// </summary> /// <typeparam name="T">Result type.</typeparam> /// <param name="cnn">Connection.</param> /// <param name="sql">Sql.</param> /// <param name="transaction">Transactions to be executed at the data source.</param> /// <param name="buffered">Is buffered,</param> /// <param name="commandTimeout">Command timeout.</param> /// <param name="commandType">Command type.</param> /// <returns> /// A sequence of data of the supplied type; if a basic type (int, string, etc) is /// queried then the data from the first column in assumed, otherwise an instance /// is created per row, and a direct column-name===member-name mapping is assumed /// (case insensitive). /// </returns> public static IEnumerable <T> Query <T>(this IDbConnection cnn, BuildedSql <T> sql, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?)) => Query <T>(cnn, (BuildedSql)sql, transaction, buffered, commandTimeout, commandType);
