Adding custom data to bulkcopy

asp.net c# sqlbulkcopy

Question

I have a bulk copy code :

 private void SqlbulkCopy(DataTable dt)
    {

        if (dt.Rows.Count > 0)
        {
            string consString = ConfigurationManager.ConnectionStrings["Bulkcopy"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {

                    sqlBulkCopy.DestinationTableName = "dbo.leads";

                   sqlBulkCopy.ColumnMappings.Add(dt.Columns[0].ToString(), "Name");
                sqlBulkCopy.ColumnMappings.Add(dt.Columns[1].ToString(), "Type");
                sqlBulkCopy.ColumnMappings.Add(dt.Columns[2].ToString(), "Emaily");
                sqlBulkCopy.ColumnMappings.Add(dt.Columns[3].ToString(), "EmailSecondary");
                sqlBulkCopy.ColumnMappings.Add(dt.Columns[4].ToString(), "Address");

                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
        }
    }

Is it possible to add in my own data in each row while this method gets called? For Example:

sqlcommand1.Parameters.Add("@secret", SqlDbType.NVarChar).Value = txtsecret.Text.Trim();

How do I add this type of insert code in? And the data from this insert code will be added to each row that's being stored inside database.

Tried:

foreach (DataRow row in dt.Rows)
{
    sqlcommand1.Parameters.Add("@secret", SqlDbType.NVarChar).Value = txtsecret.Text.Trim();

    sSql= "Insert into details(secret) ";
    sSql+= " values(@secret)";

    sSqlCmd.CommandText = sSql;
    Conn.DBParaQuery(sSql, sqlcommand1);
}

i tried the foreach loop but the data gets saved to null rows in the database without the name and stuff only with the secretans

results(in database) :

    (secret)        (name)  (type) (email) (2ndemail)(address)
    secreetans      NULL    Null    NULL    NULL      NULL  
    NULL    qwwer   n   1@mail.com  2@mail.com         hi   
    ^^^
 i nid this null to be secretans 

Popular Answer

I doubt that's possible.

According to MSDN

While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why