以“块区”的形式将 BLOB 值写入 SQL Server

原创 2004年08月11日 12:05:00

通过插入或更新带有字符串值或字节数组(取决于数据库中的字段类型)的字段,可以将二进制大对象 (BLOB) 写入数据库。但是,BLOB 可能相当大,因此在作为单个值写入时可能要使用大量的系统内存,从而降低应用程序的性能。

该示例向 Northwind 数据库的 Employees 表添加了一个包含员工图像的新记录,该图像就是一个 BLOB。该示例使用 SQL Server 的 UPDATETEXT 函数将新添加的员工的图像以指定大小的块区写入 Photo 字段。

UPDATETEXT 函数要求一个指向所更新的 BLOB 字段的指针。在此示例中,在添加新员工的记录后,将调用 SQL Server TEXTPTR 函数以返回一个指向新记录的 Photo 字段的指针。返回的指针值将作为输出参数传递回去。示例中的代码保留此指针,并在追加数据块区时将其传递到 UPDATETEXT。

用于插入新员工记录和保留指向 Photo 字段的指针的 Transact-SQL 将在下例中显示(其中 @Identity@Pointer 被标识为 SqlCommand 的输出参数)。

INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) 
  Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity

请注意,在 Photo 字段中插入了初始值 0x0(空)。这确保可以检索到新插入记录的 Photo 字段的指针值。但是,空值不会影响追加的数据块区。

在保留指向新插入记录中的 Photo 字段的指针后,示例可以接着使用 SQL Server 的 UPDATETEXT 函数向 BLOB 字段追加数据块区。UPDATETEXT 函数接受以下对象作为输入:字段标识符 (Employees.Photo)、指向 BLOB 字段的指针、表示 BLOB 中写入当前块区的位置的偏移量值,以及要追加的数据块区。以下代码示例显示 UPDATETEXT 函数的语法(其中 @Pointer@Offset @Bytes 被标识为 SqlCommand 的输入参数)。

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes

偏移量值由内存缓冲区的大小确定,而该大小取决于应用程序的需要。大的缓冲区写入 BLOB 的速度较快,但会使用更多的系统内存。此示例使用的缓冲区相当小,只有 128 字节。为第一个数据块区分配的偏移量值为 0,然后偏移量值按每个连续块区的缓冲区大小递增。

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Import namespace="System.IO"%>
<SCRIPT LANGUAGE="c#" RUNAT="SERVER">

  public void Page_Load(Object sender, EventArgs e)
  {
    DateTime hireDate = DateTime.Now;
    int newID  = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "C://93.jpg");
    Response.Write("New Employee added. EmployeeID = " + newID);
  }

  public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
  {
    SqlConnection nwindConn = new SqlConnection("server=(local);database=NorthWind;uid=sa;pwd=;");

    SqlCommand addEmp  = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
      "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
      "SELECT @Identity = SCOPE_IDENTITY();" +
      "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn);

    addEmp.Parameters.Add("@LastName",  SqlDbType.NVarChar, 20).Value = lastName;
    addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
    addEmp.Parameters.Add("@Title",     SqlDbType.NVarChar, 30).Value = title;
    addEmp.Parameters.Add("@HireDate",  SqlDbType.DateTime).Value     = hireDate;
    addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value          = reportsTo;

    SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
    idParm.Direction = ParameterDirection.Output;
    SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Direction = ParameterDirection.Output;

    nwindConn.Open();

    addEmp.ExecuteNonQuery();

    int newEmpID = (int)idParm.Value;

    StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn);

    nwindConn.Close();

    return newEmpID;
  }

  public static void StorePhoto(string fileName, byte[] pointer,  SqlConnection nwindConn)
  {
    int bufferLen = 128;  // The size of the "chunks" of the image.

    SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn);

    SqlParameter ptrParm  = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
    ptrParm.Value = pointer;
    SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen);
    SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int);
    offsetParm.Value = 0;

    //''''''''''''''''''''''''''''''''''
    // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
    // Tune bufferLen for best performance. Larger values write faster, but
    // use more system resources.

    FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    BinaryReader br = new BinaryReader(fs);

    byte[] buffer = br.ReadBytes(bufferLen);
    int offset_ctr = 0;

    while (buffer.Length > 0)
    {
      photoParm.Value = buffer;
      appendToPhoto.ExecuteNonQuery();
      offset_ctr += bufferLen;
      offsetParm.Value = offset_ctr;
      buffer = br.ReadBytes(bufferLen);
    }

    br.Close();
    fs.Close();
  }

</SCRIPT>


<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Import namespace="System.IO"%>

? public void Page_Load(Object sender, EventArgs e)
? {
??? DateTime hireDate = DateTime.Now;
??? int newID? = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "C://93.jpg");
??? Response.Write("New Employee added. EmployeeID = " + newID);
? }

? public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
? {
??? SqlConnection nwindConn = new SqlConnection("server=(local);database=NorthWind;uid=sa;pwd=;");

??? SqlCommand addEmp? = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
????? "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
????? "SELECT @Identity = SCOPE_IDENTITY();" +
????? "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn);

??? addEmp.Parameters.Add("@LastName",? SqlDbType.NVarChar, 20).Value = lastName;
??? addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
??? addEmp.Parameters.Add("@Title",???? SqlDbType.NVarChar, 30).Value = title;
??? addEmp.Parameters.Add("@HireDate",? SqlDbType.DateTime).Value???? = hireDate;
??? addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value????????? = reportsTo;

??? SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
??? idParm.Direction = ParameterDirection.Output;
??? SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
??? ptrParm.Direction = ParameterDirection.Output;

??? nwindConn.Open();

??? addEmp.ExecuteNonQuery();

??? int newEmpID = (int)idParm.Value;

??? StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn);

??? nwindConn.Close();

??? return newEmpID;
? }

? public static void StorePhoto(string fileName, byte[] pointer,? SqlConnection nwindConn)
? {
??? int bufferLen = 128;? // The size of the "chunks" of the image.

??? SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn);

??? SqlParameter ptrParm? = appendToPhoto.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
??? ptrParm.Value = pointer;
??? SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes", SqlDbType.Image, bufferLen);
??? SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset", SqlDbType.Int);
??? offsetParm.Value = 0;

??? //''''''''''''''''''''''''''''''''''
??? // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
??? // Tune bufferLen for best performance. Larger values write faster, but
??? // use more system resources.

??? FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
??? BinaryReader br = new BinaryReader(fs);

??? byte[] buffer = br.ReadBytes(bufferLen);
??? int offset_ctr = 0;

??? while (buffer.Length > 0)
??? {
????? photoParm.Value = buffer;
????? appendToPhoto.ExecuteNonQuery();
????? offset_ctr += bufferLen;
????? offsetParm.Value = offset_ctr;
????? buffer = br.ReadBytes(bufferLen);
??? }

??? br.Close();
??? fs.Close();
? }

显示部分:

<% @Page Language="C#" %>
<% @Import Namespace="System.IO" %><% @Page Language="C#" %>
<% @Import Namespace="System.IO" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
private void Page_Load(Object sender, EventArgs e) {
string sql="SELECT * FROM Employees WHERE EmployeeID = '" + Request.QueryString["ID"] + "'";
SqlConnection connection = new SqlConnection("Server=(local);uid=sa;pwd=;Database=Northwind");
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if(dr.Read()){
 Response.Clear();
 Response.AddHeader("Content-Type","images/jpeg");
 Response.BinaryWrite((byte[])dr["Photo"]);
}
dr.Close();
connection.Close();
}
</script>

<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>

SqlServer之代码块相关

一、go语句 Go语句是SqlServer中用来表示当前代码块结束提交并确认结果的语句。 Go语句不能和其他Sql命令卸载同一行上! 定义的局部变量作用域局限在定义它的代码快中,如:在go语句前...
  • shunxin520
  • shunxin520
  • 2016年03月04日 08:53
  • 577

SQL SERVER插入数据操作

准备工作: 我需要一张User表,这张表有几个字段,还有一个自增长的数字id,表结构如下: 这是一张比现实应用中简单的多的用户表,UserID是自增长字段。 开始执行一个最简单的INSER...
  • wangzhifuhaha
  • wangzhifuhaha
  • 2014年01月15日 11:29
  • 1003

Oracle 11g PL/SQL程序块,存储图片等BLOB字段类型的列PL/

Oracle 11g PL/SQL程序块,存储图片等BLOB字段类型的列 例如:把一个人的图片放入PERSON表的photo列中 一、在Oracle中创建,一个存储图片的目录逻辑,其与硬盘中的图片存储...
  • m0_37934173
  • m0_37934173
  • 2017年05月15日 14:29
  • 652

mysql的blob读取和sqlserver的image读取方式 blob和image兼容问题及varbinary(max)

blob和image兼容问题 mysql的blob读取和sqlserver的image读取方式 遇到的问题案例:mysql数据库和sqlserver数据库(blob和image)兼容工作...
  • chx10051413
  • chx10051413
  • 2013年11月21日 15:36
  • 2633

pl/sql代码块

PL/SQL代码块包括3个部分 声明(Declaration) 可执行命令(Executeable Command) 异常处理(Exception Hading)    典型结构      de...
  • yanliling0909
  • yanliling0909
  • 2013年02月06日 15:35
  • 3588

CLOB、BLOB , CLOB与BLOB的区别

原文链接:http://blog.csdn.net/magister_feng/article/details/7825892 CLOB 定义   数据库中的一种保存文件所使用的类型。 ...
  • u013063153
  • u013063153
  • 2016年11月16日 11:02
  • 725

上传图片至服务器,写入到数据库Blob字段中,以及从数据库读取Blob信息(iframe父子页面传值)(2)

首先说写入oracle的Blob字段 上一文章的实现方法没有贴出来,如下: TempUserService.java [html] view plaincopy packa...
  • Shirley_John_Thomas
  • Shirley_John_Thomas
  • 2016年09月11日 19:34
  • 751

C语言以数据块的形式读写文件

fgets() 有局限性,每次最多只能从文件中读取一行内容,因为 fgets 遇到换行符就结束读取。如果希望读取多行内容,需要使用 fread 函数;相应地写入函数为 fwrite。 fre...
  • superywf
  • superywf
  • 2017年06月07日 22:20
  • 270

安装数据库注意要点

关于数据库安装网上有很多教程,这里不赘述,主要分享安装过程中的弯路,方便以后查询以及分享。 一、需要确认已经安装了.NET Framework4 , 以管理员的身份运行 二、产品密钥 企业E...
  • sinat_27011897
  • sinat_27011897
  • 2016年04月28日 10:47
  • 1285

SQL优化实战:临时表+分批提交+按日结存

结存数据太慢怎么办? (1)第1次优化 一开始,客户经理说客户,不想在多个查询中看数据,想在一个表中看所有的数据,也就是说需要把原有的多个查询的sql合并为一个,但是实际上合并后,每个月的原始...
  • yupeigu
  • yupeigu
  • 2016年05月09日 13:53
  • 2597
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:以“块区”的形式将 BLOB 值写入 SQL Server
举报原因:
原因补充:

(最多只允许输入30个字)