带有 SQL 批量插入的 Refractor 代码 {ADO.NET, ODP.NET}

发布于 2024-11-30 08:53:50 字数 4345 浏览 0 评论 0原文

您好,我使用 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

攀登最高峰 2024-12-07 08:53:50

这可能不是完整的解决方案,但首先我会尝试避免对参数名称进行硬编码。我将从表模式本身获取参数名称,然后通过它提交数据。这是一些重构。我写了一篇关于此的文章,请查看: 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

错々过的事 2024-12-07 08:53:50

这对我来说看起来不错,但我建议对其运行 StyleCop。

此外,您还可以对 SQL 语句使用其他格式:

string sqlStatement = @"
insert into TABLE
( COL1,  COL2,  COL3) VALUES
(:COL1, :COL2, :COL3)
";

还可以对连接和命令使用 using。它实现了IDisposable,因此可以包装在using 块中。

using(var connection = this.GetSomeConnection())
using(var command = this.InitTheCommandSomehow(connection))
{
    // here your code can safely throw exceptions etc.
    // and the connection will still be closed

    ...

    cmd.Parameters.Add(
        "id",
        OracleDbType.Varchar2,
        _id,
        ParameterDirection.Input);

    ...

}

This looks good to me, but I'd suggest to run StyleCop over it.

Additionally you could use another formatting for the SQL statements:

string sqlStatement = @"
insert into TABLE
( COL1,  COL2,  COL3) VALUES
(:COL1, :COL2, :COL3)
";

Also use using for the connection and the command. It implements IDisposable and thus can be wrapped in a using block.

using(var connection = this.GetSomeConnection())
using(var command = this.InitTheCommandSomehow(connection))
{
    // here your code can safely throw exceptions etc.
    // and the connection will still be closed

    ...

    cmd.Parameters.Add(
        "id",
        OracleDbType.Varchar2,
        _id,
        ParameterDirection.Input);

    ...

}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文