在 PostgreSQL 中將數據表作為參數傳遞
我在 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。
我真的試過這個。我遇到了很多問題。
- 弄清楚如何使用 nuGet 安裝軟體包甚至都不容易,
- 安裝了 nuGet,儘管它是 nuGet 上的 #1 包,但它還不能與 .NET Core 2.x 一起使用
- 安裝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 傳遞給DataTable
PostgreSQL。
我不相信在 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(); }