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.
332 lines
14 KiB
332 lines
14 KiB
using NFine.Code;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Data.SqlClient;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Data.Extensions
|
|
{
|
|
public class ERPSqlServerHelper
|
|
{
|
|
#region [ 连接串相关 ]
|
|
/// <summary>
|
|
/// 数据中心DB的连接字符串
|
|
/// </summary>
|
|
public static string ConnectionString = FromMd5( ConfigurationManager.ConnectionStrings["ERPconnstr"].ConnectionString);
|
|
/// <summary>
|
|
/// 获取同步服务器的连接
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
|
|
#region 字符串加解密
|
|
/// <summary>
|
|
/// MD5加密
|
|
/// </summary>
|
|
/// <param name="str"></param>
|
|
/// <returns></returns>
|
|
public static string ToMd5(string str)
|
|
{
|
|
return Encrypt(str, "&%#@?,:*_");
|
|
}
|
|
/// <summary>
|
|
/// MD5解密
|
|
/// </summary>
|
|
/// <param name="str"></param>
|
|
/// <returns></returns>
|
|
public static string FromMd5(string str)
|
|
{
|
|
//return str;
|
|
return Decrypt(str, "&%#@?,:*_");
|
|
}
|
|
/// <summary>
|
|
/// 加密
|
|
/// </summary>
|
|
/// <param name="strText"></param>
|
|
/// <param name="strEncrKey"></param>
|
|
/// <returns></returns>
|
|
private static String Encrypt(String strText, String strEncrKey)
|
|
{
|
|
Byte[] byKey = { };
|
|
Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
|
|
try
|
|
{
|
|
byKey = System.Text.Encoding.UTF8.GetBytes(strEncrKey.Substring(0, 8));
|
|
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
|
|
Byte[] inputByteArray = Encoding.UTF8.GetBytes(strText);
|
|
MemoryStream ms = new MemoryStream();
|
|
CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(byKey, IV),
|
|
CryptoStreamMode.Write);
|
|
cs.Write(inputByteArray, 0, inputByteArray.Length);
|
|
cs.FlushFinalBlock();
|
|
return Convert.ToBase64String(ms.ToArray());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 解密
|
|
/// </summary>
|
|
/// <param name="strText"></param>
|
|
/// <param name="sDecrKey"></param>
|
|
/// <returns></returns>
|
|
private static String Decrypt(String strText, String sDecrKey)
|
|
{
|
|
Byte[] byKey = { };
|
|
Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
|
|
Byte[] inputByteArray = new byte[strText.Length];
|
|
try
|
|
{
|
|
byKey = System.Text.Encoding.UTF8.GetBytes(sDecrKey.Substring(0, 8));
|
|
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
|
|
inputByteArray = Convert.FromBase64String(strText);
|
|
MemoryStream ms = new MemoryStream();
|
|
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV),
|
|
CryptoStreamMode.Write);
|
|
cs.Write(inputByteArray, 0, inputByteArray.Length);
|
|
cs.FlushFinalBlock();
|
|
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
|
|
return encoding.GetString(ms.ToArray());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
}
|
|
}
|
|
#endregion
|
|
private static SqlConnection GetConnectionString()
|
|
{
|
|
return new SqlConnection(ConnectionString);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据连接串获取连接
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
private static SqlConnection GetConnByString(string conn)
|
|
{
|
|
return new SqlConnection(conn);
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region 数据分页
|
|
/// <summary>
|
|
/// 摘要:
|
|
/// 数据分页
|
|
/// 参数：
|
|
/// sql：传入要执行sql语句
|
|
/// param：参数化
|
|
/// orderField：排序字段
|
|
/// orderType：排序类型
|
|
/// pageIndex：当前页
|
|
/// pageSize：页大小
|
|
/// count：返回查询条数
|
|
/// </summary>
|
|
public static DataTable GetPageTable(string sql, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
string DataActionsql = GetDataActionSql();
|
|
sql += " " + DataActionsql;
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = "";
|
|
if (!string.IsNullOrEmpty(orderField))
|
|
OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
else
|
|
OrderBy = "order by (select 0)";
|
|
strSql.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
strSql.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
|
|
count = Convert.ToInt32(ExecuteScalar(CommandType.Text, "Select Count(1) From (" + sql + ") As t", param));
|
|
IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
return DatabaseReader.ReaderToDataTable(dr);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataTable
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableBySql(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = GetConnectionString())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr != null && spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
return dt;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 使用提供的参数，执行有结果集返回的数据库操作命令、并返回SqlDataReader对象
|
|
/// </summary>
|
|
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
|
|
/// <param name="commandText">存储过程名称或者T-SQL命令行<</param>
|
|
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
|
|
/// <returns>返回SqlDataReader对象</returns>
|
|
private static IDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] parameters)
|
|
{
|
|
DbCommand cmd = DbFactory.CreateDbCommand();
|
|
DbConnection conn = DbFactory.CreateDbConnection(ConnectionString);
|
|
try
|
|
{
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);
|
|
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
cmd.Parameters.Clear();
|
|
return rdr;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
conn.Close();
|
|
cmd.Dispose();
|
|
//log.Error(ex.Message);
|
|
throw;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 依靠数据库连接字符串connectionString,
|
|
/// 使用所提供参数，执行返回首行首列命令
|
|
/// </summary>
|
|
/// <param name="commandType">执行命令的类型（存储过程或T-SQL，等等）</param>
|
|
/// <param name="commandText">存储过程名称或者T-SQL命令行</param>
|
|
/// <param name="parameters">执行命令所需的sql语句对应参数</param>
|
|
/// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
|
|
private static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] parameters)
|
|
{
|
|
try
|
|
{
|
|
DbCommand cmd = DbFactory.CreateDbCommand();
|
|
using (DbConnection connection = DbFactory.CreateDbConnection(ConnectionString))
|
|
{
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);
|
|
object val = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error(ex.Message);
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 为即将执行准备一个命令
|
|
/// </summary>
|
|
/// <param name="cmd">SqlCommand对象</param>
|
|
/// <param name="conn">SqlConnection对象</param>
|
|
/// <param name="isOpenTrans">DbTransaction对象</param>
|
|
/// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
|
|
/// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>
|
|
/// <param name="cmdParms">SqlParameters to use in the command</param>
|
|
private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction isOpenTrans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
|
|
{
|
|
if (conn.State != ConnectionState.Open)
|
|
conn.Open();
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = cmdText;
|
|
if (isOpenTrans != null)
|
|
cmd.Transaction = isOpenTrans;
|
|
cmd.CommandType = cmdType;
|
|
if (cmdParms != null)
|
|
{
|
|
cmd.Parameters.AddRange(cmdParms);
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
public static string GetDataActionSql()
|
|
{
|
|
OperatorModel oo = NFine.Code.OperatorProvider.Provider.GetCurrent();
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string sqlstr = string.Empty;
|
|
try
|
|
{
|
|
string sql = @" select B.DataActionId from Sys_SRM_User A
|
|
INNER JOIN Sys_RoleDataPower B ON B.RoleId=A.F_RoleId
|
|
where F_Account='{0}' and F_Location='{1}'";
|
|
sql = string.Format(sql, MUSER, WorkPoint);
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
if (dt.Rows.Count == 0)
|
|
{
|
|
sqlstr = "";
|
|
}
|
|
else
|
|
{
|
|
string DataActionID = "";
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
DataActionID += "'" + dr["DataActionId"].ToString() + "',";
|
|
}
|
|
sql = @" SELECT CCaption 条件名称, CValueBegin 起始值,CValueEnd 结束值
|
|
from Sys_FormDataAction con
|
|
where con.ID in ({0})";
|
|
sql = string.Format(sql, DataActionID.TrimEnd(','));
|
|
dt = SqlHelper.GetDataTableBySql(sql);
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
if (dr["起始值"].ToString() != "" && dr["结束值"].ToString() != "")
|
|
{
|
|
sqlstr += " and " + dr["条件名称"].ToString() + " >= '" + dr["起始值"].ToString() + @"'
|
|
and " + dr["条件名称"].ToString() + " <= '" + dr["结束值"].ToString() + "'";
|
|
}
|
|
else if (dr["起始值"].ToString() == "" && dr["结束值"].ToString() == "")
|
|
{
|
|
continue;
|
|
}
|
|
else
|
|
{
|
|
if (dr["起始值"].ToString() != "")
|
|
{
|
|
sqlstr += " and " + dr["条件名称"].ToString() + " = '" + dr["起始值"].ToString() + "'";
|
|
}
|
|
else
|
|
{
|
|
sqlstr += " and " + dr["条件名称"].ToString() + " = '" + dr["结束值"].ToString() + "'";
|
|
}
|
|
}
|
|
if (dr["起始值"].ToString().Contains("[AppConfig.WorkPointCode]") || dr["起始值"].ToString().Contains("[AppConfig.UserId]")
|
|
|| dr["起始值"].ToString().Contains("[AppConfig.UserCode]") || dr["起始值"].ToString().Contains("[AppConfig.UserName]")
|
|
|| dr["起始值"].ToString().Contains("[AppConfig.RoleCode]"))
|
|
{
|
|
sqlstr = sqlstr.Replace("[AppConfig.WorkPointCode]", oo.Location);
|
|
sqlstr = sqlstr.Replace("[AppConfig.UserId]", oo.UserId);
|
|
sqlstr = sqlstr.Replace("[AppConfig.UserCode]", oo.UserCode);
|
|
sqlstr = sqlstr.Replace("[AppConfig.UserName]", oo.UserName);
|
|
sqlstr = sqlstr.Replace("[AppConfig.RoleCode]", oo.RoleEnCode);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return sqlstr;
|
|
}
|
|
|
|
|
|
}
|
|
}
|