参数化的SQL查询 [英] Parameterize SQL query
问题描述
有关参数在SQL中使用C#的许多职位,但我还是失去了一些东西。我没有收到错误信息,但没有数据插入。缺什么?我有一个名为FNAME,LNAME,地址,城市,州和邮编文本框。
私人无效enter_button_Click(对象发件人,EventArgs的)
{
字符串第一个,最后,阿迪,city1,统计,活泼;
第一= fname.Text;
的SqlParameter firstparam;
firstparam =新的SqlParameter();
firstparam.ParameterName =@First;
firstparam.Value =第一;
最后= lname.Text;
的SqlParameter lastparam;
lastparam =新的SqlParameter();
lastparam.ParameterName =@Last;
lastparam.Value =最后;
阿迪= address.Text;
的SqlParameter addressparam;
addressparam =新的SqlParameter();
addressparam.ParameterName =@addy;
addressparam.Value =阿迪;
city1 = city.Text;
的SqlParameter cityparam;
cityparam =新的SqlParameter();
cityparam.ParameterName =@ city1;
cityparam.Value = city1;
STAT = state.Text;
的SqlParameter stateparam;
stateparam =新的SqlParameter();
stateparam.ParameterName =@stat;
stateparam.Value =统计;
比比= zip.Text;
的SqlParameter zipparam;
zipparam =新的SqlParameter();
zipparam.ParameterName =@zippy;
zipparam.Value =活泼;
尝试
{
验证(FNAME);
验证(LNAME);
验证(市);
验证(州);
}
赶上(例外前)
{
抛出新的异常(ex.ToString(),前);
}
尝试
{
exValidate(地址);
}
赶上(例外EX1)
{
抛出新的异常(ex1.ToString(),EX1);
}
尝试
{
numValidate(ZIP);
}
赶上(例外EX2)
{
抛出新的异常(ex2.ToString(),EX2);
}
字符串连接=数据源= TX-MANAGER;初始目录=联系人;集成安全性=真;
VAR的SqlString =的String.Format(插入联系人([首页] [尾页],[地址],[市],[状态],[ZIP)值{0},{1},{2},{ 3},{4},{5}),@First,@Last,@addy,@ city1,@stat,@zippy);
SqlConnection的康恩=新的SqlConnection(连接);
SqlCommand的通讯=新的SqlCommand();
comm.CommandText =的SqlString;
尝试
{
conn.Open();
//的SqlTransaction反式= conn.BeginTransaction();
//comm.Transaction =反;
comm.Parameters.Add(@第一,SqlDbType.Text);
comm.Parameters.Add(@最后一个,SqlDbType.Text);
comm.Parameters.Add(@阿迪,SqlDbType.Text);
comm.Parameters.Add(@ city1,SqlDbType.Text);
comm.Parameters.Add(@ STAT,SqlDbType.Text);
comm.Parameters.Add(@活泼,SqlDbType.SmallInt);
}
赶上(例外commex)
{
抛出新的异常(commex.ToString(),commex);
}
conn.Close();
}
于是我改变这一点,仍然没有任何反应。
字符串连接=数据源= TX-MANAGER;初始目录=联系人;集成安全性=真;
VAR的SqlString =的String.Format(插入联系人([首页] [尾页],[地址],[市],[状态],[ZIP)值{0},{1},{2},{ 3},{4},{5}),@First,@Last,@addy,@ city1,@stat,@zippy);
SqlConnection的康恩=新的SqlConnection(连接);
SqlCommand的通讯= conn.CreateCommand();
comm.CommandText =的SqlString;
尝试
{
conn.Open();
//的SqlTransaction反式= conn.BeginTransaction();
//comm.Transaction =反;
comm.Parameters.AddWithValue(@第一,第一);
comm.Parameters.AddWithValue(@最后的,最后一次);
comm.Parameters.AddWithValue(@阿迪,阿迪);
comm.Parameters.AddWithValue(@ city1,city1);
comm.Parameters.AddWithValue(@统计,统计);
comm.Parameters.AddWithValue(@活泼,比比);
comm.ExecuteNonQuery();
您忘了要执行的命令;)
编辑:你也没有用,你在方法开始时创建的参数
。 ...
尝试
{
conn.Open();
//的SqlTransaction反式= conn.BeginTransaction();
//comm.Transaction =反;
comm.Parameters.Add(firstparam);
comm.Parameters.Add(lastparam);
comm.Parameters.Add(addressparam);
comm.Parameters.Add(cityparam);
comm.Parameters.Add(stateparam);
comm.Parameters.Add(zipparam);
//这是你忘了什么:
comm.ExecuteNonQuery();
}
...
顺便说一句,不要做这样的事情:
赶上(例外EX1)
{
抛出新的异常(ex1.ToString(),EX1);
}
这是没有用的,它只是增加了异常的一个新的水平,而不添加任何用处。只是让异常泡沫堆栈,直到达到它实际一些有用的catch块。
Many posts about Parameters in SQL with C# but I am still missing something. I am not getting an error message but no data is inserted. What is missing? I have text boxes named fname, lname, address, city, state and zip.
private void enter_button_Click(object sender, EventArgs e)
{
string first, last, addy, city1, stat, zippy;
first = fname.Text;
SqlParameter firstparam;
firstparam = new SqlParameter();
firstparam.ParameterName = "@first";
firstparam.Value = first;
last = lname.Text;
SqlParameter lastparam;
lastparam = new SqlParameter();
lastparam.ParameterName = "@last";
lastparam.Value = last;
addy = address.Text;
SqlParameter addressparam;
addressparam = new SqlParameter();
addressparam.ParameterName = "@addy";
addressparam.Value = addy;
city1 = city.Text;
SqlParameter cityparam;
cityparam = new SqlParameter();
cityparam.ParameterName = "@city1";
cityparam.Value = city1;
stat = state.Text;
SqlParameter stateparam;
stateparam = new SqlParameter();
stateparam.ParameterName = "@stat";
stateparam.Value = stat;
zippy = zip.Text;
SqlParameter zipparam;
zipparam = new SqlParameter();
zipparam.ParameterName = "@zippy";
zipparam.Value = zippy;
try
{
Validate(fname);
Validate(lname);
Validate(city);
Validate(state);
}
catch (Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
try
{
exValidate(address);
}
catch (Exception ex1)
{
throw new Exception(ex1.ToString(), ex1);
}
try
{
numValidate(zip);
}
catch (Exception ex2)
{
throw new Exception(ex2.ToString(), ex2);
}
string connection = "Data Source=TX-MANAGER;Initial Catalog=Contacts;Integrated Security=True";
var sqlstring = string.Format("INSERT INTO Contacts ([First] ,[Last] ,[Address] ,[City] ,[State],[ZIP]) VALUES {0}, {1}, {2}, {3}, {4}, {5})", @first, @last, @addy, @city1, @stat, @zippy);
SqlConnection conn = new SqlConnection(connection);
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlstring;
try
{
conn.Open();
//SqlTransaction trans = conn.BeginTransaction();
//comm.Transaction = trans;
comm.Parameters.Add("@first", SqlDbType.Text);
comm.Parameters.Add("@last", SqlDbType.Text);
comm.Parameters.Add("@addy", SqlDbType.Text);
comm.Parameters.Add("@city1", SqlDbType.Text);
comm.Parameters.Add("@stat", SqlDbType.Text);
comm.Parameters.Add("@zippy", SqlDbType.SmallInt);
}
catch (Exception commex)
{
throw new Exception(commex.ToString(), commex);
}
conn.Close();
}
So I changed to this and still nothing happens.
string connection = "Data Source=TX-MANAGER;Initial Catalog=Contacts;Integrated Security=True";
var sqlstring = string.Format("INSERT INTO Contacts ([First] ,[Last] ,[Address] ,[City] ,[State],[ZIP]) VALUES {0}, {1}, {2}, {3}, {4}, {5})", @first, @last, @addy, @city1, @stat, @zippy);
SqlConnection conn = new SqlConnection(connection);
SqlCommand comm = conn.CreateCommand();
comm.CommandText = sqlstring;
try
{
conn.Open();
//SqlTransaction trans = conn.BeginTransaction();
//comm.Transaction = trans;
comm.Parameters.AddWithValue("@first", first);
comm.Parameters.AddWithValue("@last", last);
comm.Parameters.AddWithValue("@addy", addy);
comm.Parameters.AddWithValue("@city1", city1);
comm.Parameters.AddWithValue("@stat", stat);
comm.Parameters.AddWithValue("@zippy", zippy);
comm.ExecuteNonQuery();
You forgot to execute the command ;)
EDIT: you also didn't use the parameters that you created at the beginning of the method.
...
try
{
conn.Open();
//SqlTransaction trans = conn.BeginTransaction();
//comm.Transaction = trans;
comm.Parameters.Add(firstparam);
comm.Parameters.Add(lastparam);
comm.Parameters.Add(addressparam);
comm.Parameters.Add(cityparam);
comm.Parameters.Add(stateparam);
comm.Parameters.Add(zipparam);
// This is what you forgot:
comm.ExecuteNonQuery();
}
...
BTW, don't do things like that:
catch (Exception ex1)
{
throw new Exception(ex1.ToString(), ex1);
}
It's useless, it just adds a new level of exception without adding anything useful. Just let the exception bubble up the stack until it reaches a catch block that actually does something useful.
这篇关于参数化的SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!