数据库课程设计心得【2】------SqlCommand.Parameter属性

SqlCommand.Parameter属性的作用是利用参数化的方法来调用T-SQL语句或者存储过程，它相较于传统的字符串拼接的优势是明显的：（1）可以减少诸如string Strsql = "select * from CM_Users where CM_User_id='" + nid + "' and CM_User_password='" + pwd + "'";的运用，代码可读性高；（2）可以在一定程度上防范SQL注入，安全性高。

SqlCommand.Parameter属性的add()方法的作用是：添加参数到SqlParameterCollection参数集,add里面的第一个参数是要添加的参数名,第二个参数是参数的数据类型。举两个例子如下：

例1：

string strconn = "Data Source=xxx;user id=sa;pwd=;initial catalog=gltest"; SqlConnection Conn = new SqlConnection(strconn); Conn.Open(); string sql = "insert into users(name,pwd) values (@name,@pwd)"; SqlCommand cmd = new SqlCommand(sql, Conn); cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50)); cmd.Parameters.Add(new SqlParameter("@pwd", SqlDbType.NVarChar, 50)); cmd.Parameters["@name"].Value = this.TextBox1.Text; cmd.Parameters["@pwd"].Value = this.TextBox2.Text; cmd.ExecuteNonQuery(); Conn.Close();

例2：

private static void UpdateDemographics(Int32 customerID, string demoXml, string connectionString) { // Update the demographics for a store, which is stored // in an xml column. string commandText = "UPDATE Sales.Store SET Demographics = @demographics " + "WHERE CustomerID = @ID;"; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(commandText, connection); command.Parameters.Add("@ID", SqlDbType.Int); command.Parameters["@ID"].Value = customerID; // Use AddWithValue to assign Demographics. // SQL Server will implicitly convert strings into XML. command.Parameters.AddWithValue("@demographics", demoXml); try { connection.Open(); Int32 rowsAffected = command.ExecuteNonQuery(); Console.WriteLine("RowsAffected: {0}", rowsAffected); } catch (Exception ex) { Console.WriteLine(ex.Message); } } }