将动态 JSON 数据插入数据库

Question

我想将 JSON 数据插入 sql 服务器表中，我通过 URL 检索我的 json 数据，我已经在按钮单击事件上编写了我的插入逻辑，它通过明显的错误{"Must declare the scalar variable "@Name"."}因为我没有提供任何参数来插入数据，因为我无法传递这些参数硬编码它们

private string HttpContent(string url)
{
WebRequest objRequest = System.Net.HttpWebRequest.Create(url);
StreamReader sr = new StreamReader(objRequest.GetResponse().GetResponseStream());
string result = sr.ReadToEnd();
sr.Close();
return result;
}
protected void btnsave_Click(object sender, EventArgs e)
{
var conString = ConfigurationManager.ConnectionStrings["SQLDBConnection"];
string strConnString = conString.ConnectionString;
SqlConnection conn = new SqlConnection(strConnString);
SqlCommand com;
string data = HttpContent(txturl.Text);
JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
var table = jsSerializer.Deserialize<dynamic>(data);
conn.Open();
com = new SqlCommand("INSERT INTO tbl_insert_json (Name, Mobile, Email) VALUES(@Name, @Mobile, @Email)", conn);
com.CommandType = CommandType.Text;
int refId = com.ExecuteNonQuery();
conn.Close();
if (refId > 0)
{
Response.Write("{"response":{"status":"success","msg":"Details Saved Successfully.."}}");
}
else
{
Response.Write("{"response":{"status":"fail","msg":"oops!! something went wrong"}}");
}
}

现在我的问题是我应该更改什么或如何将我的数据插入 sql 服务器表

我用于每个循环来映射每一行，然后插入但对我不起作用 请分享将数据插入表格的任何想法或方法？ 我在本节中遇到问题com = new SqlCommand("INSERT INTO tbl_insert_json (Name, Mobile, Email) VALUES(@Name, @Mobile, @Email)", conn);如何将这些参数插入表格

Answer 1

必须在 SqlCommand 对象的实例行之后添加参数以将参数替换为 json 值，例如：

com = new SqlCommand("INSERT INTO tbl_insert_json (Name, Mobile, Email) VALUES(@Name, @Mobile, @Email)", conn);
com.Parameters.Add("@Name", SqlDbType.NChar);
com.Parameters["@Name"].Value = "NameDataJson";
com.Parameters.Add("@Mobile", SqlDbType.NChar);
com.Parameters["@Mobile"].Value = "MobileDataJson";
com.Parameters.Add("@Email", SqlDbType.NChar);
com.Parameters["@Email"].Value = "EmailDataJson";