如何将json数据从url传递到sql

Question

我从url 获取json数据

我已经在数据库中创建了相应的列

然后我试图将这些数据传递给SQL

这是我的代码

public async Task<IEnumerable<Inserch>> GetMaskmapdata()
{
string targetURI = "https://quality.data.gov.tw/dq_download_json.php?nid=116285&md5_url=53a72b2dcfdd9ecae43afda4b86089be";
HttpClient client = new HttpClient();
client.MaxResponseContentBufferSize = Int32.MaxValue;
var response = await client.GetStringAsync(targetURI);
var pharmacy = JsonConvert.DeserializeObject<List<Inserch>>(response);
SqlConnection connection = new SqlConnection(connstr);
connection.Open();
string listvalue = string.Join(",", pharmacy);
string sql = "Insert into pharmacy(ID,Name,Address,Phonenumber) values(@ID,@Name,@Address,@Phonenumber)";
SqlCommand cmd = new SqlCommand(sql, connection);
foreach (var item in pharmacy)
{
cmd.Parameters.AddWithValue("@ID", item.ID);
cmd.Parameters.AddWithValue("@Name", item.Name);
cmd.Parameters.AddWithValue("@Address", item.Address);
cmd.Parameters.AddWithValue("@Phonenumber", item.Phonenumber);

cmd.ExecuteNonQuery();
connection.Close();
}

return pharmacy;
}

这不可能是工作我做错了什么？

Answer 1

对于数据存储部分，您需要不断添加新参数。此外，您在第一次迭代后关闭连接，在第二次迭代时阻塞它。

该部分的更好解决方案(忽略从该URL获取数据(：

using (var connection = new SqlConnection(connstr))
{
connection.Open();
string sql = "Insert into pharmacy(ID,Name,Address,Phonenumber) values(@ID,@Name,@Address,@Phonenumber)";
var cmd = new SqlCommand(sql, connection);

// add parameters once (guessing at types and sizes - please correct)
var pId = cmd.Parameters.Add("@ID", System.Data.SqlDbType.Int);
var pName = cmd.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 100);
var pAddress = cmd.Parameters.Add("@Address", System.Data.SqlDbType.NVarChar, 100);
var pPhoneNumber = cmd.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.VarChar, 20);

foreach (var item in pharmacy)
{
// update the values of the parameters
pId.Value = item.ID;
pName.Value = item.Name;
pAddress.Value = item.Address;
pPhoneNumber.Value = item.PhoneNumber;
// execute the command with the latest parameter values
cmd.ExecuteNonQuery();
// do NOT close the connection, you may still need it for a second iteration
}
}
// connection is closed automatically by exiting the 'using' block

现在您仍然可以return pharmacy;。