带有 SQL 批量插入的 Refractor 代码 {ADO.NET, ODP.NET}
您好,我使用 SQL 批量插入到 Oracle 10g Db。我使用 ODP.NET 因为我需要在表中插入 20.000 - 40.000 行。长时间使用 LINQ to SQL(对于 oracle devart http://www.devart.com/linqconnect/)。 现在我必须使用“ADO.NET”对象,但我的代码看起来很糟糕。你能帮助我并给出我如何折射它的想法吗?我想在代码的可读性和性能之间找到折衷方案。
对不起我的英语
private void InitArrays(int size)
{
_id = new string[size];
_lm = new DateTime[size];
_priceWithoutDiscount = new decimal[size];
_priceWithDiscount = new decimal[size];
_talkTime = new int[size];
_type = new string[size];
_voiceNetwork = new string[size];
_callNo = new string[size];
_callDate = new DateTime[size];
_callType = new string[size];
_surname = new string[size];
_name = new string[size];
_no = new string[size];
}
private void PrepareArrays(IList<Call> calls)
{
InitArrays(calls.Count);
Parallel.For(0, calls.Count, i =>
{
_id[i] = IdGenerator.GenerateGuidForCall(calls[i]);
_no[i] = calls[i].Number;
_name[i] = calls[i].Name;
_surname[i] = calls[i].Surname;
_callType[i] = calls[i].CallType;
_callDate[i] = calls[i].Dt;
_callNo[i] = calls[i].CallingNumber;
_voiceNetwork[i] = calls[i].VoiceNetwork;
_type[i] = calls[i].Type;
_talkTime[i] = calls[i].TalkTimeInSec;
_priceWithDiscount[i] = (decimal)calls[i].PriceWithDiscount;
_priceWithoutDiscount[i] = (decimal)calls[i].PriceWithoutDiscount;
_lm[i] = DateTime.Now;
});
}
public void InsertCalls(IList<Call> calls)
{
PrepareArrays(calls);
string sql = "insert into r_calls (ID, NO, NAME, SURNAME, CALL_TYPE, CALL_DATE, CALL_NO, VOICE_NETWORK,"
+"TYPE, TALK_TIME,PRICE_WITH_DISCOUNT, PRICE_WITHOUT_DISCOUNT, LM_MODIFIED) "
+ "values (:id, :no, :name, :surname, :callType, :callDate, :callNo, :voiceNetwork, :type,"
+" :talkTime, :priceWithDiscount, :priceWithoutDiscount, :lm)";
var cnn = new OracleConnection(GenerateConnectionString());
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = _id.Count();
// Instead of single values pass arrays of values as parameters
cmd.Parameters.Add(":id", OracleDbType.Varchar2,
_id, ParameterDirection.Input);
cmd.Parameters.Add(":no", OracleDbType.Varchar2,
_no, ParameterDirection.Input);
cmd.Parameters.Add(":name", OracleDbType.Varchar2,
_name, ParameterDirection.Input);
cmd.Parameters.Add(":surname", OracleDbType.Varchar2,
_surname, ParameterDirection.Input);
cmd.Parameters.Add(":callType", OracleDbType.Varchar2,
_callType, ParameterDirection.Input);
cmd.Parameters.Add(":callDate", OracleDbType.Date,
_callDate, ParameterDirection.Input);
cmd.Parameters.Add(":callNo", OracleDbType.Varchar2,
_callNo, ParameterDirection.Input);
cmd.Parameters.Add(":voiceNetwork", OracleDbType.Varchar2,
_voiceNetwork, ParameterDirection.Input);
cmd.Parameters.Add(":type", OracleDbType.Varchar2,
_type, ParameterDirection.Input);
cmd.Parameters.Add(":talkTime", OracleDbType.Decimal,
_talkTime, ParameterDirection.Input);
cmd.Parameters.Add(":priceWithDiscount", OracleDbType.Decimal,
_priceWithDiscount, ParameterDirection.Input);
cmd.Parameters.Add(":priceWithoutDiscount", OracleDbType.Decimal,
_priceWithoutDiscount, ParameterDirection.Input);
cmd.Parameters.Add(":lm", OracleDbType.Date,
_lm, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close();
}
Hi I use SQL bulk insert to Oracle 10g Db. I use ODP.NET because I need insert 20.000 - 40.000 rows in table. Long time a use LINQ to SQL (for oracle devart http://www.devart.com/linqconnect/).
Now I must use "ADO.NET" object but my code look horrible. Can you help me and give my ideas how to refractor it? I would like to find compromise between readability of code and performacne.
Sorry for my english
private void InitArrays(int size)
{
_id = new string[size];
_lm = new DateTime[size];
_priceWithoutDiscount = new decimal[size];
_priceWithDiscount = new decimal[size];
_talkTime = new int[size];
_type = new string[size];
_voiceNetwork = new string[size];
_callNo = new string[size];
_callDate = new DateTime[size];
_callType = new string[size];
_surname = new string[size];
_name = new string[size];
_no = new string[size];
}
private void PrepareArrays(IList<Call> calls)
{
InitArrays(calls.Count);
Parallel.For(0, calls.Count, i =>
{
_id[i] = IdGenerator.GenerateGuidForCall(calls[i]);
_no[i] = calls[i].Number;
_name[i] = calls[i].Name;
_surname[i] = calls[i].Surname;
_callType[i] = calls[i].CallType;
_callDate[i] = calls[i].Dt;
_callNo[i] = calls[i].CallingNumber;
_voiceNetwork[i] = calls[i].VoiceNetwork;
_type[i] = calls[i].Type;
_talkTime[i] = calls[i].TalkTimeInSec;
_priceWithDiscount[i] = (decimal)calls[i].PriceWithDiscount;
_priceWithoutDiscount[i] = (decimal)calls[i].PriceWithoutDiscount;
_lm[i] = DateTime.Now;
});
}
public void InsertCalls(IList<Call> calls)
{
PrepareArrays(calls);
string sql = "insert into r_calls (ID, NO, NAME, SURNAME, CALL_TYPE, CALL_DATE, CALL_NO, VOICE_NETWORK,"
+"TYPE, TALK_TIME,PRICE_WITH_DISCOUNT, PRICE_WITHOUT_DISCOUNT, LM_MODIFIED) "
+ "values (:id, :no, :name, :surname, :callType, :callDate, :callNo, :voiceNetwork, :type,"
+" :talkTime, :priceWithDiscount, :priceWithoutDiscount, :lm)";
var cnn = new OracleConnection(GenerateConnectionString());
cnn.Open();
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = _id.Count();
// Instead of single values pass arrays of values as parameters
cmd.Parameters.Add(":id", OracleDbType.Varchar2,
_id, ParameterDirection.Input);
cmd.Parameters.Add(":no", OracleDbType.Varchar2,
_no, ParameterDirection.Input);
cmd.Parameters.Add(":name", OracleDbType.Varchar2,
_name, ParameterDirection.Input);
cmd.Parameters.Add(":surname", OracleDbType.Varchar2,
_surname, ParameterDirection.Input);
cmd.Parameters.Add(":callType", OracleDbType.Varchar2,
_callType, ParameterDirection.Input);
cmd.Parameters.Add(":callDate", OracleDbType.Date,
_callDate, ParameterDirection.Input);
cmd.Parameters.Add(":callNo", OracleDbType.Varchar2,
_callNo, ParameterDirection.Input);
cmd.Parameters.Add(":voiceNetwork", OracleDbType.Varchar2,
_voiceNetwork, ParameterDirection.Input);
cmd.Parameters.Add(":type", OracleDbType.Varchar2,
_type, ParameterDirection.Input);
cmd.Parameters.Add(":talkTime", OracleDbType.Decimal,
_talkTime, ParameterDirection.Input);
cmd.Parameters.Add(":priceWithDiscount", OracleDbType.Decimal,
_priceWithDiscount, ParameterDirection.Input);
cmd.Parameters.Add(":priceWithoutDiscount", OracleDbType.Decimal,
_priceWithoutDiscount, ParameterDirection.Input);
cmd.Parameters.Add(":lm", OracleDbType.Date,
_lm, ParameterDirection.Input);
cmd.ExecuteNonQuery();
cnn.Close();
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
这可能不是完整的解决方案,但首先我会尝试避免对参数名称进行硬编码。我将从表模式本身获取参数名称,然后通过它提交数据。这是一些重构。我写了一篇关于此的文章,请查看: http://www.codeproject.com/ KB/TipsnTricks/StoredProcSchemaSaving.aspx
This may not be the whole solution, but first i would try avoiding hardcoding of the parameter names. I will get the param names from the table schema itself and then submit data over it. A bit of refactoring this is. I wrote an article on this, check it out: http://www.codeproject.com/KB/TipsnTricks/StoredProcSchemaSaving.aspx
这对我来说看起来不错,但我建议对其运行 StyleCop。
此外,您还可以对 SQL 语句使用其他格式:
还可以对连接和命令使用
using
。它实现了IDisposable
,因此可以包装在using
块中。This looks good to me, but I'd suggest to run StyleCop over it.
Additionally you could use another formatting for the SQL statements:
Also use
using
for the connection and the command. It implementsIDisposable
and thus can be wrapped in ausing
block.