//----------Members-----------
#region 连接字符串
#if lang_zh
/// <summary>
/// 设置连接字符串
/// </summary>
/// <param name="m_connectionString">连接字符串</param>
/// <param name="m_ConnectionAliasName">连接别名；如果没起别名，默认为DEFAULT，会以此作为默认的连接字符串</param>
#else
/// <summary>
/// Set the connection string
/// </summary>
/// <param name="m_connectionString">connection string</param>
/// <param name="m_ConnectionAliasName">the Alias Name of Connection (if not pass name,it will use the DEFAULT name instead.)</param>
#endif
public static void SetConnection(string m_connectionString, string m_ConnectionAliasName = ALIAS_NAME_DEFAULT)
{
//检查连接字符串
SqlConnectionStringBuilder builder = null;
try
{
builder = new SqlConnectionStringBuilder(m_connectionString);
}
catch (Exception ex)
{
#if lang_zh
const string errMsg = "连接字符串有错误。";
#else
const string errMsg = "Connection String is invalid.";
#endif
throw new ArgumentException(errMsg, "m_connectionString", ex);
}
//##异步的
/* 启用异步SQL支持的时候，连接字符串必须包含以下属性。
* https://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqlconnectionstringbuilder.asynchronousprocessing(v=vs.110).aspx
*
* 标记为“允许异步”。即：
* 1.没有标记时，及时使用Command的Async方法，依然是同步操作；
* 2.有标记时，如果继续使用Command的同步方法，仍然能用于同步操作。
*/
#if NET_STD
#else
builder.AsynchronousProcessing = true;
#endif
string finalConnectionString = builder.ToString();
//检查连接是否可用,同时获取SqlServer的产品版本号
string version;
try
{
version = AsyncSQLHelper.ExecuteScalar(finalConnectionString, CommandType.Text, "SELECT SERVERPROPERTY('ProductVersion')") as String;
}
catch (Exception ex)
{
#if lang_zh
const string errMsg = "此连接字符串无法正常工作。";
#else
const string errMsg = "This Connection String was unable to work properly.";
#endif
throw new ArgumentException(errMsg, "m_connectionString", ex);
}
//记录结果
var item = new ConnectionRepositoryItem
{
ConnectionString = finalConnectionString,
ServerProductVersion = new Version(version)
};
ConnectionRepository.SetRepositoryItem(m_ConnectionAliasName, item);
}
/// <summary>
/// 异步执行
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="transaction">使用指定的事务处理</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <returns>受影响行数</returns>
#else
/// <summary>
/// async ExecuteNonQuery
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="transaction">transaction</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <returns>the affected rows</returns>
#endif
public static async Task <int> ExecuteNonQueryAsync(string commandText, SqlParameter[] commandParameters, MicroDBTransaction transaction, CommandType commandType = CommandType.Text)
{
if (transaction == null)
{
throw new InvalidOperationException(ERRMSG_TRANSACTION_IS_NULL, new ArgumentNullException("transaction"));
}
return(await AsyncSQLHelper.ExecuteNonQueryAsync(transaction.tran, commandType, commandText, commandParameters));
}
/// <summary>
/// 查询，返回DataTable结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <param name="connectionAliasName">连接别名；如果没起别名，则使用默认的连接字符串</param>
/// <returns>DataTable结果集</returns>
#else
/// <summary>
/// async Query，Get result of DataTable
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <param name="connectionAliasName">the Alias Name of Connection (if not pass name,it will use the DEFAULT name instead.)</param>
/// <returns>DataTable</returns>
#endif
public static async Task <DataTable> ExecuteDataTableAsync(string commandText, SqlParameter[] commandParameters, string connectionAliasName = ALIAS_NAME_DEFAULT, CommandType commandType = CommandType.Text)
{
DataSet ds = await AsyncSQLHelper.ExecuteDatasetAsync(GetConnection(connectionAliasName), commandType, commandText, commandParameters);
//返回结果
if (ds == null || ds.Tables == null || ds.Tables.Count <= 0)
{
return(null);
}
return(ds.Tables[0]);
}
/// <summary>
/// 异步查询，返回DataTable结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="transaction">使用指定的事务处理</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <returns>DataTable结果集</returns>
#else
/// <summary>
/// async Query，Get result of DataTable
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="transaction">transaction</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <returns>DataTable</returns>
#endif
public static async Task <DataTable> ExecuteDataTableAsync(string commandText, SqlParameter[] commandParameters, MicroDBTransaction transaction, CommandType commandType = CommandType.Text)
{
DataSet ds = await AsyncSQLHelper.ExecuteDatasetAsync(transaction.tran, commandType, commandText, commandParameters);
//返回结果
if (ds == null || ds.Tables == null || ds.Tables.Count <= 0)
{
return(null);
}
return(ds.Tables[0]);
}
/// <summary>
/// 异步执行
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <param name="connectionAliasName">连接别名；如果没起别名，则使用默认的连接字符串</param>
/// <returns>受影响行数</returns>
#else
/// <summary>
/// async ExecuteNonQuery
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <param name="connectionAliasName">the Alias Name of Connection (if not pass name,it will use the DEFAULT name instead.)</param>
/// <returns>the affected rows</returns>
#endif
public static async Task <int> ExecuteNonQueryAsync(string commandText, SqlParameter[] commandParameters, string connectionAliasName = ALIAS_NAME_DEFAULT, CommandType commandType = CommandType.Text)
{
return(await AsyncSQLHelper.ExecuteNonQueryAsync(GetConnection(connectionAliasName), commandType, commandText, commandParameters));
}
/// <summary>
/// 查询，返回单一结果
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <param name="connectionAliasName">连接别名；如果没起别名，则使用默认的连接字符串</param>
/// <returns>单一结果</returns>
#else
/// <summary>
/// Query，Get result of One Object
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <param name="connectionAliasName">the Alias Name of Connection (if not pass name,it will use the DEFAULT name instead.)</param>
/// <returns>Object</returns>
#endif
public static object ExecuteScalar(string commandText, SqlParameter[] commandParameters, string connectionAliasName = ALIAS_NAME_DEFAULT, CommandType commandType = CommandType.Text)
{
return(SyncSQLHelper.ExecuteScalar(GetConnection(connectionAliasName), commandType, commandText, commandParameters));
}
/// <summary>
/// 查询，返回单一结果
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="transaction">使用指定的事务处理</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <returns>单一结果</returns>
#else
/// <summary>
/// Query，Get result of One Object
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="transaction">transaction</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <returns>Object</returns>
#endif
public static object ExecuteScalar(string commandText, SqlParameter[] commandParameters, MicroDBTransaction transaction, CommandType commandType = CommandType.Text)
{
return(SyncSQLHelper.ExecuteScalar(transaction.tran, commandType, commandText, commandParameters));
}
/// <summary>
/// 查询，返回DataSet结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <param name="connectionAliasName">连接别名；如果没起别名，则使用默认的连接字符串</param>
/// <returns>DataSet结果集</returns>
#else
/// <summary>
/// async Query，Get result of DataSet
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <param name="connectionAliasName">the Alias Name of Connection (if not pass name,it will use the DEFAULT name instead.)</param>
/// <returns>DataSet</returns>
#endif
public static async Task <DataSet> ExecuteDataSetAsync(string commandText, SqlParameter[] commandParameters, string connectionAliasName = ALIAS_NAME_DEFAULT, CommandType commandType = CommandType.Text)
{
DataSet ds = await AsyncSQLHelper.ExecuteDatasetAsync(GetConnection(connectionAliasName), commandType, commandText, commandParameters);
return(ds);
}
/// <summary>
/// 异步查询，返回DataSet结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="transaction">使用指定的事务处理</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <returns>DataSet结果集</returns>
#else
/// <summary>
/// async Query，Get result of DataSet
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="transaction">transaction</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <returns>DataSet</returns>
#endif
public static async Task <DataSet> ExecuteDataSetAsync(string commandText, SqlParameter[] commandParameters, MicroDBTransaction transaction, CommandType commandType = CommandType.Text)
{
DataSet ds = await AsyncSQLHelper.ExecuteDatasetAsync(transaction.tran, commandType, commandText, commandParameters);
return(ds);
}
//------DB Operate Methods------
#region 查询，返回DataSet结果集
#if lang_zh
/// <summary>
/// 查询，返回DataSet结果集
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <param name="commandParameters">参数集合</param>
/// <param name="transaction">使用指定的事务处理</param>
/// <param name="commandType">SQL语句 | 存储过程</param>
/// <returns>DataSet结果集</returns>
#else
/// <summary>
/// Query，Get result of DataSet
/// </summary>
/// <param name="commandText">SQL statement</param>
/// <param name="commandParameters">SqlParameter Collection</param>
/// <param name="transaction">transaction</param>
/// <param name="commandType">Text | StoredProcedure</param>
/// <returns>DataSet</returns>
#endif
public static DataSet ExecuteDataSet(string commandText, SqlParameter[] commandParameters, MicroDBTransaction transaction, CommandType commandType = CommandType.Text)
{
DataSet ds = SyncSQLHelper.ExecuteDataset(transaction.tran, commandType, commandText, commandParameters);
return(ds);
}
