SQL参数化查询讲座 (五)

在.NET中访问SQL Server数据库有两种方式，一种是使用专为.NET开发的驱动程序，另一种是用SQL Server的OLEDB驱动程序（在.NET中也可以用ODBC驱动程序，不过微软不推荐）。下面分别演示。先是专为.NET开发的驱动程序。

 

// 连接数据库

using (SqlConnection connection = new SqlConnection(

    "Data Source=(local);Initial Catalog=school;Integrated Security=SSPI"))

{

    connection.Open();

 

    // 先清空数据表

    using (SqlCommand deleteCommand = connection.CreateCommand())

    {

        deleteCommand.CommandType = CommandType.Text;

        deleteCommand.CommandText = "DELETE FROM Students";

        deleteCommand.ExecuteNonQuery();

    }

 

    // 插入数据

    using (SqlCommand insertCommand = connection.CreateCommand())

    {

        insertCommand.CommandType = CommandType.Text;

        insertCommand.CommandText = "INSERT INTO Students(Id,Name,Photo) VALUES(@Id,@Name,@Photo)";

        insertCommand.Prepare();

       

        // 创建参数

        SqlParameter idParameter = insertCommand.CreateParameter();

        idParameter.ParameterName = "Id";

        idParameter.Direction = ParameterDirection.Input;

        idParameter.SqlDbType = SqlDbType.Int;

        insertCommand.Parameters.Add(idParameter);

        SqlParameter nameParameter = insertCommand.CreateParameter();

        nameParameter.ParameterName = "Name";

        nameParameter.Direction = ParameterDirection.Input;

        nameParameter.SqlDbType = SqlDbType.VarChar;

        nameParameter.Size = 10;

        insertCommand.Parameters.Add(nameParameter);

        SqlParameter photoParameter = insertCommand.CreateParameter();

        photoParameter.ParameterName = "Photo";

        photoParameter.Direction = ParameterDirection.Input;

        photoParameter.SqlDbType = SqlDbType.VarBinary;

        photoParameter.Size = -1;

        insertCommand.Parameters.Add(photoParameter);

 

        // 插入第1条记录

        idParameter.Value = 1;

        nameParameter.Value = "LiMing";

        photoParameter.Value = File.ReadAllBytes("d://1.jpg");

        insertCommand.ExecuteNonQuery();

        // 插入第2条记录

        idParameter.Value = 2;

        nameParameter.Value = "WangLing";

        photoParameter.Value = File.ReadAllBytes("d://2.jpg");

        insertCommand.ExecuteNonQuery();

    }

 

    // 查询数据

    using (SqlCommand selectCommand = connection.CreateCommand())

    {

        selectCommand.CommandType = CommandType.Text;

        selectCommand.CommandText = "SELECT Photo FROM Students WHERE Name=@Name";

        selectCommand.Prepare();

       

        // 创建参数

        SqlParameter nameParameter = selectCommand.CreateParameter();

        nameParameter.ParameterName = "Name";

        nameParameter.Direction = ParameterDirection.Input;

        nameParameter.DbType = DbType.String;

        nameParameter.Size = 10;

        selectCommand.Parameters.Add(nameParameter);

 

        // 指定参数后执行

        nameParameter.Value = "Liming";

        using (SqlDataReader reader = selectCommand.ExecuteReader())

        {

            while (reader.Read())

            {

                byte[] photo = (byte[])reader["Photo"];

                // 这里省略处理查询结果的代码

            }

        }

    }

 

}