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 [email=Name=@Name]Name=@Name[/email]";
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"];
// 这里省略处理查询结果的代码
}
}
}
}
更多其他信息可登录http://www.qingniao.info

路过。。。。。然后在飘回来




























祛痘产品哪种好