存储过程

 大虾 13:19:49
CREATE PROCEDURE UserData
(
@index int,
@name nvarchar(50),
@remark nvarchar(100)
)
AS
if exists(select * from 用户信息 where 编号=@index)
 update 用户信息 set 名称=@name,备注=@remark where 编号=@index
else
INSERT INTO 用户信息 (名称,备注)
VALUES (@name,@remark)

GO
@index int,
@name nvarchar(50),
@remark nvarchar(100)
大虾 13:22:44
CREATE PROCEDURE UserData
(
@index int,
@name nvarchar(50),
@remark nvarchar(100)
)
AS
if exists(select * from 用户信息 where 编号=@index)
 update 用户信息 set 名称=@name,备注=@remark where 编号=@index
else
INSERT INTO 用户信息 (名称,备注)
VALUES (@name,@remark)

GO
其中
@index int,
@name nvarchar(50),
@remark nvarchar(100) 为参数,这个存储过程即可以添加,也可以修改,通过if exists(select * from 用户信息 where 编号=@index)
主键是否变化判断是添加,还是修改

 

程序中：

private void btnSave_Click(object sender, EventArgs e)
        {
            if (txtUserName.Text.Equals(""))//如果文件框txtUserName的文本为空的话,则弹出提示
            {
                MessageBox.Show("用户名称不能为空!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            SqlConnection conn = new SqlConnection(SQLHelperClass.GetSettings("SQL"));
            SqlCommand scmd = new SqlCommand("UserData", conn);//"UserData"是存储过程名字,它的功能就是保存和修改用户资料

            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();//打开连接
                }
                scmd.CommandType = CommandType.StoredProcedure;//此句的意思是执行的是存储过程
                scmd.Parameters.Add(new SqlParameter("@index", SqlDbType.Int)).Value = index;//编号
                scmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50)).Value = txtUserName.Text.Trim();//将用户名称附给存储过程里的参数@name
                scmd.Parameters.Add(new SqlParameter("@remark", SqlDbType.NVarChar, 100)).Value = txtRemark.Text.Trim();//将备注附给存储过程里的参数@remark
                scmd.ExecuteNonQuery();//执行此存储过程
                txtUserName.Text = "";//清空
                txtRemark.Text = "";
                MessageBox.Show("保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException expSQL)
            {
                MessageBox.Show(expSQL.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }