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
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.