SqlBulkCopy - empty datarows in db2table

c# csv sqlbulkcopy

Question

now I finally got to write the rows of my csv (somewhat) into the table. It does add the 7 rows of the csv, but without data. The only thing it sets is the id, but that's autoincremented anyways. The data out of the csv doesn't arrive in the table.

 static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader, string csvSelection)
        {
            string header= isFirstRowHeader ? "Yes" : "No";

            string pathOnly = Path.GetDirectoryName(path);
            string fileName = Path.GetFileName(path);
            List<string> headerList = getHeader(fileName);
            string sql = @"SELECT " + "*" + " FROM [" + fileName + "]";
            //string sql = @"SELECT * FROM [" +  fileName  + "];";

            using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + headerList + ";FMT=Delimited(;)\""))
            using (OleDbCommand command = new OleDbCommand(sql, connection))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
            {
                DataTable dataTable = new DataTable();
                dataTable.Locale = CultureInfo.CurrentCulture;
                foreach (var item in headerList)
                {
                    dataTable.Columns.Add(item);
                }

                adapter.Fill(dataTable);
                writeSchema(dataTable.Columns, pathOnly, fileName);
                return dataTable;
            }

        }

public static Boolean BatchBulkCopy(DataTable dt, string destTable, List<string> columnMapping, IBM.Data.DB2.DB2Connection connection, int batchSize)
        {
            DataTable dtInsertRows = dt;
            using (IBM.Data.DB2.DB2BulkCopy sbc = new IBM.Data.DB2.DB2BulkCopy(connection, IBM.Data.DB2.DB2BulkCopyOptions.TableLock))
            {
                sbc.DestinationTableName = "\"" + destTable + "\"";

                foreach (var mapping in columnMapping)
                {
                    var split = mapping.Split(new[] { ',' });
                    sbc.ColumnMappings.Add("\"" + split.First() + "\"", "\"" + split.Last() + "\"");
                }
                try{
                    sbc.WriteToServer(dtInsertRows);
                    sbc.Close();
                }
                catch(Exception e){
                    System.Console.WriteLine(e.ToString());
                }
            }
            connection.Close();
            return false;
        }
private static void writeSchema(DataColumnCollection columns, string csvDir, string csvFileName)
        {
            FileStream fsOutput =
                     new FileStream(csvDir + "\\schema.ini",
                                         FileMode.Create, FileAccess.Write);
            StreamWriter srOutput = new StreamWriter(fsOutput);

            srOutput.WriteLine("[" + csvFileName + "]");
            int i = 1;
            foreach (DataColumn item in columns)
            {
                srOutput.WriteLine("Col" + i + "=\"" + item.ToString() + "\" Text");
                i++;
            }
            srOutput.Close();
            fsOutput.Close();
        }

        private static List<string> getHeader(string fileName)
        {
            string fullpath = @ImportPath + "\\" + fileName;
            List<string> header = new List<string>();
            var reader = new StreamReader(File.OpenRead(fullpath));

            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(';');

                foreach (var spalte in values)
                {
                    header.Add(spalte);
                }
                reader.Close();
                return header;
            }
            return null;
        }

    }

Accepted Answer

I'm not sure about your sbc.ColumnMappings. I think it is just needed if your datatable did not reflect the structure of your db2 table. In my code I process the datatable to have the same number and order of columns as the DB2 table. I don`t use ColumnMappings.

2nd: your sbc.DestinationTableName. I use here a string with the exact schema and tablename from DB2. DestinationTableName = "DB2-Schema.DB2-Tablename"


Popular Answer

What version is the DB2 running on? Bulkcopy is supported from version 9.7




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