241 lines
11 KiB
C#
241 lines
11 KiB
C#
using DapperExtensions;
|
||
using Oracle.ManagedDataAccess.Client;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Configuration;
|
||
using System.Data;
|
||
using System.Linq;
|
||
using WX.CRM.Common;
|
||
using WX.CRM.DataSynClient.Domain;
|
||
|
||
namespace WX.CRM.DataSynClient.Dao
|
||
{
|
||
public class OracleStore : IStore
|
||
{
|
||
private static readonly string _conn = ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;
|
||
|
||
public static OracleConnection GetConnection()
|
||
{
|
||
DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.OracleDialect();
|
||
return new OracleConnection(_conn);
|
||
}
|
||
|
||
public IEnumerable<SYNC_PUSH> GetList(int topNum)
|
||
{
|
||
using (var db = GetConnection())
|
||
{
|
||
//var predicate = Predicates.Field<SYNC_PUSH>(f => f.deptcode, Operator.Eq, string.Empty);
|
||
IList<ISort> sort = new List<ISort>
|
||
{
|
||
new Sort { PropertyName = "pkid", Ascending = true }
|
||
};
|
||
return db.GetPage<SYNC_PUSH>(null, sort, 0, topNum).ToList();
|
||
}
|
||
}
|
||
|
||
public void PushSucc(SYNC_PUSH info)
|
||
{
|
||
if (info == null)
|
||
{
|
||
LogHelper.Info("PushSucc录入失败，info为空！");
|
||
return;
|
||
}
|
||
bool bs = true;
|
||
try
|
||
{
|
||
var suc = new SYNC_PUSH_SUCC(info.jsontext, info.bidatatype, info.deptcode);
|
||
suc.PKID = info.pkid;
|
||
LogHelper.Info(suc.PKID + "：调用SYNC_PUSH准备录入数据...");
|
||
bs = InsertSynCreceiveSucc(suc);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error(string.Concat("【录入数据】 WX.CRM.DataSynClient.Dao.OracleStore.PushSucc()", ex.Message, ex.StackTrace));
|
||
}
|
||
string result = bs == true ? "成功" : "失败";
|
||
LogHelper.Info(info.pkid + "：调用SYNC_PUSH录入" + result);
|
||
}
|
||
|
||
public void Insert(SYNC_RECEIVE info)
|
||
{
|
||
LogHelper.Info("Insert:" + info.PKID + "准备录入数据...");
|
||
bool bs = true;
|
||
try
|
||
{
|
||
bs = InsertSynCreceive(info);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error(string.Concat("【录入数据】 WX.CRM.DataSynClient.Dao.OracleStore.Insert()", ex.Message, ex.StackTrace));
|
||
}
|
||
string result = bs == true ? "成功" : "失败";
|
||
LogHelper.Info("Insert:" + info.PKID + "录入" + result);
|
||
}
|
||
|
||
/// <summary>
|
||
/// SYNC_RECEIVE 录入数据
|
||
/// </summary>
|
||
/// <param name="info"></param>
|
||
private bool InsertSynCreceive(SYNC_RECEIVE info)
|
||
{
|
||
OracleConnection conn = new OracleConnection(_conn);
|
||
if (conn.State == ConnectionState.Closed)
|
||
conn.Open();
|
||
try
|
||
{
|
||
using (OracleTransaction trans = conn.BeginTransaction())
|
||
{
|
||
String strSql = @"INSERT INTO WSKUSER.SYNC_RECEIVE A
|
||
(A.PKID,
|
||
A.PICI,
|
||
A.JSONTEXT,
|
||
A.FILENAME,
|
||
A.ISBATCH,
|
||
A.CTIME,
|
||
A.BIDATATYPE,
|
||
A.DEPTCODE,
|
||
A.CANREPEATNUM,
|
||
A.GRADE)
|
||
VALUES
|
||
(:PKID,
|
||
:PICI,
|
||
:JSONTEXT,
|
||
:FILENAME,
|
||
:ISBATCH,
|
||
:CTIME,
|
||
:BIDATATYPE,
|
||
:DEPTCODE,
|
||
:CANREPEATNUM,
|
||
:GRADE)";
|
||
|
||
OracleParameter[] cmdParms =
|
||
{
|
||
new OracleParameter(){ ParameterName = ":PKID", OracleDbType = OracleDbType.Decimal, Value = info.PKID},
|
||
new OracleParameter(){ ParameterName = ":PICI", OracleDbType = OracleDbType.Decimal, Value = info.PICI},
|
||
new OracleParameter(){ ParameterName = ":JSONTEXT", OracleDbType = OracleDbType.Clob, Value = info.JSONTEXT },
|
||
new OracleParameter(){ ParameterName = ":FILENAME", OracleDbType = OracleDbType.Varchar2, Value = info.FILENAME},
|
||
new OracleParameter(){ ParameterName = ":ISBATCH", OracleDbType = OracleDbType.Decimal, Value = info.ISBATCH},
|
||
new OracleParameter(){ ParameterName = ":CTIME", OracleDbType = OracleDbType.Date, Value = info.CTIME },
|
||
new OracleParameter(){ ParameterName = ":BIDATATYPE", OracleDbType = OracleDbType.Varchar2, Value = info.BIDATATYPE},
|
||
new OracleParameter(){ ParameterName = ":DEPTCODE", OracleDbType = OracleDbType.Varchar2, Value = info.DEPTCODE },
|
||
new OracleParameter(){ ParameterName = ":CANREPEATNUM", OracleDbType = OracleDbType.Decimal, Value = info.CANREPEATNUM},
|
||
new OracleParameter(){ ParameterName = ":GRADE", OracleDbType = OracleDbType.Decimal, Value = info.GRADE},
|
||
|
||
};
|
||
ExecuteSqlCommand(conn, trans, strSql, cmdParms);
|
||
trans.Commit();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error(string.Concat("【录入数据】WX.CRM.DataSynClient.Dao.OracleStore.InsertSynCreceive(),入参：", info.ToJson(), ex.Message, ex.StackTrace));
|
||
return false;
|
||
}
|
||
finally { conn.Close(); }
|
||
return true;
|
||
}
|
||
|
||
/// <summary>
|
||
/// SYNC_RECEIVE_SUCC 录入数据
|
||
/// </summary>
|
||
/// <param name="info"></param>
|
||
private bool InsertSynCreceiveSucc(SYNC_PUSH_SUCC info)
|
||
{
|
||
bool bs = true;
|
||
OracleConnection conn = new OracleConnection(_conn);
|
||
if (conn.State == ConnectionState.Closed)
|
||
conn.Open();
|
||
try
|
||
{
|
||
using (OracleTransaction trans = conn.BeginTransaction())
|
||
{
|
||
String strSql = @"INSERT INTO WSKUSER.SYNC_RECEIVE_SUCC A
|
||
(A.PKID,
|
||
A.PICI,
|
||
A.JSONTEXT,
|
||
A.FILENAME,
|
||
A.ISBATCH,
|
||
A.CTIME,
|
||
A.SENDTIME,
|
||
A.BIDATATYPE,
|
||
A.DEPTCODE,
|
||
A.CANREPEATNUM)
|
||
VALUES
|
||
(:PKID,
|
||
:PICI,
|
||
:JSONTEXT,
|
||
:FILENAME,
|
||
:ISBATCH,
|
||
:CTIME,
|
||
:SENDTIME,
|
||
:BIDATATYPE,
|
||
:DEPTCODE,
|
||
:CANREPEATNUM)";
|
||
|
||
OracleParameter[] cmdParms =
|
||
{
|
||
new OracleParameter(){ ParameterName = ":PKID", OracleDbType = OracleDbType.Decimal, Value = info.PKID},
|
||
new OracleParameter(){ ParameterName = ":PICI", OracleDbType = OracleDbType.Decimal, Value = info.PICI},
|
||
new OracleParameter(){ ParameterName = ":JSONTEXT", OracleDbType = OracleDbType.Clob, Value = info.JSONTEXT },
|
||
new OracleParameter(){ ParameterName = ":FILENAME", OracleDbType = OracleDbType.Varchar2, Value = info.FILENAME},
|
||
new OracleParameter(){ ParameterName = ":ISBATCH", OracleDbType = OracleDbType.Decimal, Value = info.ISBATCH},
|
||
new OracleParameter(){ ParameterName = ":CTIME", OracleDbType = OracleDbType.Date, Value = info.CTIME },
|
||
new OracleParameter(){ ParameterName = ":SENDTIME", OracleDbType = OracleDbType.Date, Value = info.SENDTIME},
|
||
new OracleParameter(){ ParameterName = ":BIDATATYPE", OracleDbType = OracleDbType.Varchar2, Value = info.BIDATATYPE},
|
||
new OracleParameter(){ ParameterName = ":DEPTCODE", OracleDbType = OracleDbType.Varchar2, Value = info.DEPTCODE },
|
||
new OracleParameter(){ ParameterName = ":CANREPEATNUM", OracleDbType = OracleDbType.Decimal, Value = info.CANREPEATNUM},
|
||
};
|
||
ExecuteSqlCommand(conn, trans, strSql, cmdParms);
|
||
string strDelete = $" delete from wskuser.sync_push a where a.pkid='{info.PKID}' ";
|
||
ExecuteSqlCommand(conn, trans, strDelete);
|
||
trans.Commit();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
LogHelper.Error(string.Concat("【录入数据】 WX.CRM.DataSynClient.Dao.OracleStore.InsertSynCreceiveSucc()，入参：", info.ToJson(), ex.Message, ex.StackTrace));
|
||
bs = false;
|
||
}
|
||
finally { conn.Close(); }
|
||
return bs;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行数据库语句操作
|
||
/// </summary>
|
||
/// <param name="conn"></param>
|
||
/// <param name="trans"></param>
|
||
/// <param name="cmdText"></param>
|
||
/// <param name="cmdParms"></param>
|
||
private void ExecuteSqlCommand(OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms = null)
|
||
{
|
||
try
|
||
{
|
||
OracleCommand mycmd = new OracleCommand(cmdText);
|
||
mycmd.Connection = conn;
|
||
if (trans != null)
|
||
mycmd.Transaction = trans;
|
||
mycmd.CommandType = CommandType.Text;
|
||
if (cmdParms != null)
|
||
{
|
||
foreach (OracleParameter parameter in cmdParms)
|
||
{
|
||
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
||
(parameter.Value == null))
|
||
{
|
||
parameter.Value = DBNull.Value;
|
||
}
|
||
mycmd.Parameters.Add(parameter);
|
||
}
|
||
}
|
||
mycmd.ExecuteNonQuery();
|
||
}
|
||
catch
|
||
{
|
||
throw;
|
||
}
|
||
}
|
||
|
||
}
|
||
}
|