You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
786 lines
22 KiB
786 lines
22 KiB
using System;
|
|
using System.Collections;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using IBatisNet.Common;
|
|
using IBatisNet.Common.Exceptions;
|
|
using IBatisNet.Common.Pagination;
|
|
using IBatisNet.Common.Utilities;
|
|
using IBatisNet.DataMapper;
|
|
using IBatisNet.DataMapper.Configuration;
|
|
using IBatisNet.DataMapper.Configuration.Statements;
|
|
using IBatisNet.DataMapper.Exceptions;
|
|
using IBatisNet.DataMapper.MappedStatements;
|
|
using IBatisNet.DataMapper.Scope;
|
|
using System.Data;
|
|
using System.Reflection;
|
|
using System.Data.Common;
|
|
using SiaSun.LMS.Model;
|
|
|
|
|
|
namespace SiaSun.LMS.Persistence
|
|
{
|
|
public class P_Base
|
|
{
|
|
public P_Base()
|
|
{
|
|
|
|
}
|
|
|
|
public ISqlMapper _sqlMap;
|
|
|
|
|
|
public string _sConfig;
|
|
|
|
#region Connection&Transaction
|
|
|
|
#endregion
|
|
|
|
#region Methods
|
|
|
|
/// <summary>
|
|
/// 得到列表
|
|
/// </summary>
|
|
/// <typeparam name="T">实体类型</typeparam>
|
|
/// <param name="statementName">操作名称，对应xml中的Statement的id</param>
|
|
/// <param name="parameterObject">参数</param>
|
|
/// <returns></returns>
|
|
protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.QueryForList<T>(statementName, parameterObject);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
public ObjectList ExecuteQueryForList(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
IList<object> ls = this._sqlMap.QueryForList<object>(statementName, parameterObject);
|
|
|
|
ObjectList objlist = new ObjectList(ls);
|
|
|
|
return new Model.ObjectList(ls);
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 得到指定数量的记录数
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="statementName"></param>
|
|
/// <param name="parameterObject">参数</param>
|
|
/// <param name="skipResults">跳过的记录数</param>
|
|
/// <param name="maxResults">最大返回的记录数</param>
|
|
/// <returns></returns>
|
|
protected IList<T> ExecuteQueryForList<T>(string statementName, object parameterObject, int skipResults, int maxResults)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.QueryForList<T>(statementName, parameterObject, skipResults, maxResults);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for list. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
protected IPaginatedList ExecuteQueryForPaginatedList(string statementName, object parameterObject, int pageSize)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.QueryForPaginatedList(statementName, parameterObject, pageSize);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new IBatisNetException("Error executing query '" + statementName + "' for paginated list. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询得到对象的一个实例
|
|
/// </summary>
|
|
/// <typeparam name="T">对象type</typeparam>
|
|
/// <param name="statementName">操作名</param>
|
|
/// <param name="parameterObject">参数</param>
|
|
/// <returns></returns>
|
|
public T ExecuteQueryForObject<T>(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.QueryForObject<T>(statementName, parameterObject);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for object. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
public ObjectT ExecuteQueryForObject(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
object obj = this._sqlMap.QueryForObject(statementName, parameterObject);
|
|
return new ObjectT(obj);
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for object. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行添加
|
|
/// </summary>
|
|
/// <param name="statementName">操作名</param>
|
|
/// <param name="parameterObject">参数</param>
|
|
protected int ExecuteInsert(string statementName, object parameterObject)
|
|
{
|
|
object generatedKey = null;
|
|
|
|
try
|
|
{
|
|
generatedKey = this._sqlMap.Insert(statementName, parameterObject);
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for insert. Cause: " + e.Message, e);
|
|
}
|
|
|
|
return generatedKey == null ? 0 : 1;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行修改
|
|
/// </summary>
|
|
/// <param name="statementName">操作名</param>
|
|
/// <param name="parameterObject">参数</param>
|
|
protected int ExecuteUpdate(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.Update(statementName, parameterObject);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for update. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行删除
|
|
/// </summary>
|
|
/// <param name="statementName">操作名</param>
|
|
/// <param name="parameterObject">参数</param>
|
|
protected int ExecuteDelete(string statementName, object parameterObject)
|
|
{
|
|
try
|
|
{
|
|
return this._sqlMap.Delete(statementName, parameterObject);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw new DataMapperException("Error executing query '" + statementName + "' for delete. Cause: " + e.Message, e);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 自定义
|
|
|
|
/// <summary>获得Command
|
|
/// 获得Command
|
|
/// </summary>
|
|
/// <param name="statementName"></param>
|
|
/// <param name="paramObject"></param>
|
|
/// <returns></returns>
|
|
private IDbCommand GetCommand(IDalSession session, string statementName, object paramObject)
|
|
{
|
|
IStatement statement = this._sqlMap.GetMappedStatement(statementName).Statement;
|
|
|
|
IMappedStatement mapStatement = this._sqlMap.GetMappedStatement(statementName);
|
|
|
|
RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject,session as ISqlMapSession);
|
|
|
|
mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
|
|
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = request.IDbCommand.CommandText;
|
|
//return request.IDbCommand;
|
|
return cmd;
|
|
|
|
|
|
//return request.IDbCommand;
|
|
}
|
|
|
|
/// <summary>获得DataSet
|
|
/// 获得DataSet
|
|
/// </summary>
|
|
/// <param name="statementName">语句ID</param>
|
|
/// <param name="paramObject">语句所需要的参数</param>
|
|
/// <returns>得到的DataTable</returns>
|
|
public DataSet ExecuteQueryForDataSet(string statementName, object paramObject)
|
|
{
|
|
DataSet ds = new DataSet();
|
|
|
|
bool isSessionLocal = false;
|
|
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
|
|
session.OpenConnection();
|
|
|
|
isSessionLocal = true;
|
|
}
|
|
|
|
IDbCommand cmd = this.GetCommand(session, statementName, paramObject);
|
|
|
|
//IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
//cmd.CommandText = paramObject.ToString();
|
|
|
|
|
|
cmd.CommandTimeout = 5000;
|
|
try
|
|
{
|
|
cmd.Connection = session.Connection;
|
|
|
|
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
|
|
|
|
adapter.Fill(ds);
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
}
|
|
finally
|
|
{
|
|
if (isSessionLocal)
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
|
|
/// <summary>获得DataTable
|
|
/// 获得DataTable
|
|
/// </summary>
|
|
/// <param name="statementName">语句ID</param>
|
|
/// <param name="paramObject">语句所需要的参数</param>
|
|
/// <returns>得到的DataTable</returns>
|
|
public DataTable ExecuteQueryForDataTable(string statementName, object paramObject)
|
|
{
|
|
DataTable dtResult = new DataTable();
|
|
|
|
try
|
|
{
|
|
dtResult = this.ExecuteQueryForDataSet(statementName, paramObject).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
|
|
return dtResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 通过传入SQL进行查询，返回数据集
|
|
/// </summary>
|
|
/// <param name="strSQL"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetDataSet(string strSQL)
|
|
{
|
|
DataSet dtResult = new DataSet();
|
|
|
|
try
|
|
{
|
|
dtResult = this.ExecuteQueryForDataSet("dynamicSQL", strSQL);
|
|
}
|
|
catch
|
|
{
|
|
}
|
|
|
|
return dtResult;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 通过传入SQL进行查询，返回数据集
|
|
/// </summary>
|
|
/// <param name="strSQL"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataTable(string strSQL)
|
|
{
|
|
DataTable dtResult = new DataTable();
|
|
|
|
try
|
|
{
|
|
dtResult = this.ExecuteQueryForDataTable("dynamicSQL", strSQL);
|
|
}
|
|
catch
|
|
{
|
|
}
|
|
|
|
return dtResult;
|
|
}
|
|
|
|
/// <summary>保存DataSet
|
|
/// 保存DataSet
|
|
/// </summary>
|
|
/// <param name="ds"></param>
|
|
/// <param name="statementName"></param>
|
|
/// <param name="paramObject"></param>
|
|
/// <returns></returns>
|
|
public int SaveDataSet(DataSet ds, string statementName, object paramObject)
|
|
{
|
|
int iResult = 0;
|
|
|
|
bool isSessionLocal = false;
|
|
|
|
|
|
ISqlMapSession session = this._sqlMap.LocalSession;
|
|
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
|
|
session.OpenConnection();
|
|
|
|
isSessionLocal = true;
|
|
}
|
|
|
|
IDbCommand cmd = GetCommand(session, statementName, paramObject);
|
|
// IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
|
|
try
|
|
{
|
|
cmd.Connection = session.Connection;
|
|
|
|
IDbDataAdapter adapter = session.CreateDataAdapter(cmd);
|
|
|
|
ConstructorInfo cif = _sqlMap.DataSource.DbProvider.CommandBuilderType.GetConstructor(new Type[] { adapter.GetType() });
|
|
|
|
cif.Invoke(new object[] { adapter });
|
|
|
|
|
|
//foreach (DataRow row in ds.Tables[0].Rows)
|
|
//{
|
|
|
|
//}
|
|
|
|
iResult = adapter.Update(ds);
|
|
|
|
// cmd.Cancel();
|
|
}
|
|
finally
|
|
{
|
|
if (isSessionLocal)
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
|
|
return iResult;
|
|
}
|
|
|
|
/// <summary>保存DataTable
|
|
/// 保存DataTable
|
|
/// </summary>
|
|
/// <param name="dt"></param>
|
|
/// <param name="tablename"></param>
|
|
/// <returns></returns>
|
|
public int SaveDataTable(DataTable dt, string tablename)
|
|
{
|
|
int iResult;
|
|
|
|
DataSet ds;
|
|
|
|
if (null == dt.DataSet)
|
|
{
|
|
ds = new DataSet();
|
|
|
|
ds.Tables.Add(dt);
|
|
}
|
|
else
|
|
{
|
|
ds = dt.DataSet;
|
|
}
|
|
|
|
string tmpTableName = dt.TableName;
|
|
|
|
dt.TableName = "Table";
|
|
|
|
//for (int i = 0; i < dt.Rows.Count; i++)
|
|
//{
|
|
// if (DataRowState.Added == dt.Rows[i].RowState)
|
|
// {
|
|
// //dt.Rows[i][0] = this.GetPrimaryID(tablename, dt.Columns[0].ColumnName);
|
|
// }
|
|
//}
|
|
string sStament = "dynamicSQL";
|
|
|
|
string paramObject = " select * from " + tablename;
|
|
|
|
iResult = this.SaveDataSet(ds, sStament, paramObject);
|
|
|
|
dt.TableName = tmpTableName;
|
|
|
|
ds.Tables.Remove(dt);
|
|
|
|
return iResult;
|
|
}
|
|
|
|
/// <summary>执行无结果语句
|
|
/// 执行无结果语句
|
|
/// </summary>
|
|
/// <param name="SQL"></param>
|
|
/// <param name="statementName"></param>
|
|
/// <returns></returns>
|
|
public int ExecuteNonQuery(string SQL, string statementName)
|
|
{
|
|
int iResult = 0;
|
|
|
|
bool isSessionLocal = false;
|
|
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
|
|
session.OpenConnection();
|
|
|
|
isSessionLocal = true;
|
|
}
|
|
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
|
|
try
|
|
{
|
|
cmd.CommandText = SQL;
|
|
|
|
iResult = cmd.ExecuteNonQuery();
|
|
}
|
|
finally
|
|
{
|
|
if (isSessionLocal)
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
|
|
return iResult;
|
|
}
|
|
|
|
public int GetPrimaryID(string tableName, string fieldName)
|
|
{
|
|
try
|
|
{
|
|
Hashtable ht = new Hashtable();
|
|
|
|
ht.Add("TABLENAME", tableName);
|
|
|
|
ht.Add("FIELDNAME", fieldName);
|
|
|
|
ht.Add("NEXTID", 0);
|
|
|
|
_sqlMap.QueryForList("GetTableID", ht);
|
|
|
|
return (int)ht["NEXTID"];
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw (e);
|
|
//System.Threading.Thread.Sleep(3000);
|
|
//return this.GetPrimaryID(tableName, fieldName);
|
|
}
|
|
}
|
|
|
|
public int GetPrimaryID(string tableName)
|
|
{
|
|
|
|
try
|
|
{
|
|
string sSQL = string.Format(string.Format("select {0}_SEQ.nextval from dual", tableName));
|
|
|
|
return Convert.ToInt32(this.ExecuteQueryForDataTable("dynamicSQL", sSQL).Rows[0][0]);
|
|
}
|
|
|
|
catch (Exception ex)
|
|
{
|
|
throw (ex);
|
|
}
|
|
}
|
|
|
|
/// <summary>初始化系统
|
|
/// 初始化系统
|
|
/// </summary>
|
|
public void InitSystem()
|
|
{
|
|
try
|
|
{
|
|
_sqlMap.QueryForList("InitSystem", null);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
throw (e);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 事物控制
|
|
|
|
public void BeginTransaction()
|
|
{
|
|
this._sqlMap.BeginTransaction();
|
|
}
|
|
|
|
public void BeginTransaction(bool bTrans)
|
|
{
|
|
if (!bTrans)
|
|
{
|
|
return;
|
|
}
|
|
|
|
this.BeginTransaction();
|
|
}
|
|
|
|
public void CommitTransaction()
|
|
{
|
|
this._sqlMap.CommitTransaction();
|
|
}
|
|
|
|
public void CommitTransaction(bool bTrans)
|
|
{
|
|
if (!bTrans)
|
|
{
|
|
return;
|
|
}
|
|
|
|
this.CommitTransaction();
|
|
}
|
|
|
|
public void RollBackTransaction()
|
|
{
|
|
this._sqlMap.RollBackTransaction();
|
|
}
|
|
|
|
public void RollBackTransaction(bool bTrans)
|
|
{
|
|
if (!bTrans)
|
|
{
|
|
return;
|
|
}
|
|
|
|
this.RollBackTransaction();
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Silverlight需要的基本方法
|
|
/// <summary>
|
|
/// 执行命令并返回结果数
|
|
/// </summary>
|
|
/// <param name="cmd">命令</param>
|
|
/// <returns>结果数</returns>
|
|
public int ExecuteNonQuery(string command)
|
|
{
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
session.OpenConnection();
|
|
}
|
|
try
|
|
{
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = command;
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
catch
|
|
{
|
|
return -1;
|
|
}
|
|
finally
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行命令并返回首行首列内容
|
|
/// </summary>
|
|
/// <param name="command">命令</param>
|
|
/// <returns>结果</returns>
|
|
public object ExecuteScalar(string command)
|
|
{
|
|
int result = 0;
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
session.OpenConnection();
|
|
}
|
|
try
|
|
{
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = command;
|
|
return cmd.ExecuteScalar();
|
|
}
|
|
catch
|
|
{
|
|
return result;
|
|
}
|
|
finally
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行命令并返回结果
|
|
/// </summary>
|
|
/// <param name="command">命令</param>
|
|
/// <returns>结果</returns>
|
|
public Dictionary<string, string> GetEntityAsDictionary(string command)
|
|
{
|
|
Dictionary<string, string> result = new Dictionary<string, string>();
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
session.OpenConnection();
|
|
}
|
|
try
|
|
{
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = command;
|
|
using (IDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
string[] fieldName = new string[dr.FieldCount];
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
fieldName[i] = dr.GetName(i);
|
|
}
|
|
dr.Read();
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
result.Add(fieldName[i], Convert.ToString(dr[i]));
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
catch
|
|
{
|
|
return result;
|
|
}
|
|
finally
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行命令并返回结果集
|
|
/// </summary>
|
|
/// <param name="command">命令</param>
|
|
/// <returns>结果集</returns>
|
|
public List<string[]> GetEntitiesAsStringList(string command)
|
|
{
|
|
List<string[]> result = new List<string[]>();
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
session.OpenConnection();
|
|
}
|
|
try
|
|
{
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = command;
|
|
using (IDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
while (dr.Read())
|
|
{
|
|
string[] s = new string[dr.FieldCount];
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
s[i] = Convert.ToString(dr[i]);
|
|
}
|
|
result.Add(s);
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
catch
|
|
{
|
|
return result;
|
|
}
|
|
finally
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行命令并返回结果集
|
|
/// </summary>
|
|
/// <param name="command">命令</param>
|
|
/// <returns>结果集</returns>
|
|
public List<Dictionary<string, string>> GetEntitiesAsDictionaryList(string command)
|
|
{
|
|
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
|
|
IDalSession session = _sqlMap.LocalSession;
|
|
if (session == null)
|
|
{
|
|
session = new SqlMapSession(_sqlMap);
|
|
session.OpenConnection();
|
|
}
|
|
try
|
|
{
|
|
IDbCommand cmd = session.CreateCommand(CommandType.Text);
|
|
cmd.CommandText = command;
|
|
using (IDataReader dr = cmd.ExecuteReader())
|
|
{
|
|
string[] fieldName = new string[dr.FieldCount];
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
fieldName[i] = dr.GetName(i);
|
|
}
|
|
while (dr.Read())
|
|
{
|
|
Dictionary<string, string> row = new Dictionary<string, string>();
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
row.Add(fieldName[i], Convert.ToString(dr[i]));
|
|
}
|
|
result.Add(row);
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
catch
|
|
{
|
|
return result;
|
|
}
|
|
finally
|
|
{
|
|
session.CloseConnection();
|
|
}
|
|
}
|
|
#endregion
|
|
}
|
|
}
|