C# Bulk insert of array into database (Oracle vis-a-vis SQL Server)

Question

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

Answer 1

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.

---

Digital Forensic Software Developer
CCS LABS Digital Forensic Software
Mark as Answer or Vote up if useful thank you!