参数化查询是访问数据库时,在需要填入数值或数据的地方,使用参数 来给值。 在参数化SQL中参数名的格式跟其在存储过程中生命存储过程参数一致,例如在Oracle中存储过程参数一律以”:”开头,在MS SQL Server中存储过程参数一律以”@”开头,而在MySQL中存储过程(MySQL从5.0以后版本支持存储过程)参数一律以“?”开头,所以在参数化SQL语句中参数名有些不一样 。
asp使用access数据库参数化例子:
ASP环境下的参数化查询主要由Connection对象和Command对象完 成。 var cmd = Server.CreateObject(“ADODB.Command”); cmd.ActiveConnection = conn; cmd.CommandType = 1; cmd.CommandText = “SELECT TOP 1 * FROM [User] WHERE UserName = ? AND Password = ?“; cmd.Parameters.Append(cmd.CreateParameter(”@UserName”, 200, 1, 20, “user01″)); cmd.Parameters.Append(cmd.CreateParameter(”@Password”, 200, 1, 16, “123456″)); var rs = cmd.Execute(); Response.Write(rs(”UserId”).value); rs.Close(); conn.Close()
在.NET程序中使用参数化查询 SqlCommand cmd = new SqlCommand(“SELECT TOP 1 * FROM [User] WHERE UserName = @UserName AND Password = @Password“); cmd.Connection = conn; cmd.Parameters.AddWithValue(”UserName”, “user01″); cmd.Parameters.AddWithValue(”Password”, “123456″); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); int userId = reader.GetInt32(0); reader.Close(); conn.Close(); MySQL的参数格式与SQL Server有点区别,是以“?”加上参数名。 MySqlCommand cmd = new MySqlCommand(“SELECT * FROM `User` WHERE UserName = ?UserName AND Password = ?Password LIMIT 1″); cmd.Connection = conn; cmd.Parameters.AddWithValue(”UserName”, “user01″); cmd.Parameters.AddWithValue(”Password”, “123456″); MySqlDataReader reader = cmd.ExecuteReader(); reader.Read(); int userId = reader.GetInt32(0); reader.Close(); conn.Close(); 在Oracle中使用参数化SQL是以“:”加上参数名。 OracleCommand cmd = new OracleCommand(“SELECT * FROM `User` WHERE UserName = :UserName AND Password = :Password LIMIT 1″); cmd.Connection = conn; cmd.Parameters.AddWithValue(”UserName”, “user01″); cmd.Parameters.AddWithValue(”Password”, “123456″); OracleDataReader reader = cmd.ExecuteReader(); reader.Read(); int userId = reader.GetInt32(0); reader.Close(); conn.Close(); |