示例#1
0
        public void CopyDataTableToDB(DataTable table, string destinationTable)
        {
            var filename = table.TableName + "preload.csv";

            using (TextWriter writer = new StreamWriter(filename))
            {
                Rfc4180Writer.WriteDataTable(table, writer, false);
            }

            using (MySqlConnection conn = new MySqlConnection(_ConnectionString))
            {
                conn.Open();


                //  CreateTable(dt.TableName, dt, conn);

                var bl = new MySqlBulkLoader(conn);
                bl.TableName               = destinationTable;
                bl.FieldTerminator         = ",";
                bl.FieldQuotationCharacter = '"';
                bl.LineTerminator          = "\r\n";
                bl.FileName = filename;
                // bl.NumberOfLinesToSkip = 1;
                bl.Load();
                File.Delete(filename);
            }
        }
示例#2
0
        public void CopyDataTableToDB(DataTable table, string destinationTable)
        {
            try
            {
                //COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, DATA_SRC) from 'C:\...\popgrid.csv' DELIMITERS ',' CSV;
                var filename = table.TableName + "preload.csv";
                filename = @"C:\TAFC\\" + filename;


                using (TextWriter writer = new StreamWriter(filename))
                {
                    Rfc4180Writer.WriteDataTable(table, writer, false);
                }

                //foreach (DataColumn column in destdt.Columns)
                //{
                //    fields.Add(column.ColumnName);
                //}

                //   string headers = string.Join("\",\"", fields);

                //   filename = AppDomain.CurrentDomain.BaseDirectory + "\\" + filename;

                using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString))
                {
                    conn.Open();

                    NpgsqlCommand cmd = new NpgsqlCommand();
                    cmd.Connection = conn;
                    var sql = "COPY " + destinationTable + " FROM '" + filename + "' DELIMITERS ',' CSV";
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }

            catch (Exception e)
            {
                try
                {
                    var destdt = getData("select * from " + destinationTable + " limit 1");

                    destdt.Clear();

                    foreach (DataRow row in table.Rows)
                    {
                        var newrow = destdt.NewRow();

                        foreach (DataColumn column in table.Columns)
                        {
                            newrow[column.ColumnName] = row[column.ColumnName];
                        }

                        destdt.Rows.Add(newrow);
                    }

                    CopyDataTableToDB(destdt, destinationTable);
                }
                catch
                {
                    throw (e);
                }
            }
        }