DBAVisual Studio 2005.NET 2.0SQL Server 2005C# 2.0IntermediateDevVisual StudioSQL ServerSQLWindows.NETASP.NETC#
使用 VS 2005、C# 和 SQL Server 2005 创建媒体库的非常简单的示例
3.12/5 (19投票s)
2006年9月26日
4分钟阅读
80167
2020
提供一套非常简单的说明，用于创建您自己的媒体库并将图片从 SQL Server 2005 加载进来。
引言
本示例将向您展示如何使用 Microsoft Visual Studio 2005、ASP.NET 和 C# 创建一个非常简单的媒体库，并从 Microsoft SQL Server 2005 中提取图片。在此示例中，我基本上将我在互联网上找到的所有无用的示例重新组合成一些真实且可操作的东西，您可以使用它来启动您自己的、由数据库驱动的媒体库。本文不讨论如何将图片加载到数据库中。我找到了数千篇类似的关于如何允许用户将图片上传到 SQL Server 数据库的文章，它们都做得很好。对于那些对此文章真正感兴趣的人来说，这是一个功能齐全、面向公众的媒体库，并且已经准备好了上一页和下一页导航。您可以根据自己的需求进行自定义，但它已经可以使用了。我在网上找到的示例没有一个像这样详尽，而这正是我所需要的示例。
考虑因素
我已经阅读了关于将图片存储在数据库中的利弊的宣传。我并不在意，但我不得不从中筛选出我想要的东西。为了您的利益，我将简要总结一下利弊。
优点包括增强了数据安全性，以保护您的图像数据。除非您的图片非常大，即每张图片大于 2 或 3MB，否则实际上没有缺点。如果您的图片如此之大，您真的需要重新考虑所有的选择，看看是否可以将它们压缩到更易于管理的大小。对我而言，我的所有图片都小于 100KB，这对我来说非常完美。专家们会对此争论不休，我让他们去争论吧。我需要使用 SQL Server，就这样。
必备组件
您需要使用以下其中一种来编写代码：Visual Studio 2005 SDK、Visual Studio 2005 Professional Edition，或者可能还有 Visual Studio 2005 Express，尽管我不确定。您还需要 SQL Server 2005，或者 SQL Server 2000 Developer 或 Enterprise Edition。但我很确定您可以非常轻松地将其改编到任何数据库。除了 Visual Studio 和 SQL Server，您仅需的其他东西就是一些图片。我将把将它们导入数据库的任务留给读者自行练习。
用于创建表的 SQL
ImageID是一个int，我将其用作主键，用于唯一标识数据库中的每个图像。ImageNameprobably doesn't need an explanation。ImageCaption可用作工具提示，也可用于全尺寸图片页面中的文本。ImagePath我没有使用。我创建它是为了将来可能需要从磁盘而不是数据库读取图片的情况。GalleryName用于能够拥有多个画廊（例如，飞机、汽车、火车等）。ImageNumber是图片在画廊和页面组合中的位置索引。所以，如果ImageNumber是 2，它表示PageNumber上GalleryName中的第二张图片。PageNumber用于将图片与其相应的页面关联起来。BinaryImageFile是表示图像的实际数据流。
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MediaGallery](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ImageCaption] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImagePath] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GalleryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ImageNumber] [int] NULL,
[PageNumber] [int] NULL,
[BinaryImageFile] [image] NULL,
CONSTRAINT [PK_MediaGallery] PRIMARY KEY CLUSTERED (
[ImageID] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
代码后台 - MediaGallery.aspx.cs
此页面只有一个目的：以 5x4 的图片矩阵获取并显示我们的图片。如果我们没有正好 20 张图片，这也不是问题。我已经处理了这个问题。
private void RefreshGalleryImages(string _GalleryName, int _PageNumber)
{
/*
* The only really interesting code in
* the project and not really all that interesting.
* We use an image counter as we need
* a string property in our call to FindControl.
* Then we just build out the gallery according
* to the passed in parameters. I'm not
* going to explain to much of it.
* If you don't understand the database calls there are
* 1000's of examples just use Google.
*/
int imageCounter = 1;
string tmpStr = "";
SqlDataReader thisReader = null;
SqlConnection thisConn = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"MediaGalleryConnectionString"].ConnectionString);
SqlCommand thisCmd = new SqlCommand("Command String", thisConn);
tmpStr = "SELECT * from MediaGallery WHERE GalleryName='" +
_GalleryName + "' AND PageNumber='" + _PageNumber +
"' ORDER BY ImageNumber, PageNumber ASC";
thisCmd.CommandText = tmpStr;
//This code is going to loop through every ImageButton
//on the form and hide it. Why? Because at the end of our
//Gallery we might not have 20 pictures we might only
//have 17 so those last 3 won't get filled and we hid them.
for (int i = 1; i < 21; i++)
{
System.Web.UI.WebControls.ImageButton _CurrentImage =
Page.FindControl("ImageButton" + i.ToString())
as System.Web.UI.WebControls.ImageButton;
_CurrentImage.Visible = false;
}
try
{
thisConn.Open();
thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (thisReader.Read())
{
//Here we are just using the values returned
//from the database. Should be pretty clear what is going on.
System.Web.UI.WebControls.ImageButton _CurrentImage =
Page.FindControl("ImageButton" + imageCounter.ToString())
as System.Web.UI.WebControls.ImageButton;
//The line below is the *coolness* of this gallery.
//It calls another page that has one purpose in life.
//Fetch the image I pass you by ID (note you could also
//pass the gallery name here)
//and stream it back to me in binary.
_CurrentImage.ImageUrl = "FetchImage.aspx?ImgID=" +
thisReader["ImageID"].ToString().Trim() +
"&height=72&width=108";
_CurrentImage.ToolTip =
thisReader["ImageCaption"].ToString().Trim();
//Just calling some javascript popUp
//window code here. Kind of cool you can do this.
_CurrentImage.OnClientClick =
("popUp('gallery/ViewTarget.aspx?Element=" +
thisReader["ImageID"].ToString().Trim() +
"&Description=" +
thisReader["ImageCaption"].ToString().Trim() + "')");
_CurrentImage.Visible = true;
imageCounter++;
}
//Show or hide our navigation buttons.
CheckNextPageAvailability(_GalleryName, _PageNumber);
CheckPreviousPageAvailability(_PageNumber);
}
catch (System.Exception ex)
{
if (ex.Message == "An error has occurred while establishing" +
" a connection to the server. When connecting to " +
"SQL Server 2005, this failure may be caused by the fact" +
" that under the default settings SQL Server does not " +
"allow remote connections. (provider: Named Pipes " +
"Provider, error: 40 - Could not open a connection to SQL Server)")
{
Response.Write("Please check the Username, Password, " +
"Server and Database values in the " +
"web.config file. Make sure they " +
"reflect your environment.");
NextImageButton.Visible = false;
PreviousImageButton.Visible = false;
}
else
{
throw ex;
}
}
finally
{
if (null != thisConn)
thisConn.Close();
if (null != thisReader)
thisReader = null;
}
}
代码后台 - FetchImage.aspx.cs
此页面是用作流式传输代理，用于从数据库中获取实际的二进制图像，并将其作为图像 URL 流式传输给请求者。它会查找三个传入的 URL 参数（ImgID、Height、Width）。
protected void Page_Load(object sender, EventArgs e) {
/*
* The code below is widely available on the internet
* I think you can find examples of it
* everywhere which is what I did.
* http://www.samspublishing.com/articles/article.asp?p=377078&rl=1
* I think the above link and credit to:
*
* Getting and Displaying Images from
* SQL Server with C# (Sams Publishing)
* By Jesse Smith.
* Date: Jul 29, 2005.
*
* Had better stay in here. :)
*
* I ripped out all his connection code as I didn't care for
* it but the image stuff is what I kept.
*/
string tmpStr = "";
System.Int32 _ImgID =
Convert.ToInt32(Request.QueryString["ImgID"]);
System.Int32 _height =
System.Convert.ToInt32(Request.QueryString["height"]);
System.Int32 _width =
System.Convert.ToInt32(Request.QueryString["width"]);
SqlDataReader thisReader = null;
SqlConnection thisConn = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"MediaGalleryConnectionString"].ConnectionString);
SqlCommand thisCmd = new SqlCommand("Command String", thisConn);
tmpStr = "Select ImageName, BinaryImageFile from " +
"MediaGallery WHERE ImageID='" + _ImgID.ToString() + "'";
thisCmd.CommandText = tmpStr;
try
{
thisConn.Open();
thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (thisReader.Read())
{
System.Drawing.Image _image =
System.Drawing.Image.FromStream(new
System.IO.MemoryStream((byte[])thisReader["BinaryImageFile"]));
//Just a sanity check for images of type
//gif or jpeg feel free to add more in.
if (thisReader["ImageName"].ToString().Trim().
ToUpper().IndexOf("jpg", 0,
thisReader["ImageName"].ToString().Length) > 0)
{
System.Web.HttpContext.Current.Response.ContentType =
"image/jpeg";
}
else
{
System.Web.HttpContext.Current.Response.ContentType =
"image/gif";
}
//Here's the streaming specific stuff.
//Shouldn't be to hard to reason out.
System.Drawing.Image _newimage =
_image.GetThumbnailImage(_width,
_height, null, new System.IntPtr());
_newimage.Save(
System.Web.HttpContext.Current.Response.OutputStream,
System.Drawing.Imaging.ImageFormat.Jpeg);
}
}
catch (System.Exception Ex)
{
throw Ex;
}
finally
{
if (null != thisConn)
thisConn.Close();
if (null != thisReader)
thisReader = null;
}
}
代码后台 - ViewTarget.aspx.cs
此页面用于在一个较大的弹出窗口中显示用户点击的图像。没有什么特别之处。我只是演示了一些可能有用的东西，比如设置字体名称和大小。关于这个部分，我不知道的一件事是，Label 控件会自动调整大小以适应您发送给它的任何文本。这确实比我最初尝试使用的只读文本框更能清晰地显示描述。
protected void Page_Load(object sender, EventArgs e) {
/*
* This should be easy to follow. Just grabbing
* the URL params and getting it done.
*/
string _ImageID =
Request.QueryString["Element"].ToString().Trim();
string _Description =
Request.QueryString["Description"].ToString().Trim();
GetImageForDisplay(_ImageID, _Description);
}
private void GetImageForDisplay(string _ImageID, string _Description) {
/*
* Here I'm using the same page to stream
* the image off of. You could just as easily
* use a different page for whatever reason you might have.
*/
TargetImage.ImageUrl = "../FetchImage.aspx?ImgID=" +
_ImageID + "&height=200&width=300";
//Just showing an example to set the font and font size.
DescriptionLabel.Font.Name = "Verdana";
DescriptionLabel.Font.Size =
System.Web.UI.WebControls.FontUnit.XSmall;
DescriptionLabel.Text = _Description; }
摘要
到此结束。要获取完整的图片库，请下载文章顶部的 zip 文件。如果您有任何建议，或者如果您找到了比我在这里演示的更好的方法来做某事，我洗耳恭听。再次声明，大部分代码都分散在互联网的各个角落，但没有任何一个地方包含全面的示例。希望本文能为许多人改变这一现状。
历史
- 2006 年 9 月 25 日 - 首次发布。
- 2006 年 9 月 26 日 - 编辑了
SELECT语句，使其仅调用我正在使用的字段。为MediaGallery.aspx:RefreshImageGallery添加了一个特定的 Web.Config 错误处理程序。