ASP.NET存取Sql Server資料庫Image欄位資料

資料庫中一般儲存圖片路徑。但有些情況下，用的是Image欄位，這就涉及到Sql Server資料庫中Image欄位資料的讀取技術。

1.存入Image欄位資料

        if (FileUpload1.HasFile)
        {
            //注意：IE下有效，不同瀏覽器的檔案類型是不同的。

             if (this.FileUpload1.PostedFile.ContentType == "image/bmp" ||
                this.FileUpload1.PostedFile.ContentType == "image/pjpeg" ||
                this.FileUpload1.PostedFile.ContentType == "image/gif" ||
                this.FileUpload1.PostedFile.ContentType == "image/x-png")  
            {
                int bookId = Convert.ToInt32(TextBox1.Text);
                byte[] bookImg = FileUpload1.FileBytes;
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("insert into bookImage values(@bookId,@bookImg)", connection))
                    {

                        command.Parameters.Add(new SqlParameter("@bookId", bookId));

                        command.Parameters.Add(new SqlParameter("@bookImg", bookImg));
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                }
            }

        }

2.讀取並顯示Image欄位資料

2.1 用一般處理常式讀取

public class Handler : IHttpHandler
{

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "image/jpeg/gif/x-png";
        context.Response.Cache.SetCacheability(HttpCacheability.Public);
        context.Response.BufferOutput = false;

        Int32 id = -1;
        Stream stream = null;

        if (context.Request.QueryString["bookId"] != null && context.Request.QueryString["bookId"] != "")
        {
            id = Convert.ToInt32(context.Request.QueryString["bookId"]);

            stream = BookManager.GetImage(id);

            if (stream != null)
            {
                const int buffersize = 1024 * 16;
                byte[] buffer = new byte[buffersize];
                int count = stream.Read(buffer, 0, buffersize);
                while (count > 0)
                {
                    context.Response.OutputStream.Write(buffer, 0, count);
                    count = stream.Read(buffer, 0, buffersize);
                }
            }
        }

    }
}

GetImage方法：  

  public static Stream GetImage(int bookId)
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("select bookImg from bookImage where bookId=@bookId ", connection))
            {
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SqlParameter("@bookId", bookId));
                connection.Open();
                object result = command.ExecuteScalar();
                try
                {
                    return new MemoryStream((byte[])result);
                }
                catch
                {
                    return null;
                }
        }
    }

}

也可以用下面的代碼：

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "image/jpeg/gif/x-png";
        context.Response.Cache.SetCacheability(HttpCacheability.Public);
        context.Response.BufferOutput = false;

        Int32 id = -1;
        Stream stream = null;

        if (context.Request.QueryString["bookId"] != null && context.Request.QueryString["bookId"] != "")
        {
            id = Convert.ToInt32(context.Request.QueryString["bookId"]);
            byte[] file = (Byte[])GetImage(id);　//把圖片資訊取出來
            context.Response.BinaryWrite(file);　
        }

    }
    public static Byte[] GetImage(int bookId)
    {
        using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LibraryMSConnectionString"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("select bookImg from bookImage where bookId=@bookId ", connection))
            {
                command.Parameters.Add(new SqlParameter("@bookId", bookId));
                connection.Open();
                SqlDataReader result = command.ExecuteReader();
                if (result.Read())
                {
                    try
                    {
                        return (byte[])result[0];
                    }
                    catch
                    {
                        return null;
                    }
                }
                else
                    return null;
            }
        }
    }

2.2 頁面顯示

如果用Image控制項，則代碼為：

    Image1.ImageUrl = "Handler.ashx?bookid=" + bookId;

如果用Image標籤，則為：

    <img alt ="<%#Eval("bookNM") %>" src ='../Handler.ashx?bookId=<%#Eval("ID") %>'/>