c# - 我尝试在 C# 中运行 sql 命令但出现错误
问题描述
我正在尝试通过在我的 .net 项目中使用 insert 命令将值插入到 db
命令:
string[] dist_name = new string[] { "Yuma", "Wickenburg", "Verde", "Prescott", "Pinal", "Payson", "Paradise Valley/Eastern", "Navajo", "Mountain", "La Paz", "Goodyear/Western", "Deer Valley/Central","Coconino","Cochise","Buckeye" };
int[] cust_count = new int[] { int.Parse(TextBox11.Text), int.Parse(TextBox11.Text), int.Parse(TextBox12.Text), int.Parse(TextBox13.Text), int.Parse(TextBox14.Text), int.Parse(TextBox15.Text), int.Parse(TextBox16.Text), int.Parse(TextBox17.Text), int.Parse(TextBox18.Text), int.Parse(TextBox19.Text), int.Parse(TextBox20.Text), int.Parse(TextBox21.Text), int.Parse(TextBox22.Text), int.Parse(TextBox23.Text), int.Parse(TextBox24.Text), int.Parse(TextBox25.Text) };
string[] dist_code = new string[] { "YU","WG","VR","VP","PL","GP","PV","NN","GM","YP","YP","GY","DV","NC","CO","WB" };
SqlConnection con = new SqlConnection(_connectionString);
con.Open();
for (int i = 0; i < dist_name.Length; i++) {
SqlCommand sc1 = new SqlCommand("insert into tempdivcustcount(dist_name,dist_code,cust_count,year) Values("+dist_name[i]+","+dist_code[i]+","+cust_count[i]+","+int.Parse(thresholdYear.Text)+") ", con);
object o1 = sc1.ExecuteNonQuery();
}
但是当我运行它时,我得到了这个错误:“无效的列名“YUMA”“无效的列名“YU”
“Yuma”和“Yu”将作为记录插入到我的数据库中请让我知道如何修复它。感谢您的帮助
解决方案
哎呀!这看起来很容易受到 sql 注入问题的影响。尝试这个:
// Not a fan of matching arrays by index, but that's an issue for another day.
string[] dist_name = new string[] { "Yuma", "Wickenburg", "Verde", "Prescott", "Pinal", "Payson", "Paradise Valley/Eastern", "Navajo", "Mountain", "La Paz", "Goodyear/Western", "Deer Valley/Central","Coconino","Cochise","Buckeye" };
int[] cust_count = new int[] { int.Parse(TextBox11.Text), int.Parse(TextBox11.Text), int.Parse(TextBox12.Text), int.Parse(TextBox13.Text), int.Parse(TextBox14.Text), int.Parse(TextBox15.Text), int.Parse(TextBox16.Text), int.Parse(TextBox17.Text), int.Parse(TextBox18.Text), int.Parse(TextBox19.Text), int.Parse(TextBox20.Text), int.Parse(TextBox21.Text), int.Parse(TextBox22.Text), int.Parse(TextBox23.Text), int.Parse(TextBox24.Text), int.Parse(TextBox25.Text) };
string[] dist_code = new string[] { "YU","WG","VR","VP","PL","GP","PV","NN","GM","YP","YP","GY","DV","NC","CO","WB" };
// Define SQL outside the loop with named parameter placeholders. This could even be const.
string sql = "INSERT INTO tempdivcustcount (dist_name,dist_code,cust_count,year) VALUES ( @DistName, @DistCode, @CustCount, @Year);";
// using blocks make sure the items are disposed, **even if an exception is thrown**
using (var con = new SqlConnection(_connectionString))
using (var sc1 = new SqlCommand(sql, con))
{
// I have to guess at types/lengths here.
// You should use the actual types and lengths to match the database columns
sc1.Paramerers.Add("@DistName", SqlDbType.NVarChar, 50);
sc1.Parameters.Add("@DistCode", SqlDbType.VarChar, 10);
sc1.Parameters.Add("@CustCount", SqlDbType.Int);
sc1.Parameters.Add("@Year", SqlDbType.Int).Value = int.Parse(thresholdYear.Text);
con.Open();
for (int i = 0; i < dist_name.Length; i++)
{
// Setting parameter values this way avoids issues with things like out-of-place apostrophe characters
sc1.Parameters["@DistName"].Value = dist_name[i];
sc1.Parameters["@DistCode"].Value = dist_code[i];
sc1.Parameters["@CustCount"].Value = cust_count[i];
sc1.ExecuteNonQuery();
}
} // using block takes care of closing the connection
推荐阅读
- jmeter - --start-offset --end-offset 在 JMeterPluginsCMD.bat 命令中的用法
- javascript - 在选择列表项或单击组件外部时反应关闭下拉菜单
- android - For how long can a Worker in Android keep retrying doing a request?
- unity3d - 从相机到鼠标 Unity 3D 绘制光线?
- r - 在 R 中预测曲线上的值
- spring - 为什么我不能将间隔解析为 JPA 中的参数?
- node.js - 如何使用 Node Fetch 将来自 HTTP 请求的数据保存在变量中?
- amazon-web-services - 创建 IAM 角色时出错:MalformedPolicyDocument:已禁止字段资源
- jenkins - Jenkins - 如何为大文件发送多部分表单数据
- python - pymongo.errors.ConfigurationError: query() got an unexpected keyword argument 'lifetime'