public static bool TryCallWCFFunc(string OPNameStr, ReqeFunStruct requestObj, out RespFunStruct responseObj)
{
UserIDOP userOP = new UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = OPNameStr };
requestObj.userIDOP = userOP;
requestObj.PCClientCookieStr = GlobalStaticObj.CookieStr;
RespFunStruct returnObj = new RespFunStruct();
string requestStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(requestObj));
if (!WCFClientProxy.TestPCClientProxy())
{
returnObj.IsSuccess = "0";
returnObj.ReturnObject = "未能建立同服务器连接";
responseObj = returnObj;
MessageBox.Show("未能建立同服务器连接！");
return false;
}
try
{
string ResultStr = GlobalStaticObj.proxy.JsonOperate(requestStr);
string Str = HXCCommon.DotNetEncrypt.DESEncrypt.Decrypt(ResultStr);
responseObj = JsonConvert.DeserializeObject<RespFunStruct>(Str);
return true;
}
catch (Exception ex)
{
returnObj.IsSuccess = "0";
returnObj.ReturnObject = "发生错误";
returnObj.Msg = ex.Message;
responseObj = returnObj;
MessageBox.Show("发生错误！");
return false;
}
}
/// <summary>
/// 调用分页存储过程返回得到的数据集
/// </summary>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="tableSource"></param>
/// <param name="whereValue"></param>
/// <param name="OrderExpression"></param>
/// <param name="Fields"></param>
/// <returns></returns>
public static DataSet GetListPages(string connString, int page, int pageSize, string tableSource, string whereValue, string OrderExpression, string Fields, UserIDOP userID, ref int Counts)
{
SqlParameter[] parm = new SqlParameter[]
{
new SqlParameter("@TableSource", SqlDbType.VarChar, 1000),
new SqlParameter("@SelectList", SqlDbType.VarChar, 2000),
new SqlParameter("@SearchCondition", SqlDbType.VarChar, 4000),
new SqlParameter("@OrderExpression", SqlDbType.VarChar, 100),
new SqlParameter("@PageIndex", SqlDbType.Int, 4),
new SqlParameter("@PageSize", SqlDbType.Int, 4),
new SqlParameter("@Counts", SqlDbType.Int, 4)
};
parm[0].Value = tableSource;
parm[1].Value = Fields;
parm[2].Value = whereValue;
parm[3].Value = OrderExpression;
parm[4].Value = page;
parm[5].Value = pageSize;
parm[parm.Length - 1].Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
ds = DBUtility.SqlHelper.ExecuteDataSet(connString, CommandType.StoredProcedure, "[dbo].[list_page_tables]", userID, parm);
Counts = Convert.ToInt32(parm[parm.Length - 1].Value);
return ds;
}
/// <summary> 日志的更新操作，千万不要再次进行写日志操作
/// </summary>
public static int UpdateByHashtableLog(UserIDOP userID, string connString, string tableName, string pkName, string pkVal, Dictionary<string, string> ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(tableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@" + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@" + key);
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append("@" + pkName).Append(";"); ;
ht[pkName] = pkVal;
SqlParameter[] _params = SqlHelper.GetParameter(ht);
object _object = SqlHelper.ExecuteNonQueryNoBak(connString, CommandType.Text, sb.ToString(), userID, _params);
return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
}
/// <summary> 查询单个字段的值(一般求最大，最小值)
/// <param name="opName">操作名</param>
/// <param name="tableName">表名</param>
/// <param name="field">单个查询字段</param>
/// <param name="orderBy">排序字段，多个以“,”分割</param>
/// </summary>
/// <returns>返回Value</returns>
public static string GetSingleValue(string opName, string currAccDbName, string tableName, string field, string where, string orderBy)
{
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append("select " + field);
if (!string.IsNullOrEmpty(tableName))
{
strBuilder.Append(" from " + tableName);
if (!string.IsNullOrEmpty(where))
{
strBuilder.Append(" where " + where);
}
if (!string.IsNullOrEmpty(orderBy))
{
strBuilder.Append(" order by " + orderBy);
}
}
Dictionary<string, ParamObj> dic = new Dictionary<string, ParamObj>();
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj_Server.Instance.UserID, OPName = opName };
string connString = LocalVariable.GetConnString(currAccDbName, ConfigConst.ConnectionStringReadonly);
object obj = DbCommon.Instance.ExecuteNonQueryReturnObjectNoTrans(connString, CommandType.Text, strBuilder.ToString(), dic, userOp);
if (obj == null)
{
return "";
}
return obj.ToString();
}
/// <summary> 查询单个字段的值(一般求最大，最小值)
/// <param name="strSql">sql语句</param>
/// </summary>
/// <returns>返回Value</returns>
public static string GetSingleValue(string opName, string currAccDbName, string strSql)
{
Dictionary<string, ParamObj> dic = new Dictionary<string, ParamObj>();
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj_Server.Instance.UserID, OPName = opName };
string connString = LocalVariable.GetConnString(currAccDbName, ConfigConst.ConnectionStringReadonly);
object obj = DbCommon.Instance.ExecuteNonQueryReturnObjectNoTrans(connString, CommandType.Text, strSql, dic, userOp);
if (obj == null)
{
return "";
}
return obj.ToString();
}
/// <summary> 日志的更新操作，千万不要再次进行写日志操作
/// </summary>
public static bool Submit_AddOrEditLog(UserIDOP userID, string connString, string tableName, string pkName, string pkVal, Dictionary<string, string> ht)
{
if (string.IsNullOrEmpty(pkVal))
{
if (SqlHelper.InsertByHashtableLog(userID, connString, tableName, ht) > 0)
return true;
else
return false;
}
else
{
if (SqlHelper.UpdateByHashtableLog(userID, connString, tableName, pkName, pkVal, ht) > 0)
return true;
else
return false;
}
}
/// <summary> 日志的更新操作，千万不要再次进行写日志操作
/// </summary>
public static int InsertByHashtableLog(UserIDOP userID, string connString, string tableName, Dictionary<string, string> ht)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(tableName);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (string key in ht.Keys)
{
sb_prame.Append("," + key);
sp.Append(",@" + key);
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";");
int _object = SqlHelper.ExecuteNonQueryNoBak(connString, CommandType.Text, sb.ToString(), userID, SqlHelper.GetParameter(ht));
return _object;
}
/// <summary> 新增或更新
/// </summary>
/// <param name="opName">操作名</param>
/// <param name="accCode">帐套代码</param>
/// <param name="tableName">表名</param>
/// <param name="keyName">主键名称</param>
/// <param name="keyValue">主键值,如果有值，表示更新记录</param>
/// <param name="dicFileds">字段集合</param>
/// <returns>返回执行是否成功</returns>
public static bool Submit_AddOrEdit(string opName, string accCode, string tableName, string keyName, string keyValue, Dictionary<string, string> dicFileds)
{
ReqeFunStruct reqeFunStruct = new ReqeFunStruct();
reqeFunStruct.SubSysName = SubSysName.CommonBaseFuncCall;
reqeFunStruct.FunName = ComFunCallEnum.Submit_AddOrEdit;
reqeFunStruct.AccCode = accCode;
Submit_AddOrEdit submit_AddOrEdit = new Submit_AddOrEdit();
submit_AddOrEdit.TableName = string.Format("{0}", tableName);
submit_AddOrEdit.DicParam = dicFileds;
submit_AddOrEdit.pkName = keyName;//默认第一个
submit_AddOrEdit.pkVal = keyValue;
reqeFunStruct.FunObject = submit_AddOrEdit;
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = opName };
reqeFunStruct.userIDOP = userOp;
reqeFunStruct.PCClientCookieStr = GlobalStaticObj.CookieStr;
var addStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(reqeFunStruct));
var respO = JsonOperate(addStr);
if (respO.IsSuccess == "0")
{
return false;
}
return bool.Parse(respO.ReturnObject);
}
/// <summary> 删除 BYID
/// </summary>
/// <param name="opName">操作名</param>
/// <param name="accCode">帐套代码</param>
/// <param name="tableName">表名</param>
/// <param name="keyName">键名称</param>
/// <param name="keyValue">键值</param>
/// <returns></returns>
public static bool DeleteDataByID(string opName, string accCode, string tableName, string keyName, string keyValue)
{
ReqeFunStruct reqeFunStruct = new ReqeFunStruct();
reqeFunStruct.SubSysName = SubSysName.CommonBaseFuncCall;
reqeFunStruct.FunName = ComFunCallEnum.DeleteDataByID;
reqeFunStruct.AccCode = accCode;
DeleteDataByID DeleteDataByID = new DeleteDataByID();
DeleteDataByID.TableName = string.Format("{0}", tableName);
DeleteDataByID.pkName = keyName;//默认第一个
DeleteDataByID.pkVal = keyValue;
reqeFunStruct.FunObject = DeleteDataByID;
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = opName };
reqeFunStruct.userIDOP = userOp;
reqeFunStruct.PCClientCookieStr = GlobalStaticObj.CookieStr;
var addStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(reqeFunStruct));
var respO = JsonOperate(addStr);
if (respO.IsSuccess == "0")
{
return false;
}
return bool.Parse(respO.ReturnObject);
}
public static void LogFunctionCall(string fun_id, string setbook_id)
{
ReqeFunStruct reqeFunStruct = new ReqeFunStruct();
reqeFunStruct.SubSysName = SubSysName.ExtendBaseFuncCall;
reqeFunStruct.FunName = ComFunCallEnum.LogFunctionCall;
reqeFunStruct.AccCode = GlobalStaticObj.CurrAccCode;
UserFunctionOPLog FunctionOP_Add = new UserFunctionOPLog();
FunctionOP_Add.com_id = GlobalStaticObj.CurrUserCom_Id;
FunctionOP_Add.fun_id = fun_id;
FunctionOP_Add.access_time = Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime);
FunctionOP_Add.userOP = new SYSModel.UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = "菜单功能操作日志" };
reqeFunStruct.FunObject = FunctionOP_Add;
UserIDOP userOp = new SYSModel.UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = "菜单功能操作日志" };
reqeFunStruct.userIDOP = userOp;
reqeFunStruct.PCClientCookieStr = GlobalStaticObj.CookieStr;
string addStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(reqeFunStruct));
if (!WCFClientProxy.TestDataProxy())
{
throw new Exception("未能建立同服务器连接！");
}
GlobalStaticObj.proxy.JsonOperate(addStr);
}
/// <summary> 查询单个字段的值(一般求最大，最小值)
/// <param name="opName">操作名</param>
/// <param name="accCode">帐套代码</param>
/// <param name="tableName">表名</param>
/// <param name="field">单个查询字段</param>
/// <param name="orderBy">排序字段，多个以“,”分割</param>
/// </summary>
/// <returns>返回Value</returns>
public static string GetSingleValue(string opName, string accCode, string tableName, string field, string where, string orderBy)
{
ReqeFunStruct reqeFunStruct = new ReqeFunStruct();
reqeFunStruct.SubSysName = SubSysName.CommonBaseFuncCall;
reqeFunStruct.FunName = ComFunCallEnum.ExecuteNonQueryReturnObjectNoTrans;
reqeFunStruct.AccCode = accCode;
SQLObj sqlObj = new SQLObj();
sqlObj.cmdType = CommandType.Text;
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append("select " + field);
if (!string.IsNullOrEmpty(tableName))
{
strBuilder.Append(" from " + tableName);
if (!string.IsNullOrEmpty(where))
{
strBuilder.Append(" where " + where);
}
if (!string.IsNullOrEmpty(orderBy))
{
strBuilder.Append(" order by " + orderBy);
}
}
sqlObj.sqlString = strBuilder.ToString();
sqlObj.Param = new Dictionary<string, ParamObj>();
ExecuteNonQueryReturnDataSetNoTrans executeNonQueryReturnDataSetNoTrans = new ExecuteNonQueryReturnDataSetNoTrans();
executeNonQueryReturnDataSetNoTrans.ReadOnlyFlag = true;
executeNonQueryReturnDataSetNoTrans.sqlObj = sqlObj;
reqeFunStruct.FunObject = executeNonQueryReturnDataSetNoTrans;
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = opName };
reqeFunStruct.userIDOP = userOp;
reqeFunStruct.PCClientCookieStr = GlobalStaticObj.CookieStr;
var addStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(reqeFunStruct));
var respO = JsonOperate(addStr);
if (respO.IsSuccess == "0")
{
return "";
}
return respO.ReturnObject;
}
public int IsExist(string connStr, string tableName, string pkName, string pkVal, UserIDOP userID)
{
return DBUtility.SqlHelper.IsExist(connStr, tableName, pkName, pkVal, userID);
}
/// <summary> 批量删除 - where
/// </summary>
public static int BatchDeleteDataByWhere(string connStr, string tableName, string whereString, UserIDOP userID)
{
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where " + whereString).Append(";");
return SqlHelper.ExecuteNonQueryTran(connStr, CommandType.Text, sb.ToString(), userID, null);
}
/// <summary> 批量更新- 多条语句-事务
/// </summary
public static int BatchUpdateDataMultiByTrans(string connStr, IList<UpdateSQLObj> batUpdateList, UserIDOP userID)
{
StringBuilder sb = new StringBuilder();
List<SqlParameter> paramList = new List<SqlParameter>();
string str = string.Empty;
int index = 0;
foreach (UpdateSQLObj sqlObj in batUpdateList)
{
sb.Append(" Update ");
sb.Append(sqlObj.TableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in sqlObj.DicParam.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@Param" + index.ToString());
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@Param" + index.ToString());
}
paramList.Add(new SqlParameter("@Param" + index.ToString(), sqlObj.DicParam[key]));
index++;
}
if (sqlObj.pkName != null)
{
sb.Append(" Where ").Append(sqlObj.pkName).Append("=").Append("@Param" + index.ToString()).Append(";");
paramList.Add(new SqlParameter("@Param" + index.ToString(), sqlObj.pkVal));
index++;
}
else if (!string.IsNullOrEmpty(sqlObj.whereString))
{
sb.Append(" Where ").Append(sqlObj.whereString).Append(";");
}
}
object _object = SqlHelper.ExecuteNonQueryTran(connStr, CommandType.Text, sb.ToString(), userID, paramList.ToArray());
return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
}
public DataTable GetDataTable(string connStr,string tableName, UserIDOP userID)
{
return DBUtility.SqlHelper.GetDataTable(connStr, tableName, userID);
}
/// <summary> 批量更新- where
/// </summary>
public static int BatchUpdateDataByWhere(string connStr, string tableName, Dictionary<string, string> ht, string whereString, UserIDOP userID)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(tableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@" + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@" + key);
}
}
sb.Append(" Where ").Append(whereString).Append(";"); ;
SqlParameter[] _params = SqlHelper.GetParameter(ht);
object _object = SqlHelper.ExecuteNonQueryTran(connStr, CommandType.Text, sb.ToString(), userID, _params);
return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
}
/// <summary> 批量更新- in
/// </summary>
public static int BatchUpdateDataByIn(string connStr, string tableName, Dictionary<string, string> ht, string pkName, object[] pkValues, UserIDOP userID)
{
StringBuilder sb = new StringBuilder();
SqlParameter[] param = new SqlParameter[ht.Count + pkValues.Length];
int index = 0;
string str = string.Empty;
sb.Append(" Update ");
sb.Append(tableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@" + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@" + key);
}
param[index] = new SqlParameter("@" + key, ht[key]);
index++;
}
sb.Append(" Where ").Append(pkName).Append(" IN (");
for (int i = 0; i < (pkValues.Length); i++)
{
object obj2 = pkValues[i];
str = "@" + pkName + index;
sb.Append(str).Append(",");
param[index] = new SqlParameter(str, obj2);
index++;
}
string sqlStr = sb.ToString().TrimEnd(',');
sqlStr += " ); ";
object _object = SqlHelper.ExecuteNonQueryTran(connStr, CommandType.Text, sqlStr, userID, param);
return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
}
/// <summary> 通过Hashtable修改数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkValue"></param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static bool UpdateByHashtable(string connString, string tableName, string pkName, string pkVal, Dictionary<string, string> ht, UserIDOP userID)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Update ");
sb.Append(tableName);
sb.Append(" Set ");
bool isFirstValue = true;
foreach (string key in ht.Keys)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(key);
sb.Append("=");
sb.Append("@" + key);
}
else
{
sb.Append("," + key);
sb.Append("=");
sb.Append("@" + key);
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append("@" + pkName).Append(";");
ht[pkName] = pkVal;
SqlParameter[] myParamArray = GetParameter(ht);
return SqlHelper.ExecuteNonQueryReturnBool(connString, CommandType.Text, sb.ToString(), userID, myParamArray);
}
/// <summary> 表单提交：新增，修改
/// 参数：
/// tableName:表名
/// pkName：字段主键
/// pkVal：字段值
/// ht：参数
/// </summary>
/// <returns></returns>
public static bool Submit_AddOrEdit(string connString, string tableName, string pkName, string pkVal, Dictionary<string, string> ht, UserIDOP userID)
{
if (string.IsNullOrEmpty(pkVal))
{
return SqlHelper.InsertByHashtable(connString, tableName, ht, userID);
}
else
{
return SqlHelper.UpdateByHashtable(connString, tableName, pkName, pkVal, ht, userID);
}
}
/// <summary> 批量删除 - object[]
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static int BatchDeleteData(string connString, string tableName, string pkName, object[] pkValues, UserIDOP userID)
{
SqlParameter[] param = new SqlParameter[pkValues.Length];
int index = 0;
string str = "@ID" + index;
StringBuilder sql = new StringBuilder("DELETE FROM " + tableName + " WHERE " + pkName + " IN (");
for (int i = 0; i < (param.Length - 1); i++)
{
object obj2 = pkValues[i];
str = "@ID" + index;
sql.Append(str).Append(",");
param[index] = new SqlParameter(str, obj2);
index++;
}
str = "@ID" + index;
sql.Append(str);
param[index] = new SqlParameter(str, pkValues[index]);
sql.Append(" );");
return SqlHelper.ExecuteNonQueryTran(connString, CommandType.Text, sql.ToString(), userID, param);
}
public bool Submit_AddOrEdit(string connStr, string tableName, string pkName, string pkVal, Dictionary<string, string> ht,UserIDOP userID)
{
return DBUtility.SqlHelper.Submit_AddOrEdit(connStr, tableName, pkName, pkVal, ht, userID);
}
public bool EnableSQLObj(string connStr, string sql, Dictionary<string, ParamObj> dic, UserIDOP userID)
{
SqlParameter[] commandParameters = DBUtility.SqlHelper.GetParameter(dic);
return DBUtility.SqlHelper.ExecuteNonQueryReturnBool(connStr, CommandType.Text, sql, userID, commandParameters);
}
public int DeleteData(string connStr, string tableName, string pkName, string pkVal, UserIDOP userID)
{
return DBUtility.SqlHelper.DeleteData(connStr, tableName, pkName, pkVal, userID);
}
/// <summary> 通过Dictionary插入数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="ht">Hashtable</param>
/// <returns>int</returns>
public static bool InsertByHashtable(string connString, string tableName, Dictionary<string, string> ht, UserIDOP userID)
{
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(tableName);
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
foreach (string key in ht.Keys)
{
sb_prame.Append("," + key);
sp.Append(",@" + key);
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";"); ;
SqlParameter[] myParamArray = GetParameter(ht);
int rows = SqlHelper.ExecuteNonQuery(connString, CommandType.Text, sb.ToString(), userID, myParamArray);
return rows > 0;
}
public Dictionary<string, string> GetHashtableById(string connStr, string tableName, string pkName, string pkVal, UserIDOP userID)
{
return DBUtility.SqlHelper.GetHashtableById(connStr, tableName, pkName, pkVal, userID);
}
/// <summary> 根据通用的分页存储过程，分页查询
/// </summary>
/// <param name="opName">操作名</param>
/// <param name="tableName">表名</param>
/// <param name="fileds">查询字段集合，以“,”分割</param>
/// <param name="where">查询条件，不包含where</param>
/// <param name="groupBy">分组字段，多个以“,”分割</param>
/// <param name="orderBy">排序字段+排序方式，多个以“,”分割</param>
/// <param name="pageIndex">查询第几页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="recordCount">返回总记录数</param>
/// <returns>返回结果集</returns>
public static DataTable GetTableByPage(string opName, string tableName, string fileds, string where, string groupBy, string orderBy, int pageIndex, int pageSize, out int recordCount)
{
try
{
DataSet ds = new DataSet();
recordCount = 0;
ReqeFunStruct reqeFunStruct = new ReqeFunStruct();
reqeFunStruct.SubSysName = SubSysName.CommonBaseFuncCall;
reqeFunStruct.FunName = ComFunCallEnum.SelOPByStoreProcePage;
reqeFunStruct.AccCode = GlobalStaticObj.CurrAccCode;
SelOPByStoreProcePage procePageInfo = new SelOPByStoreProcePage();
procePageInfo.TableSource = string.Format("{0}", tableName);
procePageInfo.PageIndex = pageIndex;
procePageInfo.PageSize = pageSize;
procePageInfo.Fields = fileds;
//检查是否包含order by 关键字
if (orderBy.Replace(" ", "").ToLower().IndexOf("orderby") < 0)
{
orderBy = "order by " + orderBy;
}
procePageInfo.OrderExpression = orderBy;
procePageInfo.whereString = where;
reqeFunStruct.FunObject = procePageInfo;
UserIDOP userOp = new UserIDOP() { UserID = GlobalStaticObj.UserID, OPName = opName };
reqeFunStruct.userIDOP = userOp;
//reqeFunStruct.userIDOP.UserID = GlobalStaticObj.UserID;
//reqeFunStruct.userIDOP.OPName = opName;
reqeFunStruct.PCClientCookieStr = GlobalStaticObj.CookieStr;
var addStr = HXCCommon.DotNetEncrypt.DESEncrypt.Encrypt(Newtonsoft.Json.JsonConvert.SerializeObject(reqeFunStruct));
var respO = JsonOperate(addStr);
if (respO.IsSuccess == "0")
{
throw new Exception(respO.Msg);
}
if (respO.ReturnObject == null)
{
throw new Exception("返回数据异常:返回数据对象为空！");
}
else
{
ds = JsonConvert.DeserializeObject<DataSet>(respO.ReturnObject);
if (ds == null || ds.Tables.Count == 0)
{
throw new Exception("查询异常:数据表不存在！");
}
recordCount = respO.RecordCount;
}
return ds.Tables[0];
}
catch (Exception ex)
{
JsonOperateLogService.WriteLog(ex);
recordCount = 0;
return null;
}
}
public int GetMaxNum(string connStr, string tableName, string pkName, UserIDOP userID)
{
return DBUtility.SqlHelper.GetMaxNum(connStr, tableName, pkName, userID);
}
/// <summary> 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">字段主键</param>
/// <param name="pkVal">字段值</param>
/// <returns></returns>
public static int DeleteData(string connString, string tableName, string pkName, string pkVal, UserIDOP userID)
{
StringBuilder sb = new StringBuilder("Delete From " + tableName + " Where " + pkName + " = @ID").Append(";");
return SqlHelper.ExecuteNonQueryTran(connString, CommandType.Text, sb.ToString(), userID, new SqlParameter[] { new SqlParameter("@ID", pkVal) });
}
/// <summary> 批量操作- 多条语句
/// </summary
public static bool BatchExeSQLMultiByTrans(string connString, IList<SQLObj> batSQLObjList, UserIDOP userID)
{
bool result = false;
StringBuilder sb = new StringBuilder();
SqlParameter[] _params = null;
foreach (SQLObj sqlObj in batSQLObjList)
{
_params = null;
_params = SqlHelper.GetParameter(sqlObj.Param);
string modifyCmdText = string.Empty;
bool modifyFlag = SQLHelper_UpdateBackup.TryUpdateSQLBackup(connString, sqlObj.sqlString, out modifyCmdText, _params);
if (modifyFlag)
{
sqlObj.sqlString = modifyCmdText;
}
}
using (SqlConnection conn = new SqlConnection(connString))
{
SqlTransaction trans = null;
try
{
Open(conn);
trans = conn.BeginTransaction();
foreach (SQLObj sqlObj in batSQLObjList)
{
_params = SqlHelper.GetParameter(sqlObj.Param);
SqlHelper.ExecuteNonQueryTranNoBak(conn, trans, sqlObj.cmdType, sqlObj.sqlString, userID, _params);
}
trans.Commit();
result = true;
}
catch (Exception ex)
{
trans.Rollback();
result = false;
throw ex;
}
}
return result;
}
/// <summary> 返回执行结果;无备份操作记录功能,无SQL日志操作记录功能带事务,一批语句全部执行成功，则返回True。否则，返回为False;
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="batSQLStringList">SQ语句IList数组,每一项为一条SQL语句</param>
/// <param name="userID">UserIDOP对象</param>
/// <returns>一批SQL语句带事务的执行结果，成功Or失败</returns>
public static bool BatchExeSQLStrMultiByTransNoLogNoBackup(string connStr, IList<SysSQLString> batSQLStringList, UserIDOP userID)
{
bool result = false;
using (SqlConnection conn = new SqlConnection(connStr))
{
foreach (SysSQLString sqlString in batSQLStringList)
{
SqlParameter[] paras = GetParameter(sqlString.Param);
ExecuteNonQueryNoBak(connStr, sqlString.cmdType, sqlString.sqlString, userID, paras);
}
result = true;
}
return result;
}