929 lines
60 KiB
C#
929 lines
60 KiB
C#
using System;
|
|
using System.Collections;
|
|
using System.Data;
|
|
using System.Data.OracleClient;
|
|
using System.Text;
|
|
using WX.CRM.Model;
|
|
using WX.CRM.Model.EntitySync;
|
|
|
|
|
|
namespace WX.CRM.DAL
|
|
{
|
|
public abstract class OracleHelper
|
|
{
|
|
//Read the connection strings from the configuration file
|
|
//public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["AYCRMCONN"].ConnectionString;
|
|
public static readonly string AYCRMConn = ConStringHelper.AYCRMConn;
|
|
public static readonly string AYCRMConn2 = ConStringHelper.AYCRMConn2;
|
|
public static readonly string YunCrmConn = ConStringHelper.YunCrmConn;
|
|
public static readonly string TGConn = ConStringHelper.TGCrmConn;
|
|
public static readonly string HGCrmConn = ConStringHelper.HGCrmConn;
|
|
//public static readonly string QhCrmConn = ConStringHelper.QhCrmConn;
|
|
|
|
|
|
|
|
//Create a hashtable for the parameter cached
|
|
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
|
|
|
|
/// <summary>
|
|
/// Execute a database query which does not include a select
|
|
/// </summary>
|
|
/// <param name="connString">Connection string to database</param>
|
|
/// <param name="cmdType">Command type either stored procedure or SQL</param>
|
|
/// <param name="cmdText">Acutall SQL Command</param>
|
|
/// <param name="commandParameters">Parameters to bind to the command</param>
|
|
/// <returns></returns>
|
|
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
// Create a new Oracle command
|
|
OracleCommand cmd = new OracleCommand();
|
|
|
|
//Create a connection
|
|
using (OracleConnection connection = new OracleConnection(connectionString))
|
|
{
|
|
try
|
|
{
|
|
//Prepare the command
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
|
|
//Execute the command
|
|
int val = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
finally
|
|
{
|
|
if (connection.State == ConnectionState.Open)
|
|
connection.Close();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// dataTable批量导入数据
|
|
/// </summary>
|
|
/// <param name="connectionString">连接字符串</param>
|
|
/// <param name="tableName">表名</param>
|
|
/// <param name="dataTable">dataTable数据源</param>
|
|
public static void OracleBulkInsert(string connectionString, string tableName, DataTable dataTable)
|
|
{
|
|
if (dataTable.Rows.Count == 0)
|
|
{
|
|
return;
|
|
}
|
|
using (Oracle.DataAccess.Client.OracleBulkCopy oracleBulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(connectionString, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
|
|
{
|
|
try
|
|
{
|
|
oracleBulkCopy.DestinationTableName = tableName;
|
|
for (int i = 0; i < dataTable.Columns.Count; i++)
|
|
{
|
|
oracleBulkCopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName,
|
|
dataTable.Columns[i].ColumnName);
|
|
}
|
|
oracleBulkCopy.WriteToServer(dataTable);
|
|
}
|
|
finally
|
|
{
|
|
if (oracleBulkCopy.Connection.State == ConnectionState.Open)
|
|
oracleBulkCopy.Connection.Close();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// dataTable批量导入数据
|
|
/// </summary>
|
|
/// <param name="oracleBulkCopy">传入操作对象</param>
|
|
/// <param name="tableName">数据库表名</param>
|
|
/// <param name="dataTable">要导入的dataTable</param>
|
|
public static void OracleBulkInsert(Oracle.DataAccess.Client.OracleBulkCopy oracleBulkCopy, string tableName, DataTable dataTable)
|
|
{
|
|
if (dataTable.Rows.Count == 0)
|
|
{
|
|
return;
|
|
}
|
|
|
|
try
|
|
{
|
|
oracleBulkCopy.DestinationTableName = tableName;
|
|
for (int i = 0; i < dataTable.Columns.Count; i++)
|
|
{
|
|
oracleBulkCopy.ColumnMappings.Add(dataTable.Columns[i].ColumnName,
|
|
dataTable.Columns[i].ColumnName);
|
|
}
|
|
oracleBulkCopy.WriteToServer(dataTable);
|
|
}
|
|
finally
|
|
{
|
|
if (oracleBulkCopy.Connection.State == ConnectionState.Open)
|
|
oracleBulkCopy.Connection.Close();
|
|
}
|
|
}
|
|
|
|
public static void ExecuteOracleTransaction()
|
|
{
|
|
OracleConnection conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (OracleTransaction trans = conn.BeginTransaction())
|
|
{
|
|
|
|
String cmdText = @"MERGE INTO WSKUSER.SYNC_RECEIVE_SUCC A
|
|
USING(SELECT :PKID PKID FROM DUAL) B
|
|
ON(A.PKID = B.PKID)
|
|
WHEN NOT MATCHED THEN
|
|
INSERT
|
|
(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,
|
|
:SENDTIME,
|
|
:CTIME,
|
|
:BIDATATYPE,
|
|
:DEPTCODE,
|
|
:CANREPEATNUM)";
|
|
SYNC_PUSH_SUCC info = new SYNC_PUSH_SUCC()
|
|
{
|
|
PKID = 59125259,
|
|
PICI = 0,
|
|
JSONTEXT = "{\"resid\":\"047012725787469725\",\"userid\":\"wm9jNKCAAAN64mDModNfWKOac_xjX5pg\",\"deptcode\":\"DNG8\"}",
|
|
FILENAME = "23",
|
|
ISBATCH = 1,
|
|
CTIME = DateTime.Parse("2022-07-06 15:19:44"),
|
|
SENDTIME = DateTime.Parse("2022-07-06 16:13:03"),
|
|
BIDATATYPE = "Client_CloseOrder",
|
|
DEPTCODE = "DNG8",
|
|
CANREPEATNUM = 0
|
|
};
|
|
OracleParameter[] cmdParms =
|
|
{
|
|
new OracleParameter(){ ParameterName = ":PKID", OracleType = OracleType.Number, Value = info.PKID},
|
|
new OracleParameter(){ ParameterName = ":PICI", OracleType = OracleType.Number, Value = info.PICI},
|
|
new OracleParameter(){ ParameterName = ":JSONTEXT", OracleType = OracleType.Clob, Value = info.JSONTEXT },
|
|
new OracleParameter(){ ParameterName = ":FILENAME", OracleType = OracleType.VarChar, Value = info.FILENAME},
|
|
new OracleParameter(){ ParameterName = ":ISBATCH", OracleType = OracleType.Number, Value = info.ISBATCH},
|
|
new OracleParameter(){ ParameterName = ":CTIME", OracleType = OracleType.DateTime, Value = info.CTIME },
|
|
new OracleParameter(){ ParameterName = ":SENDTIME", OracleType = OracleType.DateTime, Value = info.SENDTIME},
|
|
new OracleParameter(){ ParameterName = ":BIDATATYPE", OracleType = OracleType.VarChar, Value = info.BIDATATYPE},
|
|
new OracleParameter(){ ParameterName = ":DEPTCODE", OracleType = OracleType.VarChar, Value = info.DEPTCODE },
|
|
new OracleParameter(){ ParameterName = ":CANREPEATNUM", OracleType = OracleType.Int32, Value = info.CANREPEATNUM},
|
|
|
|
};
|
|
|
|
|
|
|
|
ExecuteSqlCommand(conn, trans, cmdText, cmdParms);
|
|
string strDelete = " delete from wskuser.sync_receive a where a.pkid='59125239' ";
|
|
ExecuteSqlCommand(conn, trans, strDelete);
|
|
trans.Commit();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
}
|
|
finally { conn.Close(); }
|
|
}
|
|
|
|
public static void InsertParame()
|
|
{
|
|
try
|
|
{
|
|
/* SYNC_RECEIVE info = new SYNC_RECEIVE() {
|
|
PKID = 59125262,
|
|
PICI=null,
|
|
JSONTEXT= "{\"type\":20,\"data\":[{\"uid\":223078,\"cid\":7071,\"appid\":\"ww8aded0c13e305598\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":1001,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":223231,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23\",\"appuserid\":\"SongZhiPeng\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":226509,\"cid\":7071,\"appid\":\"wweabc4c20eadfdf84\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":227635,\"cid\":7071,\"appid\":\"wwd4cd11d60db47118\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":363186,\"cid\":7071,\"appid\":\"ww94e35af67a976d23\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":490773,\"cid\":7071,\"appid\":\"wwb9c94b6d51580360\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":497131,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23\",\"appuserid\":\"SongZhiPeng2\",\"unionid\":\"\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":500651,\"cid\":7071,\"appid\":\"ww9bff45564457b627\",\"appuserid\":\"SongZhiPeng\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":2151858,\"cid\":7071,\"appid\":\"ww28c4af06a24f4ace\",\"appuserid\":\"220411-154707-46\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":3349350,\"cid\":7071,\"appid\":\"wwd4cd11d60db47118\",\"appuserid\":\"220620-202256-25\",\"unionid\":\"\",\"mobile\":\"\",\"resid\":\"415806728527919657\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":508768,\"cid\":7071,\"appid\":\"com.web\",\"appuserid\":\"415806728527919657\",\"unionid\":\"\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":0,\"deptcode\":null,\"type\":\"浏览器\"},{\"uid\":173785,\"cid\":7071,\"appid\":\"wx65343ff31edbd720\",\"appuserid\":\"oNHrvwzJ7YJrwM5wTMQVMpf7ZjI0\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":176142,\"cid\":7071,\"appid\":\"wx355be7cad257a8c4\",\"appuserid\":\"obk3K1AyuM4mOGvhbRapOZ7NtKfk\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":185983,\"cid\":7071,\"appid\":\"wxd824da5e2059804d\",\"appuserid\":\"oxae_uLahz59uDYRLwYuu6Y13L0Q\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":340897,\"cid\":7071,\"appid\":\"wxd824da5e2059804d\",\"appuserid\":\"oxae_uEfET9dcVvzP6JAlQNJ7TBI\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":351918,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwvwrOqOPBC8Sn0DcC174rm4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":355122,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwtdTQIrDIE_6S74-wB5o34k\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":553205,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwpdSkiMWuL-VMN41mFB7g2c\",\"unionid\":\"o5bj2wU5snrPcECUvtMABjb4HUlU\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":751885,\"cid\":7071,\"appid\":\"wx31a652de45126ceb\",\"appuserid\":\"oe8ohwRf6W-1sF-kQEqsp1mhEqcE\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":751894,\"cid\":7071,\"appid\":\"wx31a652de45126ceb\",\"appuserid\":\"oe8ohwYVxfL-EnmDkD2u-DLf9FdM\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":1197373,\"cid\":7071,\"appid\":\"wx258d09d55d9e78b0\",\"appuserid\":\"otkh854jqkxllQJkGbeRSjm3_2j4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":3107553,\"cid\":7071,\"appid\":\"wx1d0e531a680d947b\",\"appuserid\":\"olxOo6O2FMj5qP9SVD3wAD1h9p4c\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":3537573,\"cid\":7071,\"appid\":\"wx6ec1576e35d9b83c\",\"appuserid\":\"oEIA7xGbZrN_RNV_ueulrAABwsQA\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":785846,\"cid\":7071,\"appid\":\"wxae75f21257c5a58e\",\"appuserid\":\"o924L408hpO6MgPTo-5vph5W-oEY\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"小程序\"},{\"uid\":1223207,\"cid\":7071,\"appid\":\"wxf5a5d1b630c7fb0c\",\"appuserid\":\"oD5wn5RChW-XmnbVICVleKsHN9T4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":1373959,\"cid\":7071,\"appid\":\"wx638da3adf79dcab9\",\"appuserid\":\"oajmh4ivMXdJpvV8U5t84hjMMpj8\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":2215353,\"cid\":7071,\"appid\":\"wx25a6209d82179a4c\",\"appuserid\":\"oFEEC47QrLe-jOM7oML5nd_x4oRQ\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":2276733,\"cid\":7071,\"appid\":\"wx3ba7e0076cc52130\",\"appuserid\":\"ojOmR5TIcvOdciN_uZbwogdLYh3g\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":230701,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23_1\",\"appuserid\":\"wm3uf_DAAAy61LlydoVZWRTTca3oeSaA\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":234591,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23_1\",\"appuserid\":\"wm3uf_DAAA3LQxxLk013SYHYN4ZHekGw\",\"unionid\":\"o5bj2wU5snrPcECUvtMABjb4HUlU\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":237892,\"cid\":7071,\"appid\":\"ww8aded0c13e305598_1\",\"appuserid\":\"wmZFg0DwAAZIEq0lcMs-kBhu37g0sQ3g\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":1001,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":378318,\"cid\":7071,\"appid\":\"wweabc4c20eadfdf84_1\",\"appuserid\":\"wmXmZYDAAAhbb5AT2A4MOpagVXQ-y-rQ\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":737668,\"cid\":7071,\"appid\":\"ww89347c2378b6e050_1\",\"appuserid\":\"wm9jNKCAAA0-7MqH8ZOGzIUnHFbShfNg\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":3482412,\"cid\":7071,\"appid\":\"ww2cdef17101a6aef6_1\",\"appuserid\":\"wmO758CQAAYXKW3IxIBJzhx5Nvh9R_jw\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信外部联系人\"},{\"uid\":7071,\"cid\":7071,\"appid\":\"com.dongniu\",\"appuserid\":\"ebdl1001\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"PC软件\"},{\"uid\":9075,\"cid\":7071,\"appid\":\"com.dongniu\",\"appuserid\":\"aaazzz888\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"PC软件\"}]}",
|
|
FILENAME=null,
|
|
ISBATCH=0,
|
|
CTIME=DateTime.Now,
|
|
BIDATATYPE= "Client_CloseOrder",
|
|
DEPTCODE= "DNG8",
|
|
CANREPEATNUM=null
|
|
};*/
|
|
SYNC_RECEIVE info = new SYNC_RECEIVE()
|
|
{
|
|
PKID = 59125177,
|
|
PICI = null,
|
|
JSONTEXT = "{\"type\":20,\"data\":[{\"uid\":223078,\"cid\":7071,\"appid\":\"ww8aded0c13e305598\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":1001,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":223231,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23\",\"appuserid\":\"SongZhiPeng\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":226509,\"cid\":7071,\"appid\":\"wweabc4c20eadfdf84\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":227635,\"cid\":7071,\"appid\":\"wwd4cd11d60db47118\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":363186,\"cid\":7071,\"appid\":\"ww94e35af67a976d23\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":490773,\"cid\":7071,\"appid\":\"wwb9c94b6d51580360\",\"appuserid\":\"songgg\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":497131,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23\",\"appuserid\":\"SongZhiPeng2\",\"unionid\":\"\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":500651,\"cid\":7071,\"appid\":\"ww9bff45564457b627\",\"appuserid\":\"SongZhiPeng\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信\"},{\"uid\":2151858,\"cid\":7071,\"appid\":\"ww28c4af06a24f4ace\",\"appuserid\":\"220411-154707-46\",\"unionid\":\"\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":3349350,\"cid\":7071,\"appid\":\"wwd4cd11d60db47118\",\"appuserid\":\"220620-202256-25\",\"unionid\":\"\",\"mobile\":\"\",\"resid\":\"415806728527919657\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信\"},{\"uid\":508768,\"cid\":7071,\"appid\":\"com.web\",\"appuserid\":\"415806728527919657\",\"unionid\":\"\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":0,\"deptcode\":null,\"type\":\"浏览器\"},{\"uid\":173785,\"cid\":7071,\"appid\":\"wx65343ff31edbd720\",\"appuserid\":\"oNHrvwzJ7YJrwM5wTMQVMpf7ZjI0\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":176142,\"cid\":7071,\"appid\":\"wx355be7cad257a8c4\",\"appuserid\":\"obk3K1AyuM4mOGvhbRapOZ7NtKfk\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":185983,\"cid\":7071,\"appid\":\"wxd824da5e2059804d\",\"appuserid\":\"oxae_uLahz59uDYRLwYuu6Y13L0Q\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":340897,\"cid\":7071,\"appid\":\"wxd824da5e2059804d\",\"appuserid\":\"oxae_uEfET9dcVvzP6JAlQNJ7TBI\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":351918,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwvwrOqOPBC8Sn0DcC174rm4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":355122,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwtdTQIrDIE_6S74-wB5o34k\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":553205,\"cid\":7071,\"appid\":\"wx9b0b7cb5a0ee6b2f\",\"appuserid\":\"oC6DDwpdSkiMWuL-VMN41mFB7g2c\",\"unionid\":\"o5bj2wU5snrPcECUvtMABjb4HUlU\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"服务号\"},{\"uid\":751885,\"cid\":7071,\"appid\":\"wx31a652de45126ceb\",\"appuserid\":\"oe8ohwRf6W-1sF-kQEqsp1mhEqcE\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":751894,\"cid\":7071,\"appid\":\"wx31a652de45126ceb\",\"appuserid\":\"oe8ohwYVxfL-EnmDkD2u-DLf9FdM\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":1197373,\"cid\":7071,\"appid\":\"wx258d09d55d9e78b0\",\"appuserid\":\"otkh854jqkxllQJkGbeRSjm3_2j4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":3107553,\"cid\":7071,\"appid\":\"wx1d0e531a680d947b\",\"appuserid\":\"olxOo6O2FMj5qP9SVD3wAD1h9p4c\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":3537573,\"cid\":7071,\"appid\":\"wx6ec1576e35d9b83c\",\"appuserid\":\"oEIA7xGbZrN_RNV_ueulrAABwsQA\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"服务号\"},{\"uid\":785846,\"cid\":7071,\"appid\":\"wxae75f21257c5a58e\",\"appuserid\":\"o924L408hpO6MgPTo-5vph5W-oEY\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"小程序\"},{\"uid\":1223207,\"cid\":7071,\"appid\":\"wxf5a5d1b630c7fb0c\",\"appuserid\":\"oD5wn5RChW-XmnbVICVleKsHN9T4\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":1373959,\"cid\":7071,\"appid\":\"wx638da3adf79dcab9\",\"appuserid\":\"oajmh4ivMXdJpvV8U5t84hjMMpj8\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":2215353,\"cid\":7071,\"appid\":\"wx25a6209d82179a4c\",\"appuserid\":\"oFEEC47QrLe-jOM7oML5nd_x4oRQ\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":2276733,\"cid\":7071,\"appid\":\"wx3ba7e0076cc52130\",\"appuserid\":\"ojOmR5TIcvOdciN_uZbwogdLYh3g\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":0,\"deptcode\":null,\"type\":\"小程序\"},{\"uid\":230701,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23_1\",\"appuserid\":\"wm3uf_DAAAy61LlydoVZWRTTca3oeSaA\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":234591,\"cid\":7071,\"appid\":\"ww9c725b712bb08a23_1\",\"appuserid\":\"wm3uf_DAAA3LQxxLk013SYHYN4ZHekGw\",\"unionid\":\"o5bj2wU5snrPcECUvtMABjb4HUlU\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":237892,\"cid\":7071,\"appid\":\"ww8aded0c13e305598_1\",\"appuserid\":\"wmZFg0DwAAZIEq0lcMs-kBhu37g0sQ3g\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":null,\"ch\":1001,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":378318,\"cid\":7071,\"appid\":\"wweabc4c20eadfdf84_1\",\"appuserid\":\"wmXmZYDAAAhbb5AT2A4MOpagVXQ-y-rQ\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":2200,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":737668,\"cid\":7071,\"appid\":\"ww89347c2378b6e050_1\",\"appuserid\":\"wm9jNKCAAA0-7MqH8ZOGzIUnHFbShfNg\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"\",\"resid\":null,\"ch\":2300,\"deptcode\":\"QBJX\",\"type\":\"企业微信外部联系人\"},{\"uid\":3482412,\"cid\":7071,\"appid\":\"ww2cdef17101a6aef6_1\",\"appuserid\":\"wmO758CQAAYXKW3IxIBJzhx5Nvh9R_jw\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"\",\"resid\":\"\",\"ch\":0,\"deptcode\":null,\"type\":\"企业微信外部联系人\"},{\"uid\":7071,\"cid\":7071,\"appid\":\"com.dongniu\",\"appuserid\":\"ebdl1001\",\"unionid\":\"o5bj2waCxr5YYk5BTe-IvfqFSe_E\",\"mobile\":\"18926152693\",\"resid\":\"415806728527919657\",\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"PC软件\"},{\"uid\":9075,\"cid\":7071,\"appid\":\"com.dongniu\",\"appuserid\":\"aaazzz888\",\"unionid\":\"o5bj2wTwoWwqQ_YAhcHuZLPYfB8k\",\"mobile\":\"15219281918\",\"resid\":\"048534020218722595\",\"ch\":1000,\"deptcode\":\"QBJX\",\"type\":\"PC软件\"}]}",
|
|
FILENAME = null,
|
|
ISBATCH = 1,
|
|
CTIME = DateTime.Parse("2022-07-06 15:19:44"),
|
|
BIDATATYPE = "Client_CloseOrder",
|
|
DEPTCODE = "DNG8",
|
|
CANREPEATNUM = null
|
|
};
|
|
using (OracleConnection conn = new OracleConnection(AYCRMConn))
|
|
{
|
|
String strSql = @"MERGE INTO WSKUSER.SYNC_RECEIVE A
|
|
USING(SELECT :PKID PKID FROM DUAL) B
|
|
ON(A.PKID = B.PKID)
|
|
WHEN NOT MATCHED THEN
|
|
INSERT
|
|
(A.PKID,
|
|
A.PICI,
|
|
A.JSONTEXT,
|
|
A.FILENAME,
|
|
A.ISBATCH,
|
|
A.CTIME,
|
|
A.BIDATATYPE,
|
|
A.DEPTCODE,
|
|
A.CANREPEATNUM)
|
|
VALUES
|
|
(:PKID,
|
|
:PICI,
|
|
:JSONTEXT,
|
|
:FILENAME,
|
|
:ISBATCH,
|
|
TO_DATE(:CTIME, 'YYYY-MM-DD HH24:MI:SS'),
|
|
:BIDATATYPE,
|
|
:DEPTCODE,
|
|
:CANREPEATNUM)";
|
|
|
|
OracleCommand mycmd = new OracleCommand(strSql);
|
|
conn.Open();
|
|
mycmd.Connection = conn;
|
|
OracleParameter paPkid = new OracleParameter(":PKID", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paPkid.Value = info.PKID;
|
|
OracleParameter paPici = new OracleParameter(":PICI", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paPici.Value = info.PICI;
|
|
if (info.PICI == null)
|
|
{
|
|
paPici.Value = DBNull.Value;
|
|
}
|
|
OracleParameter paJsonText = new OracleParameter(":JSONTEXT", Oracle.DataAccess.Client.OracleDbType.Clob);
|
|
paJsonText.Value = string.IsNullOrEmpty(info.JSONTEXT) ? "" : info.JSONTEXT;
|
|
OracleParameter paFileName = new OracleParameter(":FILENAME", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paFileName.Value = string.IsNullOrEmpty(info.FILENAME) ? "" : info.FILENAME;
|
|
OracleParameter paIsBatch = new OracleParameter(":ISBATCH", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paIsBatch.Value = info.ISBATCH;
|
|
if (info.ISBATCH == null)
|
|
{
|
|
paIsBatch.Value = DBNull.Value;
|
|
}
|
|
OracleParameter paCtime = new OracleParameter(":CTIME", Oracle.DataAccess.Client.OracleDbType.Date);
|
|
paCtime.Value = info.CTIME;
|
|
OracleParameter paBiDataType = new OracleParameter(":BIDATATYPE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paBiDataType.Value = string.IsNullOrEmpty(info.BIDATATYPE) ? "" : info.BIDATATYPE;
|
|
OracleParameter paDeptCode = new OracleParameter(":DEPTCODE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paDeptCode.Value = string.IsNullOrEmpty(info.DEPTCODE) ? "" : info.DEPTCODE;
|
|
OracleParameter paCanrePentNum = new OracleParameter(":CANREPEATNUM", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paCanrePentNum.Value = info.CANREPEATNUM;
|
|
if (info.CANREPEATNUM == null)
|
|
{
|
|
paCanrePentNum.Value = DBNull.Value;
|
|
}
|
|
|
|
mycmd.Parameters.Add(paPkid);
|
|
mycmd.Parameters.Add(paPici);
|
|
mycmd.Parameters.Add(paJsonText);
|
|
mycmd.Parameters.Add(paFileName);
|
|
mycmd.Parameters.Add(paIsBatch);
|
|
mycmd.Parameters.Add(paCtime);
|
|
mycmd.Parameters.Add(paBiDataType);
|
|
mycmd.Parameters.Add(paDeptCode);
|
|
mycmd.Parameters.Add(paCanrePentNum);
|
|
mycmd.ExecuteNonQuery();
|
|
conn.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
}
|
|
|
|
|
|
private static void ExecuteSqlCommand(OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms = null)
|
|
{
|
|
OracleCommand mycmd = new OracleCommand(cmdText);
|
|
mycmd.Connection = conn;
|
|
if (trans != null)
|
|
mycmd.Transaction = trans;
|
|
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();
|
|
}
|
|
|
|
|
|
|
|
public static void InsertParameSucc()
|
|
{
|
|
try
|
|
{
|
|
SYNC_PUSH_SUCC info = new SYNC_PUSH_SUCC()
|
|
{
|
|
PKID = 80060938909898,
|
|
PICI = 5,
|
|
JSONTEXT = "{\"resid\":\"047012725787469725\",\"userid\":\"wm9jNKCAAAN64mDModNfWKOac_xjX5pg\",\"deptcode\":\"DNG8\"}",
|
|
FILENAME = null,
|
|
ISBATCH = 1,
|
|
CTIME = DateTime.Parse("2022-07-06 15:19:44"),
|
|
SENDTIME = DateTime.Parse("2022 - 07 - 06 16:13:03"),
|
|
BIDATATYPE = "Client_CloseOrder",
|
|
DEPTCODE = "DNG8",
|
|
CANREPEATNUM = null
|
|
};
|
|
using (OracleConnection conn = new OracleConnection(AYCRMConn))
|
|
{
|
|
String strSql = @"MERGE INTO WSKUSER.SYNC_RECEIVE_SUCC A
|
|
USING(SELECT :PKID PKID FROM DUAL) B
|
|
ON(A.PKID = B.PKID)
|
|
WHEN NOT MATCHED THEN
|
|
INSERT
|
|
(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,
|
|
:SENDTIME,
|
|
TO_DATE(:CTIME, 'YYYY-MM-DD HH24:MI:SS'),
|
|
:BIDATATYPE,
|
|
:DEPTCODE,
|
|
:CANREPEATNUM)";
|
|
|
|
OracleCommand mycmd = new OracleCommand(strSql);
|
|
conn.Open();
|
|
mycmd.Connection = conn;
|
|
OracleParameter paPkid = new OracleParameter(":PKID", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
/* paPkid.Value = info.PKID;
|
|
OracleParameter paPici = new OracleParameter(":PICI", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paPici.Value = info.PICI;
|
|
if (info.PICI == null)
|
|
{
|
|
paPici.Value = DBNull.Value;
|
|
}
|
|
OracleParameter paJsonText = new OracleParameter(":JSONTEXT", Oracle.DataAccess.Client.OracleDbType.Clob);
|
|
paJsonText.Value = string.IsNullOrEmpty(info.JSONTEXT) ? "" : info.JSONTEXT;
|
|
OracleParameter paFileName = new OracleParameter(":FILENAME", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paFileName.Value = string.IsNullOrEmpty(info.FILENAME) ? "" : info.FILENAME;
|
|
OracleParameter paIsBatch = new OracleParameter(":ISBATCH", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paIsBatch.Value = info.ISBATCH;
|
|
if (info.ISBATCH == null)
|
|
{
|
|
paIsBatch.Value = DBNull.Value;
|
|
}
|
|
OracleParameter paCtime = new OracleParameter(":CTIME", Oracle.DataAccess.Client.OracleDbType.Date);
|
|
paCtime.Value = info.CTIME;
|
|
OracleParameter paSEndTime = new OracleParameter(":SENDTIME", Oracle.DataAccess.Client.OracleDbType.Date);
|
|
paSEndTime.Value = info.SENDTIME;
|
|
OracleParameter paBiDataType = new OracleParameter(":BIDATATYPE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paBiDataType.Value = string.IsNullOrEmpty(info.BIDATATYPE) ? "" : info.BIDATATYPE;
|
|
OracleParameter paDeptCode = new OracleParameter(":DEPTCODE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paDeptCode.Value = string.IsNullOrEmpty(info.DEPTCODE) ? "" : info.DEPTCODE;
|
|
OracleParameter paCanrePentNum = new OracleParameter(":CANREPEATNUM", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paCanrePentNum.Value = info.CANREPEATNUM;
|
|
if (info.CANREPEATNUM == null)
|
|
{
|
|
paCanrePentNum.Value = DBNull.Value;
|
|
}*/
|
|
|
|
paPkid.Value = info.PKID;
|
|
OracleParameter paPici = new OracleParameter(":PICI", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paPici.Value = info.PICI;
|
|
OracleParameter paJsonText = new OracleParameter(":JSONTEXT", Oracle.DataAccess.Client.OracleDbType.Clob);
|
|
paJsonText.Value = info.JSONTEXT;
|
|
OracleParameter paFileName = new OracleParameter(":FILENAME", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paFileName.Value = info.FILENAME;
|
|
OracleParameter paIsBatch = new OracleParameter(":ISBATCH", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paIsBatch.Value = info.ISBATCH;
|
|
OracleParameter paCtime = new OracleParameter(":CTIME", Oracle.DataAccess.Client.OracleDbType.Date);
|
|
paCtime.Value = info.CTIME;
|
|
OracleParameter paSEndTime = new OracleParameter(":SENDTIME", Oracle.DataAccess.Client.OracleDbType.Date);
|
|
paSEndTime.Value = info.SENDTIME;
|
|
OracleParameter paBiDataType = new OracleParameter(":BIDATATYPE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paBiDataType.Value = info.BIDATATYPE;
|
|
OracleParameter paDeptCode = new OracleParameter(":DEPTCODE", Oracle.DataAccess.Client.OracleDbType.Varchar2);
|
|
paDeptCode.Value = info.DEPTCODE;
|
|
OracleParameter paCanrePentNum = new OracleParameter(":CANREPEATNUM", Oracle.DataAccess.Client.OracleDbType.Long);
|
|
paCanrePentNum.Value = info.CANREPEATNUM;
|
|
|
|
|
|
mycmd.Parameters.Add(paPkid);
|
|
mycmd.Parameters.Add(paPici);
|
|
mycmd.Parameters.Add(paJsonText);
|
|
mycmd.Parameters.Add(paFileName);
|
|
mycmd.Parameters.Add(paIsBatch);
|
|
mycmd.Parameters.Add(paCtime);
|
|
mycmd.Parameters.Add(paSEndTime);
|
|
mycmd.Parameters.Add(paBiDataType);
|
|
mycmd.Parameters.Add(paDeptCode);
|
|
mycmd.Parameters.Add(paCanrePentNum);
|
|
|
|
if (mycmd.Parameters != null)
|
|
{
|
|
foreach (OracleParameter parameter in mycmd.Parameters)
|
|
{
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
|
(parameter.Value == null))
|
|
{
|
|
parameter.Value = DBNull.Value;
|
|
}
|
|
}
|
|
}
|
|
mycmd.ExecuteNonQuery();
|
|
conn.Close();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// dataTable批量导入数据(连接字符串默认为AYCRMConn)
|
|
/// </summary>
|
|
/// <param name="tableName">表名</param>
|
|
/// <param name="dataTable">dataTable数据源</param>
|
|
public static void OracleBulkInsert(string tableName, DataTable dataTable)
|
|
{
|
|
OracleBulkInsert(AYCRMConn, tableName, dataTable);
|
|
}
|
|
/// <summary>
|
|
/// 默认的连接串为AYCRMConn
|
|
/// </summary>
|
|
/// <param name="cmdType"></param>
|
|
/// <param name="cmdText"></param>
|
|
/// <param name="commandParameters"></param>
|
|
/// <returns></returns>
|
|
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
return ExecuteNonQuery(AYCRMConn, cmdType, cmdText, commandParameters);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Execute an OracleCommand (that returns no resultset) against an existing database transaction
|
|
/// using the provided parameters.
|
|
/// </summary>
|
|
/// <remarks>
|
|
/// e.g.:
|
|
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
|
|
/// </remarks>
|
|
/// <param name="trans">an existing database transaction</param>
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
|
|
/// <returns>an int representing the number of rows affected by the command</returns>
|
|
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
|
|
int val = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Execute an OracleCommand (that returns no resultset) against an existing database connection
|
|
/// using the provided parameters.
|
|
/// </summary>
|
|
/// <remarks>
|
|
/// e.g.:
|
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
|
|
/// </remarks>
|
|
/// <param name="conn">an existing database connection</param>
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
|
|
/// <returns>an int representing the number of rows affected by the command</returns>
|
|
public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
|
|
try
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
int val = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
finally
|
|
{
|
|
if (connection.State == ConnectionState.Open)
|
|
connection.Close();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 返回一个dataset
|
|
/// </summary>
|
|
/// <param name="connectionstring"></param>
|
|
/// <param name="cmdType"></param>
|
|
/// <param name="cmdText"></param>
|
|
/// <param name="commandParameters"></param>
|
|
/// <returns></returns>
|
|
public static DataSet DataQueray(string connectionstring, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
OracleConnection con = new OracleConnection(connectionstring);
|
|
OracleCommand cmd = new OracleCommand(cmdText, con);
|
|
try
|
|
{
|
|
cmd.CommandType = cmdType;
|
|
cmd.Parameters.AddRange(commandParameters);
|
|
OracleDataAdapter da = new OracleDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
da.Fill(ds);
|
|
cmd.Parameters.Clear();
|
|
return ds;
|
|
}
|
|
finally
|
|
{
|
|
if (con.State == ConnectionState.Open)
|
|
con.Close();
|
|
}
|
|
}
|
|
public static DataSet GetPageList(string connectionstring, CommandType cmdType, string cmdText, string orderField, string orderType, int pageIndex, int pageSize, ref int count, params OracleParameter[] commandParameters)
|
|
{
|
|
StringBuilder builder = new StringBuilder();
|
|
OracleConnection con = new OracleConnection(connectionstring);
|
|
try
|
|
{
|
|
string sqlcount = "Select Count(1) From (" + cmdText + ") t";
|
|
count = Convert.ToInt32(ExecuteScalar(connectionstring, CommandType.Text, sqlcount, commandParameters));
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num2 = pageSize;
|
|
int num3 = num + num2 + 1;
|
|
builder.Append("select * from(select t.*,rownum rn from(" + cmdText + " order by " + orderField + " " + orderType + ") t where rownum<" + num3 + ") where rn>" + num + "");
|
|
|
|
OracleCommand cmd = new OracleCommand(builder.ToString(), con);
|
|
cmd.CommandType = cmdType;
|
|
cmd.Parameters.AddRange(commandParameters);
|
|
OracleDataAdapter da = new OracleDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
da.Fill(ds);
|
|
cmd.Parameters.Clear();
|
|
return ds;
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
if (con.State == ConnectionState.Open)
|
|
con.Close();
|
|
}
|
|
}
|
|
public static DataSet DataQueray(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
if (transaction == null)
|
|
throw new ArgumentNullException("transaction");
|
|
if (transaction != null && transaction.Connection == null)
|
|
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
|
|
|
|
OracleCommand cmd = new OracleCommand();
|
|
PrepareCommand(cmd, transaction.Connection, transaction, cmdType, cmdText, commandParameters);
|
|
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
|
|
{
|
|
DataSet ds = new DataSet();
|
|
da.Fill(ds);
|
|
cmd.Parameters.Clear();
|
|
return ds;
|
|
}
|
|
|
|
|
|
|
|
//OracleConnection con = new OracleConnection(connectionstring);
|
|
//OracleCommand cmd = new OracleCommand(cmdText, con);
|
|
//try
|
|
//{
|
|
// cmd.CommandType = cmdType;
|
|
// cmd.Parameters.AddRange(commandParameters);
|
|
// OracleDataAdapter da = new OracleDataAdapter(cmd);
|
|
// DataSet ds = new DataSet();
|
|
// da.Fill(ds);
|
|
// cmd.Parameters.Clear();
|
|
// return ds;
|
|
//}
|
|
//catch
|
|
//{
|
|
// con.Close();
|
|
// throw;
|
|
//}
|
|
}
|
|
/// <summary>
|
|
/// 默认的连接串为AYCRMConn
|
|
/// </summary>
|
|
/// <param name="cmdType"></param>
|
|
/// <param name="cmdText"></param>
|
|
/// <param name="commandParameters"></param>
|
|
/// <returns></returns>
|
|
public static DataSet DataQueray(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
return DataQueray(AYCRMConn, cmdType, cmdText, commandParameters);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Execute a select query that will return a result set
|
|
/// </summary>
|
|
/// <param name="connString">Connection string</param>
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
|
|
/// <returns></returns>
|
|
public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
|
|
//Create the command and connection
|
|
OracleCommand cmd = new OracleCommand();
|
|
OracleConnection conn = new OracleConnection(connectionString);
|
|
|
|
try
|
|
{
|
|
//Prepare the command to execute
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
|
|
//Execute the query, stating that the connection should close when the resulting datareader has been read
|
|
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
cmd.Parameters.Clear();
|
|
return rdr;
|
|
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 默认的连接串为AYCRMConn
|
|
/// </summary>
|
|
/// <param name="cmdType"></param>
|
|
/// <param name="cmdText"></param>
|
|
/// <param name="commandParameters"></param>
|
|
/// <returns></returns>
|
|
public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
return ExecuteReader(AYCRMConn, cmdType, cmdText, commandParameters);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string
|
|
/// using the provided parameters.
|
|
/// </summary>
|
|
/// <remarks>
|
|
/// e.g.:
|
|
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
|
|
/// </remarks>
|
|
/// <param name="connectionString">a valid connection string for a SqlConnection</param>
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
|
|
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
|
|
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
|
|
using (OracleConnection conn = new OracleConnection(connectionString))
|
|
{
|
|
try
|
|
{
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
object val = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 默认的连接串为AYCRMConn
|
|
/// </summary>
|
|
/// <param name="cmdType"></param>
|
|
/// <param name="cmdText"></param>
|
|
/// <param name="commandParameters"></param>
|
|
/// <returns></returns>
|
|
public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
return ExecuteScalar(AYCRMConn, cmdType, cmdText, commandParameters);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// Execute a OracleCommand (that returns a 1x1 resultset) against the specified SqlTransaction
|
|
/// using the provided parameters.
|
|
/// </summary>
|
|
/// <param name="transaction">A valid SqlTransaction</param>
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">The stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">An array of OracleParamters used to execute the command</param>
|
|
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
|
|
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
|
|
{
|
|
if (transaction == null)
|
|
throw new ArgumentNullException("transaction");
|
|
if (transaction != null && transaction.Connection == null)
|
|
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
|
|
|
|
// Create a command and prepare it for execution
|
|
OracleCommand cmd = new OracleCommand();
|
|
|
|
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
|
|
|
|
// Execute the command & return the results
|
|
object retval = cmd.ExecuteScalar();
|
|
|
|
// Detach the SqlParameters from the command object, so they can be used again
|
|
cmd.Parameters.Clear();
|
|
return retval;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Execute an OracleCommand that returns the first column of the first record against an existing database connection
|
|
/// using the provided parameters.
|
|
/// </summary>
|
|
/// <remarks>
|
|
/// e.g.:
|
|
/// Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
|
|
/// </remarks>
|
|
/// <param name="conn">an existing database connection</param>
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
|
|
/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
|
|
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
|
|
public static object ExecuteScalar(OracleConnection connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
|
|
{
|
|
try
|
|
{
|
|
OracleCommand cmd = new OracleCommand();
|
|
|
|
PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);
|
|
object val = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
return val;
|
|
}
|
|
finally
|
|
{
|
|
connectionString.Close();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Add a set of parameters to the cached
|
|
/// </summary>
|
|
/// <param name="cacheKey">Key value to look up the parameters</param>
|
|
/// <param name="commandParameters">Actual parameters to cached</param>
|
|
public static void CacheParameters(string cacheKey, params OracleParameter[] commandParameters)
|
|
{
|
|
parmCache[cacheKey] = commandParameters;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Fetch parameters from the cache
|
|
/// </summary>
|
|
/// <param name="cacheKey">Key to look up the parameters</param>
|
|
/// <returns></returns>
|
|
public static OracleParameter[] GetCachedParameters(string cacheKey)
|
|
{
|
|
OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
|
|
|
|
if (cachedParms == null)
|
|
return null;
|
|
|
|
// If the parameters are in the cache
|
|
OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];
|
|
|
|
// return a copy of the parameters
|
|
for (int i = 0, j = cachedParms.Length; i < j; i++)
|
|
clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();
|
|
|
|
return clonedParms;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Internal function to prepare a command for execution by the database
|
|
/// </summary>
|
|
/// <param name="cmd">Existing command object</param>
|
|
/// <param name="conn">Database connection object</param>
|
|
/// <param name="trans">Optional transaction object</param>
|
|
/// <param name="cmdType">Command type, e.g. stored procedure</param>
|
|
/// <param name="cmdText">Command test</param>
|
|
/// <param name="commandParameters">Parameters for the command</param>
|
|
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
|
|
{
|
|
|
|
//Open the connection if required
|
|
if (conn.State != ConnectionState.Open)
|
|
conn.Open();
|
|
|
|
//Set up the command
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = cmdText;
|
|
cmd.CommandType = cmdType;
|
|
|
|
//Bind it to the transaction if it exists
|
|
if (trans != null)
|
|
cmd.Transaction = trans;
|
|
|
|
// Bind the parameters passed in
|
|
if (commandParameters != null)
|
|
{
|
|
foreach (OracleParameter parm in commandParameters)
|
|
cmd.Parameters.Add(parm);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converter to use boolean data type with Oracle
|
|
/// </summary>
|
|
/// <param name="value">Value to convert</param>
|
|
/// <returns></returns>
|
|
public static string OraBit(bool value)
|
|
{
|
|
if (value)
|
|
return "Y";
|
|
else
|
|
return "N";
|
|
}
|
|
|
|
/// <summary>
|
|
/// Converter to use boolean data type with Oracle
|
|
/// </summary>
|
|
/// <param name="value">Value to convert</param>
|
|
/// <returns></returns>
|
|
public static bool OraBool(string value)
|
|
{
|
|
if (value.Equals("Y"))
|
|
return true;
|
|
else
|
|
return false;
|
|
}
|
|
|
|
#region 扩展方法
|
|
/// <summary>
|
|
/// 批量插入数据
|
|
/// </summary>
|
|
/// <param name="dt">要插入的数据</param>
|
|
/// <param name="targetTable">数据库中的表</param>
|
|
public static void BulkToDB(string connectionString, DataTable dt, string targetTable, int timeOut = 300)
|
|
{
|
|
if (dt.Rows.Count == 0)
|
|
{
|
|
return;
|
|
}
|
|
using (Oracle.DataAccess.Client.OracleBulkCopy oracleBulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(connectionString, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
|
|
{
|
|
try
|
|
{
|
|
oracleBulkCopy.DestinationTableName = targetTable;
|
|
for (int i = 0; i < dt.Columns.Count; i++)
|
|
{
|
|
oracleBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName,
|
|
dt.Columns[i].ColumnName);
|
|
}
|
|
oracleBulkCopy.WriteToServer(dt);
|
|
}
|
|
finally
|
|
{
|
|
oracleBulkCopy.Connection.Close();
|
|
}
|
|
}
|
|
}
|
|
|
|
public static void BulkToDB(DataTable dt, string targetTable, int timeOut = 300)
|
|
{
|
|
BulkToDB(AYCRMConn, dt, targetTable, timeOut);
|
|
}
|
|
#endregion
|
|
}
|
|
}
|