在ADO.NET中使用参数化SQL语句的大同小异
本文共 7183 字，大约阅读时间需要 23 分钟。
在ADO.NET中经常需要跟各种数据库打交道，在不实用存储过程的情况下，使用参数化SQL语句一定程度上可以防止SQL注入，同时对一些较难赋值的字段（如在SQL Server中Image字段，在Oracle中Clob字段等）使用参数化SQL语句很容易就能赋值，所以本人经常在ADO.NET中使用参数化SQL语句，近几年来陆续跟SQL Server/Oracle/ MySQL/Access打交道，积累了一些心得，现在整理出来供大家参考。
我们假设数据可的结构如下图（设置的数据库为Oracle10g）： 
它在 SQL Server 中的创建语句是：
create table S_Admin ( UserName varchar ( 60 ) not null , Password varchar ( 60 ) not null , Remark varchar ( 50 ) null , Mail varchar ( 120 ) not null , AddDate datetime null default GETDATE (), LoginDate datetime null default GETDATE (), LoginIP varchar ( 50 ) null , Active smallint null default 1 , LoginCount int null default 1 , Power int null default 0 , Departid int null default 0 , constraint PK_S_ADMIN primary key nonclustered (UserId) ) go
下面假设数据库的主键都采用了数据库的本地化技术解决了（例如在Access、SQL Server和MySQL中采用自增字段，在Oracle中使用了sequence结合触发器），假如在Oracle中向表中插入一记录的代码如下：
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OracleClient; /// <summary> /// 在Oracle中使用参数化SQL的例子 /// 代码编写：周公 /// 日期：2008-3-19 /// 发表网址： /// </summary> public class OracleUtil { public OracleUtil() { } public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power) { string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(:UserName,:Password,:Remark,:Mail,:DepartId,:Power) " ; OracleConnection connection = new OracleConnection(); connection.ConnectionString = "" ; // 此处设置链接字符串 OracleCommand command = new OracleCommand(sql, connection); command.Parameters.Add( " :UserName " , OracleType.NVarChar, 60 ).Value = userName; command.Parameters.Add( " :Password " , OracleType.NVarChar, 60 ).Value = password; command.Parameters.Add( " :Remark " , OracleType.NVarChar, 60 ).Value = remark; command.Parameters.Add( " :Mail " , OracleType.NVarChar, 60 ).Value = mail; command.Parameters.Add( " :DepartId " , OracleType.Int32, 4 ).Value = departId; command.Parameters.Add( " :Power " , OracleType.Int32, 4 ).Value = power; connection.Open(); int rowsAffected = command.ExecuteNonQuery(); connection.Close(); command.Dispose(); return rowsAffected > 0 ; } }
在MySQL中增加同样一条记录的代码如下（需要到MySQL官方网站下载.net驱动程序）：
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using MySql.Data; using MySql.Data.MySqlClient; /// <summary> /// 在MySQL中使用参数化SQL的例子 /// 代码编写：周公 /// 日期：2008-3-19 /// 发表网址： /// </summary> public class MySqlUtil { public MySqlUtil() { } public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power) { string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?UserName,?Password,?Remark,?Mail,?DepartId,?Power) " ; MySqlConnection connection = new MySqlConnection(); connection.ConnectionString = "" ; // 此处设置链接字符串 MySqlCommand command = new MySqlCommand(sql, connection); command.Parameters.Add( "? UserName " , MySqlDbType.VarChar, 60 ).Value = userName; command.Parameters.Add( "? Password " , MySqlDbType.VarChar, 60 ).Value = password; command.Parameters.Add( "? Remark " , MySqlDbType.VarChar, 60 ).Value = remark; command.Parameters.Add( "? Mail " , MySqlDbType.VarChar, 60 ).Value = mail; command.Parameters.Add( "? DepartId " , MySqlDbType.Int32, 4 ).Value = departId; command.Parameters.Add( "? Power " , MySqlDbType.Int32, 4 ).Value = power; connection.Open(); int rowsAffected = command.ExecuteNonQuery(); connection.Close(); command.Dispose(); return rowsAffected > 0 ; } }
在SQL Server中增加同样一条记录的代码如下：
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; /// <summary> /// 在SQL Server中使用参数化SQL的例子 /// 代码编写：周公 /// 日期：2008-3-19 /// 发表网址： /// </summary> public class SqlUtil { public SqlUtil() { } public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power) { string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(@UserName,@Password,@Remark,@Mail,@DepartId,@Power) " ; SqlConnection connection = new SqlConnection(); connection.ConnectionString = "" ; // 此处设置链接字符串 SqlCommand command = new SqlCommand(sql, connection); command.Parameters.Add( " @UserName " ,SqlDbType.NVarChar, 60 ).Value = userName; command.Parameters.Add( " @Password " , SqlDbType.NVarChar, 60 ).Value = password; command.Parameters.Add( " @Remark " , SqlDbType.NVarChar, 60 ).Value = remark; command.Parameters.Add( " @Mail " , SqlDbType.NVarChar, 60 ).Value = mail; command.Parameters.Add( " @DepartId " , SqlDbType.Int, 4 ).Value = departId; command.Parameters.Add( " @Power " , SqlDbType.Int, 4 ).Value = power; connection.Open(); int rowsAffected = command.ExecuteNonQuery(); connection.Close(); command.Dispose(); return rowsAffected > 0 ; } }
在Access中增加同样一条记录的代码如下：
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb; /// <summary> /// 在Access中使用参数化SQL的例子 /// 代码编写：周公 /// 日期：2008-3-19 /// 发表网址： /// </summary> public class AccessUtil { public AccessUtil() { } public bool InsertAdmin( string userName, string password, string remark, string mail, int departId, int power) { string sql = " insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?,?,?,?,?,?) " ; OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = "" ; // 此处设置链接字符串 // 注意下面参数的顺序一定要按照sql语句中的插入的列的顺序赋值，否则一定会报异常 OleDbCommand command = new OleDbCommand(sql, connection); command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = userName; command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = password; command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = remark; command.Parameters.Add( " ? " , OleDbType.LongVarWChar, 60 ).Value = mail; command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = departId; command.Parameters.Add( " ? " , OleDbType.Integer, 4 ).Value = power; connection.Open(); int rowsAffected = command.ExecuteNonQuery(); connection.Close(); command.Dispose(); return rowsAffected > 0 ; } }
需要说明的是，除了Access之外，操作其它数据库可以不必要按照参数在SQL语句中出现的顺序添加进去一样可以正确执行，但是在Access中一定按照插入的列的顺序添加参数，因为“OLE DB.NET Framework 数据提供程序使用标有问号 (?) 的定位参数，而不使用命名参数（MSDN）”,所以给添加参数和赋值一定要按照列的顺序。
通过上面的例子，基本上可以总结出一个规律：在参数化 SQL 中参数名的格式跟其在存储过程中生命存储过程参数一致，例如在 Oracle 中存储过程参数一律以 ”:” 开头，在 MS SQL Server 中存储过程参数一律以 ”@” 开头，而在 MySQL 中存储过程（ MySQL 从 5.0 以后版本支持存储过程）参数一律以“？”开头，所以在参数化 SQL 语句中参数名有些不一样（记得在 csdn 上有朋友提到过不知道为什么 MySQL 中参数化 SQL 语句中要用“？ ” 而不是和 SQL Server 一样使用 ”@” ），如果那位朋友看过本文，我想他就会解开这个疑虑了。
本文转自周金桥51CTO博客，原文链接： http://blog.51cto.com/zhoufoxcn/167031，如需转载请自行联系原作者
你可能感兴趣的文章
与Bob McWhirter的问答：WildFly Swarm更名为Thorntail项目
查看>>
Java 11正式发布，新特性解读
查看>>
《Fit for Purpose》作者访谈录
查看>>
与Brian Goetz聊Java的数据类
查看>>
Emoji 上的 Swift：换一种视角来理解 Swift 高阶函数
查看>>
中国最愿意为程序员花钱的公司有哪些？
查看>>
区块链将颠覆游戏业，游戏内商品未来也可带出游戏、自由交易
查看>>
Linux/Mac安装oh-my-zsh后不执行~/.bash_profile、~/.bashrc解决办法
查看>>
安卓开发_深入理解广播机制
查看>>
技术大咖云集，GIAC 2017全球互联网架构大会圆满落幕
查看>>
php取整函数ceil，floor，round，intval函数的区别
查看>>
安卓应用安全指南 4.2.2 创建/使用广播接收器 规则书
查看>>
Stratus Technologies与海得控制升级长期战略合作，助力中国工业自动化与工业物联网解决方案...
查看>>
新建的SQL Server账号无法使用跟踪功能
查看>>
远程线程注入引出的问题
查看>>
「镁客·请讲」NXROBO林天麟：我们分三步走，首先要做的就是打通机器人行业的产业链...
查看>>
这款创意相机，能让盲人更真实的感触身边世界
查看>>
hdu 1285 确定比赛名次(很典型的拓扑排序)
查看>>
学习iOS【3】数组、词典和集合
查看>>
8Python全栈之路系列之Django Cookie 与Sessi
查看>>