CODEWITHSUNDEEP
c#sqlsql-server
user3014698
user3014698

Reputation: 35

Faster Way to do Inserts C# & SQL

I am having trouble thinking of a way to do bulk SQL insert commands in C# or even a SQL query.

I usually get a part number list as so... sometimes its 100+ parts

LTM-120
LTM-130
LTM-140
LTM-120
LTM-130
LTM-140

And, I use a button to insert it in C# as so...

Textbox20 is the PartNumber and Textbox15 is the PartNumber ID

SqlConnection sqlCon3 = new SqlConnection("REMOVED");
SqlCommand sqlCmd3 = new SqlCommand();
sqlCmd3.CommandText = "INSERT INTO [Products].[Features] " +
                      "([ProductID] ,[Title] ,[ViewOrder]) VALUES ('" +
                      textBox15.Text + "', '" + textBox20.Text + "', NULL) ";
sqlCmd3.Connection = sqlCon3;

sqlCon3.Open();
sqlCmd3.ExecuteNonQuery();
sqlCon3.Close();

Is there any way to do a bulk query for a bunch of part numbers? Or does anyone have any ideas on how to do something like this?

Upvotes: 0

Views: 403

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74267

Bulk Copy is certainly a way to go.

However, since your data set is relative small (100 items), you might consider using SQL Server's support for XML. Given a table like this:

create table dbo.item
(
  id    int          not null primary key clustered ,
  name  varchar(100) not null unique nonclustered ,
)

You can write a stored procedure like this:

create procedure dbo.insert_items

  @item_list xml

as

  insert dbo.item
  (
    id   ,
    name
  )
  select id   =              X.value( './id[1]'   , 'int'          )    ,
         name = ltrim(rtrim( X.value( './name[1]' , 'varchar(100)' ) ))
  from @item_list.nodes('/items/item') as itemList(X) 

  return @@rowcount
go

which can be invoked like this (in SQL Server Management Studio):

exec insert_items '
  <items>
    <item><id> 1 </id><name> alpha   </name></item>
    <item><id> 2 </id><name> bravo   </name></item>
    <item><id> 3 </id><name> charlie </name></item>
    <item><id> 4 </id><name> delta   </name></item>
  </items>
  '

Or, from C#, like this:

string connect_string = "Server=localhost;Database=sandbox;Trusted_Connection=True;" ;

string myXmlString    = @"
  <items>
    <item><id> 1 </id><name> alpha   </name></item>
    <item><id> 2 </id><name> bravo   </name></item>
    <item><id> 3 </id><name> charlie </name></item>
    <item><id> 4 </id><name> delta   </name></item>
  </items>
" ;

using ( SqlConnection conn = new SqlConnection(connect_string) )
using ( SqlCommand    cmd  = conn.CreateCommand() )
{
  cmd.CommandText = "dbo.insert_items" ;
  cmd.CommandType = CommandType.StoredProcedure ;
  cmd.Parameters.AddWithValue( "@item_list" , myXmlString ) ;
  conn.Open() ;
  cmd.ExecuteNonQuery() ;
  conn.Close() ;
}

Then it's a taking your part number list and serializing it into suitable XML (if it's not already) and building the correct XQuery/XPath in your stored procedure to pull out the necessary bits.

In the real world, since XML processing can be...expensive and painful, I find it easiest to have the stored procedure first load the XML into a temporary table using OpenXml() first, just to get dealing with the XML out of the way up front. That done, the remainder of the stored procedure can be straight SQL. It simplifies debugging and maintenance, too.

Upvotes: 0

shadowjfaith
shadowjfaith

Reputation: 942

You could read into an array and loop through inserting one line at a time. I'm sure there are better solutions than this, but it will work.

Pseudocode-

Fill array from file

for(count int = 0; count < arr.length; count++)
{
  sqlcon.open()
  sqlcmd = New select statment with arr[count] values
  sqlcmd.execute
  sqlcon.close()
}

Essentially get your data into and array, loop through the array, pull each data value and insert into your DB. This can cause issues if you have bad data. You may want to do some research on SQL Injection and Parameters to implement some methods to ensure you DB doesn't get corrupted.

EDIT:

While safer with a close connection each time how much will this effect speed? I would assume opening and closing the connection every time would be slower than open before the loop and close after data processing is done.

EDIT 2:

Do as stated above, but instead of filling from a static file fill the array from the user selected file by implementing a browse/open file capability seen here

http://msdn.microsoft.com/en-us/library/cc221415(v=vs.95).aspx

Then your INSERT statement will remain constant for each value except for the new data value being inserted.

Upvotes: 0

Paul Williams
Paul Williams

Reputation: 17020

There are many different techniques for doing multiple INSERTs at once. Other answers have discussed these (SqlBulkCopy, BULK INSERT, etc.)

You can also build a SQL statement that does multiple INSERTs at once. For example, using INSERT VALUES, string multiple VALUES clauses together:

INSERT INTO [Products].[Features] ([ProductID], [Title], [ViewOrder])
VALUES (1, 'Title 1', NULL),
    (2, 'Title 2', NULL),
    -- etc.

Or use INSERT with a UNION:

INSERT INTO [Products].[Features] ([ProductID], [Title], [ViewOrder])
SELECT 1, 'Title 1', NULL
UNION ALL SELECT 2, 'Title 2', NULL
UNION ALL -- etc.

You really need to be careful with your parameters, however. As others have pointed out, your current code is vulnerable to SQL injection attacks.

Upvotes: 0

seddik
seddik

Reputation: 646

or you can use parameters its safer

SqlConnection sqlCon3 = new SqlConnection("REMOVED");
                SqlCommand sqlCmd3 = new SqlCommand();
                sqlCmd3.CommandText = "INSERT INTO [Products].[Features] ([ProductID] ,[Title] ,[ViewOrder]) VALUES (@myID, @myTitle, NULL) ";
                sqlCmd3.Connection = sqlCon3;

                sqlCon3.Open();
                for (int i = 0; i < 10; i++)
                {
                    sqlCmd3.Parameters.Add("myID", SqlDbType.Int).Value = myobjID[i];
                    sqlCmd3.Parameters.Add("myTitle", SqlDbType.Text).Value = myobjTitle[i];
                    sqlCmd3.ExecuteNonQuery();
                    sqlCmd3.Parameters.Clear();
                }

                sqlCon3.Close();

you can also use transactions Executing query with parameters

Upvotes: 0

Frazell Thomas
Frazell Thomas

Reputation: 6111

You can use SqlBulkCopy to do mass inserts to the DB.

    // Create a table with some rows. 
    DataTable newProducts = MakeTable();

    // Create the SqlBulkCopy object.  
    // Note that the column positions in the source DataTable  
    // match the column positions in the destination table so  
    // there is no need to map columns.  
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = 
            "dbo.BulkCopyDemoMatchingColumns";

        try
        {
            // Write from the source to the destination.
            bulkCopy.WriteToServer(newProducts);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

See: MSDN

Upvotes: 1

Related Questions