/// <summary>
/// 执行存储中的 SQL 语句
/// </summary>
/// <returns>空 函数执行成功 其他 返回错误信息</returns>
public string runSQL()
{
try
{
OleDbConnection m_Conn = Conn.getConn();
OleDbTransaction m_Trans;
OleDbCommand m_Command = m_Conn.CreateCommand();
m_Trans = m_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
m_Command.Connection = m_Conn;
m_Command.Transaction = m_Trans;
try
{
foreach (string strSQL in this.m_lst_SQL)
{
m_Command.CommandText = strSQL;
m_Command.ExecuteNonQuery();
}
m_Trans.Commit();
}
catch (Exception eT)
{
m_Trans.Rollback();
// 记录错误SQL，便于分析处理
ErrorLog.addErrorLog(m_lst_SQL);
throw eT;
}
finally
{
m_Conn.Close();
}
return("");
}
catch (Exception e)
{
return(e.Message);
}
}
/// <summary>
/// 根据T-SQL获得记录集对象
/// </summary>
/// <param name="strSQL">T-SQL(SELECT)语句</param>
/// <param name="rst">OleDbDataReader记录集对象</param>
/// <returns>string 0 成功，其他为错误描述</returns>
public static string qryRst(string strSQL, ref OleDbDataReader rst)
{
OleDbConnection m_Conn = Conn.getConn();
try
{
OleDbCommand mdo = new OleDbCommand(strSQL, m_Conn);
rst = mdo.ExecuteReader();
if (rst.HasRows)
{
return("0");
}
return("1");
}
catch (Exception e)
{
return(e.Message);
}
}
/// <summary>
/// 分页查询，返回标准的查询XML
/// </summary>
/// <param name="strFieldList">查询字段列表</param>
/// <param name="strTableList">查询表列表</param>
/// <param name="strOtherClause">Where条件</param>
/// <param name="strOrdFld">排序字段 例如：PERSONID DESC</param>
/// <param name="strDicFieldList">待翻译字典字段列表 例如：SEX,NATION </param>
/// <param name="strDicNameList">待翻译字典名称列表 例如：DIC_SEX,DIC_NATIVE 注意：strDicFieldList和strDicNameList必须一一对应 </param>
/// <param name="strDateFieldList">待处理的日期型字段列表 例如：BIRTHDAY,COMEDATE</param>
/// <param name="intTotalRecords">记录总数</param>
/// <param name="intTotalPages">总页数</param>
/// <param name="intPageSize">每页记录数</param>
/// <param name="intCurrentPage">待查询的页数</param>
/// <param name="intCountTotal">是否需要统计记录总数</param>
/// <param name="blnDicInSv">字典翻译方式</param>
/// <param name="strListName">生成列表名称</param>
/// <returns>标准的查询 XML 返回结构的字符串</returns>
public static string basicListQuery(string strFieldList,
string strTableList,
string strOtherClause,
string strOrdFld,
string[] strDicFieldList,
string[] strDicNameList,
string[] strDateFieldList,
int intTotalRecords,
int intTotalPages,
int intPageSize,
int intCurrentPage,
int intCountTotal,
Boolean blnDicInSv,
String strListName)
{
OleDbConnection m_Conn = Conn.getConn();
try
{
string str_FieldList = strFieldList.Replace(Common.MARK, Common.QUOTE);
string str_TableList = strTableList.Replace(Common.MARK, Common.QUOTE);
string str_OtherClause = null;
string str_OrdFld = null;
if (!General.empty(strOtherClause))
{
str_OtherClause = strOtherClause.Replace(Common.MARK, Common.QUOTE);
}
if (!General.empty(strOrdFld))
{
str_OrdFld = strOrdFld.Replace(Common.MARK, Common.QUOTE);
}
int int_TotalRecords = intTotalRecords;
int int_TotalPages = intTotalPages;
OleDbDataReader rst = null;
OleDbCommand m_Command = new OleDbCommand("QueryPagination", m_Conn);
m_Command.CommandType = CommandType.StoredProcedure;
OleDbParameter param;
param = m_Command.Parameters.Add("t", OleDbType.VarChar, 1000);
param.Direction = ParameterDirection.Input;
param.Value = "";
param = m_Command.Parameters.Add("strFieldList", OleDbType.VarChar, 1000);
param.Direction = ParameterDirection.Input;
param.Value = str_FieldList;
param = m_Command.Parameters.Add("strTableList", OleDbType.VarChar, 300);
param.Direction = ParameterDirection.Input;
param.Value = str_TableList;
param = m_Command.Parameters.Add("strWhereClause", OleDbType.VarChar, 1000);
param.Direction = ParameterDirection.Input;
param.Value = str_OtherClause;
param = m_Command.Parameters.Add("strOrderFld", OleDbType.VarChar, 100);
param.Direction = ParameterDirection.Input;
param.Value = str_OrdFld;
param = m_Command.Parameters.Add("intCurrentPage", OleDbType.Integer, 4);
param.Direction = ParameterDirection.Input;
param.Value = intCurrentPage;
param = m_Command.Parameters.Add("intPageSize", OleDbType.Integer, 4);
param.Direction = ParameterDirection.Input;
param.Value = intPageSize;
param = m_Command.Parameters.Add("intCountToto", OleDbType.Integer, 4);
param.Direction = ParameterDirection.Input;
param.Value = intCountTotal;
param = m_Command.Parameters.Add("intTotoRecords", OleDbType.Integer, 10);
param.Direction = ParameterDirection.Output;
param = m_Command.Parameters.Add("intTotoPages", OleDbType.Integer, 10);
param.Direction = ParameterDirection.Output;
m_Command.ExecuteNonQuery();
int_TotalRecords = Convert.ToInt32(m_Command.Parameters["intTotoRecords"].Value);
int_TotalPages = Convert.ToInt32(m_Command.Parameters["intTotoPages"].Value);
rst = m_Command.ExecuteReader();
string strReturnXml = getQueryXml(rst,
strDicFieldList,
strDicNameList,
strDateFieldList,
int_TotalRecords,
int_TotalPages,
blnDicInSv,
strListName);
rst.Close();
return(strReturnXml);
}
catch (Exception e)
{
return(e.Message);
}
finally
{
m_Conn.Close();
}
}