SQL Server：存储图像和BLOB文件

SQL Server：存储图像和BLOB文件(一)
 
作者：佚名 文章来源：Onegreen.Net 浏览次数：2559 更新时间：2008-5-25 23:16:09 
　　什么是BLOB

　　首先，我们来比较两种文件类型ASCII和二进制。存储在SQL Server中的大部分数据值都是由ASCII(美国信息交换标准代码)字符组成。简单说来，ASCII字符就是能够在计算机键盘上找到的字母、数字和符号。我们可以通过记事本等文本编辑器对只包含了ASCII字符的文件进行修改且不会对文件造成任何破坏。而二进制文件，不仅包含了ASCII字符，还包含了键盘上没有的特殊控制字符和字节组合。如果你用记事本打开一个MP3文件，并删除其中的一些字符企图使歌曲变短，很可能会破坏掉整个文件，导致该MP3不能再正常播放，这是因为记事本只能用来编辑ASCII字符，而无法正确解析或创建二进制位。其他二进制数据包括图像和可执行文件汇编程序。BLOB顾名思义就是大型的二进制文件。

　　为什么要在SQL Server存储BLOB

　　有人喜欢在SQL server数据库里存储二进制对象，也有人反对这样做，正所谓公说公有理，婆说婆有理。我们应当两方面都考虑周全。举一个看起来比较真实的例子，假设我们在一个典型的销售企业当数据库管理员。这个企业通常有很多待售的产品线，包含不同类别的产品，而产品线以下的水平就是单个产品或部件，我们把他们称之为构件。对于每个构件，都提供了标准的详细目录清单，如价格、成本、现有数量、供应商等等。此外，很多构件可能还有用来描述该构件销售手册或宣传册，这些宣传册往往以PDF、幻灯片或某种类型的图像等电子文档形式出现。处理这些电子文档的一个方法就是把他们都放入一个文件服务器，并为每个构件创建一个目录。这个一开始可能管用，不过当客户或雇员想要设计一个可以调用这些文档的应用程序，只需要输入搜索参数就可以让数据库返回匹配的销售手册时，问题就来了。例如，输入“显示所有售价低于100元的蓝色构件的文档”。这时，就会涉及到将应用程序与数据库绑定的问题了。因此，在这个系列文章的后半部分，我们会创建一个连接到SQL Server的Visual Studio应用程序来检索构件销售手册。

　　文件存储位置

　　第一个需要解决的问题是要将这些电子宣传小册子存放在哪里。我们可以用一个应用程序来存储导向该文档的文件系统路径信息，例如“e:saleswidgeta-picture.jpg”，将这些信息存放在varchar类型的数据列里，而把实际的文档保留在文件系统;也可以将实际的图像文件直接放在一个二进制或图像类型的数据列里。回答下面几个重要问题将有助于确定最佳的选择方案：

　　· 性能：这些二进制对象对性能要求是不是很高，如视频流?如果是的话，保留在文件系统会比用二进制流文件存储要好。

　　· 大小：需要检索的二进制对象是不是很大?有没有超过1MB?如果对象很大，那么用文件系统来显示和读取该对象的效率往往比从SQL Server显示和读取的效率更高。如果二进制对象比较小，也就是说每个构件只有很小的图像文件，那么将它们存储在SQL server内则绰绰有余。

　　· 安全：对这些二进制对象的访问是否要高度关注安全性。如果这些对象存储在SQL Server中，那么可以通过常规的数据库访问方式来管理其安全性。如果文件存储在文件系统，那么就需要另外设置更安全的替代管理办法了。

　　· 客户访问：客户对数据库的访问通过什么方式，是通过ODBC还是Native SQL Drive?对于大型视频流，以ODBC方式连接的客户可能出现连接超时和连接失败的问题。

　　· 碎片：如果要对这些二进制对象进行频繁的修改操作，或者这些文件很大，那么文件系统处理碎片的能力可能比SQL Server更好。

　　· 事务：是否需要进行事务控制，如果需要，那么SQL Server可能更好，因为其具有内置解决方案。

　　如果您想更深入探讨这个关于将BLOB文件存储在数据库还是文件系统的问题，以及想知道上面提到的1MB大小标准的划分有什么依据，可以通过以下链接，参阅微软的技术文献：http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45 .

　　数据类型

　　回到第一个例子，我们将要创建一个应用程序来存储

 各个产品的相关图片。因为这些文件都很小，所以我们会选择将它们直接存储在SQL Server里。在SQL Server 2000中，有两种不同的数据类型选择类群用来存储这些文件类型：二进制和图像(Image)。二进制类群包括三种不同的数据类型。第一种就是标准二进制数据类型Binary，但其要求固定的数据长度。对于上面的例子，由于我们的图片大小不等，所以我们需要使用varbinary数据类型，这里的“var”就是变量的意思。Varbinary数据类型的最大长度为8000字节。从SQL Server 2005开始，“varbinary(max)”也包含在二进制数据类型的类群当中了。其关键词MAX就表示大小不限。如果SQL Server的版本是2005以前的，而数据长度又超过8000字节的话，就可以使用图像数据类型进行存储了。这种数据类型的文件大小可变，最大可存储2GB的文件。虽然图像数据类型也包含在SQL Server 2005和2008中，但在这些版本下不应当使用这种类型。微软称，还保留图像数据类型是未来向下兼容之需，在将来某个时候就会将其舍弃。因此，本例中将使用二进制数据类型，这个类群的三种类型扼要重述如下：

　　· Binary：文件大小固定，最大长度可达8000字节;

　　· VarBinary(n): 文件大小可变，最大长度可达8000字节，(n指明最大文件长度);

　　· VarBianry(max): 文件大小可变，不限最大长度。

　　总结

　　在本系列的下一篇文章当中，我们会继续探讨在SQL Server存储BLOB文件的问题，通过创建 Visual Studio应用程序来对SQL Server二进制数据类型进行读写操作。VarBinary(MAX)数据类型的使用技巧将在介绍SQL Server 2008新的文件流数据类型选项后进行探讨。

SQL Server：存储图像和BLOB文件(二)
 
作者：佚名 文章来源：Onegreen.Net 浏览次数：4371 更新时间：2008-5-25 23:16:02 
　　在本系列的第一篇文章中，我们讨论了二进制和BLOB(二进制大型对象)的定义，以及在什么情况下应当将这些对象存储在SQL Server里。将图像和二进制数据存储在SQL Server内部的原因包括方便安全性管理、客户访问之便和事务控制。本文将着眼于varBinary(MAX)数据类型，在SQL Server 2005和2008中都有这种数据类型，其扩展说明符(MAX)意味着不设置文件大小的上限。“var”指文件大小是可变，不像标准二进制数据类型那样是个固定值。在SQL Server的联机丛书提供了三种二进制数据类型的使用条件说明：当表列的所有输入数据大小为固定值(且长度小于8000字节)时，使用标准二进制Binary数据类型;当表列的输入数据长度有很大差别(且长度均小于8000字节)时，使用varBinary数据类型;当表列输入数据长度超过8000字节时，使用varBinary(max) 数据类型。对于SQL Server 2000，则使用图像(Image)数据类型。不过请记住，微软已经声明图像数据类型是为了向下兼容才保留下来的，而且可能会在未来的版本中停止使用。

　　例表

　　首先，我们要创建一个测试数据库和表来装载我们的图片。使用以下的T-SQL语句：

　　USEmaster;
　　GO
　　CREATEDATABASETest;
　　GO
　　USETest;
　　GO
　　CREATETABLEBLOBTest
　　(
　　TestIDintIDENTITY(1,1),
　　BLOBNamevarChar(50),
　　BLOBDatavarBinary(MAX)
　　);

　　在本例中，列的名称为 BLOBData，不过您可以使用任何标准的SQL名称为列命名。二进制数据类型对命名没有任何限制。

　　如果您运行的是SQL Server 2005和2008，找一张小图片，并执行下面的T-SQL语句插入该图像来测试数据库。该语句在SQL Server 2000中无效，因为只有varBinary(MAX)数据类型才能成为插入目标：

INSERT INTO BLOBTest
(BLOBName, BLOBData)
SELECT 'First test file',
　BulkColumn FROM OPENROWSET(
　Bulk 'D:test.jpg', SINGLE_BLOB) AS BLOB

　　这里用“D:test.jpg”代入作为文件的文件系统路径。OPENROWSET语句允许SQL从外部数据源提供程序来存取数据。Bulk是特别为OPENROWSET插入文件和图像而设计的数据源提供程序。想了解详细的信息，可以查阅SQL Server的联机丛书。执行选择表的操作将产生如下显示的一条记录：

　　SELECT*
　　FROMBLOBTest

 

　　SELECT语句会核实数据是否已经插入，不过这还不是在SQL中查看该图片的方法。为了查看图片，我们需要创建一个小的Visual Studio应用程序。

　　二进制数据读取

　　本例将从SQL读取已存储的图像，并利用Visual Studio在网页上展示图像。创建一个没有代码分离的新页面。代码分离的例子稍后再做介绍。

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%
　　string sConn = @"server=.; database=Test; Integrated Security=True";
　　SqlConnection objConn = new SqlConnection(sConn);
　　objConn.Open();
　　string sTSQL = "SELECT BLOBData FROM BLOBTest";
　　SqlCommand objCmd = new SqlCommand(sTSQL, objConn);
　　objCmd.CommandType = CommandType.Text;
　　SqlDataReader dr = objCmd.ExecuteReader();
　　dr.Read();
　　Response.BinaryWrite((byte[])dr["BLOBData"]);
　　objConn.Close();
%>
　　<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
　　<script runat="server">
　　
</script>
　　<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
　　<title>Untitled Page</title>
</head>
<body>
　　<form id="form1" runat="server">
　　<div>
　　</div>
　　</form>
</body>
</html>
　　该网页将使用sConn变量来查找数据库。“Integatred Security = True”将指定使用的Windows安全机制。Server名称应当是您的服务器名，如果SQL server和网页服务器再同一台本地机上，就可以使用点来代替。SQL语句只是获取图像，不过在后面的例子中，我们还要查询文件名，并构建一个更真实化的应用程序。指令类型设置为“test”的意思是传递一条SQL语句(相对于存储过程的名称)。

　　下面要用的语句：

　　SqlCommand objCmd = new SqlCommand(sTSQL, objConn);

　　创建一个将SQL语句和连接绑定在一起的指令对象，然后在以下的语句中执行该指令对象：

　　SqlDataReader dr = objCmd.ExecuteReader();

　　此语句为该指令对象返回指定并设置了一个新的DataReader。dr.Read语句加载了第一条结果(也是本例中唯一的结果)。如果我们不能保证能够成功读取数据，那么可以使用IF语句来进行测试，在本系列的下一篇文章会举这样一个例子。

　　现在DataReader读取了图像二进制数据，并使用Response.BinaryWrite来将数据流入到指定网页。注意网页的html是空的，没有任何内容。BinaryWrite不需要任何的html对象。事实上，网页上的任何html都不会显示出来，例如我们可以在div之间粘贴一段字符串：

<div>
　Enjoy this image
　　</div>

　　但当打开运行网页时，只显示了图像，并没有显示该字符串：　

 

　　在我们例子里的数据库中，在SQL Server中还存放了相关的其他数据，例如文件名，我们想要让这些数据和该图像一起在页面中显示出来。因为Binary Write会覆盖页面上的任何html，所以我们需要另外想一个解决办法。将该页面存储为GetPicture.aspx。创建一个新页面，并在上面放置一个图像控件，将图像的网址指向GetPicture页，如下所示：

<%@ Page Language="C#" %>
　　<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transiti
onal//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
　　<script runat="server">
　　</script>
　　<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
　　<title>Untitled Page</title>
</head>
<body>
　　<form id="form1" runat="server">
　　<div>
　　　　Here is the image <asp:Image ID="Image1" runat="server" ImageUrl="GetPicture.aspx" />
　　</div>
　　</form>
</body>
</html>
　　因为存储在SQL Server的二进制流不能够直接传递给图像空间，所以间接的传递包含该二进制流的网页。

　　总结

　　二进制数据可以存储在SQL Server，并通过一个网页应用程序进行查询。在本系列的下一篇文章中，我们将创建一个网页应用程序来把图像插入到SQL当中。另外，还会对本文中的例子进行扩展，包括将图片的文件名传递会应用程序，通过传递一个特定的图像名来检索，并对这些应用程序进行转换以使用存储过程和代码分离页面。

SQL Server：存储图像和BLOB文件(三)
 
作者：佚名 文章来源：Onegreen.Net 浏览次数：1546 更新时间：2008-5-25 23:15:55 
　　内容提要

　　在本系列的前面的文章SQL Server：存储图像和BLOB文件(一)和SQL Server：存储图像和BLOB文件(二)中，对BLOB进行了定义，并阐明了在什么情况下将这些文件存储在SQL Server内部而不存在文件系统中。此外，还通过举例介绍了VARBINARIES的几种不同数据类型，包括本文要集中讨论了VARBINARY(MAX)数据类型。本文第一部分的代码例子将图像加载到数据库，然后通过使用aspx页面对该图像进行检索和显示。本文将创建一个基于网络的应用程序用来将图像插入到SQL Server里。另外，将对前面两篇文章的例子进行扩展和改进。

　　SQL测试数据库

　　我们先来创建一个测试数据库和表用来存储图像，T-SQL脚本如下：

　　USEmaster;
　　GO
　　CREATEDATABASEBLOBTest3;
　　GO
　　USEBLOBTest3;
　　GO
　　CREATETABLEBLOBFromWeb
　　(
　　BLOBDatavarbinary(max)
　　);
　　GO

　　以上脚本创建了一个包含一个表的数据库，该表只有一列。该测试列“BLOBData”以MAX为关键词，因此无论多大的二进制文件，它都可以存储。接下来，我们创建一个可以存储过程，网页可以使用该存储程序来上传图片：

　　CREATEPROCEDUREWebUp
　　(
　　@FileDatavarbinary(max)
　　)
　　AS
　　INSERTINTOBLOBFromWeb
　　(BLOBData)
　　VALUES
　　(@FileData);

　　该存储过程会将变量——图像“@FileData”传递到SQL Server。接下来的例子都将会使用存储过程而不再使用SQL语句了。使用存储过程的好处很多，例如可以增强安全性、防止SQL注入攻击、方便使用、提高运行性能等等。

　　从网页将图像插入到SQL Server

　　本例将使用Microsoft Visual Studio 2008来创建一个网页表单，该表单会要求终端用户浏览并上传文件到SQL Server。这个应用程序也可以在Visual Studio 2005中使用。首先，创建一个新网站和一个代码分离的aspx页面，如下图所示，拖动FileUpload和按钮控制到该表单上。

　　切换到该页面的源视图，并按下文所示更改默认<form>标签：<form id="form1" runat="server" enctype="multipart/form-data">

　　
　　“enctype”指定了表单数据如何编码。切换回到设计视图，双击按钮创建OnClick事件，这会将该代码分离的页面调出来，如下显示：　

 

　　在该页面顶部，添加以下using语句：

　　usingSystem.Data.Sql;
　　usingSystem.Data.SqlClient;
　　Andalsoastatementforthefilesystem:
　　usingSystem.IO;

　　添加如下代码到Button1_Click事件中：

　　stringsConn=@"server=.;database=BLOBTest3;IntegratedSecurity=True";
　　SqlConnectionobjConn=newSqlConnection(sConn);
　　objConn.Open();

 SqlCommandobjCmd=newSqlCommand("WebUp",objConn);
　　objCmd.CommandType=CommandType.StoredProcedure;
　　SqlParameterparamFileData=objCmd.Parameters.Add("@FileData",SqlDbType.VarBinary);
　　paramFileData.Direction=ParameterDirection.Input;
　　byte[]bImage=newbyte[FileUpload1.PostedFile.ContentLength];
　　StreamobjStream=FileUpload1.PostedFile.InputStream;
　　objStream.Read(bImage,0,FileUpload1.PostedFile.ContentLength);
　　paramFileData.Value=bImage;
　　objCmd.ExecuteNonQuery();
　　objConn.Close();

　　语句的第一行设置了与SQL Server的连接。“Integrated Security”表示使用Windows安全登录，而不是SQL ID登录。接下来打开到数据库的连接。启动该数据库之后，接下来的两行语句用来创建一个SQL指令对象：

　　SqlCommandobjCmd=newSqlCommand("WebUp",objConn);
　　objCmd.CommandType=CommandType.StoredProcedure;

　　“WebUp”是之前创建的存储过程的名称。将该存储过程绑定到开放的数据库连接，然后会告诉SQL指令对象，“WebUp”是一个存储过程，而不是T-SQL文本语句。该存储过程要求传递一个单一的参数，也就是要插入的图像。参数创建如下面的语句。“paramFileData”名称可以取任何名称，没什么特别要注意的，不过对于Parameters.Add，“@FileData”必须和存储过程中的参数名称匹配。

　　SqlParameter paramFileData = objCmd.Parameters.Add("@FileData", SqlDbType.VarBinary);

　　有了参数以后，就可以指定上传方向了：

　　paramFileData.Direction = ParameterDirection.Input;

　　任何传递到SQL Server的数据都是Input的指令类型，输出的数据将是ParameterDirection.Output。

　　我们设想的理想状态是，现在FileUpload控件应当将图像直接传递给存储过程参数，不过这是行不通的。相反，我们要创建一个可输入图像大小的字节数组：

　　byte[] bImage = new byte[FileUpload1.PostedFile.ContentLength];

　　紧接着，创建一个流对象指向图像内容：

　　Stream objStream = FileUpload1.PostedFile.InputStream;

　　最后，流将图像传递给上面的字节数组：

　　objStream.Read(bImage, 0, FileUpload1.PostedFile.ContentLength);

　　现在，图像数据就可以传递进入SQL参数，并运行了：

　　paramFileData.Value=bImage;
　　objCmd.ExecuteNonQuery();

　　运行该应用程序

　　在浏览器中查看该网页，点击浏览按钮。这时会出现一个如下所示的文件选择窗口：
浏览文件夹选择好文件后点击完成按钮，就会执行上面创建的SQL存储过程。在SQL Server内部查看运行结果，会显示有二进制数据，但不会显示数据的真实内容：　　

 

　　因为SQL Server本身不带有BLOB浏览器，所以我们要自动动手制作一个类似于前一篇文章所示的浏览器，不同的是这个浏览器通过存储过程驱动的。

　　浏览器

　　创建一个简单的可以选择图像数据的存储过程，T-SQL代码如下：

　　CREATEPROCEDUREBLOBViewer
　　AS
　　SELECTBLOBData
　　FROMBLOBFromWeb

　　接下来，制作一个代码分离的aspx网页。在代码分离页面添加SQL Server的使用语句：

　　usingSystem.Data.Sql;
　　usingSystem.Data.SqlClient;

　　在Page_load的部分，使用下面的代码执行刚刚创建的存储过程：

　　stringsConn=@"server=.;database=BLOBTest3;IntegratedSecurity=True";
　　SqlConnectionobjConn=newSqlConnection(sConn);
　　objConn.Open();
　　SqlCommandobjCmd=newSqlCommand("BLOBViewer",objConn);
　　objCmd.CommandType=CommandType.StoredProcedure;
　　SqlDataReaderdr=objCmd.ExecuteReader();
　　dr.Read();
　　Response.BinaryWrite((byte[])dr["BLOBData"]);
　　objConn.Close();

　　这里的网页浏览器代码和前一篇文章中的代码很相似，不过把CommandType变成了Stored Procedure，而且SQLCommand的执行使用了该存储过程名而不是一串SQL字符串。

　　总结

　　SQL Server可以作为存储图像和BLOB数据的便携容器使用，不过处理二进制数据和处理ASCII有所不同：存储在SQL Server内部的数据并不像字符数据那样能看到它们是什么样子的;此外，在将数据发送到SQL Server之前，还需要对数据进行一些特殊处理，例如使用字节数组。不过总体而言，在SQL Server中使用和存储BLOB文件还是很有价值的。

 

 

 

一下是tangwing总结出的关键代码，供参考：

CREATE TABLE Employees ( Id int, Name varchar(50) , Photo varbinary(max) ) INSERT INTO Employees(id,name,photo) SELECT BulkColumn from Openrowset( Bulk 'd:/test/0.jpg', Single_Blob) as EmployeePicture update Employees set photo=(SELECT * from Openrowset( Bulk 'd:/test/0.jpg', Single_Blob)as alias)