C＃将数组批量插入数据库（Oracle对等SQL Server） [英] C# Bulk insert of array into database (Oracle vis-a-vis SQL Server)

查看：323
本文介绍了C＃将数组批量插入数据库（Oracle对等SQL Server）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

嗨！



以下代码段1适用于将数组批量插入Oracle数据库。



但是，相应的代码片段2无法将数组批量插入SQL数据库。



当然，我不想使用SQLBulkCopy / Stored Procedure /任何循环来执行"commandSql.ExecuteNonQuery（）;" ;。



我只想要相应的SQL代码。



当且仅当您有明确的解决方案时，请回复，因为不必要的答案会减少获得答案的机会。



谢谢。




代码段1：



usi ng 系统;


使用 System.Data;


使用 Oracle.DataAccess.Client;


使用 Oracle.DataAccess.Types;



命名空间 BulkInsert


{


class classBulkInsert


{


static void Main（ string [] args）


{


OracleConnection connectionOracle = new OracleConnection（）;


connectionOracle.ConnectionString = " User Id = scott;密码= tiger;数据源= oracle;" ;



connectionOracle.Open（）;


控制台 .WriteLine（ "已成功连接" ）;



int [] myArrayDeptNo = new int [3] {10,20,30};


string < font size = 2> [] myArrayDeptName = new string [3] { " Accounts" ， " HR" ， " Admin" };


OracleCommand commandOracle = new OracleCommand（）;



//在OracleCommand对象上设置命令文本


commandOracle.CommandText = " insert into dept（DEPTNO，DEPTNAME）值（< img alt ="Big Smile"src ="http://forums.microsoft.com/MSDN/emoticons/emotion-2.gif"> eptno，eptname)";


commandOracle.Connection = connectionOracl e;



//设置ArrayBindCount以指示值的数量


commandOracle.ArrayBindCount = 3;



//为数组操作创建参数


OracleParameter prmdeptno = new OracleParameter（ " deptno" ，OracleDbType.Int32）;


prmdeptno.Direction = ParameterDirection .Input;


prmdeptno.Value = myArrayDeptNo;


commandOracle.Parameters.Add（prmdeptno ）;




OracleParameter prmdeptname = new OracleParameter（ " deptname" ，OracleDbType.Varchar2）;


prmdeptname.Direction = ParameterDirection .Input;


prmdeptname.Value = myArrayDeptName;



//将参数添加到参数集合


commandOracle.Parameters.Add（prmdeptname） ;



//执行命令


commandOracle.ExecuteNonQuery（）;


控制台 .WriteLine（ " Insert Completed Successfully" ）;



//关闭并处置OracleConnection对象


connectionOracle.Close（）;


connectionOracle.Dispose（） ;


}


}


}



代码段2：


< font color ="＃0000ff"size = 2>

使用 系统;


使用 System.Data;


使用 System.Data.SqlClient;


使用 < font size = 2> System.Data.SqlTypes;


namespace BulkInsert


{


class classBulkInsert


{


static void Main（ string [] arg s）


{


SqlConnection connectionSql = new SqlConnection （）;


connectionSql.ConnectionString = " Data Source =（local） ;集成安全性= sspi" ;


connectionSql.Open（）;


< font color ="＃008080"size = 2>控制台 .WriteLine（ ""已成功连接" ）;




int [] myArrayDeptNo = new int [3] {10 ，20,30};


string [] my ArrayDeptName = new string [3] { " Accounts" ， " HR" ， " Admin" };



SqlCommand commandSql = new SqlCommand （）;



//在SqlCommand对象上设置命令文本


commandSql.CommandText = " insert into dept（DEPTNO，DEPTNAME）值（@ deptno，@ deptname）" ; ;


commandSql.Co nnection = connectionSql;



//设置ArrayBindCount表示值的数量


// commandSql.ArrayBindCount = 3 ; //？


//为数组操作创建参数




SqlParameter prmdeptno = new SqlParameter （ < font color ="＃800000"size = 2>" deptno" ， SqlDbType .Int）;


prmdeptno.Direction = ParameterDirection 。输入;


prmdeptno.Value = myArrayDeptNo;


commandSql.Parameters.Add（prmdeptno）;



SqlParameter prmdeptname = new SqlParameter （ " deptname" ， SqlDbType .VarChar）;


prmdeptname.Direction = < font color ="＃008080"size = 2> ParameterDirection .Input;


prmdeptname.Value = myArrayDeptName;



//将参数添加到参数集合


commandSql.Parameters.Add（prmdeptname）;



//执行命令


commandSql.ExecuteNonQuery（）;



控制台 .WriteLine（ " Insert Insertted successfullyfully" ）;



//关闭并处置SqlConnection对象


connectionSql.Close（）;


connectionSql .Dispose（）;


控制台 .Read（）;


}


}


}


< font color ="＃008000"size = 2>


SQL查询：



USE [master];


Go



IF OBJECT_ID（'dbo.dept'，'U '）IS NOT NULL


DROP TABLE dbo.dept


Go



CREATE TABLE dbo.dept


（


DEPTNO INT，


DEPTNAME VARCHAR（100）


）


Go



SELECT * FROM dbo.dept

解决方案

您需要使用交易来在SQLServer上做大量工作。


很抱歉这是从我的应用程序中获取的，但是你必须弄清楚这些方法在做什么：

 private void DoDTransaction（）
 {
 DStarted = true; 
 string tCommand ="" ;; 
 
 SqlConnection sCon = new SqlConnection（" Data Source = tcp：92.27.114.95; Initial Catalog = OperatingSystems; Persist Security Info = True; User ID =< user> ;; Password =< pass> "）; 
 SqlCommand sCommand = new SqlCommand（）; 
 
 ArrayList alHashes = new ArrayList（）; 
 int idx = 0; 
 int idx2 = 0; 
 long tmpTotalFileLength = 0; 
 
 foreach（alFiles中的FileInfo信息）
 {
 Application.DoEvents（）; 
 DNumberOfFilesProcessed ++; 
 //首先获取文件哈希
任务Sha1Task = Task.Factory.StartNew（（）=> Sha1 = Hash.SHA1HashFile（info.FullName））; 
 Task.WaitAll（Sha1Task）; 
 alHashes.Add（GetString（Sha1））; 
 DNumberOfFilesProcessed = idx2; 
 
 tmpTotalFileLength + = info.Length; 
 
 if（idx == 99）
 {
 tCommand = BuildDTransactionCommand（alHashes）; 
 alHashes.Clear（）; 
 
 sCommand.Connection = sCon; 
 sCommand.CommandType = CommandType.Text; 
 sCommand.CommandText = tCommand; 
 idx = 0; 
 
尝试
 {
 if（sCon.State！= ConnectionState.Open）sCon.Open（）; 
} 
 catch（例外）
 {
} 
 
尝试
 {
 int res =（int）sCommand.ExecuteScalar （）; 
 
 if（res< = 100）
 {
 DName = info.Name; 
 DNumberOfFilesRecognised + = res; 
 tsDamoclesStatus.Image = ilDatabases.Images [1]; 
 DTotalFileSize + = tmpTotalFileLength; 
} 
其他
 {
 DNumberOfErrors + = res; 
 tsDamoclesStatus.Image = ilDatabases.Images [3]; 
} 
 
 Application.DoEvents（）; 
} 
 catch（exception ex）
 {
 string ee = ex.Message; 
} 
 alHashes.Clear（）; 
} 
 
 idx ++; 
 idx2 ++; 
} 
 
 tCommand = BuildDTransactionCommand（alHashes）; 
 alHashes.Clear（）; 
 
 sCommand.Connection = sCon; 
 sCommand.CommandType = CommandType.Text; 
 sCommand.CommandText = tCommand; 
尝试
 {
 sCon.Open（）; 
} 
 catch（exception ex）
 {
 string ee = ex.Message; 
 // throw; 
} 
 
 try 
 {
 int res =（int）sCommand.ExecuteScalar（）; 
 if（res< = 100）
 {
 // DName = info.Name; 
 DNumberOfFilesRecognised + = res; 
 DTotalRowsReturned = DNumberOfFilesRecognised; 
 
 tsDamoclesStatus.Image = ilDatabases.Images [1]; 
 DTotalFileSize + = tmpTotalFileLength; 
} 
其他
 {
 DNumberOfErrors + = res; 
 tsDamoclesStatus.Image = ilDatabases.Images [3]; 
} 
} 
 catch（例外）
 {
} 
} 
 
私有字符串BuildDTransactionCommand（ArrayList alHashes）
 {
 StringBuilder sbCommand = new StringBuilder（）; 
 
 sbCommand.Append（" SELECT COUNT（1）FROM Files WHERE SHA1Hash IN（"）; 
 foreach（alHashes中的字符串哈希）
 {
应用程序.DoEvents（）; 
 sbCommand.Append（"'" + hash +"'，"）; 
} 
 
 string fCommand = sbCommand.ToString（） ; 
 fCommand = fCommand.Trim（'，'）; 
 fCommand + ="）" ;; 
 
返回fCommand; 
}


另外，不要忘记将代码示例放在代码块中。


Hi!

 

The following code snippet 1 works fine for bulk insert of array into Oracle database.

 

However, the corresponding code snippet 2 fails for bulk insert of array into SQL database.

 

Definitely, I do not want to use SQLBulkCopy/Stored Procedure/any loop for "commandSql.ExecuteNonQuery();".

 

I just want the corresponding SQL code.

 

Please reply if and only if you have a definite solution as unnecessary answers reduce the chances of getting the answer .

 

Thank you.

 

 

Code snippet 1:

 

using System;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

 

namespace BulkInsert

{

class classBulkInsert

{

static void Main(string[] args)

{

OracleConnection connectionOracle = new OracleConnection();

connectionOracle.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";

connectionOracle.Open();

Console.WriteLine("Connected successfully");

 

int[] myArrayDeptNo = new int[3] { 10, 20, 30 };

string[] myArrayDeptName = new string[3] { "Accounts","HR","Admin" };

OracleCommand commandOracle = new OracleCommand();

 

// Set the command text on an OracleCommand object

commandOracle.CommandText = "insert into dept(DEPTNO,DEPTNAME) values (eptno,eptname)";

commandOracle.Connection = connectionOracle;

 

// Set the ArrayBindCount to indicate the number of values

commandOracle.ArrayBindCount = 3;

 

// Create a parameter for the array operations

OracleParameter prmdeptno = new OracleParameter("deptno", OracleDbType.Int32);

prmdeptno.Direction = ParameterDirection.Input;

prmdeptno.Value = myArrayDeptNo;

commandOracle.Parameters.Add(prmdeptno);

 

OracleParameter prmdeptname = new OracleParameter("deptname", OracleDbType.Varchar2);

prmdeptname.Direction = ParameterDirection.Input;

prmdeptname.Value = myArrayDeptName;

 

// Add the parameter to the parameter collection

commandOracle.Parameters.Add(prmdeptname);

 

// Execute the command

commandOracle.ExecuteNonQuery();

Console.WriteLine("Insert Completed Successfully");

 

// Close and Dispose OracleConnection object

connectionOracle.Close();

connectionOracle.Dispose();

}

}

}

 

Code snippet 2:

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

namespace BulkInsert

{

class classBulkInsert

{

static void Main(string[] args)

{

SqlConnection connectionSql = new SqlConnection();

connectionSql.ConnectionString = "Data Source=(local);Integrated Security=sspi";

connectionSql.Open();

Console.WriteLine("Connected successfully");

 

int[] myArrayDeptNo = new int[3] { 10, 20, 30 };

string[] myArrayDeptName = new string[3] { "Accounts", "HR", "Admin" };

 

SqlCommand commandSql = new SqlCommand();

 

// Set the command text on an SqlCommand object

commandSql.CommandText = "insert into dept(DEPTNO,DEPTNAME) values (@deptno,@deptname)";

commandSql.Connection = connectionSql;

 

// Set the ArrayBindCount to indicate the number of values

//commandSql.ArrayBindCount = 3; // ?

// Create a parameter for the array operations

 

SqlParameter prmdeptno = new SqlParameter("deptno", SqlDbType.Int);

prmdeptno.Direction = ParameterDirection.Input;

prmdeptno.Value = myArrayDeptNo;

commandSql.Parameters.Add(prmdeptno);

 

SqlParameter prmdeptname = new SqlParameter("deptname", SqlDbType.VarChar);

prmdeptname.Direction = ParameterDirection.Input;

prmdeptname.Value = myArrayDeptName;

 

// Add the parameter to the parameter collection

commandSql.Parameters.Add(prmdeptname);

 

// Execute the command

commandSql.ExecuteNonQuery();

 

Console.WriteLine("Insert Completed Successfully");

 

// Close and Dispose SqlConnection object

connectionSql.Close();

connectionSql.Dispose();

Console.Read();

}

}

}

 

 

SQL Query :

 

USE [master];

Go

 

IF OBJECT_ID('dbo.dept', 'U') IS NOT NULL

DROP TABLE dbo.dept

Go

 

CREATE TABLE dbo.dept

(

DEPTNO INT,

DEPTNAME VARCHAR(100)

)

Go

 

SELECT * FROM dbo.dept

解决方案

You need to use transactions to do a bulk job on SQLServer.

Sorry this is taken from an app of mine's it works but you will have to work out what the methods are doing:

   private void DoDTransaction()
        {
            DStarted = true;
            string tCommand = "";

            SqlConnection sCon = new SqlConnection("Data Source=tcp:92.27.114.95;Initial Catalog=OperatingSystems;Persist Security Info=True;User ID=<user>;Password=<pass>");
            SqlCommand sCommand = new SqlCommand();

            ArrayList alHashes = new ArrayList();
            int idx = 0;
            int idx2 = 0;
            long tmpTotalFileLength = 0;

            foreach (FileInfo info in alFiles)
            {
                Application.DoEvents();
                DNumberOfFilesProcessed++;
                // Get file Hashes first
                Task Sha1Task = Task.Factory.StartNew(() => Sha1 = Hash.SHA1HashFile(info.FullName));
                Task.WaitAll(Sha1Task);
                alHashes.Add(GetString(Sha1));
                DNumberOfFilesProcessed = idx2;

                tmpTotalFileLength += info.Length;

                if (idx == 99)
                {
                    tCommand = BuildDTransactionCommand(alHashes);
                    alHashes.Clear();

                    sCommand.Connection = sCon;
                    sCommand.CommandType = CommandType.Text;
                    sCommand.CommandText = tCommand;
                    idx = 0;

                    try
                    {
                        if (sCon.State != ConnectionState.Open) sCon.Open();
                    }
                    catch (Exception)
                    {
                    }

                    try
                    {
                        int res = (int)sCommand.ExecuteScalar();

                        if (res <= 100)
                        {
                            DName = info.Name;
                            DNumberOfFilesRecognised += res;
                            tsDamoclesStatus.Image = ilDatabases.Images[1];
                            DTotalFileSize += tmpTotalFileLength;
                        }
                        else
                        {
                            DNumberOfErrors += res;
                            tsDamoclesStatus.Image = ilDatabases.Images[3];
                        }

                        Application.DoEvents();
                    }
                    catch (Exception ex)
                    {
                        string ee = ex.Message;
                    }
                    alHashes.Clear();
                }

                idx++;
                idx2++;
            }

            tCommand = BuildDTransactionCommand(alHashes);
            alHashes.Clear();

            sCommand.Connection = sCon;
            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = tCommand;
            try
            {
                sCon.Open();
            }
            catch (Exception ex)
            {
                string ee = ex.Message;
                //   throw;
            }

            try
            {
                int res = (int)sCommand.ExecuteScalar();
                if (res <= 100)
                {
                    //  DName = info.Name;
                    DNumberOfFilesRecognised += res;
                    DTotalRowsReturned = DNumberOfFilesRecognised;

                    tsDamoclesStatus.Image = ilDatabases.Images[1];
                    DTotalFileSize += tmpTotalFileLength;
                }
                else
                {
                    DNumberOfErrors += res;
                    tsDamoclesStatus.Image = ilDatabases.Images[3];
                }
            }
            catch (Exception)
            {
            }
        }

        private string BuildDTransactionCommand(ArrayList alHashes)
        {
            StringBuilder sbCommand = new StringBuilder();

            sbCommand.Append("SELECT COUNT(1) FROM Files WHERE SHA1Hash IN (");
            foreach (string hash in alHashes)
            {
                Application.DoEvents();
                sbCommand.Append("'" + hash + "',");
            }

            string fCommand = sbCommand.ToString();
            fCommand = fCommand.Trim(',');
            fCommand += ")";

            return fCommand;
        }

Also try not to forget to put your code examples in a code block.


这篇关于C＃将数组批量插入数据库（Oracle对等SQL Server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆