I get json data from url

and I have created the corresponding column in the database

then I tried to pass this data to SQL

here's my code

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;
}

It can't be work What did i do wrong?

CodePudding user response：

For your data storage part, you keep adding new parameters. Also you close the connection after the first iteration, blocking it for a second one.

A better solution for that part (ignoring getting the data from that 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

and now you can still return pharmacy;.