Batch Insert?

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I was wondering if anyone knows of a way to insert
multiple records without using the command builder.
I have to loop through a directory of files and depending
on how many files there are is how many inserts that I
have to do. There could be hundreds of files. I would
rather try to to some sort of batch insert rather than do
an insert each time it loops through. Does anyone know
of a good way to approach this issue. I would really
appreciate any help I could get here.

Thanks,
Rick
 
One thing I've done in the past to accomplish this is to dynamically build a string that is a single batch of a bunch SQL INSERT statements. Then, you can create a new SqlCommand with that string as the command text and execute it once

You can even put transaction processing in there, if you're concerned about the whole batch completing
 
Rick said:
I was wondering if anyone knows of a way to insert
multiple records without using the command builder.
I have to loop through a directory of files and depending
on how many files there are is how many inserts that I
have to do. There could be hundreds of files. I would
rather try to to some sort of batch insert rather than do
an insert each time it loops through. Does anyone know
of a good way to approach this issue. I would really
appreciate any help I could get here.

Thanks,
Rick
Click to expand...

Rick,

you can also benefit a lot if you look at implementing SqlXml 3.0, i was
able to load 2Gig's worth of data in less than a minute. lookup sqlxml30
in MSDN

hth,
Jonel
 
Here is a function that can do the job---you must pass the corresponding
command to this method. If the datatable has some inserted record and you
don't supply insertCommand, it will generate an error. I use this method to
update a dataTable(with changes only DataTable.GetChanges) in the database
in a batch. You have to build your own command and use stored procedure to
do the update, insert or delete.

public int ExecuteNonQuery(string connectionString,OleDbCommand
updateCommand,OleDbCommand insertCommand,OleDbCommand
deleteCommand,DataTable dataTable,bool useTransaction)
{
int retval=0;
OleDbTransaction txn=null;
OleDbConnection cn=null;

//Check if the DataTable has updated records. If so, an update command is
required.
DataTable updatedTable=dataTable.GetChanges(DataRowState.Modified);
if ((updatedTable !=null) && (updateCommand==null))
{
throw new Exception("Update Failed. Data has been modified but no update
command is specified.");
}

//Check if the DataTable has inserted records. If so, an insert command
is required.
DataTable insertedTable=dataTable.GetChanges(DataRowState.Added);
if ((insertedTable !=null) && (insertCommand==null))
{
throw new Exception("Update Failed. Data has been inserted but no insert
command is specified.");
}

//Check if the DataTable has deleted records. If so, a delete command is
required.
DataTable deletedTable=dataTable.GetChanges(DataRowState.Deleted);
if ((deletedTable !=null) && (deleteCommand==null))
{
throw new Exception("Update Failed. Data has been deleted but no delete
command is specified.");
}


try
{

//create & open a OleDbConnection, and dispose of it after we are done.
cn = new OleDbConnection(connectionString);
cn.Open();

//Create a data adapter
OleDbDataAdapter da = new OleDbDataAdapter();

//Assign commands to the adapter
if (updatedTable !=null)
{
updateCommand.Connection=cn;
da.UpdateCommand=updateCommand;
}

if (insertedTable !=null)
{
insertCommand.Connection=cn;
da.InsertCommand=insertCommand;
}

if (deletedTable !=null)
{
deleteCommand.Connection=cn;
da.DeleteCommand=deleteCommand;
}


//Create a new transaction if useTransaction is true
if (useTransaction==true)
{
txn = cn.BeginTransaction();

//Set the Transaction property of the DataAdapter's Commands.
if (updatedTable !=null)
{
da.UpdateCommand.Transaction = txn;
}
if (insertedTable !=null)
{
da.InsertCommand.Transaction = txn;
}
if (deletedTable !=null)
{
da.DeleteCommand.Transaction = txn;
}
}


//Submit the changes.
retval=da.Update(dataTable);

//Commit the changes and close the connection.
if (txn !=null)
{
txn.Commit();
}

return retval;

}
catch(Exception ex)
{

try
{
if (txn !=null)
{
txn.Rollback();
}

}
catch (OleDbException OleDbex)
{
throw OleDbex;
}

throw ex;
}
finally
{
if (cn !=null)
{
if (cn.State ==ConnectionState.Open)
{
cn.Close();
}
}
}


}
 
Back
Top