请问四种方式写入数据库的区别：直接用sql语句的，存储过程的，SqlBulkCopy与OracleBulkCopy。 非常感谢

发布网友 发布时间：2022-04-07 21:01

我来回答

共2个回答

懂视网 时间：2022-04-08 01:22

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Contract.IService;
using DevExpress.Data.PLinq.Helpers;
using Framework;
using Holworth.RiskInterface;
using HraWeb.Common;
using Oracle.DataAccess.Client;
using Utility;
using WebApp.Common;
using Contract.Domain;
using System.Collections;
using OracleConnection = System.Data.OracleClient.OracleConnection;

namespace HraWeb
{
public static class DataTableExtensions
{
public static DataTable ToDataTable<T>(this IList<T> list)
{

//创建属性的集合
List<PropertyInfo> pList = new List<PropertyInfo>();
//获得反射的入口

Type type = typeof(T);
DataTable dt = new DataTable();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
foreach (var item in list)
{
//创建一个DataRow实例
DataRow row = dt.NewRow();
//给row 赋值
pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
//加入到DataTable
dt.Rows.Add(row);
}
return dt;
}

}
public partial class BasLimitToConfigureManage : JQEntityManage<Contract.Domain.BasLimitConfig>
{
IRiskLimitControlService _riskLimitControlViewService;
IRiskLimitControlService RiskLimitControlViewService
{
get
{
if (_riskLimitControlViewService == null)
{
_riskLimitControlViewService = (IRiskLimitControlService)ctx["RiskLimitControlService"];
}
return _riskLimitControlViewService;
}
}

protected override void Page_Load(object sender, EventArgs e)
{
switch (Request["_method"])
{
case "setBatchDelRows":
setBatchDelRows();
break;
case "BasLimitControlRun":
BasLimitControlRun();
break;

}

//给基类服务接口复制，可不付
//base.svc = Psvc;
if (!IsPostBack)
{

}

base.Page_Load(sender, e);
}
#region 批量插入数据
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="dt">要插入的数据</param>
/// <param name="targetTable">数据库中的表</param>
public void BulkToDB(DataTable dt, string targetTable)
{

QueryInfo info=new QueryInfo();
info.CustomSQL = "select HIBERNATE_SEQUENCE.NEXTVAL from dual";
DataTable table= Dao.ExcuteDataSet(info).Tables[0];
Int64 increId = Convert.ToInt64(table.Rows[0][0].ToString());
foreach (DataRow t in dt.Rows)
{
t["risk_book_id"]=increId++;

}

string connOrcleString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.252)(PORT = 1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME =hra)));User Id=nethra;Password=nethra;";//= "Data Source=192.168.2.252;Database=hra;Uid=nethra;Pwd=nethra;";
OracleConnection conn = new OracleConnection(connOrcleString);
// OracleBulkCopy bulkCopy = new OracleBulkCopy(connOrcleString, OracleBulkCopyOptions.UseInternalTransaction); //用其它源的数据有效批量加载Oracle表中
OracleBulkCopy bulkCopy = new OracleBulkCopy(connOrcleString,
OracleBulkCopyOptions.UseInternalTransaction | OracleBulkCopyOptions.Default);
bulkCopy.BatchSize = 100000;
bulkCopy.BulkCopyTimeout = 260;
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = dt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if (dt != null && dt.Rows.Count != 0)

bulkCopy.WriteToServer(dt); //将提供的数据源中的所有行复制到目标表中
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
}

#endregion
private void BasLimitControlRun()
{
//IList list = Dao.FindList(new QueryInfo("RskBook"));
//IList<RskBook> funlist=new List<RskBook>();
//foreach (RskBook rskBook in list)
//{
// funlist.Add(rskBook);
//}
QueryInfo info=new QueryInfo();
info.QueryObject = "select * from ccdd";
DataTable dt = Dao.ExcuteDataSet(info).Tables[0];
// dt.Columns.Remove("risk_book_id");
BulkToDB(dt,"ccdd" );
Int64 BasLimitControlId = 0;
if (!string.IsNullOrEmpty(Request["BasLimitControlId"]))
{
Int64.TryParse(Request["BasLimitControlId"], out BasLimitControlId);
}
RiskLimitControlViewService.ProcessRiskLimitControl(BasLimitControlId);
}

private void setBatchDelRows()
{
string CheckRowIds = Request["rowidList"];
List<string> idsList = CheckRowIds.Split(‘,‘).ToList();
List<int> intIds=new List<int>();
idsList.ForEach(a => { intIds.Add(int.Parse(a));});
if (!string.IsNullOrEmpty(CheckRowIds))
{
List<string> checkList = CheckRowIds.Split(‘,‘).ToList();
info=new QueryInfo();
info.CustomSQL = "delete from BasLimitConfig t where t.id in (:ids)";
info.Parameters.Add("ids",intIds.ToArray());
Dao.ExecuteUpdate(info);

}

}

/// <summary>
/// 设置查询条件或者语句
/// </summary>
/// <returns></returns>
protected override Framework.QueryInfo SetInfo()
{
InitSearchCache();
info = base.SetInfo();
if (!string.IsNullOrEmpty(Request["BaslimitproStruPId"]))
{

string LimitDim2Id = Request["BaslimitproStruPId"];
List<string> LimitDim2IdList = GetAllProChildIds(LimitDim2Id);
LimitDim2IdList.Add(LimitDim2Id);
string[] LimitDim2Ids = LimitDim2IdList.ToArray();

info.AddParam("LimitDim2Ids", LimitDim2Ids, " and LimitDim2Id in (:LimitDim2Ids)");

}
return info;
}

private void InitSearchCache()
{
SpringCache.Remove("GetBasLimitProStrucs");
List<BasLimitProStruc> list1 = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;

if (list1 == null || list1.Count <= 0)
{
IList list2 = Dao.FindList(new QueryInfo("BasLimitProStruc"));

List<BasLimitProStruc> list3 = new List<BasLimitProStruc>();
foreach (var b in list2)
{

list3.Add(b as BasLimitProStruc);
}

SpringCache.Insert("GetBasLimitProStrucs", list3);

}
SpringCache.Remove("GetBasLimitConfigs");
List<BasLimitConfig> list11 = SpringCache.Get("GetBasLimitConfigs") as List<BasLimitConfig>;

if (list11 == null || list11.Count <= 0)
{
IList list12 = Dao.FindList(new QueryInfo("BasLimitConfig"));

List<BasLimitConfig> list3 = new List<BasLimitConfig>();
foreach (var b in list12)
{

list3.Add(b as BasLimitConfig);
}

SpringCache.Insert("GetBasLimitConfigs", list3);

}
SpringCache.Remove("BasLimitBusiStrucs");
List<BasLimitBusiStruc> childList = SpringCache.Get("BasLimitBusiStrucs") as List<BasLimitBusiStruc>;
if (childList == null || childList.Count <= 0)
{
IList list12 = Dao.FindList(new QueryInfo("BasLimitBusiStruc"));

List<BasLimitBusiStruc> list3 = new List<BasLimitBusiStruc>();
foreach (var b in list12)
{

list3.Add(b as BasLimitBusiStruc);
}

SpringCache.Insert("BasLimitBusiStrucs", list3);

}
}

private List<string> GetAllProChildIds(string parentID)
{
List<string> ids = new List<string>();

List<BasLimitProStruc> list1 = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;

List<BasLimitProStruc> prosFuncList = new List<BasLimitProStruc>();
prosFuncList = list1;
prosFuncList = (from s in prosFuncList where s.ParentId.ToString() == parentID select s).ToList();

//QueryInfo info = new QueryInfo("BasLimitProStruc b");
//info.AddParam("ParentId", parentID, " and (b.ParentId=:ParentId )");

//prosList = Dao.FindList(info);
foreach (BasLimitProStruc b1 in prosFuncList)
{
//BasLimitProStruc b1 = b as BasLimitProStruc;
ids.Add(b1.Id);
if (ParentHasChild(b1.Id))
{

ids.AddRange(GetAllProChildIds(b1.Id));
}

}

return ids;

}
private Spring.Caching.ICache cache;
private Spring.Caching.ICache SpringCache
{
get
{
if (cache == null)
cache = (Spring.Caching.ICache)ctx.GetObject("AspNetCache");
return cache;
}
set
{
cache = value;
}
}
private bool ParentHasChild(string id)
{

List<BasLimitProStruc> list1 = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;
list1 = (from s in list1 where s.ParentId.ToString() == id select s).ToList();
if (list1.Count > 0)
return true;
return false;

}

/// <summary>
/// 初始化页面控件
/// </summary>
protected override void InitPage()
{
base.InitPage();

// BindDrop(drp_Unit, "SPSW", true);
}
public static List<int?> list1 = new List<int?>();
public static List<int?> list2 = new List<int?>();
public static List<int?> list3 = new List<int?>();
public static List<int?> list4 = new List<int?>();
public override void ChangeList(QueryInfo infoList)
{

List<PorductUpperLimit> dic = new List<PorductUpperLimit>();

List<BasLimitConfig> funcBasLimitConfigList = new List<BasLimitConfig>();
list1.Clear();
list2.Clear();
foreach (var b in infoList.List)
{
BasLimitConfig b1 = b as BasLimitConfig;
list1.Add(b1.LimitDim1Id);
list2.Add(b1.LimitDim2Id);
funcBasLimitConfigList.Add(b as BasLimitConfig);

}

// 1.算出每个交易员对应货币的实际额度
foreach (BasLimitConfig b in funcBasLimitConfigList)
{
if (IsTrader(b.LimitDim1Id) && IsMoney(Convert.ToString(b.LimitDim2Id)))
{

dic.Add(new PorductUpperLimit() { TraderId = b.LimitDim1Id, TraderName = b.LimitDim1Value, ProductId = b.LimitDim2Id, ProductName = b.LimitDim2Value, UpperLimitValue = b.UpperLimitValue });
b.ActualIsAmount = b.UpperLimitValue;
// b.PartIsAmount = b.UpperLimitValue;
}
else
{

b.ActualIsAmount = NextnodeAmount(b);
// b.PartIsAmount = NextnodePartAmount(b);

}
//if (b.ActualIsAmount <= 0 && b.PartIsAmount > 0)
//{
// b.ActualIsAmount = b.PartIsAmount;
//}

}

}

private decimal? NextnodePartAmount(BasLimitConfig b)
{
decimal? d = 0;
string childServiceId = "";
string childProductId = "";
int? id1 = b.LimitDim1Id;
int? id2 = b.LimitDim2Id;
try
{

string strId1s = GetchildServiceId(id1);
if (strId1s.Contains(‘,‘))
strId1s = strId1s.Substring(0, strId1s.Length - 1);
string strId2s = GetchildProductId(id2);
if (strId2s.Contains(‘,‘))
strId2s = strId2s.Substring(0, strId2s.Length - 1);

List<string> strid1Arr = strId1s.Split(‘,‘).ToList();
List<string> strid2Arr = strId2s.Split(‘,‘).ToList();

for (int i = 0; i < strid1Arr.Count; i++)
{
for (int j = 0; j < strid2Arr.Count; j++)
{
List<BasLimitConfig> BasLimitConfigs = SpringCache.Get("GetBasLimitConfigs") as List<BasLimitConfig>;
string i1 = strid1Arr[i];
string i2 = strid2Arr[j];
//if (list1.Contains(int.Parse(i1)) && list2.Contains(int.Parse(i2)))
{
BasLimitConfigs = (from s in BasLimitConfigs
where s.LimitDim1Id.ToString() == i1.ToString() && s.LimitDim2Id.ToString() == i2.ToString()
select s).ToList();
if (BasLimitConfigs != null && BasLimitConfigs.Count > 0)
{
BasLimitConfig b1 = BasLimitConfigs[0];
//QueryInfo info = new QueryInfo("BasLimitConfig");
//info.AddParam("LimitDim1Id", i1, " and LimitDim1Id=:LimitDim1Id");
//info.AddParam("LimitDim2Id", i2, " and LimitDim2Id=:LimitDim2Id");
//BasLimitConfig b1 = Dao.FindOne(info) as BasLimitConfig;
if (b1 != null)
{
d += b1.UpperLimitValue;

}
}

}

}

}
}
catch (Exception ex)
{

JSUtil.log(ex.ToString());
}

return d;
}

private decimal? NextnodeAmount(BasLimitConfig b)
{
decimal? d = 0;
int? id1 = b.LimitDim1Id;
int? id2 = b.LimitDim2Id;

try
{

string strId1s = GetAllTraderId(id1);
if (strId1s.Contains(‘,‘))
strId1s = strId1s.Substring(0, strId1s.Length - 1);
string strId2s = GetAllProductId(id2);
if (strId2s.Contains(‘,‘))
strId2s = strId2s.Substring(0, strId2s.Length - 1);

List<string> strid1Arr = strId1s.Split(‘,‘).ToList();
List<string> strid2Arr = strId2s.Split(‘,‘).ToList();

for (int i = 0; i < strid1Arr.Count; i++)
{
for (int j = 0; j < strid2Arr.Count; j++)
{
List<BasLimitConfig> BasLimitConfigs = SpringCache.Get("GetBasLimitConfigs") as List<BasLimitConfig>;
string i1 = strid1Arr[i];
string i2 = strid2Arr[j];
//if (list1.Contains(int.Parse(i1)) && list2.Contains(int.Parse(i2)))
{
BasLimitConfigs = (from s in BasLimitConfigs
where s.LimitDim1Id.ToString() == i1.ToString() && s.LimitDim2Id.ToString() == i2.ToString()
select s).ToList();
if (BasLimitConfigs != null && BasLimitConfigs.Count > 0)
{
BasLimitConfig b1 = BasLimitConfigs[0];

if (b1 != null)
{
d += b1.UpperLimitValue;

}
}

}

}

}
}
catch (Exception ex)
{

JSUtil.log(ex);
}

return d;

}
//why
private string GetchildProductId(int? id2)
{
string PNameList = string.Empty;
List<BasLimitProStruc> childList = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;
childList = (from s in childList where s.ParentId.ToString() == id2.ToString() select s).ToList();
for (int i = 0; i < childList.Count; i++)
{
PNameList += (int.Parse(childList[i].Id)) + ",";
}

return PNameList;
}

private string GetchildServiceId(int? id2)
{
string PNameList = string.Empty;
List<BasLimitBusiStruc> childList = SpringCache.Get("BasLimitBusiStrucs") as List<BasLimitBusiStruc>;
childList = (from s in childList where s.ParentId.ToString() == id2.ToString() select s).ToList();
for (int i = 0; i < childList.Count; i++)
{
PNameList += (int.Parse(childList[i].Id)) + ",";
}
return PNameList;
}
private string GetAllProductId(int? id2)
{
string PNameList = string.Empty;
if (IsMoney(id2.ToString()))
{
list4.Add(id2);
return id2.ToString();
}
List<BasLimitProStruc> childList2 = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;
childList2 = (from s in childList2 where s.ParentId.ToString() == id2.ToString() select s).ToList();

//QueryInfo info = new QueryInfo("BasLimitProStruc");
//info.AddParam("ParentId", id2, " and ParentId=:ParentId");

//IList childList = Dao.FindByQueryInfo(info).List;

for (int i = 0; i < childList2.Count; i++)
{
//BasLimitProStruc basLimitPro = childList[i] as BasLimitProStruc;
PNameList += GetAllProductId(int.Parse(childList2[i].Id)) + ",";
}

return PNameList;
}

private string GetAllTraderId(int? id1)
{
string PNameList = string.Empty;
if (IsTrader(id1))
{

list3.Add(id1);

return id1.ToString();
}
//
List<BasLimitBusiStruc> childList = SpringCache.Get("BasLimitBusiStrucs") as List<BasLimitBusiStruc>;
childList = (from s in childList where s.ParentId.ToString() == id1.ToString() select s).ToList();

//
//QueryInfo info = new QueryInfo("BasLimitBusiStruc");
//info.AddParam("ParentId", id1, " and ParentId=:ParentId");
//IList childList = Dao.FindByQueryInfo(info).List;
if (childList != null && childList.Count > 0)
{
for (int i = 0; i < childList.Count; i++)
{
//BasLimitBusiStruc child = childList[i] as BasLimitBusiStruc;

PNameList += GetAllTraderId(int.Parse(childList[i].Id)) + ",";
}
}

return PNameList;

}

private bool IsMoney(string limitDim2Id)
{

List<BasLimitProStruc> list = SpringCache.Get("GetBasLimitProStrucs") as List<BasLimitProStruc>;
list = (from s in list where s.Id.ToString() == limitDim2Id.ToString() && s.IsMoney == 1 select s).ToList();

//QueryInfo MoneyInfo = new QueryInfo("BasLimitProStruc");

//MoneyInfo.Where.Add("where", " and Id=:limitDim2Id and ProLevel=4");
//MoneyInfo.Parameters.Add("limitDim2Id", limitDim2Id);
//IList list = Dao.FindByQueryInfo(MoneyInfo).List;

if (list.Count > 0)
{
return true;
}
return false;
}

private bool IsTrader(decimal? limitDim1Id)
{
List<BasLimitBusiStruc> list = SpringCache.Get("BasLimitBusiStrucs") as List<BasLimitBusiStruc>;
list = (from s in list where s.Id == limitDim1Id.ToString() && s.IsTrader == 1 select s).ToList();

//QueryInfo TraderInfo = new QueryInfo("BasLimitBusiStruc");

//TraderInfo.Where.Add("where", " and Id=:limitDim1Id and IsTrader=1");
//TraderInfo.Parameters.Add("limitDim1Id", limitDim1Id);
//IList list = Dao.FindByQueryInfo(TraderInfo).List;

if (list.Count > 0)
{
return true;
}
return false;
}

}

internal class PorductUpperLimit
{
public int? TraderId { get; set; }
public string TraderName { get; set; }
public int? ProductId { get; set; }
public string ProductName { get; set; }
public decimal? UpperLimitValue { get; set; }

}
}

OracleBulkCopy

标签：

热心网友 时间：2022-04-07 22:30