/// <summary>
/// 返回结果集中的第一行的第一列
/// </summary>
/// <param name="tag">语句ID</param>
/// <param name="paramObject">语句所需要的参数</param>
/// <returns>结果集中的第一行的第一列</returns>
protected object QueryScalar(string tag, object paramObject)
{
bool isSessionLocal = false;
object result;
if (SqlMap.LocalSession == null)
{
isSessionLocal = true;
SqlMap.OpenConnection();
}
//记录SQL语句
GetSql(tag, paramObject);
try
{
IDbCommand cmd = GetDbCommand(tag, paramObject);
result = cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception("Can't QueryScalar, tag = " + tag, ex);
}
finally
{
if (isSessionLocal)
{
SqlMap.CloseConnection();
}
}
return(result);
}
public string GetSql(string statementName, object paramObject)
{
string sql = "";
try
{
IMappedStatement statement = mapper.GetMappedStatement(statementName);
if (!mapper.IsSessionStarted)
{
mapper.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
sql = scope.PreparedStatement.PreparedSql;
}
catch (Exception e1)
{
}
finally
{
try
{
mapper.CloseConnection();
}
catch (Exception)
{
}
}
return(sql);
}
public TResult One <TResult>(string command, object parameter)
{
try
{
_mapper.OpenConnection();
return(_mapper.QueryForObject <TResult>(command, parameter));
}
finally
{
_mapper.CloseConnection();
}
}
/// <summary>
/// 执行添加
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数</param>
public static object ExecuteInsert(string statementName, object parameterObject)
{
ISqlMapper sqlMap = Mapper();
object obj = null;
try
{
//分布式事务操作方法
//using (TransactionScope tx = new TransactionScope())
//{
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
}
obj = sqlMap.Insert(statementName, parameterObject);
sqlMap.CloseConnection();
// tx.Complete();
//}
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for insert. Cause: " + e.Message, e);
}
return(obj);
}
//Close Connection 원형
private static void CloseConnectionCore(ISqlMapper mapper)
{
if (mapper.IsSessionStarted)
{
mapper.CloseConnection();
}
}
/// <summary>
/// Query for a DataSet.
/// </summary>
/// <param name="statementName">Name of the statement.</param>
/// <param name="paramObject">The parameter object.</param>
/// <returns>DataSet.</returns>
protected DataSet QueryForDataSet(string statementName, object paramObject)
{
try
{
DataSet ds = new DataSet();
ISqlMapper mapper = GetLocalSqlMap();
IMappedStatement statement = mapper.GetMappedStatement(statementName);
if (!mapper.IsSessionStarted)
{
mapper.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);
IDbCommand dc = mapper.LocalSession.CreateCommand(scope.IDbCommand.CommandType);
dc.CommandText = scope.IDbCommand.CommandText;
foreach (IDbDataParameter para in scope.IDbCommand.Parameters)
{
dc.Parameters.Add(para);
}
IDbDataAdapter dda = mapper.LocalSession.CreateDataAdapter(dc);
dda.Fill(ds);
mapper.CloseConnection();
return(ds);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
/// <summary>
/// Releasing, or resetting resources.
/// </summary>
public void Dispose()
{
if (_isTransactionOpen == false)
{
if (_connection.State != ConnectionState.Closed)
{
_sqlMapper.CloseConnection();
}
}
else
{
if (_consistent)
{
_sqlMapper.CommitTransaction();
_isTransactionOpen = false;
}
else
{
if (_connection.State != ConnectionState.Closed)
{
_sqlMapper.RollBackTransaction();
_isTransactionOpen = false;
}
}
}
}
public void Close()
{
if (null != _session)
{
_mapper.CloseConnection();
}
}
public DataSet SelectDS(string statementName, object paramObject, object _sqlmapper)
{
DataSet ds = new DataSet();
ISqlMapper _mapper = _sqlmapper as ISqlMapper;
try
{
IMappedStatement statement = _mapper.GetMappedStatement(statementName);
if (!_mapper.IsSessionStarted)
{
_mapper.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, _mapper.LocalSession);
statement.PreparedCommand.Create(scope, _mapper.LocalSession, statement.Statement, paramObject);
IDbCommand command = _mapper.LocalSession.CreateCommand(CommandType.Text);
command.CommandText = scope.IDbCommand.CommandText;
Log.Info(scope.IDbCommand.CommandText);
foreach (IDataParameter pa in scope.IDbCommand.Parameters)
{
command.Parameters.Add(new OracleParameter(pa.ParameterName, pa.Value));
}
_mapper.LocalSession.CreateDataAdapter(command).Fill(ds);
}
finally
{
_mapper.CloseConnection();
}
return(ds);
}
/// <summary>
/// 执行修改
/// </summary>
/// <param name="statementName">操作名</param>
/// <param name="parameterObject">参数，里面存放的是一个具体对象的list集合（key:对象名，value:对象list）</param>
/// <returns>返回影响行数</returns>
public static int ExecuteUpdate(string statementName, object parameterObject)
{
ISqlMapper sqlMap = Mapper();
int number = 0;
try
{
//分布式事务操作方法
//using (TransactionScope tx = new TransactionScope())
//{
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
}
number = sqlMap.Update(statementName, parameterObject);
sqlMap.CloseConnection();
// tx.Complete();
//}
}
catch (Exception e)
{
throw new DataMapperException("Error executing query '" + statementName + "' for update. Cause: " + e.Message, e);
}
return(number);
}
/// <summary>
/// 得到运行时ibatis.net动态生成的SQL
/// </summary>
/// <param name="sqlMapper"></param>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public virtual string GetRuntimeSql(string statementName, object paramObject)
{
string result = string.Empty;
ISqlMapper sqlMap = GetLocalSqlMap();
try
{
sqlMap.OpenConnection();
IMappedStatement statement = sqlMap.GetMappedStatement(statementName);
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, sqlMap.LocalSession);
result = scope.PreparedStatement.PreparedSql;
}
catch (Exception ex)
{
result = "获取SQL语句出现异常:" + ex.Message;
}
finally
{
sqlMap.CloseConnection();
}
return(result);
}
/// <summary>
/// Releasing, or resetting resources.
/// </summary>
public void Dispose()
{
if (_logger.IsDebugEnabled)
{
_logger.Debug("Dispose SqlMapSession");
}
if (_isTransactionOpen == false)
{
if (_connection.State != ConnectionState.Closed)
{
_sqlMapper.CloseConnection();
}
}
else
{
if (_consistent)
{
_sqlMapper.CommitTransaction();
_isTransactionOpen = false;
}
else
{
if (_connection.State != ConnectionState.Closed)
{
_sqlMapper.RollBackTransaction();
_isTransactionOpen = false;
}
}
}
}
/// <summary>
/// SQL 문장을 가져온다.
/// <para>트랜잭션 실행 중일 경우 아래 오류 발생</para>
/// <para>SqlMap could not invoke BeginTransaction(). A Transaction is already started. Call CommitTransaction() or RollbackTransaction first.</para>
/// </summary>
/// <param name="statementName"></param>
/// <returns></returns>
protected String GetPreparedStatement(string statementName)
{
String strSQL = "";
ISqlMapper mapper = DaoFactory.Instance;
try
{
if (!mapper.IsSessionStarted)
{
mapper.OpenConnection(); //트랜잭션 중간에 사용할 경우 오류 발생
}
IMappedStatement statement = mapper.GetMappedStatement(statementName);
RequestScope request = statement.Statement.Sql.GetRequestScope(statement, param, mapper.LocalSession);
strSQL = request.PreparedStatement.PreparedSql;
strSQL += "\r\n" + GetParameters(param);
if (mapper.IsSessionStarted)
{
mapper.CloseConnection();
}
}
catch (Exception ex)
{
throw new IBatisNetException("Error Occured in GetPreparedStatement=" + statementName, ex);
}
return(strSQL);
}
public void Close()
{
if (_session.IsSessionStarted)
{
_session.CloseConnection();
}
}
/// <summary>
/// 清除SqlMap对象
/// </summary>
public static void Reset()
{
try {
sqlMap.CloseConnection();
}
catch { }
finally {
sqlMap = null;
}
}
public DataSet QueryForDataSet(string statementName, object paramObject)
{
DataSet ds = new DataSet();
try
{
IMappedStatement statement = mapper.GetMappedStatement(statementName);
if (!mapper.IsSessionStarted)
{
mapper.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);
IDbCommand command = mapper.LocalSession.CreateCommand(CommandType.Text);
command.CommandText = scope.IDbCommand.CommandText;
foreach (IDataParameter pa in scope.IDbCommand.Parameters)
{
if (Compare(mapper))
{
command.Parameters.Add(new OracleParameter(pa.ParameterName, pa.Value));
}
else
{
command.Parameters.Add(new SqlParameter(pa.ParameterName, pa.Value));
}
}
mapper.LocalSession.CreateDataAdapter(command).Fill(ds);
}
finally
{
try
{
mapper.CloseConnection();
}
catch (Exception)
{
}
}
return(ds);
}
//Select object 원형
private object QueryForObjectCore(string statementName, object parameterObject, string datasourceCode)
{
PreSelectExecute(datasourceCode); //Select PreProcess 실행
ISqlMapper mapper = (ISqlMapper)maps[datasourceCode + GetProcessID()];
WriteLog(datasourceCode, mapper, statementName, parameterObject);
mapper.CloseConnection();
//CloseAll(Process.GetCurrentProcess().Id.ToString());
return(mapper.QueryForObject(statementName, parameterObject));
}
/// <summary>
/// 只返回部分符合条件的数据
/// </summary>
/// <param name="statementName"></param>
/// <param name="paramObject"></param>
/// <param name="thePageInfo"></param>
/// <returns></returns>
//protected DataSet QueryForDataSet(string statementName, object paramObject, PageInfo thePageInfo)
//{
// try
// {
// DataSet ds = new DataSet();
// ISqlMapper mapper = GetLocalSqlMap();
// IMappedStatement statement = mapper.GetMappedStatement(statementName);
// if (!mapper.IsSessionStarted)
// {
// mapper.OpenConnection();
// }
// RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
// statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);
// ///
// /// 生成标准SQL语句
// IDbCommand dc = mapper.LocalSession.CreateCommand(scope.IDbCommand.CommandType);
// string mySQL = scope.IDbCommand.CommandText;
// if (thePageInfo == null)
// dc.CommandText = mySQL;
// else
// dc.CommandText = thePageInfo.GetQueryStringPageSplit(mySQL);
// foreach (IDbDataParameter para in scope.IDbCommand.Parameters)
// {
// dc.Parameters.Add(para);
// }
// IDbDataAdapter dda = mapper.LocalSession.CreateDataAdapter(dc);
// dda.Fill(ds);
// return ds;
// }
// catch (Exception e)
// {
// throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
// }
//}
protected DataSet QueryForDataSet(string statementName, object paramObject, int currentPage, int pageSize)
{
/* 获得分页后的DataSet
* 直接传入当前页与页面大小
*/
try
{
DataSet ds = new DataSet();
ISqlMapper mapper = GetLocalSqlMap();
IMappedStatement statement = mapper.GetMappedStatement(statementName);
if (!mapper.IsSessionStarted)
{
mapper.OpenConnection();
}
RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);
IDbCommand dc = mapper.LocalSession.CreateCommand(scope.IDbCommand.CommandType);
string mySQL = scope.IDbCommand.CommandText;
/* 拼出分页语句 */
if (currentPage < 1 ||
pageSize < 1)
{
dc.CommandText = mySQL;
}
else
{
dc.CommandText = GetPagedSQLForOracle(mySQL, currentPage, pageSize);
}
foreach (IDbDataParameter para in scope.IDbCommand.Parameters)
{
dc.Parameters.Add(para);
}
IDbDataAdapter dda = mapper.LocalSession.CreateDataAdapter(dc);
dda.Fill(ds);
mapper.CloseConnection();
return(ds);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
}
//Select DataTable 원형
private DataTable QueryForTableCore(string statementName, object parameterObject, string datasourceCode)
{
DataTable dataTable = null;
try
{
PreSelectExecute(datasourceCode); //Select PreProcess 실행
ISqlMapper mapper = (ISqlMapper)maps[datasourceCode + GetProcessID()];
WriteLog(datasourceCode, mapper, statementName, parameterObject);
dataTable = new DataTable(statementName);
ISqlMapSession session = mapper.LocalSession;
IMappedStatement statement = mapper.GetMappedStatement(statementName);
RequestScope request = statement.Statement.Sql.GetRequestScope(statement, parameterObject, session);
statement.PreparedCommand.Create(request, session, statement.Statement, parameterObject);
using (request.IDbCommand)
{
dataTable.Load(request.IDbCommand.ExecuteReader());
}
if (dataTable.Columns.Count != 0)
{
foreach (DataColumn col in dataTable.Columns)
{
col.AllowDBNull = true;
col.ReadOnly = false;
col.MaxLength = -1;
}
}
mapper.CloseConnection();
//CloseAll(Process.GetCurrentProcess().Id.ToString());
}
catch (Exception e)
{
throw e;
}
return(dataTable);
}
//Select One Line 원형
private Hashtable QueryForOnelineCore(string statementName, object parameterObject, string datasourceCode)
{
Hashtable result = new Hashtable();
PreSelectExecute(datasourceCode); //Select PreProcess 실행
ISqlMapper mapper = (ISqlMapper)maps[datasourceCode + GetProcessID()];
object tmpResult = mapper.QueryForObject(statementName, parameterObject);
if (tmpResult != null)
{
result = (Hashtable)tmpResult;
}
mapper.CloseConnection();
//CloseAll(Process.GetCurrentProcess().Id.ToString());
return(result);
}
/// <summary>
/// 返回指定数量的结果集
/// </summary>
/// <param name="statementName">SqlMapID</param>
/// <param name="map">传入参数</param>
/// <param name="skipResults">起始数据索引</param>
/// <param name="maxResults">返回数据最大数量</param>
/// <returns></returns>
public virtual IList GetListByQuery(string statementName, Hashtable map, int skipResults, int maxResults)
{
ISqlMapper sqlMap = GetLocalSqlMap();
IList list = default(IList);
try
{
sqlMap.OpenConnection();
list = sqlMap.QueryForList(statementName, map, skipResults, maxResults);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
}
finally
{
sqlMap.CloseConnection();
}
return(list);
}
//获得实体类, 传map查询条件
/// <summary>
/// 获取实体类
/// </summary>
/// <param name="map">传入参数</param>
/// <returns></returns>
public virtual T GetObject(Hashtable map)
{
ISqlMapper sqlMap = GetLocalSqlMap();
T t = default(T);
try
{
sqlMap.OpenConnection();
t = sqlMap.QueryForObject <T>(getBuildObjectSqlMap(), map);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + getBuildObjectSqlMapId() + "' for object. Cause: " + e.Message, e);
}
finally
{
sqlMap.CloseConnection();
}
return(t);
}
/// <summary>
/// 获取实体类
/// </summary>
/// <param name="keyString">传入参数</param>
/// <param name="statementName">SqlMapID</param>
/// <returns></returns>
public virtual T GetObject(string keyString, string statementName)
{
ISqlMapper sqlMap = GetLocalSqlMap();
T t = default(T);
try
{
sqlMap.OpenConnection();
t = sqlMap.QueryForObject <T>(statementName, keyString);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for object. Cause: " + e.Message, e);
}
finally
{
sqlMap.CloseConnection();
}
return(t);
}
/// <summary>
/// 返回指定类型实体
/// </summary>
/// <typeparam name="Entity"></typeparam>
/// <param name="statementName">SqlMapID</param>
/// <param name="pamaryKey">传入实体类</param>
/// <returns></returns>
public virtual Entity GetObject <Entity>(string statementName, object parameterObject)
{
ISqlMapper sqlMap = GetLocalSqlMap();
Entity obj = default(Entity);
try
{
sqlMap.OpenConnection();
obj = sqlMap.QueryForObject <Entity>(statementName, parameterObject);
}
catch (Exception ex)
{
throw new IBatisNetException("Error executing query '" + statementName + "':" + ex.Message, ex);
}
finally
{
sqlMap.CloseConnection();
}
return(obj);
}
/// <summary>
/// 根据statementName返回结果统计（以skipResults为起始点，查询出最多maxResults条数据）
/// </summary>
/// <typeparam name="Entity"></typeparam>
/// <param name="statementName"></param>
/// <param name="parameterObject"></param>
/// <param name="skipResults">查询起始点索引</param>
/// <param name="maxResults">返回结果集最大数量</param>
/// <returns></returns>
public virtual IList <Entity> GetListByQuery <Entity>(string statementName, object parameterObject, int skipResults, int maxResults)
{
ISqlMapper sqlMap = GetLocalSqlMap();
IList <Entity> ilist = null;
try
{
sqlMap.OpenConnection();
ilist = sqlMap.QueryForList <Entity>(statementName, parameterObject, skipResults, maxResults);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + statementName + "' for GenericType IList<" + typeof(Entity).Name + ">. Cause: " + e.Message, e);
}
finally
{
sqlMap.CloseConnection();
}
return(ilist);
}
/// <summary>
/// 获得结果集
/// </summary>
/// <param name="map"></param>
/// <param name="sql"></param>
/// <returns></returns>
public virtual IList GetListByQueryLimit(Hashtable map, string sql)
{
ISqlMapper sqlMap = GetLocalSqlMap();
IList list = default(IList);
try
{
sqlMap.OpenConnection();
list = sqlMap.QueryForList(sql, map);
}
catch (Exception e)
{
throw new IBatisNetException("Error executing query '" + sql + "' for list. Cause: " + e.Message, e);
}
finally
{
sqlMap.CloseConnection();
}
return(list);
}
public System.Data.DataTable QueryForDataTable(string statementName, object paramObject)
{
ISqlMapper sqlMap = this.GetSqlMap();
bool flag = false;
System.DateTime now = System.DateTime.Now;
System.Data.DataTable result;
try
{
if (!sqlMap.IsSessionStarted)
{
sqlMap.OpenConnection();
flag = true;
}
System.Data.DataSet dataSet = new System.Data.DataSet();
IMappedStatement mappedStatement = sqlMap.GetMappedStatement(statementName);
RequestScope requestScope = mappedStatement.Statement.Sql.GetRequestScope(mappedStatement, paramObject, sqlMap.LocalSession);
mappedStatement.PreparedCommand.Create(requestScope, sqlMap.LocalSession, mappedStatement.Statement, paramObject);
System.Reflection.FieldInfo field = requestScope.IDbCommand.GetType().GetField("_innerDbCommand", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
using (System.Data.IDbCommand dbCommand = (System.Data.IDbCommand)field.GetValue(requestScope.IDbCommand))
{
int num = sqlMap.LocalSession.CreateDataAdapter(dbCommand).Fill(dataSet);
}
result = dataSet.Tables[0];
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (flag)
{
sqlMap.CloseConnection();
}
}
return(result);
}
/// <summary>
/// 封装了对应的SqlMap的方法，以事务的方式批量删除
/// </summary>
/// <param name="statementNames"></param>
/// <param name="parameterObjects"></param>
/// <returns></returns>
protected object[] ExecuteBatchDelete(IList <string> statementNames,
IList <object> parameterObjects)
{
Debug.Assert(statementNames.Count ==
parameterObjects.Count, "语句和参数的个数不匹配");
ISqlMapper sqlMap = GetLocalSqlMap();
var results = new object[statementNames.Count];
if (sqlMap.IsSessionStarted)
{
sqlMap.CloseConnection();
}
using (ISqlMapSession sqlMapSession = sqlMap.BeginTransaction())
{
try
{
for (int i = 0; i < statementNames.Count; i++)
{
results[i] = sqlMap.Delete(statementNames[i], parameterObjects[i]);
}
sqlMapSession.CommitTransaction();
}
catch (Exception e)
{
Trace.Write(e.Message + "\n" + e.StackTrace);
sqlMapSession.RollBackTransaction();
throw new IBatisNetException(
"Error executing batch query '" + statementNames[0] + "' for delete. Cause: " + e.Message, e);
}
}
return(results);
}
/// <summary>
/// Closes the connection
/// </summary>
public override void CloseConnection()
{
_sqlMap.CloseConnection();
}
public void Close()
{
_session.CloseConnection();
}