How to carry out multiple requests at once

Question

I'm trying this to transfer data from a .csv file to a sql-server database.

string str = Path.GetFullPath(".");
    String conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Text;HDR=No;FMT=Delimited"";Data Source="+str+"";
    string strCnx = "Database=" + ConfigurationManager.AppSettings["base"] + ";data source=" + ConfigurationManager.AppSettings["servername"] + ";User Id=" + ConfigurationManager.AppSettings["user"] + ";Password=" + ConfigurationManager.AppSettings["password"] + ";Connect Timeout=10";
    // Open a sourceConnection to the AdventureWorks database. 
    //using (OleDbConnection sourceConnection = new OleDbConnection(strCnx))
    {
        //sourceConnection.Open();

        // Get data from the source table as a SqlDataReader.
        OleDbConnection cn = new OleDbConnection(conn);
        OleDbCommand commandSourceData = new OleDbCommand("SELECT * FROM [" + ConfigurationManager.AppSettings["csvname"] + "]", cn);
        OleDbDataAdapter da = new OleDbDataAdapter(commandSourceData);
        cn.Open();
        OleDbDataReader reader = commandSourceData.ExecuteReader();

        // Open the destination connection. In the real world you would  
        // not use SqlBulkCopy to move data from one table to the other  
        // in the same database. This is for demonstration purposes only. 
        using (SqlConnection destinationConnection = new SqlConnection(strCnx))
        {
            destinationConnection.Open();

            // Set up the bulk copy object.  
            // Note that the column positions in the source 
            // data reader match the column positions in  
            // the destination table so there is no need to 
            // map columns. 
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
            {
                bulkCopy.DestinationTableName = "GudsisUser";

                bulkCopy.WriteToServer(reader);
            }
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }

The application returns an error message :

Impossible to parse a string value into a nchar value

What kind of "request" are you talking about, Are you trying to insert records one by one ? — Habib, Feb 11 '15 at 15:40
You could do all your data modifications on a DataSet and hide away the actual process of updating or inserting but in the end it will always be INSERT or UPDATE statements executed against your database. — Filburt, Feb 11 '15 at 15:43
post some code. your question is really vague and difficult to understando without any code — Paolo, Feb 11 '15 at 15:43
I'm not 100% sure what you are asking, but I think you want to perform a batch of inserts in one go. In that case [SqlDataAdapter.InsertCommand](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand%28v=vs.110%29.aspx) might be helpful. — Daniel Kelley, Feb 11 '15 at 15:53
Yes you could do this. You could fill a datatable instead of executing query after query. Then create an insert procedure that receives a table valued parameter in sql. Then you just pass your DataTable and it will do all the inserts at once. The other option is to use BULK INSERT as suggested below. — Sean Lange, Feb 11 '15 at 17:07

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

I guess what you're looking for is something called BULK INSERT (to do everything within one DB request?)

If so, check out the answer here

edited May 23 '17 at 12:12

Community

1
1

answered Feb 11 '15 at 15:50

quetzy

109
4

In addition, there is a really nice generic solution here: http://blog.developers.ba/bulk-insert-generic-list-sql-server-minimum-lines-code/ – quetzy Feb 11 '15 at 15:55
I need to "store" my request and his parameters to execute all requests at the and of the loop – Benoit Devienne Feb 11 '15 at 16:02
For what you want to do, you can't have SqlCommand being created and executed every time within the loop. Pull the creation of the SqlCommand in front of the loop, only load the values into a table within the loop, and execute the bulk insert (with all that data) only once after the loop. For the implementation ideas, please check both the links. – quetzy Feb 11 '15 at 16:11

How to carry out multiple requests at once

1 Answers1