locked
Bulk Insert RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I am using below code to BukInsert data from Excel to Sql . I want to add Record No by autoincrementing . Is it possible . I dont want to create

    Identity field

    SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);
    objbulkInsert.DestinationTableName = "[Data]";
    objbulkInsert.ColumnMappings.Add(0, 0);

    Thanks

    Wednesday, September 12, 2018 6:23 AM

All replies

  • User1724605321 posted

    Hi JagjitSingh ,

    Do you want to use the identity values from the source(your reader/datatable ) ? You can use :

    using (var sqlBulk = new SqlBulkCopy(_connectionString, SqlBulkCopyOptions.KeepIdentity))

    And you would like to know how to implement SqlBulkCopyColumnMapping according to your code :
    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopycolumnmapping?redirectedfrom=MSDN&view=netframework-4.7.2 

    Best Regards,

    Nan Yu

    Wednesday, September 12, 2018 7:49 AM
  • User-1499457942 posted

    Hi

      I want to add serial no thru code . I dont want to use Identity  

    Thanks

    Wednesday, September 12, 2018 8:44 AM
  • User1724605321 posted

    Hi JagjitSingh ,

    I want to add serial no thru code . I dont want to use Identity  

    What do you mean by "add serial no thru code " .  Can you detail explain your requirement ? If you have a datatable  , and need to make one column auto increase , you can directly assign the value inside loop , or creating AutoIncrement Columns .

    Best Regards,

    Nan Yu

    Wednesday, September 12, 2018 9:15 AM
  • User-1499457942 posted

    Hi

      I am using below code . I want that value in a particular column say recno should get increment by 1.  Is it possible according to my code

    SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);
    
                    objbulkInsert.DestinationTableName = "[Data]";
                    objbulkInsert.ColumnMappings.Add(0, 0);     
                    objbulkInsert.ColumnMappings.Add(1, 1);     
                    objbulkInsert.ColumnMappings.Add(2, 2);     
                    objbulkInsert.ColumnMappings.Add(3, 3);     
                    objbulkInsert.ColumnMappings.Add(4, 4);     
                    objbulkInsert.ColumnMappings.Add(5, 5);     
                    objbulkInsert.ColumnMappings.Add(6, 6);

    Thanks

    Wednesday, September 12, 2018 9:29 AM
  • User753101303 posted

    Hi,

    Who will "autoincrement" then ? You are writing from a DataTable ? I believe it could be done on the DataTable table side but how will you know from where to start ?

    Wednesday, September 12, 2018 2:18 PM
  • Wednesday, September 12, 2018 8:52 PM
  • User1724605321 posted

    Hi JagjitSingh ,

    Your codes related to  SqlBulkCopyColumnMapping , you should add auto-increase column in DataTable side as my previous reply if you don't want to use Identity column .

    Best Regards,

    Nan Yu

    Thursday, September 13, 2018 5:29 AM
  • User1520731567 posted

    Hi JagjitSingh,

    It seems that you want to use SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex);

    Creates a new SqlBulkCopyColumnMapping and adds it to the collection, using ordinals to specify both source and destination columns.

    Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match.

    SqlBulkCopyColumnMapping objects are used to create a column map for the bulk copy using the ordinal position of the source and destination columns.

    You could refer to:

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopycolumnmappingcollection.add?view=netframework-4.7.2#System_Data_SqlClient_SqlBulkCopyColumnMappingCollection_Add_System_Int32_System_Int32_

    Best Regards.

    Yuki Tao

    Thursday, September 13, 2018 6:20 AM
  • User753101303 posted

    If nothing helps so far please clarify. My understanding is that this is not an identity column and still you want this behavior ? This is used for ordering rather than as an id ?

    Thursday, September 13, 2018 7:59 AM
  • User-168407370 posted

    Click Event Code For Button Import student from excel to sql server databse table :

    private void btnSaveimportstudent_Click(object sender, RoutedEventArgs e)</div> <div>        {</div> <div>            showlblcount.Text = "";</div> <div>            dataGridStudent.ItemsSource = null;</div> <div>            dataGridStudent.Items.Refresh();</div> <div>            //openFileDialog1.ShowDialog();</div> <div>            //   lblRows.Text = "Total ID = " + dtgridviewImportPreview.RowCount.ToString();</div> <div>            openFileDialog1.CheckFileExists = true;</div> <div>            openFileDialog1.CheckPathExists = true;</div> <div> </div> <div>            openFileDialog1.DefaultExt = ".xls,.xlsx";</div> <div>            // openFileDialog1.Filter = "GIF files (*.gif)|*.gif| jpg files (*.jpg)|*.jpg| PNG files (*.png)|*.png| All files (*.*)|*.*";</div> <div>            openFileDialog1.Filter = "Excel files (*.xls)|*.XLS|(*.xlsx)|*.XLSX";</div> <div> </div> <div>            openFileDialog1.FilterIndex = 2;</div> <div>            openFileDialog1.RestoreDirectory = true;</div> <div> </div> <div>            if (openFileDialog1.ShowDialog() == true)</div> <div>            {</div> <div>                var lst = new List<string>();</div> <div>                lst.Add("sr_no");</div> <div>                lst.Add("firstname");</div> <div>                lst.Add("gender");</div> <div>                lst.Add("father_name");</div> <div>                lst.Add("father_phone");</div> <div> </div> <div>                ////  lblFileExtension.Text =  openFileDialog.FileName;</div> <div>                //picItemimage.Source = new BitmapImage(new Uri(openFileDialog1.FileName));</div> <div>                ////// textBox1.Text = openFileDialog1.FileName;</div> <div>                ////picItemimage.Source = openFileDialog1.FileName;</div> <div>                //lblFileExtension.Text = System.IO.Path.GetExtension(openFileDialog1.FileName);</div> <div>                string filePath = openFileDialog1.FileName;</div> <div>                string extension = System.IO.Path.GetExtension(filePath);</div> <div>                if (extension.ToLower() == ".xls" || extension.ToLower() == ".xlsx")</div> <div>                {</div> <div>                    string header = "YES";</div> <div> </div> <div>                    Workbook workbook = new Workbook();</div> <div>                    workbook.LoadFromFile(filePath, ExcelVersion.Version97to2003);</div> <div>                    Worksheet sheet = workbook.Worksheets[0];</div> <div> </div> <div>                    dataTable = sheet.ExportDataTable();</div> <div>                    if (dataTable == null)</div> <div>                    {</div> <div>                        showlblcount.Visibility = Visibility.Visible;</div> <div>                        showlblcount.Text = "This Excel File Is Null";</div> <div>                        return;</div> <div>                    }</div> <div>                    var cnt = 0;</div> <div>                    for (int i = 0; i < lst.Count; i++)</div> <div>                    {</div> <div>                        for (int j = 0; j < dataTable.Columns.Count; j++)</div> <div>                        {</div> <div>                            if (dataTable.Columns[j].ToString().ToLower() == lst[i].ToLower())</div> <div>                            {</div> <div>                                cnt += 1;</div> <div>                            }</div> <div>                        }</div> <div>                    }</div> <div>                    if (cnt != lst.Count)</div> <div>                    {</div> <div>                        showlblcount.Visibility = Visibility.Visible;</div> <div>                        showlblcount.Text = "Not Valid Data Header";</div> <div>                        return;</div> <div>                    }</div> <div>                    DataView view = new DataView(dataTable);</div> <div>                    this.dataGridStudent.ItemsSource = view;</div> <div>                    //this.dataGridStudent.AutoGenerateColumns = true;</div> <div>                    dataGridStudent.Visibility = Visibility.Visible;</div> <div>                    lblerrormsg.Visibility = Visibility.Collapsed;</div> <div>                    showlblcount.Visibility = Visibility.Visible;</div> <div>                    showlblcount.Text = "Total Students Get From Excel File Is : = " + dataGridStudent.Items.Count.ToString();</div> <div>                }</div> <div>                else</div> <div>                {</div> <div>                    showlblcount.Text = "Not Valid File Extension [Please Select (*.xls)|*.XLS|(*.xlsx)|*.XLSX File";</div> <div> </div> <div>                    return;</div> <div>                }</div> <div> </div> <div>            }</div> <div>        }

    Its Completely works for me!! i already add this code in my app than i will give you!! so plz proper set in your code its helps you to solve your problem!!! Thanks 

    Thursday, September 13, 2018 8:00 AM