IT评测·应用市场-qidao123.com

标题: Backend - C# 操作PostgreSQL DB [打印本页]

---

作者: 莱莱    时间: 2024-9-3 19:36
标题: Backend - C# 操作PostgreSQL DB
目次
一、安装 Npgsql 插件
（一）作用
（二）操作
（三）注意
二、操作类
（一）操作类
1.NpgsqlConnection类
（1）作用
（2）引入
（3）数据库路由
（4）连接数据库
2.NpgsqlCommand类
（1）作用
（2）应用
（3）方法
       ① ExecuteNonQuery()  不返回任何效果。
        ② ExecuteScalar()  返回一个值。可搭配ExecuteScalar().ToString();输出值。
        ③ ExecuteReader()  返回一个IDataReader。
3.NpgsqlDataReader类
（1）作用
（2）应用
4.DataSet类
（1）作用
（2）应用
5.DataAdapter类：数据适配下令
（1）结合DataSet类，将数据可存储在DataSet对象中
（2）DataAdapter属性
（3）DataAdapter方法
（4）应用
（二）using
三、连接数据库
1. 简朴写法
2. 灵活写法
四、操作数据库
（一）创建&删除表
（二）插入
 1. 单笔插入
（1）使用NpgsqlCommand
（2）使用BeginBinaryImport
① 定义时
② 调用时
 2. 批量插入
（三）查询
（四）更新
 1. 批量更新
五、写法
（一）字符串中的双引号
        1. 用@符号+“”两个双引号
        2. 用 \" 转义
六、问题
 1. 问题：于 Npgsql.NpgsqlException 擲回破例狀況: 'mscorlib.dll'
 2. 问题：42601: INSERT has more target columns than expressions 和 42601: syntax error at or near "[" 这两个问题
 3. 问题：Binary importer closed in the middle of a row, cancelling import.
 4. 问题：22P03: incorrect binary data format
 5. 问题：Binary importer closed in the middle of a row, cancelling import.
 6. 问题：error: Write for column 17 resolves to a different PostgreSQL type: OID 1114 than the first row resolved to (OID 0). Please make sure to use clr types that resolve to the same PostgreSQL type across rows. Alternatively pass the same NpgsqlDbType or DataTypeName to ensure the PostgreSQL type ends up to be identical.
 7. 问题：The binary import operation was started with 23 column(s), but 24 value(s) were provided.
 8. 问题： 22021: invalid byte sequence for encoding "UTF8": 0x00
 9. 问题：Writing values of 'System.DateTimeOffset' is not supported for parameters having NpgsqlDbType 'Timestamp'.
 10. 问题：Cannot write DateTimeOffset with Offset=08:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported.
 11. 问题：error: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange.

---

               注意，这篇文章主要都是针对npgsql ，而不是sql。
        一、安装 Npgsql 插件

    （一）作用

            Npgsql是用C#语言编写的，符合ADO.NET规范的，操作PostgreSQL数据库的驱动插件。
    （二）操作

            点击工具栏的“工具” > NuGet套件管理员 > 套件管理器主控台。
            打开控制台后，输入安装下令：
   

1. Install-Package Npgsql

复制代码

           安装成功后会提示：
   

    （三）注意

            不同解决方案在使用Npgsql，都需要各自安装一次。
            查看或勾选安装：点击工具栏的“工具” > NuGet套件管理员 > 管理方案的NuGet套件（程式若启动了，先停止运行） > 征采插件名 > 查看某项目是否安装。
   

    二、操作类

    （一）操作类

            核心有：NpgsqlConnection、NpgsqlCommand、NpgsqlDataReader、DataSet和DataAdapter、NpgsqlDataProvider。
    1.NpgsqlConnection类

    （1）作用

            创建数据库的连接。
    （2）引入

   

1. using Npgsql;

复制代码

   （3）数据库路由

   

1. string DbUrl = "Host=127.0.0.1;Port=5432;Database=TestPro;Username=postgres;Password=root;";

复制代码

   （4）连接数据库

   

1. NpgsqlConnection DbConn = new NpgsqlConnection(DbUrl);
2. DbConn.Open();
3. DbConn.Close();

复制代码

   2.NpgsqlCommand类

    （1）作用

            实行SQL下令
    （2）应用

   

1. // 第1种
2. using var cmd1 = new NpgsqlCommand();
3. cmd1.Connection = DbConn;
4. cmd1.CommandText = sqlstr;
5. cmd1.ExecuteNonQuery();
7. // 第2种
8. NpgsqlCommand cmd2 = new NpgsqlCommand(sqlstr, DbConn);
9. cmd2.ExecuteNonQuery();

复制代码

   （3）方法

           ① ExecuteNonQuery()  不返回任何效果。

            举例：
   

1. cmd.CommandText = "DROP TABLE IF EXISTS book"; // 如果book表存在，则删掉
2. cmd.ExecuteNonQuery();
4. cmd.CommandText = @"CREATE TABLE book(id SERIAL PRIMARY KEY,
5.     bookname VARCHAR(255), bookprice INT)";
6. cmd.ExecuteNonQuery();
8. cmd.CommandText = "INSERT INTO cars(name, price) VALUES('Volkswagen',21600)";
9. cmd.ExecuteNonQuery();

复制代码

           ② ExecuteScalar()  返回一个值。可搭配ExecuteScalar().ToString();输出值。

            ③ ExecuteReader()  返回一个IDataReader。

    3.NpgsqlDataReader类

    （1）作用

            读取数据。
    （2）应用

   

1. NpgsqlConnection sourceConn = new NpgsqlConnection(sourceUrl);
2. NpgsqlCommand s_cmd = new NpgsqlCommand("select * from public."myApp_book" limit 100", sourceConn);
3. NpgsqlDataReader reader = s_cmd.ExecuteReader();
4. while (reader.Read()) // 循环
5.  {
6.      Console.WriteLine($"first value:{reader.GetValue(0)}, second value: {reader.GetValue(1)}"); //使用下标取值
7.  }

复制代码

   4.DataSet类

    （1）作用

            离线处理大量数据，得到数据集
    （2）应用

            搭配DataAdapter类
   
    5.DataAdapter类：数据适配下令

    （1）结合DataSet类，将数据可存储在DataSet对象中

    （2）DataAdapter属性

            SelectCommand：选取数据源中的记载；
            InsertCommand：向数据源中新插入一条记载；
            UpdateCommand：更新数据源中的数据；
            DeleteCommand：删除数据源中的记载。
    （3）DataAdapter方法

            Fill：从数据源增长或刷新行，并将这些行放到DataSet表中。
            Updata：将DataSet表的更改传送到相应的数据源中。
    （4）应用

   

1. var npgsqlDataAdapter = new NpgsqlDataAdapter();
2. npgsqlDataAdapter.SelectCommand = new NpgsqlCommand("select * from public."myApp_book" limit 100", sourceConn);
3. var dataSet = new DataSet();
4. npgsqlDataAdapter.Fill(dataSet);//将数据填充到dataSet
5. dataSet.Tables[0]; //获取dataSet的table数据

复制代码

   
    （二）using

            using语句在变量超出范围时，开释数据库连接资源。
            举例：
   

1. using var con = new NpgsqlConnection(targetUrl);
2. using var cmd = new NpgsqlCommand(sql, con);

复制代码

   
    三、连接数据库

    举例：
    1. 简朴写法

   

1. using Npgsql;
2. using System;
4. class TestConnDB
5. {
6.     static void Main(string[] args)
7.     {
8.         string DbUrl = "Host=127.0.0.1;Port=5432;Database=TestPro;Username=postgres;Password=admin;";
9.         NpgsqlConnection DbConn = new NpgsqlConnection(DbUrl);
10.         try
11.         {
12.             DbConn.Open();
13.             Console.WriteLine("connect success!");
14.             Console.ReadKey();
15.         }
16.         catch (Exception ex)
17.         {
18.             Console.WriteLine(ex.Message);
19.             Console.ReadKey();
20.         }
21.         DbConn.Close();
22.     }
23. }

复制代码

   2. 灵活写法

   

1. using CommSettingSpace;
2. using Npgsql;
3. using System;
4. using System.Collections.Generic;
6. namespace DBSpace
7. {
8.     //---------设置DB详情
9.     class CommSetting
10.     {
11.         //-------------------------设置Source DB信息
12.         public static Dictionary<string, string> SourceDbInfo = new Dictionary<string, string>
13.         { // 用static，则使用该方法时，不用实例化所属类
14.             { "Host", "127.0.0.1" },
15.             { "Port", "5432" },
16.             { "Database", "bookDb" },
17.             { "Username", "admin" },
18.             { "Password", "123" }
19.         };
21.         //-------------------------设置Target DB信息
22.         public static Dictionary<string, string> TargetDbInfo = new Dictionary<string, string>
23.         {
24.             { "Host", "127.0.0.1" },
25.             { "Port", "5432" },
26.             { "Database", "copyBookDb" },
27.             { "Username", "postgres" },
28.             { "Password", "456" }
29.         };
30.     }
32.     // ---------设置DB连接
33.     class SetDBConn
34.     {
35.         public string PgUrl(Dictionary<string, string> Db)
36.         {
37.             return $"Host={Db["Host"]};Port={Db["Port"]};Database={Db["Database"]};Username={Db["Username"]};Password={Db["Password"]};";
38.         }
39.     }
41.     // ---------执行项目
42.     class Test
43.     {
44.         //main方法（测试连接不同数据库）
45.         static void Main(string[] args)
46.         {
47.             try
48.             {
49.                 //连接第1个数据库（比如在拷贝数据时，这是来源数据库）
50.                 SetDBConn sourceDB = new SetDBConn();
51.                 string sourceUrl = sourceDB.PgUrl(CommSetting.SourceDbInfo);
52.                 NpgsqlConnection sourceConn = new NpgsqlConnection(sourceUrl);
54.                 //连接第2个数据库（这是目标数据库）
55.                 SetDBConn targetDB = new SetDBConn();
56.                 string targetUrl = targetDB.PgUrl(CommSetting.TargetDbInfo);
57.                 NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
58.             }
59.             catch (Exception ex)
60.             {
61.                 Console.WriteLine($"error: {ex.Message}");
62.                 Console.ReadKey();
63.             }
64.         }
65.     }
66. }

复制代码

   
    四、操作数据库

    （一）创建&删除表

   

1. using Npgsql;
2. using Npgsql.Internal;
3. using NpgsqlTypes;
4. using System;
5. using System.Collections.Generic;
6. using System.Data;
7. using System.Data.SqlClient;
8. using System.Linq;
9. using System.Text.Json.Serialization;
11. namespace DBConnSpace
12. {
13.     // ---------设置DB连接
14.     class SetDBConn
15.     {
16.         public string PgUrl(string db_host, string db_port, string db_dbname, string db_user, string db_pwd)
17.         {
18.             return $"Host={db_host};Port={db_port};Database={db_dbname};Username={db_user};Password={db_pwd};";
19.         }
20.     }
21.     class Test
22.     {
23.         //main方法
24.         static void Main(string[] args)
25.         {
26.             //-------------------------设置Pg DB信息
27.             SetDBConn sourceDB = new SetDBConn();
28.             string sourceUrl = sourceDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
29.             NpgsqlConnection sourceConn = new NpgsqlConnection(sourceUrl);
31.             SetDBConn targetDB = new SetDBConn();
32.             string targetUrl = targetDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
33.             NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
34.             try
35.             {
36.                 //-------------------------打开数据库
37.                 sourceConn.Open();
38.                 targetConn.Open();
39.                 Console.WriteLine("connect success!");
40.                 //-------------------------创建数据库表
41.                 NpgsqlCommand cmd2 = new NpgsqlCommand(@"CREATE TABLE book(id SERIAL PRIMARY KEY, bookname VARCHAR(255), bookprice INT)", targetConn);
42.             -------------------------删除已存在的数据库表
43.             //NpgsqlCommand cmd2 = new NpgsqlCommand(@"DROP TABLE IF EXISTS book", targetConn);
44.                 cmd2.ExecuteNonQuery();
45.             }
46.             catch (Exception ex)
47.             {
48.                 Console.WriteLine(ex.Message);
49.                 Console.ReadKey();
50.             }
51.             sourceConn.Close();
52.             targetConn.Close();
53.         }
54.     }
55. }

复制代码

   
    （二）插入

     1. 单笔插入

    （1）使用NpgsqlCommand

   

1. using Npgsql;
2. using Npgsql.Internal;
3. using NpgsqlTypes;
4. using System;
5. using System.Collections.Generic;
6. using System.Data;
7. using System.Data.SqlClient;
8. using System.Linq;
9. using System.Text.Json.Serialization;
11. namespace DBConnSpace
12. {
13.     // ---------设置DB连接
14.     class SetDBConn
15.     {
16.         public string PgUrl(string db_host, string db_port, string db_dbname, string db_user, string db_pwd)
17.         {
18.             return $"Host={db_host};Port={db_port};Database={db_dbname};Username={db_user};Password={db_pwd};";
19.         }
20.     }
21.     class Test
22.     {
23.         static void Main(string[] args)
24.         {
25.             //-------------------------设置Pg DB信息
26.             SetDBConn sourceDB = new SetDBConn();
27.             string sourceUrl = sourceDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
28.             NpgsqlConnection SourceConn = new NpgsqlConnection(sourceUrl);
30.             SetDBConn targetDB = new SetDBConn();
31.             string targetUrl = targetDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
32.             NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
34.             try
35.             {
36.                 //-------------------------打开数据库
37.                 SourceConn.Open();
38.                 targetConn.Open();
39.                 Console.WriteLine("connect success!");
40.                 //-------------------------插入Target DB（单笔）
41.                 NpgsqlCommand t_cmd = new NpgsqlCommand("insert into "myApp_book" " +
42.                     "(card, name, author, btype, price, num) values " +
43.                     "(@card, @name, @author, @btype, @price, @num)", targetConn);
44.                 t_cmd.Parameters.AddWithValue("@card", "T1F001");
45.                 t_cmd.Parameters.AddWithValue("@name", "T数据结构");
46.                 t_cmd.Parameters.AddWithValue("@author", "萝卜吖");
47.                 t_cmd.Parameters.AddWithValue("@btype", "T教育");
48.                 t_cmd.Parameters.AddWithValue("@price", 101);
49.                 t_cmd.Parameters.AddWithValue("@num", 12);
50.                 object res = t_cmd.ExecuteNonQuery();
51.                 Console.WriteLine(res); // 返回插入笔数
54.                 Console.WriteLine("select success!");
55.                 Console.ReadKey();
56.             }
57.             catch (Exception ex)
58.             {
59.                 Console.WriteLine(ex.Message);
60.                 Console.ReadKey();
61.             }
62.             SourceConn.Close();
63.             targetConn.Close();
64.         }
65.     }
66. }

复制代码

   （2）使用BeginBinaryImport

    ① 定义时

   

1. public void SoloInsertDbData(List<Dictionary<string, object>> datalist, NpgsqlConnection conn, string copysql)
3. {
5.     using (var writer = conn.BeginBinaryImport(copysql))
7.     {
9.         writer.StartRow();
11.         foreach (var item in datalist)
13.         {
15.             NpgsqlDbType keyType = (NpgsqlDbType)Convert.ChangeType(item["key"], typeof(NpgsqlDbType));
17.             writer.Write(item["val"], keyType);
19.         }
21.         writer.Complete(); // 直接操作数据库
23.     }
25.     Console.WriteLine("one insert success!");
27. }

复制代码

   ② 调用时

   

1. string targetUrl = "Host=127.0.0.1;Port=5432;Database=TestPro;Username=postgres;Password=root;"
2. NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
3. targetConn.Open();
4. var hisdata = new List<Dictionary<string, object>> //列表包字典
5. {
6.     new Dictionary<string, object> { { "key", NpgsqlDbType.TimestampTz }, { "val", DateTime.UtcNow } },
7.     new Dictionary<string, object> { { "key", NpgsqlDbType.Varchar }, { "val", "failure_analysis" } },
8.     new Dictionary<string, object> { { "key", NpgsqlDbType.Varchar }, { "val", tbtime } },
9.     new Dictionary<string, object> { { "key", NpgsqlDbType.Varchar }, { "val", nowtime } },
10. };
11. var addhissql =@"copy ""rep_updhist""(lastupdate, tablename, starttimekey, endtimekey) FROM STDIN BINARY";
12. tl.SoloInsertDbData(hisdata, targetConn, addhissql);

复制代码

   
     2. 批量插入

   

1. using Npgsql;
2. using Npgsql.Internal;
3. using NpgsqlTypes;
4. using System;
5. using System.Collections.Generic;
6. using System.Data;
7. using System.Data.SqlClient;
8. using System.Linq;
9. using System.Text.Json.Serialization;
11. namespace DBConnSpace
12. {
13.     // ---------设置DB连接
14.     class SetDBConn
15.     {
16.         public string PgUrl(string db_host, string db_port, string db_dbname, string db_user, string db_pwd)
17.         {
18.             return $"Host={db_host};Port={db_port};Database={db_dbname};Username={db_user};Password={db_pwd};";
19.         }
20.     }
21.     class Test
22.     {
23.         static void Main(string[] args)
24.         {
25.             //-------------------------设置Pg DB信息
26.             SetDBConn sourceDB = new SetDBConn();
27.             string sourceUrl = sourceDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
28.             NpgsqlConnection SourceConn = new NpgsqlConnection(sourceUrl);
30.             SetDBConn targetDB = new SetDBConn();
31.             string targetUrl = targetDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
32.             NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
34.             try
35.             {
36.                 //-------------------------打开数据库
37.                 SourceConn.Open();
38.                 targetConn.Open();
39.                 Console.WriteLine("connect success!");
41.                 //-------------------------获取Source DB
42.                 //使用DataAdapter查询（会自动断开链接，把数据存在内存里）
43.                 var npgsqlDataAdapter = new NpgsqlDataAdapter();
44.                 npgsqlDataAdapter.SelectCommand = new NpgsqlCommand("select * from public."myApp_book" limit 100", SourceConn);
45.                 //将数据填充到dataTable
46.                 var dataTable = new DataTable();
47.                 npgsqlDataAdapter.Fill(dataTable);
49.                 //-------------------------插入Target DB（批量）
50.                 //第1种：插入写死的测试数据，即直接插入的完整sql
51.                 NpgsqlCommand t_cmd1 = new NpgsqlCommand("insert into "myApp_book" " +
52.                     "(card, name, author, btype, price, num) values " +
53.                     "('T1F001', 'T数据结构', '萝卜吖', 'T教育', 101, 22), " +
54.                     "('T1F002', 'T操作系统', '玉米吖', 'T教育', 102, 33)", targetConn);
55.                 object res1 = t_cmd1.ExecuteNonQuery();
56.                 Console.WriteLine(res1); // 返回插入笔数
58.                 //第2种：将SourceDB的数据重新整理，拼接字串
59.                 var sqlstr = "insert into "myApp_book" " + "(card, name, author, btype, price, num) values ";
60.                 foreach (DataRow record in dataTable.Rows)
61.                 {
62.                     sqlstr = sqlstr + "('" + record["card"] + "','" + record["name"] +
63.                         "','" + record["author"] + "','" + record["btype"] +
64.                         "'," + record["price"] + "," + record["num"] + "),";
65.                 }
66.                 sqlstr = sqlstr.Substring(0, sqlstr.Length - 1); // 移除字符串的最后一个字符
67.                 NpgsqlCommand t_cmd2 = new NpgsqlCommand(sqlstr, targetConn);
68.                 object res2 = t_cmd2.ExecuteNonQuery();
69.                 Console.WriteLine(res2); // 返回插入笔数
71.                 //第3种：将SourceDB的数据重新整理，使用BeginBinaryImport和copy
72.             // 使用注意事项：确保每列各自存取的所有数据类型都一致。
73.                 var writer = targetConn.BeginBinaryImport("copy "myApp_book"(card, name, author, btype, price, num) FROM STDIN BINARY");
74.                 foreach (DataRow record in dataTable.Rows)
75.                 {
76.                     writer.StartRow();
77.                     writer.Write(record["card"]); // 只是整理数据，不会直接操作数据库
78.                     writer.Write(record["name"]);
79.                     writer.Write(record["author"]);
80.                     writer.Write(record["btype"]);
81.                     writer.Write(record["price"]);
82.                     writer.Write(record["num"]);
84.                 //writer.StartRow();
85.                 //foreach (DataColumn col in dbdata.Columns) // 每个单元格
86.                 //{
87.                 //    writer.Write(record[col]); // 只是整理数据，不会直接操作数据库
88.                 //}
90.                 }
91.                 writer.Complete(); // 直接操作数据库
93.              Console.WriteLine($"bulk insert success, count( {dbdata.Rows.Count} )"); // 操作笔数
94.                 Console.ReadKey();
95.             }
96.             catch (Exception ex)
97.             {
98.                 Console.WriteLine(ex.Message);
99.                 Console.ReadKey();
100.             }
101.             SourceConn.Close();
102.             targetConn.Close();
103.         }
104.     }
105. }

复制代码

   
    （三）查询

   

1. using Npgsql;
2. using System;
3. using System.Collections.Generic;
4. using System.Data;
5. using System.Linq;
7. namespace DBConnSpace
8. {
9.     // ---------设置DB连接
10.     class SetDBConn
11.     {
12.         public string PgUrl(string db_host, string db_port, string db_dbname, string db_user, string db_pwd)
13.         {
14.             return $"Host={db_host};Port={db_port};Database={db_dbname};Username={db_user};Password={db_pwd};";
15.         }
16.     }
17.     class Test
18.     {
19.         static void Main(string[] args)
20.         {
21.             //-------------------------设置Pg DB信息
22.             SetDBConn sourceDB = new SetDBConn();
23.             string sourceUrl = sourceDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
24.             NpgsqlConnection SourceConn = new NpgsqlConnection(sourceUrl);
26.             SetDBConn targetDB = new SetDBConn();
27.             string targetUrl = targetDB.PgUrl("127.0.0.1", "5432", "TestPro1219DB", "postgres", "root");
28.             NpgsqlConnection targetConn = new NpgsqlConnection(targetUrl);
30.             try
31.             {
32.                 //-------------------------打开数据库
33.                 SourceConn.Open();
34.                 targetConn.Open();
35.                 Console.WriteLine("connect success!");
37.                 //-------------------------获取Source DB
38.                 //（1）使用DataAdapter查询（会自动断开链接，把数据存在内存里）
39.                 var npgsqlDataAdapter = new NpgsqlDataAdapter();
40.                 npgsqlDataAdapter.SelectCommand = new NpgsqlCommand("select * from public."myApp_book" limit 100", SourceConn);
42.                 //1.1 使用DataTable
43.                 var dataTable = new DataTable();
44.                 npgsqlDataAdapter.Fill(dataTable);//将数据填充到dataTable
45.            // 1.1.1 获取很多列的数据
46.                 foreach (DataRow dataTableRow in dataTable.Rows)
47.                 {
48.                     Console.WriteLine($"first value:{dataTableRow["bid"]}, second value: {dataTableRow["card"]}");
49.                 }
50.            // 1.1.2 获取某列的所有数据
51.                 //将$ "
52.                 // 注意，其中 npgsqlDataAdapter.SelectCommand的查询语句如下
53.                 // npgsqlDataAdapter.SelectCommand = new NpgsqlCommand("select array_agg(distinct bookname) bookname from "book", SourceConn);
54.                 if (dt.Rows.Count > 0)
55.                 {
56.                     var bknames = (object[])dt.Rows[0]["bookname"]; // 获取聚合后的数组
57.                     foreach (var bk in bknames)
58.                     {
59.                         Console.WriteLine(bk); // 打印每个书名
60.                     }
61.                 }
64.                 //1.2 使用DataSet
65.                 Console.WriteLine();
66.                 var dataSet = new DataSet();
67.                 npgsqlDataAdapter.Fill(dataSet);//将数据填充到dataSet
68.                 var dataTable2 = new DataTable();
69.                 dataTable2 = dataSet.Tables[0]; //将dataSet的table数据填充到dataTable
70.                 foreach (DataRow dataTableRow in dataTable2.Rows)
71.                 {
72.                     Console.WriteLine($"first value:{dataTableRow["bid"]}, second value: {dataTableRow["card"]}");
73.                 }
75.                 //foreach (DataRow row in dataTable.Rows) // 每行
76.                 //{
77.                 //    foreach (DataColumn col in dataTable.Columns) // 每个单元格
78.                 //    {
79.                 //        Console.Write(row[col] + "\t");
80.                 //    }
81.                 //    Console.WriteLine();
82.                 //}
84.                 //（2）使用DataReader查询
85.                 Console.WriteLine();
86.                 NpgsqlCommand s_cmd = new NpgsqlCommand("select * from public."myApp_book" limit 100", SourceConn);
87.                 NpgsqlDataReader reader = s_cmd.ExecuteReader();
88.                 if (reader.Read()) // 是否有数据
89.                 {
90.                     //使用下标取字段名
91.                     Console.WriteLine($"BookID: {reader.GetName(0).ToString()}, BookName: {reader.GetName(1).ToString()}");
92.                 }
93.                 while (reader.Read()) // 循环
94.                 {
95.                     //使用下标取值
96.                     Console.WriteLine($"first value:{reader.GetValue(0)}, second value: {reader.GetValue(1)}");
97.                     //使用字段取值
98.                     Console.WriteLine($"first value:{reader["bid"].ToString()}, second value:{reader["card"].ToString()}");
99.                 }
100.                
101.                 Console.WriteLine("select success!");
102.                 Console.ReadKey();
103.             }
104.             catch (Exception ex)
105.             {
106.                 Console.WriteLine(ex.Message);
107.                 Console.ReadKey();
108.             }
109.             SourceConn.Close();
110.             targetConn.Close();
111.         }
112.     }
113. }

复制代码

   （四）更新

     1. 批量更新

   

1. using DBConnSpace;
2. using Npgsql;
3. using System;
4. using System.Collections.Generic;
5. using System.Data;
6. using System.Text;
8. namespace TestCls
9. {
10.     class Program
11.     {
12.         static void Main()
13.         {
14.             //-------------------------设置Pg DB信息
15.             string sourceUrl = "Host=127.0.0.1;Port=5432;Database=TestPro1219DB;Username=postgres;Password=root";
16.             var sourceConn = new NpgsqlConnection(sourceUrl);
17.             string targetUrl = "Host=127.0.0.1;Port=5432;Database=TestPro1219DB;Username=postgres;Password=root";
18.             var targetConn = new NpgsqlConnection(targetUrl);
19.             sourceConn.Open();
20.             targetConn.Open();
21.             try
22.             {
23.                 //-------------------------使用sqlBuilder拼接SQL语句
24.                 StringBuilder sqlBuilder = new StringBuilder();
25.                 StringBuilder sqlBuilder2 = new StringBuilder();
26.                 StringBuilder sqlBuilder3 = new StringBuilder();
27.                 sqlBuilder.AppendLine("UPDATE "myApp_book" SET price = CASE bid ");
28.                 sqlBuilder2.AppendLine(" END, name = CASE bid ");
29.                 sqlBuilder3.AppendLine(" WHERE bid IN ( ");
31.                 // 定义一个字典，包含了需要更新的bid和对应的新价格和新名称
32.                 //var update_data = new Dictionary<int, object[]>
33.                 //{
34.                 //    { 1, new object[] { 132, "数据库原理2" } }, // 更新bid为1的书籍价格为132，名称为“数据库原理2”
35.                 //    { 2, new object[] { 134, "数据结构2" } }  // 更新bid为2的书籍价格为134，名称为“数据结构2”
36.                 //};
37.                 //foreach (var upd in update_data)
38.                 //{
39.                 //    sqlBuilder.AppendFormat(" WHEN {0} THEN {1} ", upd.Key, upd.Value[0]);
40.                 //    sqlBuilder2.AppendFormat(" WHEN {0} THEN '{1}' ", upd.Key, upd.Value[1]);
41.                 //    sqlBuilder3.AppendFormat(" {0}, ", upd.Key);
42.                 //}
43.                 //sqlBuilder.AppendLine($"{sqlBuilder2.ToString()} END {sqlBuilder3.ToString()} 0 ); ");
44.                 var npgsqlDataAdapter = new NpgsqlDataAdapter();
45.                 npgsqlDataAdapter.SelectCommand = new NpgsqlCommand("select * from "myApp_book" where bid in (1,2,3)", sourceConn);
46.                 var dataTable = new DataTable();
47.                 npgsqlDataAdapter.Fill(dataTable);//将数据填充到dataTable
48.                 foreach (DataRow dataTableRow in dataTable.Rows)
49.                 {
50.                     sqlBuilder.AppendFormat(" WHEN {0} THEN {1} ", dataTableRow["bid"], dataTableRow["price"]); // 条件是bid，值是price
51.                     sqlBuilder2.AppendFormat(" WHEN {0} THEN \'{1}\' ", dataTableRow["bid"], dataTableRow["name"]); // 条件是bid，值是name
52.                     sqlBuilder3.AppendFormat(" {0}, ", dataTableRow["bid"]); // where 所有条件
53.                 }
54.                 sqlBuilder.AppendLine($"{sqlBuilder2.ToString()} END {sqlBuilder3.ToString()} 0 ); ");
56.                 var command = new NpgsqlCommand(sqlBuilder.ToString(), targetConn);
57.                 command.ExecuteNonQuery();
58.             }
59.             catch(Exception ex)
60.             {
61.                 Console.WriteLine(ex.ToString());
62.             }
63.             sourceConn.Close();
64.             targetConn.Close();
65.         }
66.     }
67. }

复制代码

   五、写法

    （一）字符串中的双引号

            1. 用@符号+“”两个双引号

            其中，@符号包裹的内容里，两个双引号表现一个双引号。
   

1. @"select * from public.""myApp_book"" limit 100"

复制代码

           2. 用 \" 转义

   

1. "select * from public."myApp_book" limit 100"

复制代码

   
    六、问题

     1. 问题：于 Npgsql.NpgsqlException 擲回破例狀況: 'mscorlib.dll'

            解决：这个问题表达不明确，则自己写trycatch捕捉异常，并使用Console.ReadKey()让异常保留在exe控制台上查看。
     2. 问题：42601: INSERT has more target columns than expressions 和 42601: syntax error at or near "[" 这两个问题

            原因：批量添加时，不能使用[ ]或( )括号等来包裹批量记载，直接用逗号隔开每笔记载就好。
            解决：取消外括号 ( )或[ ]
     3. 问题：Binary importer closed in the middle of a row, cancelling import.

            原因：要插入的列数和表格的列数不同等。
            解决：列数和列值类型一定要对应。
     4. 问题：22P03: incorrect binary data format

            原因：在使用BeginBinaryImport()的writer.Write()时，插入值的列类型存在不同。
            解决：writer.Write()中定义列类型。
            举例：
   

1. copysql = @"copy ""book""(publishdate, author, price, num) FROM STDIN BINARY";
2. datalist = new List<object> { DateTime.UtcNow, "数据结构", 12.3, 100};
3. using (var writer = conn.BeginBinaryImport(copysql))
4. {
5.         writer.StartRow();
6.         writer.Write(datalist[0], NpgsqlDbType.TimestampTz); // 时间戳
7.         writer.Write(datalist[1], NpgsqlDbType.Varchar); // varchar类型
8.         writer.Write(datalist[2], NpgsqlDbType.Float);  // 浮点数
9.         writer.Write(datalist[3], NpgsqlDbType.Integer);  // 整数
10.         writer.Complete(); // 直接操作数据库
11. }

复制代码

    5. 问题：Binary importer closed in the middle of a row, cancelling import.

            原因：在使用BeginBinaryImport()的writer.Write()之前，没有写writer.StartRow();。
            解决：在使用BeginBinaryImport()之后，writer.Write()之前，补充writer.StartRow();。
   
     6. 问题：error: Write for column 17 resolves to a different PostgreSQL type: OID 1114 than the first row resolved to (OID 0). Please make sure to use clr types that resolve to the same PostgreSQL type across rows. Alternatively pass the same NpgsqlDbType or DataTypeName to ensure the PostgreSQL type ends up to be identical.

            原因：使用 BeginBinaryImport 的 Write<T>(T, NpgsqlDbType) 时，循环每笔数据的NpgsqlDbType类型不匹配。
            比如，针对某表，需要存入20笔记载，在write时会根据第一笔记载产生的NpgsqlDbType来定义每列类型。当前16笔的时间列值都是null，第17笔的时间列值忽然不为空了，酿成“2024-03-19 10:05:44.561 +0800”有值了，则write时的类型就会不匹配，然后报错。
            解决：确保每列各自存放的数据类型都同等。
   
     7. 问题：The binary import operation was started with 23 column(s), but 24 value(s) were provided.

            原因：使用 BeginBinaryImport 的 Write<T>(T, NpgsqlDbType) 时，write有多写。
            解决：检查write所在的代码
   
     8. 问题： 22021: invalid byte sequence for encoding "UTF8": 0x00

            原因：
            （1）使用 BeginBinaryImport 的 Write<T>(T, NpgsqlDbType) 时，没有定义write类型。然后循环时某列每笔的数据类型不同等。
            （2）表现数据库中大概插入了 NULL 值，需要转换格式为DBNull.Value。如： if (DBNull.Value.Equals(record[col])) { colval = DBNull.Value; } 。
            （3）定义的NpgsqlDbType不准确。
            （4）插入的数据，在Write时，主动转变为顺应的数据格式，与数据库想要的格式不符合。
            解决：
   

1. using (var writer = conn.BeginBinaryImport(copysql))
2. {
3.         foreach (DataRow record in dbdata.Rows)
4.         {
5.                 writer.StartRow();
6.                 foreach (DataColumn col in dbdata.Columns) // 每个单元格
7.                 {
8.                         var colval = record[col];
9.                         if (DBNull.Value.Equals(record[col])) { colval = DBNull.Value; }
10.                         var colldbtype = col.DataType.Name.ToString();
11.                         NpgsqlDbType pgtype = NpgsqlDbType.Varchar;
13.         // 最好不要使用自动识别默认数据格式，而是自定义每个栏位对应的数据格式
14.                   //  if (colldbtype.ToLower().Contains("int64") || colldbtype.ToLower().Contains("int32")) { pgtype = NpgsqlDbType.Bigint; }
15.         //  if (colldbtype.ToLower().Contains("decimal")) { pgtype = NpgsqlDbType.Numeric; }
16.          //  if (colldbtype.ToLower().Contains("double")) { pgtype = NpgsqlDbType.Double; }
17.          // if (colldbtype.ToLower().Contains("string")) { pgtype = NpgsqlDbType.Varchar; }
19.                         string[] charList = { "producttype", "factoryname", "productspecname", "productrequestname", "productquantity", "createuser", "instruction", "lasteventtimekey" };
20.                         string[] intList = { "planquantity", "process_qty", "scrap_qty", "pickingquantity", "finishedquantity", "output_qty", "no_wms_qty", "operate_date" };
21.                         string[] dblList = { "output_rate", "yield_rate", "finish_rate" };
22.                         string[] tmList = { "createtime", "trackintime", "packagetime", "lasteventtime" };
23.                         if (charList.Contains(col.ToString()) )
24.                         {
25.                                 if (!DBNull.Value.Equals(colval))
26.                                 {
27.                                         colval = colval.ToString();
28.                                 }
29.                                 pgtype = NpgsqlDbType.Varchar;
30.                         }
31.                         if (intList.Contains(col.ToString()) )
32.                         {
33.                                 pgtype = NpgsqlDbType.Integer;
34.                         }
35.                         if (dblList.Contains(col.ToString()))
36.                         {
37.                                 if (!DBNull.Value.Equals(colval))
38.                                 {
39.                                         colval = Convert.ToDouble(colval);
40.                                 }
41.                                 pgtype = NpgsqlDbType.Double;
42.                         }
43.                         if (tmList.Contains(col.ToString()))
44.                         {
45.                                 if (!DBNull.Value.Equals(colval))
46.                                 {
47.                                         DateTime dateTime = (DateTime)colval;
48.                                         colval = dateTime.ToUniversalTime();
49.                                 }
50.                                 pgtype = NpgsqlDbType.TimestampTz;
51.                         }
52.                         writer.Write(colval, pgtype);
53.                 }
54.         }
55.         Console.WriteLine($"start2");
56.         writer.Complete(); // 直接操作数据库
57. }
58. Console.WriteLine($"bulk insert success, count( {dbdata.Rows.Count} )");

复制代码

    9. 问题：Writing values of 'System.DateTimeOffset' is not supported for parameters having NpgsqlDbType 'Timestamp'.

            原因：NpgsqlDbType.Timestamp使用类型错了。
            解决：使用类型是 NpgsqlDbType.TimestampTz。
   
     10. 问题：Cannot write DateTimeOffset with Offset=08:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported.

            原因：只答应写入UTC时间的DateTimeOffset对象
            解决：用UtcDateTime格式。如：DateTime utcDateTime = yourDateTimeOffset.UtcDateTime;
   
     11. 问题：error: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange.

            原因：postgreDB上的时间栏位用的是timestamptz格式。
            解决：使用ToUniversalTime。
   

1. if (tmList.Contains(col.ToString()))
2. {
3.          if (!DBNull.Value.Equals(colval))
4.          {
5.                  DateTime dateTime = (DateTime)colval;
6.                  colval = dateTime.ToUniversalTime();
7.          }
8.          pgtype = NpgsqlDbType.TimestampTz;
9. }
10. writer.Write(colval, pgtype);

复制代码

   

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4