ADO.Net 浅析(六)-使用参数和存储过程

一 在查询中使用DbParemeter

我们先来看一个简单的查询

SELECT COUNT(*) FROM UserInfo WHERE UserName = '{0}' AND Password = '{0}'

 理想的情况下

SELECT COUNT(*) FROM UserInfo WHERE UserName = 'MyUserName' AND Password = 'MyPassword'

可是如果输入NonUser' OR 1 = 1 --  会是怎样的呢?

 SELECT COUNT(*) FROM UserInfo WHERE UserName = 'NonUser' OR 1 = 1 --'AND Password = 'MyPassword'

 

 

关于性能方面

在前面已经提到过ExecuteReader 使用 Transact-SQL sp_executesql 系统存储过程调用命令来返回DbDatareader

 

sp_executesql的使用如下

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

--设置查询语句
SET @SQLString =
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
设置参数定义
SET @ParmDefinition = N'@level tinyint'

设置参数值,并进行查询
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable
重新进行查询
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

两次查询中由于@SQLString没有改变,所以查询很好的得到了重用,无需重新解析T-SQL.提高了性能.

如果使用拼接字符串的方式,则每次都需要重新解析.


现在我们来看一个简单的使用参数的实例

        public void UseParameter()
        {
            SqlConnection conn = new SqlConnection(baseconnstr);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = "SELECT top 5 * FROM t_Customer_BaseInfo where CustomerSex=@CustomerSex";

            SqlParameter param = new SqlParameter("@CustomerSex", "男");
            
            cmd.Parameters.Add(param);

            SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            while (rd.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
    rd[0], rd[1]));
            }
            rd.Close();
 
        }

 

 下面是使用存储过程的示例

CREATE PROCEDURE [dbo].[uspGetPersonNameById]
    -- Add the parameters for the stored procedure here
    @BusinessEntityID int,
    @UserFullName varchar(50) out
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
select @UserFullName= (FirstName+' '+MiddleName+' '+LastName) from Person.Person where BusinessEntityID=@BusinessEntityID
END

GO

 

 

        public void UseParameter()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdWEntities"].ConnectionString);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = "uspGetPersonNameById";
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter("@BusinessEntityID", "1");
            
            cmd.Parameters.Add(param);



            //最后一个参数设置参数长度,如果比实际存储过程返回值的参数小,会自动被截断,如果不设置,或设置为0,则会报错
            param =new SqlParameter("@UserFullName",SqlDbType.VarChar,1);




            param.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();

            conn.Close();

            string fullName = (string)cmd.Parameters["@UserFullName"].Value.ToString();
            Console.WriteLine(fullName);
 
        }