Insert dataset to a table PostgreSQL

Question

I am converting a WCF service with a SQL Server backend to PostgreSQL. I am using Npgsql but it does not have a bulk copy option. test is the dataset here.

This is my sql code,

        DataSet ds = DuobaseHandler.ToDataSet(test);
        DataTable dt = ds.Tables[0];
        string TableNameOrig = "CDA." + TableName;
        DataTable dataTable = dt; 
        try
        {
            ConnectionStringSettings mConString = ConfigurationManager.ConnectionStrings["CommonDataConnection"];
            string[] columnNames = dt.Columns.Cast<System.Data.DataColumn>()
                     .Select(x => x.ColumnName)
                     .ToArray();
            string[] ColumnsFinal = columnNames.Take(columnNames.Count() - 2).ToArray();
            using (SqlConnection ConnectionSQL = new SqlConnection(mConString.ConnectionString))
            {
                ConnectionSQL.Open();
                // Delete old entries
                SqlCommand truncate = new SqlCommand("TRUNCATE TABLE" + " " + "CDA." + TableName, ConnectionSQL);
                truncate.ExecuteNonQuery();


            }
            SqlBulkCopy bulkCopy = new SqlBulkCopy(mConString.ConnectionString, SqlBulkCopyOptions.TableLock)
            {
                DestinationTableName = TableNameOrig,
                BatchSize = 100000,
                BulkCopyTimeout = 360
            };
            foreach (var item in ColumnsFinal)
            {
                bulkCopy.ColumnMappings.Add(item.ToString(), item.ToString());
            }

            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
        return true;

The product is called PostgreSQL (see postgresql.org) - and NOT "posegresql" as you keep insisting on misspelling it .... Please stop undoing my fixes to you post - I'm trying to make it more useful and readable here — marc_s
– marc_s, Commented Jul 20, 2014 at 16:49
@marc_s Yes, i wrongly rejected your edit, it came in the middle while reviewing others posts sorry for that. you can edit now — Sajeetharan
– Sajeetharan, Commented Jul 20, 2014 at 16:51
nPgSQL does have a bulk copy feature, it just doesn't have the same interface. See NpgsqlCopyIn. It would be interesting to write a wrapper to support the MS SQL Server interfaces instead, but it doesn't look like anyone's done it. — Craig Ringer
– Craig Ringer, Commented Jul 21, 2014 at 1:54

Craig Ringer · Accepted Answer · 2014-07-21 01:55:22Z

2

nPgSQL does have a bulk copy feature, it just doesn't have the same interface. See NpgsqlCopyIn.

It would be interesting to write a wrapper to support the MS SQL Server interfaces instead, but it doesn't look like anyone's done it, so you'll need to convert to the nPgSQL interface or write a compatibility wrapper.

This blog might be useful.

answered Jul 21, 2014 at 1:55

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sajeetharan Over a year ago

Ya i know that npsqlcopyin would work, but it needs a columns of type string, will try it anyways. Thanks

Collectives™ on Stack Overflow

Insert dataset to a table PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related