datagridview表格的数据要导入后台数据库表中时，如果记录比较多，用SQL速度慢，尝试用批量导入，未能成功，继续努力；
using Npgsql;
using NpgsqlTypes;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace TestPgsqlTrans
{
public partial class Form1 : Form
{
static string ConnectionString = DBHelperPg.ConnectionString;
NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//打开文件对话框
OpenFileDialog ofd = new OpenFileDialog();
string importFileName;
if (ofd.ShowDialog() == DialogResult.OK)
{
importFileName = ofd.FileName;
dataGridView1.DataSource = Excel.ExcelToDataTable(importFileName, true);
}
}
private void button2_Click(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
DateTime beginTime = DateTime.Now;
string sql;
// 建立临时表
sql = @"CREATE UNLOGGED TABLE dinners (
name varchar(30) default '',
devicename varchar(30) default '',
department varchar(30) default '',
date varchar(30) default '',
time varchar(30) default '',
flag varchar(30) default '',
type varchar(30) default '',
authortype varchar(30) default '',
userid varchar(30) default '',
amont decimal(10,2))";
//int t = PostgreHelper.ExecuteSQL(sql);
string tmpTabelName = "dinners";
string id, name, devicename, department, date, time, flag, type, authortype, userid, amont;
string connectionString = PostgreHelper.ConnectionString;
for (int i = 0; i < dataGridView1.RowCount; i++)
{
name = Convert.ToString(dataGridView1.Rows[i].Cells["姓名"].Value).Trim();
devicename = Convert.ToString(dataGridView1.Rows[i].Cells["设备"].Value).Trim();
department = Convert.ToString(dataGridView1.Rows[i].Cells["部门"].Value).Trim();
date = Convert.ToString(dataGridView1.Rows[i].Cells["日期"].Value).Trim();
time = Convert.ToString(dataGridView1.Rows[i].Cells["时间"].Value).Trim();
flag = Convert.ToString(dataGridView1.Rows[i].Cells["通行标签"].Value).Trim();
type = Convert.ToString(dataGridView1.Rows[i].Cells["识别类型"].Value).Trim();
authortype = Convert.ToString(dataGridView1.Rows[i].Cells["权限类型"].Value).Trim();
userid = Convert.ToString(dataGridView1.Rows[i].Cells["用户ID"].Value).Trim();
amont = "0";
sql = "Insert into " + tmpTabelName + " (name,devicename,department,date,time,flag,type,authortype,userid,amont)";
sql += " values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9})";
sql = String.Format(sql, name, devicename, department, date, time, flag, type, authortype, userid, amont);
DBHelperPg.ExecuteSQL(sql);
}
// MessageBox.Show("数据保存完成！");
MessageBox.Show("页面导入完成,用时" + (DateTime.Now - beginTime).ToString(), "提示");
}
private void button2_Click_1(object sender, EventArgs e)
{
string connString = PostgreHelper.ConnectionString;
string sql = "";
DateTime beginTime = DateTime.Now;
string tmpTabelName = "dinners";
string id, name, devicename, department, date, time, flag, type, authortype, userid, amont;
using (var conn = new NpgsqlConnection(connString))
{
//启用事务后，这个连接只打开一次，不用每次打开又关闭
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
name = Convert.ToString(dataGridView1.Rows[i].Cells["姓名"].Value).Trim();
devicename = Convert.ToString(dataGridView1.Rows[i].Cells["设备"].Value).Trim();
department = Convert.ToString(dataGridView1.Rows[i].Cells["部门"].Value).Trim();
date = Convert.ToString(dataGridView1.Rows[i].Cells["日期"].Value).Trim();
time = Convert.ToString(dataGridView1.Rows[i].Cells["时间"].Value).Trim();
flag = Convert.ToString(dataGridView1.Rows[i].Cells["通行标签"].Value).Trim();
type = Convert.ToString(dataGridView1.Rows[i].Cells["识别类型"].Value).Trim();
authortype = Convert.ToString(dataGridView1.Rows[i].Cells["权限类型"].Value).Trim();
userid = Convert.ToString(dataGridView1.Rows[i].Cells["用户ID"].Value).Trim();
amont = "0";
sql = "Insert into " + tmpTabelName + " (name,devicename,department,date,time,flag,type,authortype,userid,amont) values ";
sql += " ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9})";
sql = String.Format(sql, name, devicename, department, date, time, flag, type, authortype, userid, amont);
using (var cmd = new NpgsqlCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
MessageBox.Show("页面导入完成,用时" + (DateTime.Now - beginTime).ToString(), "提示");
}
catch (Exception)
{
transaction.Rollback();
}
}
}
}
//
//public void BulkCopy(string tableName, DataTable dt)
//{
// List<string> lsColNames = new List<string>();
// for (int i = 0; i < dt.Columns.Count; i++)
// {
// lsColNames.Add($"\"{dt.Columns[i].ColumnName}\"");
// }
// string copyString = $"COPY \"{tableName}\" ( {string.Join(",", lsColNames) } ) FROM STDIN (FORMAT BINARY)";
// using (NpgsqlConnection conn = new NpgsqlConnection(ConnectionString))
// {
// if (conn.State == ConnectionState.Closed)
// conn.Open();
// var writer = conn.BeginBinaryImport(copyString);
// foreach (DataRow row in dt.Rows)
// {
// writer.StartRow();
// IEnumerable<KeyValuePair<string, JToken>> JRowData = DataConvert.ToJObject(row);
// foreach (var kvp in JRowData)
// {
// NpgsqlParameter colParam = GetParameter(tableName, kvp);
// writer.Write(colParam.Value, colParam.NpgsqlDbType);
// }
// }
// writer.Complete();
// conn.Close();
// }
//}
//public NpgsqlParameter GetParameter(string tableName, KeyValuePair<string, JToken> columnValuePair)
//{
// string columnDBypeName = _DBTableDefProvider.GetTableColumn(tableName, columnValuePair.Key).data_type.ToLower();
// NpgsqlParameter p = new NpgsqlParameter("@" + columnValuePair.Key,
// columnDBypeName == "timestamp" || columnDBypeName == "timestamp without time zone" ? NpgsqlDbType.Timestamp
// : columnDBypeName == "timestamp with time zone" ? NpgsqlDbType.TimestampTz
// : columnDBypeName == "date" ? NpgsqlDbType.Date
// : columnDBypeName == "time" || columnDBypeName == "time without time zone" ? NpgsqlDbType.Time
// : columnDBypeName == "time with time zone" ? NpgsqlDbType.TimeTz
// : columnDBypeName == "smallint" ? NpgsqlDbType.Smallint
// : columnDBypeName == "integer" || columnDBypeName == "serial" ? NpgsqlDbType.Integer
// : columnDBypeName == "bigint" || columnDBypeName == "bigserial" ? NpgsqlDbType.Bigint
// : columnDBypeName == "double precision" ? NpgsqlDbType.Double
// : columnDBypeName == "real" ? NpgsqlDbType.Real
// : columnDBypeName == "boolean" ? NpgsqlDbType.Boolean
// : columnDBypeName == "uuid" ? NpgsqlDbType.Uuid
// : columnDBypeName == "bit" ? NpgsqlDbType.Bit //eg:0|1
// : columnDBypeName == "json" ? NpgsqlDbType.Json
// : columnDBypeName == "money" ? NpgsqlDbType.Money
// : columnDBypeName == "numeric" ? NpgsqlDbType.Numeric
// : columnDBypeName == "bit varying" ? NpgsqlDbType.Varbit //eg:01010101
// : columnDBypeName == "text" ? NpgsqlDbType.Text
// : columnDBypeName == "character varying" ? NpgsqlDbType.Varchar //NpgsqlDbType.Varchar可以直接用NpgsqlDbType.Text
// : columnDBypeName == "\"char\"" || columnDBypeName == "character" ? NpgsqlDbType.Char //NpgsqlDbType.Char可以直接用NpgsqlDbType.Text
// //: columnDBypeName == "array" ? NpgsqlDbType.Array|NpgsqlDbType.Json //ARRAY需要匹配各个基础类型的Array，且不能直接以string传值，不常用不做处理
// : columnDBypeName == "interval" ? NpgsqlDbType.Interval
// //: NpgsqlDbType.Text);
// : NpgsqlDbType.Unknown);
// p.Value = columnValuePair.Value.Type == JTokenType.Null ? DBNull.Value
// : columnDBypeName.StartsWith("timestamp") || columnDBypeName == "date" || columnDBypeName.StartsWith("time") ? Convert.ToDateTime(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "smallint" ? Convert.ToInt16(columnValuePair.Value)
// : columnDBypeName == "integer" || columnDBypeName == "serial" ? Convert.ToInt32(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "bigint" || columnDBypeName == "bigserial" ? Convert.ToInt64(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "double precision" ? Convert.ToDouble(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "real" ? Convert.ToSingle(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "boolean" ? Convert.ToBoolean(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "uuid" ? Guid.Parse((string)columnValuePair.Value)
// : columnDBypeName == "bit" ? Convert.ToString(Convert.ToInt32(columnValuePair.Value), 2).Last().ToString()
// : columnDBypeName == "json" ? JObject.Parse((string)columnValuePair.Value).ToString()
// : columnDBypeName == "money" || columnDBypeName == "numeric" ? Convert.ToDecimal(((JValue)columnValuePair.Value).Value)
// : columnDBypeName == "text" || columnDBypeName == "character varying" || columnDBypeName == "character" ? (string)columnValuePair.Value
// : columnDBypeName == "interval" ? TimeSpan.Parse(Regex.Replace((string)columnValuePair.Value, "days?", ".", RegexOptions.IgnoreCase).Replace(" ", ""))
// : (object)(string)((JValue)columnValuePair.Value).Value;
// return p;
//}
private void button3_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)dataGridView1.DataSource;
WriteToServer(dt);
}
//
public void WriteToServer(DataTable dataTable)
{
string DestinationTableName = "dinners";
try
{
conn.Open();
if (DestinationTableName == null || DestinationTableName == "")
{
throw new ArgumentOutOfRangeException("DestinationTableName", "目标表名必须要设置");
}
int colCount = dataTable.Columns.Count;
NpgsqlDbType[] types = new NpgsqlDbType[colCount];
int[] lengths = new int[colCount];
string[] fieldNames = new string[colCount];
using (var cmd = new NpgsqlCommand("SELECT * FROM " + DestinationTableName + " LIMIT 1", conn))
{
using (var rdr = cmd.ExecuteReader())
{
if (rdr.FieldCount != colCount)
{
throw new ArgumentOutOfRangeException("dataTable", "Column count in Destination Table does not match column count in source table.");
}
var columns = rdr.GetColumnSchema();
for (int i = 0; i < colCount; i++)
{
types[i] = (NpgsqlDbType)columns[i].NpgsqlDbType;
lengths[i] = columns[i].ColumnSize == null ? 0 : (int)columns[i].ColumnSize;
fieldNames[i] = columns[i].ColumnName;
}
}
}
var sB = new StringBuilder(fieldNames[0]);
for (int p = 1; p < colCount; p++)
{
sB.Append(", " + fieldNames[p]);
}
using (var writer = conn.BeginBinaryImport("COPY " + DestinationTableName + " (" + sB.ToString() + ") FROM STDIN (FORMAT BINARY)"))
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
DataRow dR = dataTable.Rows[j];
writer.StartRow();
for (int i = 0; i < colCount; i++)
{
if (dR[i] == DBNull.Value)
{
writer.WriteNull();
}
else
{
switch (types[i])
{
case NpgsqlDbType.Bigint:
writer.Write((long)dR[i], types[i]);
break;
case NpgsqlDbType.Bit:
if (lengths[i] > 1)
{
writer.Write((byte[])dR[i], types[i]);
}
else
{
writer.Write((byte)dR[i], types[i]);
}
break;
case NpgsqlDbType.Boolean:
writer.Write((bool)dR[i], types[i]);
break;
case NpgsqlDbType.Bytea:
writer.Write((byte[])dR[i], types[i]);
break;
case NpgsqlDbType.Char:
if (dR[i] is string)
{
writer.Write((string)dR[i], types[i]);
}
else if (dR[i] is Guid)
{
var value = dR[i].ToString();
writer.Write(value, types[i]);
}
else if (lengths[i] > 1)
{
writer.Write((char[])dR[i], types[i]);
}
else
{
var s = ((string)dR[i].ToString()).ToCharArray();
writer.Write(s[0], types[i]);
}
break;
case NpgsqlDbType.Time:
case NpgsqlDbType.Timestamp:
case NpgsqlDbType.TimestampTz:
case NpgsqlDbType.Date:
writer.Write((DateTime)dR[i], types[i]);
break;
case NpgsqlDbType.Double:
writer.Write((double)dR[i], types[i]);
break;
case NpgsqlDbType.Integer:
try
{
if (dR[i] is int)
{
writer.Write((int)dR[i], types[i]);
break;
}
else if (dR[i] is string)
{
var swap = Convert.ToInt32(dR[i]);
writer.Write((int)swap, types[i]);
break;
}
}
catch (Exception ex)
{
string sh = ex.Message;
}
writer.Write((object)dR[i], types[i]);
break;
case NpgsqlDbType.Interval:
writer.Write((TimeSpan)dR[i], types[i]);
break;
case NpgsqlDbType.Numeric:
case NpgsqlDbType.Money:
writer.Write((decimal)dR[i], types[i]);
break;
case NpgsqlDbType.Real:
writer.Write((Single)dR[i], types[i]);
break;
case NpgsqlDbType.Smallint:
try
{
if (dR[i] is byte)
{
var swap = Convert.ToInt16(dR[i]);
writer.Write((short)swap, types[i]);
break;
}
writer.Write((short)dR[i], types[i]);
}
catch (Exception ex)
{
string ms = ex.Message;
}
break;
case NpgsqlDbType.Varchar:
case NpgsqlDbType.Text:
writer.Write((string)dR[i], types[i]);
break;
case NpgsqlDbType.Uuid:
writer.Write((Guid)dR[i], types[i]);
break;
case NpgsqlDbType.Xml:
writer.Write((string)dR[i], types[i]);
break;
}
}
}
}
writer.Complete();
}
}
catch (Exception ex)
{
throw new Exception("Error executing NpgSqlBulkCopy.WriteToServer(). See inner exception for details", ex);
}
}
//
}
}
标签：未果,Convert,NpgsqlDbType,C#,columnDBypeName,writer,Value,DataGridView,string From： https://www.cnblogs.com/lrzy/p/17967538