locked
How to Insert data into table using SqlBulkCopy in c# RRS feed

  • Question

  • User2038048451 posted

    Hi ,

    below is data I have in data.txt file. below txt file data I want save in "test" table, when I try to save data I m getting below error.

    Error:-

    id|name
    1|ravi
    2|kumar

    I m using below code to insert my data into database,

    create table test
    ( id [uniqueidentifier] not null,
    name varchar(10) null)
    
    

    c# code :-

    In my table id is uniqueidentifier , so how to insert my data using below code.

     static void Main(string[] args)
            {
    
    
                string test = System.Configuration.ConfigurationManager.AppSettings["test"];
                DataTable dt = new DataTable("Material");
                string[] columns = null;
                var lines1 = File.ReadAllLines(test);
                if (lines1.Count() > 0)
                {
                    columns = lines1[0].Split(new char[] { '|' });
    
                    foreach (var column in columns)
                        dt.Columns.Add(column);
                }
    
                for (int i = 1; i < lines1.Count(); i++)
                {
                    DataRow dr = dt.NewRow();
                    string[] values = lines1[i].Split(new char[] { '|' });
    
                    for (int j = 0; j < values.Count() && j < columns.Count(); j++)
                        dr[j] = values[j];
    
                    dt.Rows.Add(dr);
                }
                Inserttestdata(dt);
    }
    
    
      static void Inserttestdata(DataTable dt)
            {
                string connetionString = null;
                SqlConnection connection;
                SqlDataAdapter adapter = new SqlDataAdapter();
                string sql = null;
                connetionString = "Data Source=localhost;Initial Catalog=Master;User ID=sa;Password=2015";
                connection = new SqlConnection(connetionString);
    
                using (SqlBulkCopy blkcopy = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.KeepNulls))
                {
                   
                        connection.Open();
                        blkcopy.DestinationTableName = "test";
                        //SqlBulkCopyColumnMapping id = new SqlBulkCopyColumnMapping(Convert.ToInt32(dt.Columns["id"]), "id");
                        //blkcopy.ColumnMappings.Add(id);
                        //SqlBulkCopyColumnMapping name = new SqlBulkCopyColumnMapping(Convert.ToString(dt.Columns["name"]), "name");
                        //blkcopy.ColumnMappings.Add(name);
    
                        blkcopy.WriteToServer(dt);
                    
                }
            }

    How to insert data into uniqueidentifier column using above code.

    Thanks

    Wednesday, July 15, 2015 2:01 AM

Answers

  • User-821857111 posted

    If you want to insert values into an IDENTITY column, you need to turn identity off and then back on again. The syntax is a bit counter-intuitive. You enable the capability to insert values into an identity column like this:

    SET IDENTITY_INSERT your_table ON

    You execute that as SQL before you call WriteToTable. Then you do the opposite at the end of the operation:

    SET IDENTITY_INSERT your_table OFF

    So your code should look like this:

    using (SqlBulkCopy blkcopy = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.KeepNulls))
    {
        connection.Open();
        blkcopy.DestinationTableName = "test";
        using(SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT test ON", connection))
        {
            cmd.ExecuteNonQuery();   
            blkcopy.WriteToServer(dt);
            cmd.CommandText = "SET IDENTITY_INSERT test OFF";
            cmd.ExecuteNonQuery();
        }    
    }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 15, 2015 2:27 AM