C#访问SQL——三种更新记录的方法

本文转自：

https://www.cnblogs.com/joycelee/archive/2012/10/31/2747590.html

用户通过C#Windows窗体应用程序访问并更新数据库可有三种方式，下面是三种方式的主要代码：

1.拼接字符串

private void button1_Click_1(object sender, EventArgs e)
        {
            //信息检查
            if (this.CheckInfo())
            {
                //取值
                string userId = this.textBox1.Text.Trim();
                string userName = this.textBox2.Text.Trim();
                //新建连接对象
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=(local);Initial Catalog=student;Integrated Security=SSPI";
                //拼接命令字符串
                string updateQuery = "update student set sname='" + userName + "'"+"where sno='" + userId+"'";
                //新建命令对象
                SqlCommand cmd = new SqlCommand(updateQuery, conn);
                conn.Open();
                //保存执行结果
                int RecordsAffected = cmd.ExecuteNonQuery();
                conn.Close();
                //提示结果
                Alert("更新数据数为" + RecordsAffected.ToString());
            }
        }

　　2.使用参数

private void button2_Click(object sender, EventArgs e)
        {
            //取值
            string userId = this.textBox1.Text.Trim();
            string userName = this.textBox2.Text.Trim();
            //新建连接对象
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=(local);Initial Catalog=Student;Integrated Security=SSPI";
            //拼接命令字符串
            string updateQuery = "update student set sname=@userName where sno=@userId";
            //新建命令对象
            SqlCommand cmd = new SqlCommand(updateQuery, conn);
            //添加参数
            cmd.Parameters.Add(new SqlParameter("@userName", userName));
            cmd.Parameters.Add(new SqlParameter("@userId", userId));
            conn.Open();
            //保存执行结果
            int RecordsAffected = cmd.ExecuteNonQuery();
            conn.Close();
            Alert("更新数据数为" + RecordsAffected);
        }

　　3.使用存储过程

private void button3_Click(object sender, EventArgs e)
        {
            //信息检查
            if (this.CheckInfo())
            {
                //取值
                string userId = this.textBox1.Text.Trim();
                string userName = this.textBox2.Text.Trim();
                //新建连接对象
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "Data Source=(local);Initial Catalog=Student;Integrated Security=SSPI";
                //新建命令对象
                SqlCommand cmd = new SqlCommand("UpdateStudentInfo", conn);
                //指定命令类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                //添加参数
                cmd.Parameters.Add(new SqlParameter("@userName", userName));
                cmd.Parameters.Add(new SqlParameter("@userId", userId));
                conn.Open();
                //保存执行结果
                int RecordsAffected = cmd.ExecuteNonQuery();
                conn.Close();
                //提示结果
                Alert("更新数据数为" + RecordsAffected);
            }

　　数据库中新建存储过程：

CREATE PROCEDURE UpdateStudentInfo
    (
    @userName  nvarchar(20), 
    @userId nvarchar(20)
    )
AS
  Update student
  set sname=@userName where sno=@userId
GO