◆◆如何传递1组SQL参数◆并在存储过程中遍历?◆◆ ◆◆
比如现在从用户注册页面传来参数:userName,Password,Email
SqlParameter[] queryParam = new SqlParameter();
现在有使用到SqlHelper类//没接触这个类的这句当没看见哈
存储过程里有一句SQL "INSERT INTO TableName(userName,[Password],Email) VALUES(@userName,@Password,@Email) ";
现在的问题是如何全部添加到queryParam里面?
添加以后传递给存储过程如何对应到相应的变量里?
问题得到解决后及时结帖给分!
[解决办法]
public void ExcuteStore(string StoreProName,OleDbParameter[] parameters,out string result)
{
OleDbCommand com1 = new OleDbCommand(StoreProName, GetDBcon());
if (com1.Connection.State == ConnectionState.Closed)
com1.Connection.Open();
com1.CommandType = CommandType.StoredProcedure;
//给com添加参数，并赋值
for (int i = 0; i < parameters.Length; i++)
{
com1.Parameters.Add(parameters[i]);
}
string temp = " ";
try
{
object obj = com1.ExecuteScalar();
if (obj != null)
temp = obj.ToString();
}
catch (Exception ee)
{
result = "操作失败！ " + ee.Message.ToString();
return;
}
finally
{
com1.Connection.Close();
}
result = "成功 "; //默认成功
//返回0表示数据操作成功
//返回1表示提交的数据有空值
//返回2表示编号有重复
//返回3表示数据操作错误
switch (temp)
{
case "0 ":
result = "成功 ";
//同时写日志
//DB.WirteLog(DB.opNO, DateTime.Now.ToString( "yyyyMMddHHmmss "), thisModNO);
break;
case "1 ":
result = "提交的数据有空值！ ";
break;
case "2 ":
result = "编号有重复！ ";
break;
case "3 ":
result = "数据操作错误！ ";
break;
} }
[解决办法]
上面方法的使用范例：
OleDbParameter[] param=new OleDbParameter[2];
param[0] = new OleDbParameter( "@name ", OleDbType.VarChar, 6);
param[1] = new OleDbParameter( "@pwd ", OleDbType.VarChar, 40);
param[0].Value = TextBox1.Text;
param[1].Value = TextBox2.Text;
string outstring;
ExcuteStore( "myprocedure ", param, out outstring);
if (outstring == "成功 ")
binding();
else
Label1.Text = outstring;
[解决办法]
1.
比如TextBox1.Text是用户的名字
TextBox2.Text是用户的密码
那么在存储过程里面
INSERT INTO TableName(userName,[Password],Email) VALUES(@userName,@Password,@Email)
如何把Parameter这组参数分别对应遍历到@userName,@Password,@Email里面呢?
--------------------------------
SqlClient 使用命名参数，他会按参数名称查找匹配，如
SqlParameter p1 = new SqlParameter( "@UserName "); 对应你的Sql语句中的 @UserName
[解决办法]
SqlParameter[] oParms = new SqlParameter[]{
new SqlParameter( "@userName ",txtuserName.text),
new SqlParameter( "@Email ",txtEmail.text),
new SqlParameter( "@password ",txtpassword.txt)};
这样..不用管顺序.....
[解决办法]
public void CreateMySqlCommand(SqlConnection myConnection,
string mySelectQuery, SqlParameter[] myParamArray) {
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
myCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City ";
myCommand.Parameters.Add(myParamArray);
for (int j=0; j <myParamArray.Length; j++)
{
myCommand.Parameters.Add(myParamArray[j]) ;
}
string myMessage = " ";
for (int i = 0; i < myCommand.Parameters.Count; i++)
{
myMessage += myCommand.Parameters[i].ToString() + "\n ";
}
MessageBox.Show(myMessage);
}
[解决办法]
[C#]
public void AddSqlParameters()
{
// ...
// create myDataSet and myDataAdapter
// ...
myDataAdapter.SelectCommand.Parameters.Add( "@CategoryName ", SqlDbType.VarChar, 80).Value = "toasters ";
myDataAdapter.SelectCommand.Parameters.Add( "@SerialNum ", SqlDbType.Int).Value = 239;
myDataAdapter.Fill(myDataSet);
}
[解决办法]
晕，自己看MSDN去
