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.
1039 lines
40 KiB
1039 lines
40 KiB
using ICSSoft.ERPWMS.SQL;
|
|
using Microsoft.Data.SqlClient;
|
|
using Newtonsoft.Json;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
|
|
namespace ICSSoft.ERPWMS.Entity
|
|
{
|
|
public class ICSHelper
|
|
{
|
|
public static int Count = 10; //每次执行command对象时的个数，sql语句循环增加，一次执行
|
|
public static string FileNameCompanyCon = "DLL\\CompanyConnect.txt"; //数据库链接文件
|
|
public static string FileNameCompanyCode = "DLL\\CompanyCode.txt"; //公司代码文件
|
|
|
|
|
|
private static log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
|
|
// private static string appConnectString = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString;
|
|
#region 读取配置文件
|
|
public static Dictionary<string, string> ReadConfig(string FileName)
|
|
{
|
|
try
|
|
{
|
|
//fileName = System.Web.Hosting.HostingEnvironment.MapPath("~") + "\\" + fileName;
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>();
|
|
StreamReader sr = new StreamReader(FileName, Encoding.Default);
|
|
string company;
|
|
while ((company = sr.ReadLine()) != null)
|
|
{
|
|
log.Debug(company);
|
|
if (string.IsNullOrEmpty(company))
|
|
continue;
|
|
string[] str = company.Split(':');
|
|
if (str.Length > 1)
|
|
{
|
|
dictionary.Add(str[0], str[1]);
|
|
}
|
|
else
|
|
{
|
|
throw new Exception("配置文件格式错误，请检查文件：" + FileName + "，" + str[0] + "\r\n");
|
|
}
|
|
}
|
|
sr.Close();
|
|
return dictionary;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error("读取配置文件异常：" + ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
public static DataTable GetDataTable(string sql)
|
|
{
|
|
|
|
try
|
|
{
|
|
using (SqlConnection m_cnn = new SqlConnection(ReadConfig(FileNameCompanyCon)["WMS"].ToString()))//GetConnectStringTest(key)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
SqlDataAdapter m_da = new SqlDataAdapter(sql, m_cnn);
|
|
m_da.SelectCommand.CommandTimeout = 6000;
|
|
m_da.SelectCommand.CommandType = CommandType.Text;
|
|
m_da.SelectCommand.CommandText = sql;
|
|
|
|
m_da.Fill(dt);
|
|
|
|
return dt;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error("查询SQL异常：" + ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
|
|
public static DataTable GetDataTableERP(string sql)
|
|
{
|
|
|
|
try
|
|
{
|
|
using (SqlConnection m_cnn = new SqlConnection(ReadConfig(FileNameCompanyCon)["ERP"].ToString()))
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
SqlDataAdapter m_da = new SqlDataAdapter(sql, m_cnn);
|
|
m_da.SelectCommand.CommandTimeout = 6000;
|
|
m_da.SelectCommand.CommandType = CommandType.Text;
|
|
m_da.SelectCommand.CommandText = sql;
|
|
|
|
m_da.Fill(dt);
|
|
|
|
return dt;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error("查询SQL异常：" + ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 事物取DataTable
|
|
/// </summary>
|
|
/// <param name="SQl"></param>
|
|
/// <param name="cmd"></param>
|
|
/// <returns></returns>
|
|
public static DataTable SQlReturnData(string SQl, SqlCommand cmd)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
SqlDataAdapter dr = new SqlDataAdapter();
|
|
cmd.CommandText = SQl;
|
|
dr.SelectCommand = cmd;
|
|
dr.Fill(dt);
|
|
return dt;
|
|
}
|
|
|
|
public static bool ExecuteNonQuery(string sql, SqlCommand cmd)
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
return true;
|
|
//if (result > 0)
|
|
//{
|
|
// return true;
|
|
//}
|
|
//else
|
|
//{
|
|
// log.Info("SQL执行受影响行数<0；" + sql);
|
|
// return false;
|
|
//}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string Params = string.Empty;
|
|
foreach (SqlParameter parameter in cmd.Parameters)
|
|
{
|
|
Params += parameter.SqlValue + "||";
|
|
}
|
|
log.Error("异常：" + ex.Message + "；\r\n SQL：" + sql + "参数：" + Params);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public static int ExecuteSqlERP(string sqlUpdate)
|
|
{
|
|
using (SqlConnection conn = new SqlConnection(ReadConfig(FileNameCompanyCon)["ERP"].ToString()))
|
|
{
|
|
try
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.CommandText = sqlUpdate;
|
|
if (conn.State != ConnectionState.Open)
|
|
{
|
|
conn.Open();
|
|
}
|
|
cmd.Connection = conn;
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State != ConnectionState.Closed)
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public static int ExecuteSql(string sqlUpdate)
|
|
{
|
|
using (SqlConnection conn = new SqlConnection(ReadConfig(FileNameCompanyCon)["WMS"].ToString()))
|
|
{
|
|
try
|
|
{
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.CommandText = sqlUpdate;
|
|
if (conn.State != ConnectionState.Open)
|
|
{
|
|
conn.Open();
|
|
}
|
|
cmd.Connection = conn;
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State != ConnectionState.Closed)
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public static void ExecuteDateERP(string sql)
|
|
{
|
|
try
|
|
{
|
|
using (SqlConnection con = new SqlConnection(ReadConfig(FileNameCompanyCon)["ERP"].ToString()))
|
|
{
|
|
con.Open();
|
|
try
|
|
{
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>();
|
|
using (SqlTransaction tran = con.BeginTransaction())
|
|
{
|
|
using (SqlCommand command = new SqlCommand())
|
|
{
|
|
command.Connection = con;
|
|
command.Transaction = tran;
|
|
command.CommandTimeout = 100;
|
|
command.CommandText = sql;
|
|
try
|
|
{
|
|
int result = command.ExecuteNonQuery();
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
log.Error(ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
if (con.State == ConnectionState.Open)
|
|
con.Close();
|
|
con.Dispose();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
|
|
public static void ExecuteDate(string sql)
|
|
{
|
|
try
|
|
{
|
|
using (SqlConnection con = new SqlConnection(ReadConfig(FileNameCompanyCon)["WMS"].ToString()))
|
|
{
|
|
con.Open();
|
|
try
|
|
{
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>();
|
|
using (SqlTransaction tran = con.BeginTransaction())
|
|
{
|
|
using (SqlCommand command = new SqlCommand())
|
|
{
|
|
command.Connection = con;
|
|
command.Transaction = tran;
|
|
command.CommandTimeout = 100;
|
|
command.CommandText = sql;
|
|
try
|
|
{
|
|
int result = command.ExecuteNonQuery();
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
log.Error(ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
if (con.State == ConnectionState.Open)
|
|
con.Close();
|
|
con.Dispose();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
public static string GetConnectStringTest(string key)
|
|
{
|
|
try
|
|
{
|
|
Dictionary<string, string> ss = ReadConfig(FileNameCompanyCode);
|
|
if (ss.ContainsKey(key))
|
|
{
|
|
return ss[key];
|
|
}
|
|
else
|
|
{
|
|
return "NotExit";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
public static Dictionary<string, string> InsertDate(string conStr, Dictionary<string, string> sqls)
|
|
{
|
|
try
|
|
{
|
|
SqlConnection con = new SqlConnection(conStr);
|
|
con.Open();
|
|
try
|
|
{
|
|
Dictionary<string, string> dictionary = new Dictionary<string, string>();
|
|
foreach (var sql in sqls)
|
|
{
|
|
using (SqlTransaction tran = con.BeginTransaction())
|
|
{
|
|
using (SqlCommand command = new SqlCommand())
|
|
{
|
|
command.Connection = con;
|
|
command.Transaction = tran;
|
|
|
|
command.CommandText = sql.Value;
|
|
try
|
|
{
|
|
int result = command.ExecuteNonQuery();
|
|
//获取sql中插入条数
|
|
//int count = Regex.Matches(sql.ToUpper(), "INSERT").Count;
|
|
//if (result < count)
|
|
if (result <= 0)
|
|
{
|
|
throw new Exception("插入数据失败！");
|
|
}
|
|
tran.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tran.Rollback();
|
|
log.Error(sql.Key + Environment.NewLine + ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql.Value);
|
|
dictionary.Add(sql.Key, ex.ToString());
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return dictionary;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
finally
|
|
{
|
|
if (con.State == ConnectionState.Open)
|
|
con.Close();
|
|
con.Dispose();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error(ex.ToString());
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行Insert 或者 Update
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="cmd"></param>
|
|
/// <param name="message"></param>
|
|
public static void CmdExecuteNonQuery(string sql, SqlCommand cmd, string message)
|
|
{
|
|
try
|
|
{
|
|
|
|
cmd.CommandText = sql;
|
|
int count = cmd.ExecuteNonQuery();
|
|
if (count <= 0)
|
|
{
|
|
string Msg = string.Empty;
|
|
foreach (SqlParameter parameter in cmd.Parameters)
|
|
{
|
|
Msg += "参数名：" + parameter.ParameterName + "参数值:" + parameter.Value;
|
|
}
|
|
log.Info("受影响行数小于0；" + sql + "\r\n" + Msg);
|
|
throw new Exception(message);
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
string Msg = string.Empty;
|
|
foreach (SqlParameter parameter in cmd.Parameters)
|
|
{
|
|
Msg += "参数名：" + parameter.ParameterName + "参数值:" + parameter.Value;
|
|
}
|
|
|
|
log.Info("异常：" + ex.Message + "\r\n " + message + "\r\n SQL:" + sql + "\r\n" + Msg);
|
|
throw new Exception(message + Environment.NewLine + ex.Message);
|
|
}
|
|
}
|
|
//public static List<OutInventoryModel> OutDate(string conStr, Dictionary<string, string> sqls,string uid,string meg)
|
|
//{
|
|
// List<OutInventoryModel> lonm = new List<OutInventoryModel>();
|
|
// List<OutInventory> lon = new List<OutInventory>();
|
|
// OutInventoryModel otm = new OutInventoryModel();
|
|
|
|
// try
|
|
// {
|
|
// SqlConnection con = new SqlConnection(conStr);
|
|
// con.Open();
|
|
// try
|
|
// {
|
|
|
|
// foreach (var sql in sqls)
|
|
// {
|
|
// //S:成功E:失败
|
|
// using (SqlTransaction tran = con.BeginTransaction())
|
|
// {
|
|
// using (SqlCommand command = new SqlCommand())
|
|
// {
|
|
// command.Connection = con;
|
|
// command.Transaction = tran;
|
|
|
|
// command.CommandText = sql.Value;
|
|
// try
|
|
// {
|
|
// int result = command.ExecuteNonQuery();
|
|
// //获取sql中插入条数
|
|
// //int count = Regex.Matches(sql.ToUpper(), "INSERT").Count;
|
|
// //if (result < count)
|
|
// if (result <= 0)
|
|
// {
|
|
// throw new Exception("插入数据失败！");
|
|
// }
|
|
// else
|
|
// {
|
|
// if (meg != "")
|
|
// {
|
|
// OutInventory otn = new OutInventory();
|
|
// otn.id = sql.Key;
|
|
// otn.result = "E";
|
|
// otn.message = meg;
|
|
// lon.Add(otn);
|
|
// otm.UID = uid;
|
|
// otm.product = lon;
|
|
// lonm.Add(otm);
|
|
// }
|
|
// else
|
|
// {
|
|
|
|
|
|
// OutInventory otn = new OutInventory();
|
|
// otn.id = sql.Key;
|
|
// otn.result = "S";
|
|
// otn.message = "";
|
|
// lon.Add(otn);
|
|
// otm.UID = uid;
|
|
// otm.product = lon;
|
|
// lonm.Add(otm);
|
|
// }
|
|
// }
|
|
// tran.Commit();
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// tran.Rollback();
|
|
// log.Error(sql.Key + Environment.NewLine + ex.ToString() + Environment.NewLine + "异常SQL：" + Environment.NewLine + sql.Value);
|
|
// OutInventory otn = new OutInventory();
|
|
// otn.id = sql.Key;
|
|
// otn.result = "E";
|
|
// string exe=ex.ToString().Substring(0, ex.ToString().IndexOf("在 System")).Replace("\u000d\u000a", "").ToString()+meg;
|
|
// otn.message = exe.Substring(exe.IndexOf(":"));
|
|
// lon.Add(otn);
|
|
// otm.UID = uid;
|
|
// otm.product = lon;
|
|
// lonm.Add(otm);
|
|
// }
|
|
// }
|
|
|
|
// }
|
|
// }
|
|
// List<OutInventoryModel> lom = new List<OutInventoryModel>();
|
|
// lom.Add(lonm[0]);
|
|
// return lom;
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// //log.Error(ex.ToString());
|
|
// throw ex;
|
|
// }
|
|
// finally
|
|
// {
|
|
// if (con.State == ConnectionState.Open)
|
|
// con.Close();
|
|
// con.Dispose();
|
|
// }
|
|
// }
|
|
// catch (Exception ex)
|
|
// {
|
|
// //log.Error(ex.ToString());
|
|
// throw ex;
|
|
// }
|
|
//}
|
|
|
|
|
|
/// <summary>
|
|
/// 根据传入的日期，需要增加的天数返回一个字符串
|
|
/// </summary>
|
|
/// <param name="data"></param>
|
|
/// <param name="str"></param>
|
|
public static string ReTime(DateTime data, int str)
|
|
{
|
|
|
|
int year = data.Year;
|
|
int month = data.Month;
|
|
int day = data.Day;
|
|
int n = DateTime.DaysInMonth(year, month);
|
|
int k = day + str;
|
|
if (k > n)
|
|
{
|
|
day = str - (n - day);
|
|
month = month + 1;
|
|
if (month > 12)
|
|
{
|
|
month = 1;
|
|
year = year + 1;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
day = day + str;
|
|
}
|
|
string c = year + "-" + month + "-" + day;
|
|
return c;
|
|
}
|
|
|
|
public static string GetIDSql(string caccId, string cVouchType, string key, GetID type)
|
|
{
|
|
try
|
|
{
|
|
string sql = "";
|
|
if (type.Equals(GetID.ALL))
|
|
{
|
|
sql = @"DECLARE @ID" + key + @" int
|
|
DECLARE @DID" + key + @" int
|
|
SET @ID" + key + @" = 0
|
|
SET @DID" + key + @" = 0
|
|
IF NOT EXISTS (SELECT * FROM UFSystem..ua_identity WHERE cacc_id = '{0}' AND cVouchType = '{1}')
|
|
BEGIN
|
|
INSERT INTO UFSystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE UFSystem..ua_identity
|
|
SET ifatherID = ifatherID + 1,ichildID = ichildID + 1
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'
|
|
END
|
|
|
|
SELECT @ID" + key + @" = ifatherID,@DID" + key + @" = ichildID
|
|
FROM UFSystem..ua_identity
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'";
|
|
}
|
|
else if (type.Equals(GetID.FATHER))
|
|
{
|
|
sql = @"DECLARE @ID" + key + @" int
|
|
SET @ID" + key + @" = 0
|
|
IF NOT EXISTS (SELECT * FROM UFSystem..ua_identity WHERE cacc_id = '{0}' AND cVouchType = '{1}')
|
|
BEGIN
|
|
INSERT INTO UFSystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE UFSystem..ua_identity
|
|
SET ifatherID = ifatherID + 1
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'
|
|
END
|
|
|
|
SELECT @ID" + key + @" = ifatherID
|
|
FROM UFSystem..ua_identity
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'";
|
|
}
|
|
else if (type.Equals(GetID.CHILD))
|
|
{
|
|
sql = @" DECLARE @DID" + key + @" int
|
|
SET @DID" + key + @" = 0
|
|
IF NOT EXISTS (SELECT * FROM UFSystem..ua_identity WHERE cacc_id = '{0}' AND cVouchType = '{1}')
|
|
BEGIN
|
|
INSERT INTO UFSystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE UFSystem..ua_identity
|
|
SET ichildID = ichildID + 1
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'
|
|
END
|
|
|
|
SELECT @DID" + key + @" = ichildID
|
|
FROM UFSystem..ua_identity
|
|
WHERE cAcc_id = '{0}' AND cVouchType = '{1}'";
|
|
}
|
|
sql = string.Format(sql, caccId, cVouchType);
|
|
return sql;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
}
|
|
|
|
public static string GetVouchSql(string CardNumber, string key)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"DECLARE @Num" + key + @" int,@seed VARCHAR(4)
|
|
SET @Num" + key + @" = 0
|
|
SET @seed=Substring(Convert( varchar(100),GetDate(),112),3,4)
|
|
IF NOT EXISTS (SELECT cNumber FROM VoucherHistory WHERE cSeed = @seed and CardNumber = '{1}')
|
|
BEGIN
|
|
INSERT INTO VoucherHistory(CardNumber,cContent,cContentRule,cSeed,cNumber) VALUES('{1}','单据日期','月',@seed,'1')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE VoucherHistory
|
|
SET cNumber = cNumber + 1
|
|
WHERE cSeed = @seed and CardNumber = '{1}'
|
|
END
|
|
|
|
SELECT @Num" + key + @" = cNumber
|
|
FROM VoucherHistory
|
|
WHERE cSeed = @seed and CardNumber = '{1}'";
|
|
sql = string.Format(sql, "{0}", CardNumber);
|
|
return sql;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
public static string GetVTID(string CardNumber, string key)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"DECLARE @VTID" + key + @" INT
|
|
SELECT @VTID" + key + @"=DEF_ID FROM Vouchers WHERE CardNumber = '{0}' ";
|
|
sql = string.Format(sql, CardNumber);
|
|
return sql;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
}
|
|
|
|
|
|
public static object ExecuteScalar(CommandType cmdType, string cmdText, string conn)
|
|
{
|
|
try
|
|
{
|
|
DbCommand cmd = CreateDbCommand();
|
|
using (DbConnection connection = CreateDbConnection(conn))
|
|
{
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, null);
|
|
object val = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
//log.Error(ex.Message);
|
|
throw;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 数据库类型
|
|
/// </summary>
|
|
public static DatabaseType DbType { get; set; }
|
|
/// <summary>
|
|
/// 根据配置文件中所配置的数据库类型和传入的
|
|
/// 数据库链接字符串来创建相应数据库连接对象
|
|
/// </summary>
|
|
/// <param name="connectionString"></param>
|
|
/// <returns></returns>
|
|
public static DbConnection CreateDbConnection(string connectionString)
|
|
{
|
|
DbConnection conn = null;
|
|
switch (DbType)
|
|
{
|
|
case DatabaseType.SqlServer:
|
|
conn = new SqlConnection(connectionString);
|
|
break;
|
|
//case DatabaseType.Oracle:
|
|
// conn = new OracleConnection(connectionString);
|
|
// break;
|
|
//case DatabaseType.MySql:
|
|
// conn = new MySqlConnection(connectionString);
|
|
// break;
|
|
//case DatabaseType.Access:
|
|
// conn = new OleDbConnection(connectionString);
|
|
// break;
|
|
//case DatabaseType.SQLite:
|
|
// conn = new SQLiteConnection(connectionString);
|
|
// break;
|
|
default:
|
|
throw new Exception("数据库类型目前不支持！");
|
|
}
|
|
return conn;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据配置文件中所配置的数据库类型
|
|
/// 来创建相应数据库命令对象
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static DbCommand CreateDbCommand()
|
|
{
|
|
DbCommand cmd = null;
|
|
switch (DbType)
|
|
{
|
|
case DatabaseType.SqlServer:
|
|
cmd = new SqlCommand();
|
|
break;
|
|
//case DatabaseType.Oracle:
|
|
// cmd = new OracleCommand();
|
|
// break;
|
|
//case DatabaseType.MySql:
|
|
// cmd = new MySqlCommand();
|
|
// break;
|
|
//case DatabaseType.Access:
|
|
// cmd = new OleDbCommand();
|
|
// break;
|
|
//case DatabaseType.SQLite:
|
|
// cmd = new SQLiteCommand();
|
|
// break;
|
|
default:
|
|
throw new Exception("数据库类型目前不支持！");
|
|
}
|
|
return cmd;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 方法一：获取编号 返回Dictionary字典，调用该方法用Dictionary字典接收，只需要遍历Dictionary即可得到响应的值
|
|
/// </summary>
|
|
/// <param name="cAcc_Id"></param>
|
|
/// <param name="cVouchType"></param>
|
|
/// <param name="iAmount"></param>
|
|
/// <returns></returns>
|
|
public static Dictionary<string, int> GetAllCode(string cAcc_Id, string cVouchType, string iAmount, string key)
|
|
{
|
|
string iFatherId = string.Empty, iChildId = string.Empty;
|
|
Dictionary<string, int> dic = new Dictionary<string, int>();
|
|
try
|
|
{
|
|
SqlConnection conn = new SqlConnection(GetConnectStringTest(key));
|
|
SqlCommand cmd = new SqlCommand("sp_GetIDWithoutRemote", conn);
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Parameters.AddWithValue("@cAcc_Id", cAcc_Id); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@cVouchType", cVouchType); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@iAmount", iAmount); //给输入参数赋值
|
|
//cmd.Parameters.AddWithValue("@iFatherId", iFatherId); //给输入参数赋值
|
|
//cmd.Parameters.AddWithValue("@iChildId", iChildId); //给输入参数赋值
|
|
SqlParameter parOutput = cmd.Parameters.Add("@iFatherId", SqlDbType.NVarChar, 50); //定义输出参数
|
|
cmd.Parameters["@iFatherId"].Direction = ParameterDirection.Output;
|
|
SqlParameter parOutputs = cmd.Parameters.Add("@iChildId", SqlDbType.NVarChar, 50); //定义输出参数
|
|
cmd.Parameters["@iChildId"].Direction = ParameterDirection.Output;
|
|
SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
|
|
parReturn.Direction = ParameterDirection.ReturnValue; //参数类型为ReturnValue
|
|
cmd.Parameters.Add(parReturn);
|
|
conn.Open();
|
|
cmd.ExecuteNonQuery();
|
|
iFatherId = cmd.Parameters["@iFatherId"].Value.ToString();
|
|
iChildId = cmd.Parameters["@iChildId"].Value.ToString();
|
|
if (!string.IsNullOrEmpty(iFatherId))//判断iFatherId是否为空，不为空的话将值放入dictionary字典中，否则int.Parse()会抛出异常
|
|
{
|
|
dic.Add("iFatherId", int.Parse(iFatherId));
|
|
dic.Add("iChildId", int.Parse(iChildId));
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
return dic;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 方法二 使用输出参数值
|
|
/// </summary>
|
|
/// <param name="cAcc_Id"></param>
|
|
/// <param name="cVouchType"></param>
|
|
/// <param name="iAmount"></param>
|
|
/// <returns></returns>
|
|
public static string GetAllRDCode(string CardNumber, string dDate, string UserCode, string key)
|
|
{
|
|
string iBaseCodeLen = string.Empty; string cVouchCodeBase = string.Empty;
|
|
Dictionary<string, int> dic = new Dictionary<string, int>();
|
|
try
|
|
{
|
|
|
|
SqlConnection conn = new SqlConnection(GetConnectStringTest(key));
|
|
SqlCommand cmd = new SqlCommand("ICS_VoucherNumber", conn);
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Parameters.AddWithValue("@CardNumber", CardNumber); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@dDate", dDate); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@UserCode", UserCode); //给输入参数赋值
|
|
|
|
SqlParameter parOutput = cmd.Parameters.Add("@number", SqlDbType.NVarChar, 50); //定义输出参数
|
|
parOutput.Direction = ParameterDirection.Output;
|
|
//SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
|
|
//parReturn.Direction = ParameterDirection.ReturnValue; //参数类型为ReturnValue
|
|
//cmd.Parameters.Add(parReturn);
|
|
conn.Open();
|
|
cmd.ExecuteNonQuery();
|
|
return parOutput.Value.ToString();
|
|
//iBaseCodeLen = cmd.Parameters["@iBaseCodeLen"].Value.ToString();
|
|
// cVouchCodeBase = cmd.Parameters["@cVouchCodeBase"].Value.ToString();
|
|
//UserCode= cmd.Parameters["@cVouchCodePreFix"].Value.ToString();
|
|
////if (!string.IsNullOrWhiteSpace(iBaseCodeLen))//判断iFatherId是否为空，不为空的话将值放入dictionary字典中，否则int.Parse()会抛出异常
|
|
////{
|
|
//// dic.Add("iBaseCodeLen", int.Parse(iBaseCodeLen));
|
|
////}
|
|
////else
|
|
////{
|
|
//// dic.Add("cVouchCodeBase", int.Parse(cVouchCodeBase));
|
|
// UserCode= UserCode + (cVouchCodeBase.ToString().PadLeft(int.Parse(iBaseCodeLen), '0'));
|
|
////}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
//return UserCode;
|
|
}
|
|
|
|
public static int CheckTimes(string Class,string Type)
|
|
{
|
|
DataTable dtRecordCheck = new DataTable();
|
|
string sqlRecordCheck = "Select Times from ICSApiRecord Where ApiName='{0}' and DateTime ='{1}'";
|
|
sqlRecordCheck = string.Format(sqlRecordCheck, Class, DateTime.Now.ToString("yyyyMMdd"));
|
|
if (Type=="ERP")
|
|
{
|
|
dtRecordCheck = GetDataTableERP(sqlRecordCheck);
|
|
}
|
|
else
|
|
{
|
|
dtRecordCheck = GetDataTable(sqlRecordCheck);
|
|
}
|
|
|
|
if (dtRecordCheck != null && dtRecordCheck.Rows.Count > 0)
|
|
{
|
|
return Convert.ToInt32(dtRecordCheck.Rows[0]["Times"].ToString());
|
|
}
|
|
else
|
|
{
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
//获取Token
|
|
public static TokenResult GetAuthkey(string Secret,string LoginID,string Type)
|
|
{
|
|
TokenResult res = new TokenResult();
|
|
if (Secret==""|| LoginID=="")
|
|
{
|
|
res.IsSuccess = false;
|
|
res.Message = "参数为空！";
|
|
}
|
|
else
|
|
{
|
|
try
|
|
{
|
|
GetToken action = new GetToken();
|
|
string resultStr = action.Get(new GetToken.ICSLogin { Secret = Secret,LoginID = LoginID,Type = Type});
|
|
if (resultStr.Length > 0)
|
|
{
|
|
res.IsSuccess = true;
|
|
res.Message = "调用成功！";
|
|
res.Authkey = resultStr;
|
|
}
|
|
else
|
|
{
|
|
res.IsSuccess = false;
|
|
res.Message = "调用失败";
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
log.Error("调用后台失败：" + ex.ToString());
|
|
res.IsSuccess = false;
|
|
res.Message = ex.Message;
|
|
}
|
|
}
|
|
return res;
|
|
}
|
|
|
|
|
|
public enum DatabaseType
|
|
{
|
|
/// <summary>
|
|
/// 数据库类型：Oracle
|
|
/// </summary>
|
|
Oracle,
|
|
/// <summary>
|
|
/// 数据库类型：SqlServer
|
|
/// </summary>
|
|
SqlServer,
|
|
/// <summary>
|
|
/// 数据库类型：Access
|
|
/// </summary>
|
|
Access,
|
|
/// <summary>
|
|
/// 数据库类型：MySql
|
|
/// </summary>
|
|
MySql,
|
|
/// <summary>
|
|
/// 数据库类型：SQLite
|
|
/// </summary>
|
|
SQLite
|
|
}
|
|
/// <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);
|
|
}
|
|
}
|
|
|
|
}
|
|
public enum GetID
|
|
{
|
|
ALL,
|
|
FATHER,
|
|
CHILD
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// data转List
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
public class ModelConvertHelper<T> where T : new()
|
|
{
|
|
public static IList<T> ConvertToModel(DataTable dt)
|
|
{
|
|
// 定义集合
|
|
IList<T> ts = new List<T>();
|
|
|
|
// 获得此模型的类型
|
|
Type type = typeof(T);
|
|
string tempName = "";
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
T t = new T();
|
|
// 获得此模型的公共属性
|
|
PropertyInfo[] propertys = t.GetType().GetProperties();
|
|
foreach (PropertyInfo pi in propertys)
|
|
{
|
|
tempName = pi.Name; // 检查DataTable是否包含此列
|
|
|
|
if (dt.Columns.Contains(tempName))
|
|
{
|
|
// 判断此属性是否有Setter
|
|
if (!pi.CanWrite) continue;
|
|
|
|
object value = dr[tempName];
|
|
if (value != DBNull.Value)
|
|
pi.SetValue(t, value, null);
|
|
}
|
|
}
|
|
ts.Add(t);
|
|
}
|
|
return ts;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
}
|