Postgresql

在 PostgreSQL 中將數據表作為參數傳遞

  • January 2, 2018

我在 asp.net C# 中有一個數據表。我想將該數據表作為表參數傳遞給 PostgreSQL 函式。這怎麼可能？

下面的範例是相同的，但它在 SQL Server 中。我需要同樣的東西，但問題是我有 PostgreSQL 作為後端，而不是 SQL Server。

介紹

SQL Server 儲存過程支持 System.Data.DataTable 作為參數。我們可以使用 ADO.Net 以與使用 System.Data.SqlParameter 類相同的方式將 DataTable 傳遞給儲存過程，但需要對數據類型進行一些更改。通常我們為 varchar、nvarchar、int 等普通參數提供SqlParameter 的DbType，如下面的程式碼所示。

SqlParameter sqlParam= new SqlParameter();  
sqlParam.ParameterName = "@StudentName";  
sqlParam.DbType = DbType.String;  
sqlParam.Value = StudentName;

但是在 Table 參數的情況下，我們不需要提供 DbType作為參數數據類型。我們需要提供SqlType而不是 DbType。

例子

SqlParameter Parameter = new SqlParameter;  
Parameter.ParameterName = "@PhoneBook";  
Parameter.SqlDbType = SqlDbType.Structured;  
Parameter.Value = PhoneTable;

下面的範例接收電話簿列表並使用 ADO.Net 將它們儲存在數據庫中。該範例從列表中檢索電話簿詳細資訊並將它們儲存到 DataTable 中，並將此表作為參數傳遞給名為 NewPhoneBook 的儲存過程。

//Phone book list    
List<PhoneBook> PhoneBooks    
 //CReating Table    
 DataTable PhoneTable = new DataTable();    

 // Adding Columns    
 DataColumn COLUMN=new DataColumn();    
 COLUMN.ColumnName="ID";    
 COLUMN.DataType= typeof(int);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactNumber";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 COLUMN = new DataColumn();    
 COLUMN.ColumnName = "ContactName";    
 COLUMN.DataType = typeof(string);    
 PhoneTable.Columns.Add(COLUMN);    

 // INSERTING DATA    
 foreach (UserPhoneBook UPB in PhoneBooks)    
 {    
    DataRow DR = PhoneTable.NewRow();    
    DR[0] = UPB.UserName;    
    DR[1] = UPB.ContactNumber;    
    DR[2] = UPB.ContactName;    
    PhoneTable.Rows.Add(DR);    
 }    
 //Parameter declaration    
 SqlParameter[] Parameter = new SqlParameter[2];    
 Parameter[0].ParameterName = "@PhoneBook";    
 Parameter[0].SqlDbType = SqlDbType.Structured;    
 Parameter[0].Value = PhoneTable;    

 Parameter[1].ParameterName = "@Return_Value";    
 Parameter[1].Direction = ParameterDirection.ReturnValue;    
 //Executing Procedure  
 SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

您可以在此處找到程式碼參考

我永遠不會使用這些對象。它們似乎沒有那麼有用。也就是說，PostgreSQL 確實有一種通過jsonb. 如果您希望獲得Passing a Table-Valued Parameter to a Stored Procedure中描述的功能，這可能是您的理想解決方案。您所要做的就是將 DataTable 映射到 JSON，然後將 JSON 傳遞給函式。

要將數據表轉換為 JSON，您可以使用JSON.net。另請參閱這些 Stack Overflow 答案。

如果 Datatable 太大而無法序列化為 JSON，您可能必須圍繞導出 DataRow 對象的內容創建一個外部數據包裝器，這將使您沿著“使用 DataReader 流式處理行”中提到的路徑開始。雖然如果您不需要伺服器/客戶端模型，您始終可以將 DataTable 轉儲到 CSV 並使用Foreign Data Wrapper讀取它：沿著這條路線檢查file-fdw。

我真的試過這個。我遇到了很多問題。

  1. 弄清楚如何使用 nuGet 安裝軟體包甚至都不容易，
  2. 安裝了 nuGet，儘管它是 nuGet 上的 #1 包，但它還不能與 .NET Core 2.x 一起使用
  3. 安裝nuGet 的 beta 版本，我讓它工作了。

使用 .NET Core 2.x 將 DataTable 轉換為 JSON，

using System;
using System.Data;
using Newtonsoft.Json;

class Program
{
 static void Main()
 {
   // Get the DataTable.
   DataTable table = GetTable();
   // ... Use the DataTable here with SQL.
   string json = JsonConvert.SerializeObject(table, Formatting.Indented);
   Console.WriteLine(json);

 }

 static DataTable GetTable()
 {
   // Here we create a DataTable with four columns.
   DataTable table = new DataTable();
   table.Columns.Add("Dosage", typeof(int));
   table.Columns.Add("Drug", typeof(string));
   table.Columns.Add("Patient", typeof(string));
   table.Columns.Add("Date", typeof(DateTime));

   // Here we add five DataRows.
   table.Rows.Add(25, "Indocin", "David", DateTime.Now);
   table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
   table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
   table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
   table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

   return table;
 }
}

您仍然必須連接到數據庫並將 json 放入INSERT語句中，但這應該很容易完成。

cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);

在伺服器上定義myfunc(jsonb)，你應該很好：你已經將 a 傳遞給DataTablePostgreSQL。

我不相信在 PostgreSQL 中可以像使用 SQL Server 那樣做到這一點。話雖如此，當您查看引擎蓋下發生的事情時，實際上並不難模擬。一個常見的誤解是將數據表“整體”傳遞給儲存過程。這不是真的，數據表是在 SQL Server 端使用數據表中的數據重建的。以下是在 SQL Server 中執行此操作時實際發生的情況：

  • 在記憶體中創建指定表類型的變數。
  • 為數據表中的每條記錄插入變數。
  • 對變數的引用被傳遞給儲存過程。

它看起來有點像這樣：

DECLARE @p1 [dbo].[table_type_name];
INSERT INTO @p1 VALUES(...);
INSERT INTO @p1 VALUES(...);
-- continue until all rows have been inserted
EXEC [dbo].[sp_something] @dataTable = @p1;

你看，沒什麼特別的。使用臨時表或臨時表並自己生成 SQL 很容易模擬這一點。唯一的主要區別是您不能將對錶的引用傳遞給儲存過程。

正如 Evan 提到的，還有其他將數據傳遞到 PostgreSQL 的方法，但如果您正在尋找跨平台解決方案，那麼您需要確保只選擇符合 ANSI 的方法 - 不幸的是，這不包括 SQL Server 表值參數.

這就是我將如何實現需要某種臨時表的解決方案：

  • 實現一個可用於返回會話 id 的序列
  • 創建一個臨時表，其中包含所需的列以及會話 id 的列
  • 創建一個儲存過程，從與給定會話 id 匹配的臨時表中取出數據並將其與目標表合併。

例子：

SQL

CREATE SEQUENCE seq_get_session_id START 1;

CREATE TABLE stg_table (
  session_id bigint not null,
  -- remainder of columns
);

CREATE OR REPLACE FUNCTION insert_data(_session_id BIGINT)
RETURNS void AS
$BODY$
BEGIN
   INSERT INTO my_table (...)
   SELECT /* columns */
     FROM stg_table
    WHERE session_id = _session_id;

   DELETE FROM stg_table
   WHERE session_id = _session_id;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

C＃

var phonebooks = GetPhoneBooks();

using (var connection = GetConnection())
{
   long sessionId;
   connection.Open();
   var transaction = connection.BeginTransaction();

   // get the session id
   using(var command = connection.CreateCommand())
   {
       command.Transaction = transaction;
       command.CommandText = "SELECT nextval('seq_get_session_id')";
       using (var reader = command.ExecuteReader())
       {
           if (!reader.Read())
               throw new InvalidOperationException("Unable to obtain session id");
           sessionId = reader.GetInt64(0);
       }       
   }

   // insert data into staging table
   using (var insertCommand = connection.CreateCommand())
   {
       insertCommand.CommandText = 
           "INSERT INTO stg_table (session_id, ...)" + 
           " VALUES (_session_id, ...)";
       insertCommand.Transaction = transaction;
       insertCommand.Parameters.AddWithValue("_session_id", sessionId);

       var p1 = insertCommand.CreateParameter();
       p1.ParameterName = "_p1";
       p1.DbType = DbType.DateTime;
       p1.Value = DateTime.Now;
       insertCommand.Parameters.Add(p1);

       // repeat above for each property that needs to be persisted...

       foreach(var phonebook in phonebooks)
       {
           p1.Value = phonebook.Date;
           // set values for any other parameters you have declared...

           insertCommand.ExecuteNonQuery();
       }    
   }

   // execute final stored procedure to merge the data
   using (var insertDataCommand = connection.CreateCommand())
   {
       insertDataCommand.CommandText = "insert_data";
       insertDataCommand.CommandType = CommandType.StoredProcedure;
       insertDataCommand.Parameters.AddWithValue("_session_id", sessionId);

       insertDataCommand.Transaction = transaction;
       insertDataCommand.ExecuteNonQuery();
   }  

   transaction.Commit();
}

但是，如果您不需要臨時表，則上述內容是主要的過度殺傷力，如果您能夠將數據直接插入目標表中，則可以省略上述大部分程式碼並執行以下操作：

using (var connection = GetConnection())
{
   connection.Open();
   var transaction = connection.BeginTransaction();

   using (var insertCommand = connection.CreateCommand())
   {
       insertCommand.CommandText = 
           "INSERT INTO my_table (...)" + 
           " VALUES (...)";
       insertCommand.Transaction = transaction;

       var p1 = insertCommand.CreateParameter();
       p1.ParameterName = "_p1";
       p1.DbType = DbType.DateTime;
       p1.Value = DateTime.Now;
       insertCommand.Parameters.Add(p1);

       // repeat above for each property that needs to be persisted...

       foreach(var phonebook in phonebooks)
       {
           p1.Value = phonebook.Date;
           // set values for any other parameters you have declared...

           insertCommand.ExecuteNonQuery();
       }         
   }

   transaction.Commit();
}

引用自：https://dba.stackexchange.com/questions/193827

相關問答

Postgresql

呼叫從 C# 返回本地數字的 Postgresql 函式時出現異常

  • February 13, 2021
檢視問答
Postgresql

在 postgresql 中將 50K 記錄插入大表（應用程序。350 列）

  • June 19, 2020
檢視問答
Postgresql

檢查主伺服器是否從從伺服器（複製）postgresql

  • September 27, 2018
檢視問答
Postgresql

嘗試通過 Npgsql 和 C# 使用實體框架連接到 PostgreSQL

  • May 6, 2018
檢視問答
Postgresql

獲取遠端表數據並儲存到 pgSQL 中的本地轉儲文件中？

  • December 14, 2016
檢視問答
Postgresql

自引用記錄，可能引用同一行，具有 uuid pkey 和非 null 和外鍵約束？

  • September 16, 2022
檢視問答