我试图在我的C#应用程序中插入SQL数据库。
我阅读了一些文档,并提出了我认为可行的方法。实际上,发生的情况是,当用户输入数据并按下“提交”按钮时,应用程序冻结了片刻,然后给我一个“ SqlException”,并提到了一些无法连接的信息。
我不确定我是否正确使用了连接字符串,所以我正在寻求帮助。
这些是我用来构建查询并建立连接的方法:
private void btn_Submit_Click(object sender, EventArgs e)
{
if (isValidData())
{
//MessageBox.Show("Valid", "All Entries Were Valid!");
//CONVERT FORM VALUES AND STORE IN VARIABLES TO SEND TO MYSQL QUERY
DateTime saleTime = saleDatePicker.Value;
Decimal price = Convert.ToDecimal(txt_Price.Text);
string customerName = txt_CustomerName.Text;
string customerPhone = txt_CustomerPhone.Text;
string description = rTxt_Description.Text;
//Build Query string
string query = "INSERT into SALES VALUES ('" + saleTime + "','" +
price + "','" + customerName + "','" + customerPhone + "','" +
description + "');";
insertValues(query);
}
}
private void insertValues(string q)
{
SqlConnection sqlConnection1 = new SqlConnection("Server=host;Database=dbname;User Id=username;Password=password;");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = q;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.
sqlConnection1.Close();
}
我不确定您的连接字符串,但是看到您的问题已用MySql标记,那么您需要使用不同的类来与MySql“对话”。现在,您正在使用的服务器用于与Microsoft Sql Server一起工作。
您需要更改SqlConnection
,SqlCommand
并SqlDataReader
到名为MySQL的对手MySqlConnection
,MySqlCommand
,MySqlDataReader
。在下载并安装MySql NET / Connector之后,可以使用这些类,然后设置对MySql.Data.Dll的引用并将其添加using MySql.Data.MySqlClient;
到您的项目中
关于MySql的connectionstring,您还需要遵循规则并使用此站点中说明的关键字
这些是使程序可以运行的基本步骤,但是您在这里遇到了很大的问题。它在sql命令中称为字符串串联,此习惯直接导致Sql Injection漏洞。
您需要将代码更改为以下内容:
private void btn_Submit_Click(object sender, EventArgs e)
{
if (isValidData())
{
//CONVERT FORM VALUES AND STORE IN VARIABLES TO SEND TO MYSQL QUERY
DateTime saleTime = saleDatePicker.Value;
Decimal price = Convert.ToDecimal(txt_Price.Text);
string customerName = txt_CustomerName.Text;
string customerPhone = txt_CustomerPhone.Text;
string description = rTxt_Description.Text;
// Create the query using parameter placeholders, not the actual stringized values....
string query = "INSERT into SALES VALUES (@stime, @price, @cname,@cphone,@cdesc)";
// Create a list of parameters with the actual values with the placeholders names
// Pay attention to the Size value for string parameters, you need to change it
// accordingly to your fields size on the database table.
List<MySqlParameter> prms = new List<MySqlParameter>()
{
new MySqlParameter {ParameterName="@stime", MySqlDbType=MySqlDbType.DateTime, Value = saleTime },
new MySqlParameter {ParameterName="@price", MySqlDbType=MySqlDbType.Decimal, Value = price },
new MySqlParameter {ParameterName="@cname", MySqlDbType=MySqlDbType.VarChar, Value = customerName, Size = 150 },
new MySqlParameter {ParameterName="@cphone", MySqlDbType=MySqlDbType.VarChar, Value = customerPhone , Size = 150 },
new MySqlParameter {ParameterName="@desc", MySqlDbType=MySqlDbType.VarChar, Value = description , Size = 150 }
};
// Pass query and parameters to the insertion method.
// get the return value. if it is more than zero you are ok..
int result = insertValues(query, prms);
// if(result > 0)
// .... insertion ok ....
}
}
private int insertValues(string q, List<MySqlParameter> parameters)
{
using(MySqlConnection con = new MySqlConnection(....))
using(MySqlCommand cmd = new MySqlCommand(q, con))
{
con.Open();
cmd.Parameters.AddRange(parameters.ToArray());
int rowsInserted = cmd.ExecuteNonQuery();
return rowsInserted;
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句