/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T">返回结果类型</typeparam>
/// <param name="query">查询请求内容</param>
/// <returns>结果集</returns>
public IEnumerable <T> Query <T>(SqlQuery query)
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
return _conn.Query <T>(query.CommandText, query.ParaItems, Tran, true, query.CommandTimeout, query.CommandType);
}, dbType: DatabaseTyoe.ToString(), dbConnection: Connection));
}
/// <summary>
/// 取出第一条数据
/// </summary>
/// <typeparam name="T">数据返回类型</typeparam>
/// <param name="query">请求内容</param>
/// <returns>单条数据内容</returns>
public T ExecuteScalar <T>(SqlQuery query)
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
return _conn.ExecuteScalar <T>(query.CommandText, query.ParaItems, Tran, query.CommandTimeout, query.CommandType);
}, dbType: DatabaseTyoe.ToString(), dbConnection: Connection));
}
/// <summary>
/// 返回第一条结果信息
/// </summary>
/// <typeparam name="T">返回结果类型</typeparam>
/// <param name="query">查询请求内容</param>
/// <returns>第一条结果信息</returns>
public T QuerySingleOrDefault <T>(SqlQuery query)
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
return _conn.QueryFirstOrDefault <T>(query.CommandText, query.ParaItems, Tran, query.CommandTimeout, query.CommandType);
}, dbType: DatabaseTyoe.ToString(), dbConnection: Connection));
}
/// <summary>
/// 执行增删改方法，返回受影响的行数
/// </summary>
/// <param name="query">请求内容</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(SqlQuery query)
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
return _conn.Execute(query.CommandText, query.ParaItems, Tran, query.CommandTimeout, query.CommandType);
}, dbType: DatabaseTyoe.ToString()));
}
/// <summary>
/// 返回多个结果
/// </summary>
/// <typeparam name="TResult">返回结果类型</typeparam>
/// <param name="query">查询请求内容</param>
/// <param name="action">将GridReader转为查询结果的方法</param>
/// <returns>多个结果</returns>
public TResult QueryMultiple <TResult>(SqlQuery query, Func <SqlMapper.GridReader, TResult> action)
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
using (var multiple = _conn.QueryMultiple(query.CommandText, param: query.ParaItems, transaction: Tran, commandTimeout: query.CommandTimeout, commandType: query.CommandType))
{
return action(multiple);
}
}, dbType: DatabaseTyoe.ToString(), dbConnection: Connection));
}
/// <summary>
/// 批量插入(目前只支持MSSQLServer)
/// </summary>
/// <param name="table">table</param>
/// <param name="tableName">表名字</param>
public void BulkInsert(DataTable table, string tableName)
{
SqlMonitorUtil.Monitor(() =>
{
if (table != null && DatabaseTyoe == DatabaseType.MSSQLServer)
{
Open();
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(Connection as SqlConnection, SqlBulkCopyOptions.Default, Tran as SqlTransaction))
{
sqlbulkcopy.BatchSize = table.Rows.Count;
sqlbulkcopy.DestinationTableName = tableName;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(table.CreateDataReader());
}
}
else if (table != null && DatabaseTyoe == DatabaseType.MySql)
{
string tmpPath = System.IO.Path.GetTempFileName();
try
{
string csv = table.ToCsv();
System.IO.File.WriteAllText(tmpPath, csv);
Open();
MySqlBulkLoader bulk = new MySqlBulkLoader(Connection as MySqlConnection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = tableName,
};
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
int insertCount = bulk.Load();
}
finally
{
FileUtil.DeleteFile(tmpPath);
}
}
else
{
throw new NotSupportedException(DatabaseTyoe.ToString());
}
}, dbType: DatabaseTyoe.ToString(), memberName: "BaseDataAccess-BulkInsert");
}
/// <summary>
/// 异步查询（Table）
/// </summary>
/// <param name="query">查询请求内容</param>
/// <returns>Table查询结果</returns>
public async Task <DataTable> QueryTableAsync(SqlQuery query)
{
DataTable table = new DataTable();
Open();
PrepareSqlQuery(query);
return(await SqlMonitorUtil.Monitor(query, async() =>
{
using (IDataReader reader = await _conn.ExecuteReaderAsync(query.CommandText, query.ParaItems, Tran, query.CommandTimeout, query.CommandType))
{
table.Load(reader);
return table;
}
}, dbType : DatabaseTyoe.ToString(), dbConnection : Connection));
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="TFirst"></typeparam>
/// <typeparam name="TSecond"></typeparam>
/// <typeparam name="TThird"></typeparam>
/// <typeparam name="TFourth"></typeparam>
/// <typeparam name="TFifth"></typeparam>
/// <typeparam name="TReturn">返回结果类型</typeparam>
/// <param name="query">查询请求内容</param>
/// <param name="map">匹配关系</param>
/// <param name="splitOn">分割字段</param>
/// <returns>结果集</returns>
public IEnumerable <TReturn> Query <TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(SqlQuery query, Func <TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, string splitOn = "Id")
{
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
return _conn.Query(query.CommandText,
map: map,
param: query.ParaItems,
transaction: Tran,
splitOn: splitOn,
commandTimeout: query.CommandTimeout,
commandType: query.CommandType);
}, dbType: DatabaseTyoe.ToString(), dbConnection: Connection));
}
/// <summary>
/// 查询（Table）
/// </summary>
/// <param name="query">查询请求内容</param>
/// <returns>Table查询结果</returns>
public DataTable QueryTable(SqlQuery query)
{
DataTable table = new DataTable();
Open();
PrepareSqlQuery(query);
return(SqlMonitorUtil.Monitor(query, () =>
{
using (IDataReader reader = _conn.ExecuteReader(query.CommandText, query.ParaItems, Tran, query.CommandTimeout, query.CommandType))
{
table.Load(reader);
return table;
}
}, dbType: DatabaseTyoe.ToString()));
}