如何使用storeprocedure保存数据 [英] how to save data using storeprocedure

问题描述

使用storeprocedure保存数据两个下拉列表的问题

problem on saving data two dropdownlist using storeprocedure

protected void btnsave_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[usp_Emp]";
cmd.Connection = con;
SqlParameter paremp = new SqlParameter("@EmpName", SqlDbType.NVarChar, 50);
paremp.Value = txtEmp.Text;
cmd.Parameters.Add(paremp);
SqlParameter parejob = new SqlParameter("@Job", SqlDbType.NVarChar, 50);
// if (jobdropdwn.SelectedIndex > 0)
 
parejob.Value = jobdropdwn.SelectedIndex.ToString();
//else
// parejob.Value = "";
cmd.Parameters.Add(parejob);
SqlParameter paresal = new SqlParameter("@Salary", SqlDbType.Money);
paresal.Value = txtSalary.Text;
cmd.Parameters.Add(paresal);
SqlParameter pardptname = new SqlParameter("@DeptNo", SqlDbType.NVarChar, 50);
// if (detptdropdwn.SelectedIndex > 0)
pardptname.Value = detptdropdwn.SelectedIndex.ToString();
//else
// pardptname.Value = "";
SqlParameter pardrp = new SqlParameter("@flag", 1);
cmd.Parameters.Add(pardrp);
 
cmd.Parameters.Add(pardptname);
// SqlParameter partwo = new SqlParameter("@flag", 1);
SqlParameter parusrname = new SqlParameter("@Username", SqlDbType.NVarChar, 50);
parusrname.Value = txtUsername.Text;
cmd.Parameters.Add(parusrname);
SqlParameter parepaswd = new SqlParameter("@Password1", SqlDbType.NVarChar, 50);
parepaswd.Value = txtPassword.Text;
cmd.Parameters.Add(parepaswd);


SqlParameter pareusrtype = new SqlParameter("@UserType", SqlDbType.NVarChar, 50);
// if (usertypdropdwn.SelectedIndex > 0)
pareusrtype.Value = usertypdropdwn.SelectedItem.ToString();
//else
// pareusrtype.Value = "";
SqlParameter parusr = new SqlParameter("@flag", 2);
cmd.Parameters.Add(parusr);
cmd.Parameters.Add(pareusrtype);
}

推荐答案

你已经创建了命令并添加了参数 - 顺便说一下，祝贺你避免SQL注入！ :) - 但你没有执行命令。



你需要在方法的底部添加以下行：

You've created the command and added the parameters - congratulations on avoiding SQL injection, by the way! :) - but you've not executed the command.

You need to add the following lines to the bottom of your method:

bool closeConnection = false;
if (con.ConnectionState == ConnectionState.Closed)
{
   con.Open();
   closeConnection = true;
}

try
{
    cmd.ExecuteNonQuery();
}
finally
{
    if (closeConnection)
    {
        con.Close();
    }
}

（如果您声明 SqlConnection 本地并使用块将其包装在中。）



正如Ryan所说，你可以还可以使用简化参数的添加。 AddWithValue 方法 [ ^ ]。



整理你的方法的版本看起来像这样：



(This would be simpler if you declared your SqlConnection locally and wrapped it in a using block.)

As Ryan mentioned, you could also simplify adding the parameters by using the AddWithValue method[^].

A tidied-up version of your method would look something like this:

protected void btnsave_Click(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection(ConnectionStringHere))
    using (SqlCommand cmd = new SqlCommand("dbo.usp_Emp", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpName", txtEmp.Text);
        cmd.Parameters.AddWithValue("@Job", jobdropdown.SelectedIndex.ToString());
        cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
        cmd.Parameters.AddWithValue("@DeptNo", deptdropdown.SelectedIndex.ToString());
        cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
        cmd.Parameters.AddWithValue("@Password1", txtPassword.Text);
        cmd.Parameters.AddWithValue("@UserType", usertypdropdown.SelectedItem.ToString());

        // TODO: You've added the same parameter twice - only one value will be used.
        // cmd.Parameters.AddWithValue("@flag", 1);
        cmd.Parameters.AddWithValue("@flag", 2);

        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

这篇关于如何使用storeprocedure保存数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！