我正在嘗試創建一個呼叫 REST API 并將回應插入 Azure SQL 服務器表的 SSIS 包。為此，我使用腳本任務和 ADO.NET 連接管理器。API 回應采用 JSON 格式，如下所示
{[
{"id": 1, "name": "AX", "description": "B", "shippingFreeze": false, "receivingFreeze": false, "mouseNorovirus": false, "irradiatedFeed": true, "createdAt": "2022-02-24T10:03:50.09", "lastUpdated": "2022-02-24T10:03:50.09" },
{"id": 2, "name": "PD ", "description": "F", "shippingFreeze": false, "receivingFreeze": false, "mouseNorovirus": false, "irradiatedFeed": false, "createdAt": "2022-02-24T10:03:50.09", "lastUpdated": "2022-02-24T10:03:50.09" }
]}
SQL 服務器中的表
我正在嘗試遍歷 JSON 并將它們中的每一個插入到 SQL 服務器表中，如下所示
var result = response.Content.ReadAsStringAsync().Result;
dynamic res_JSON = JsonConvert.DeserializeObject(result);
ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];
var sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
using (var sqlCmd = new System.Data.SqlClient.SqlCommand("INSERT INTO [dbo].[RM_Room]([ROOMID],[NAME],[DESCRIPTION],[SHIPPING_FREEZE],[RECEIVING_FREEZE],[MOUSE_NOROVIRUS],[IRRADIATED_FEED]) VALUES(@ROOMID,@NAME,@DESCRIPTION,@SHIPPING_FREEZE,@RECEIVING_FREEZE,@MOUSE_NOROVIRUS,@IRRADIATED_FEED,)", sqlConn))
{
foreach (var jsonObj in res_JSON)
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Parameters.AddWithValue("@ROOMID", jsonObj.id.ToString());
sqlCmd.Parameters.AddWithValue("@NAME", jsonObj.name.ToString());
sqlCmd.Parameters.AddWithValue("@DESCRIPTION", jsonObj.description.ToString());
sqlCmd.Parameters.AddWithValue("@SHIPPING_FREEZE", (jsonObj.shippingFreeze.ToString() == "true") ? "T" : "F");
sqlCmd.Parameters.AddWithValue("@RECEIVING_FREEZE", (jsonObj.receivingFreeze.ToString() == "true") ? "T" : "F");
sqlCmd.Parameters.AddWithValue("@MOUSE_NOROVIRUS", (jsonObj.mouseNorovirus.ToString() == "true") ? "T" : "F");
sqlCmd.Parameters.AddWithValue("@IRRADIATED_FEED", (jsonObj.irradiatedFeed.ToString() == "true") ? "T" : "F");
int no_exec = sqlCmd.ExecuteNonQuery();
}
}
cm.ReleaseConnection(sqlConn);
}}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
當我除錯時，它會拋出類似的錯誤
和堆疊跟蹤
在 System.Data.SqlClient.SqlConnection.OnError（SqlException 例外，布爾 breakConnection，操作1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ST_b0ab9fccfaa640008ecd1bdf57ec4324.ScriptMain.Main() in C:\Users\dv_admin\AppData\Local\Temp\2\vsta\43ff553a1bba27\ScriptMain.cs:line 76
I am not sure what is that I am missing here. Any help is greatly appreciated
Updated code
public async void Main()
{
try
{
var sqlConn = new System.Data.SqlClient.SqlConnection();
ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];
string serviceUrl = Dts.Variables["$Project::ServiceUrl"].Value.ToString();
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12 | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls;
HttpClient client = new HttpClient();
client.BaseAddress = new Uri(serviceUrl);
client.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/json"));
string APIUrl = string.Format(serviceUrl "/rooms");
var response = client.GetAsync(APIUrl).Result;
if (response.IsSuccessStatusCode)
{
var result = await response.Content.ReadAsStringAsync();
try
{
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
const string query = @"INSERT INTO dbo.RM_Room
(ROOMID, NAME, DESCRIPTION, SHIPPING_FREEZE, RECEIVING_FREEZE, MOUSE_NOROVIRUS, IRRADIATED_FEED)
SELECT id, name, description,
CASE shippingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
CASE receivingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
CASE mouseNorovirus WHEN 1 THEN 'T' ELSE 'F' END,
CASE irradiatedFeed WHEN 1 THEN 'T' ELSE 'F' END
FROM OPENJSON(@json)
WITH (
id int,
name varchar(100),
description varchar(1000),
shippingFreeze bit,
receivingFreeze bit,
mouseNorovirus bit,
irradiatedFeed bit
) j;";
using (var sqlCmd = new System.Data.SqlClient.SqlCommand(query, sqlConn))
{
sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result;
await sqlConn.OpenAsync();
await sqlCmd.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
if (sqlConn != null)
cm.ReleaseConnection(sqlConn);
}
}
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
uj5u.com熱心網友回復：
您的主要問題是 SQL 中的額外逗號，這是語法錯誤。
將整個 JSON 傳遞給 SQL Server 并使用OPENJSON
var result = await response.Content.ReadAsStringAsync();
ConnectionManager cm = Dts.Connections["SurplusMouse_ADONET"];
SqlConnection sqlConn = null;
try
{
sqlConn = (SqlConnection)cm.AcquireConnection(Dts.Transaction));
const string query = @"
INSERT INTO dbo.RM_Room
(ROOMID, NAME, DESCRIPTION, SHIPPING_FREEZE, RECEIVING_FREEZE, MOUSE_NOROVIRUS, IRRADIATED_FEED)
SELECT
id,
name,
description,
CASE shippingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
CASE receivingFreeze WHEN 1 THEN 'T' ELSE 'F' END,
CASE mouseNorovirus WHEN 1 THEN 'T' ELSE 'F' END,
CASE irradiatedFeed WHEN 1 THEN 'T' ELSE 'F' END
FROM OPENJSON(@json)
WITH (
id int,
name varchar(100),
description varchar(1000),
shippingFreeze bit,
receivingFreeze bit,
mouseNorovirus bit,
irradiatedFeed bit
) j;
";
using (var sqlCmd = new SqlCommand(query, sqlConn))
{
sqlCmd.Parameters.Add("@json", SqlDbType.NVarChar, -1).Value = result;
await sqlCmd.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
if(sqlConn != null)
cm.ReleaseConnection(sqlConn);
}
筆記：
sqlCmd.CommandType = CommandType.Text是不必要的。ReleaseConnection需要在一個finally- 盡管目前尚不清楚您
ConnectionManager首先使用的原因。您可能應該SqlConnection直接創建，并將其放入using - 避免
AddWithValue，而是明確指定型別和長度。 - Use
Asyncversions of code withawait. Do not call.Resultor you may deadlock.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/456858.html