SQLBulkCopy & Adding values [modified]

[munklefish]

Hi, I am using the following code to take data from a csv file then adding it to an SQL database using SQLBulkCopy. I need to add an additional column of data to each record transfered. This extra column is present in the SQL2005 database but not the CSV file, and relates to the users unique ID. At present i have this:

            using (OleDbCommand dcmd = new OleDbCommand("SELECT \* FROM " + strUPloadFile + "", dconn))
            {
                try
                {
                    dconn.Open();

                    using (OleDbDataReader dreader = dcmd.ExecuteReader())
                    {

                        // Bulk Copy to SQL Server
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(WebConfigurationManager.ConnectionStrings\["######"\].ConnectionString))
                        {
                            //bulkCopy.ColumnMappings.Add("adds\_ABMId", 45);
                            bulkCopy.ColumnMappings.Add("adds\_Email", "E-mail Address");
                            bulkCopy.ColumnMappings.Add("adds\_RecipientForename", "First Name");
                            bulkCopy.ColumnMappings.Add("adds\_RecipientSurname", "Last Name");

                            bulkCopy.DestinationTableName = "tbl\_AddressBookAddresses";

                            // Set the BatchSize.
                            bulkCopy.BatchSize = 500;
                            try
                            {//perform copy
                                bulkCopy.WriteToServer(dreader);
                            }
                            catch (Exception ex)
                            {//error
                                throw(ex);
                            }
                        }

                    }
                }
                finally
                {
                    dconn.Close();
                }
            }
        }

I could really do with some help on this. Thanks guys! :confused::confused::confused::confused::confused:

modified on Tuesday, June 16, 2009 6:49 AM

[himanshu2561]

Add column this way

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT Fname FROM [Sheet1$]";
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataTable dt = new DataTable();
adapter.Fill(dt);
DataColumn dc = new DataColumn();
dc.ColumnName = "Lname";
dt.Columns.Add(dc);
foreach (DataRow dr in dt.Rows)
{
dr["Lname"] = "LastName";
}

himanshu

[munklefish]

Himanshu, I see this creates a data table, which isnt what i really wanted to do. Thanks for the suggestion, but how do i then extend this code to perform the SQLBulkCopy over to the server???? Thanks.

[himanshu2561]

Hi, i dont think there is any way to add columns to datareader. try this

string connectionString1 = @"Data Source='local\SQLEXPRESS';Integrated Security=True;Pooling=False;Initial Catalog='MASTER'";

        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
        OleDbConnection con=new OleDbConnection(connectionString);
        using (OleDbCommand dcmd = new OleDbCommand("SELECT Fname FROM \[Sheet1$\]",con))
            {
                try
                {
                    con.Open();

                    using (OleDbDataReader dreader = dcmd.ExecuteReader())
                    {
                        DataRow myNewRow;
                        DataRow\[\] drcol ;
                        DataTable dt = new DataTable();
                        dt.Columns.Add("Fname");
                        dt.Columns.Add("Lname");
                        myNewRow=dt.NewRow();
                    
                         while (dreader.Read())
                         {
                             myNewRow\[0\] = dreader.GetValue(0).ToString();
                             myNewRow\[1\] = "Test123";
                             dt.Rows.Add(myNewRow);
                             
                         }
                        drcol = dt.Select();
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString1))
                        {
                            bulkCopy.ColumnMappings.Add("Fname", "FirstName");
                            bulkCopy.ColumnMappings.Add("Lname", "LastName");
                            bulkCopy.DestinationTableName = "T\_EmpDetail";
                            bulkCopy.BatchSize = 500;
                            bulkCopy.WriteToServer(drcol);
                            
                        }

                    }
                }
                finally
                {
                    con.Close();
                }
            }

himanshu

[munklefish]

I managed to come up with a very similar solution to this but a little simpler since i only need to add a column and populate it, rather than adding new rows. Ill post the details later. Thanks again for all the help everyone!