Cool. I finally come out with a solution and I will love to share this with my friends who read my blog.
I will like to thank Wee Hyong, Feelite and Serena for posting your feedbacks in my thread located in SgDotNet ASP.NET forums.
I was planning to post a document on this, but I felt that it would take a lot of time to proof check and edit it. So I just blog it. Plus I am not posting any screenshots. I will illustrate as details as I can. Do bear with my broken english. :(
Software - Microsoft Visual Studio 2005 Team Edition, Microsoft SQL Server 2005 Developer Edition (I guess you can use other editions, but I am not that sure about SQL Server Express)
1)
Assuming I have a database table like this:
CustomerImage Table
PrimaryID INT NOT NULL Primary Key
Picture VARBINARY(MAX) NOT NULL (You need to have the MAX keyword here, read Wee Hyong's blog)
2)
After you have created that database (either using T-SQL or through SQL Server Management Studio), time for you to write 2 stored procedures.
a) Write Images
Note: I had choosen this way due to 2 reasons:
i) I had tried a lot of ways, and I had problems with it.
One example, if you try saving this file (it works) but when you try to execute it (it won't). Full of suprises haha :)
CREATE
Procedure usp_TestWriteImage
(
@varPicturePath
VARCHAR(max)
)
AS
SET
NOCOUNT ON
BEGIN
INSERT
INTO CustomerImage ( Picture )
SELECT * FROM OPENROWSET(BULK ''' +
-- It doesn't understand @varPicturePath
@varPicturePath + ''', SINGLE_BLOB) AS Picture
END
ii) And you know I am very weak in Database. So someone could have solved as above. Haha.
This is my implementation. It really cracks my brain :(
ALTER PROCEDURE
dbo.usp_WriteImages
(
@varPicturePath
VARCHAR(MAX)
)
AS
BEGIN
DECLARE @SQLString NVARCHAR(max)
SET NOCOUNT ON
-- Assign into a temporary variable
SET @SQLString = N'INSERT INTO CustomerImage ( Picture ) ' +
'SELECT BulkColumn FROM OPENROWSET(BULK ''' +
@varPicturePath +
''', SINGLE_BLOB) AS Picture';
-- Execute this temporary variable
EXEC sp_executesql @SQLString
END
Yes, Yes. It works. For your information, you can do this:
i) SELECT * FROM OPENROWSET ...
OR
ii) SELECT BulkColumn FROM OPENROWSET
I think people prefer the 2nd way, maybe with the best practices - avoid using * in your T-SQL Statement.
b) Read Images
CREATE PROCEDURE
dbo.usp_ReadImages
AS
SET NOCOUNT ON
SELECT Picture FROM CustomerImage
WHERE PictureID = 5
RETURN
Okay, not you see me playing cheat here. Haha. I actually specified a PictureID = 5. Take note, I am just writing an example here. So I am not going to waste my time figuring out how to write a complex example.
3)
Cool. Database stuff alread finished. Time to work on asp.net 2.0 codes. Take note, you don't have to write sql managed to achieve all this. :)
4)
Create a standard website in vs 2005.
In your default.aspx you have to drag some controls.
(I wish I have a table to display the item belows :( )
Controls (Top to bottom):
a) Header Text (H1) with text "This is an upload and download of images"
b) 1 Horizontal Rule
c) (H2) with text "Upload"
d) Image Control from Standard Toolbar - name this imgUpload
e) FileUpload Control from Standard Toolbar - name this fuUpload
f) Button Control from Standard Toolbar - name this btnUpload and text "Upload"
g) Button Control from Standard Toolbar - name this btnSave and text "Save"
h) 1 Horizontal Rule
i) (H2) with text "Download"
j) Image Control from Standard Toolbar - name this imgDownload
k) Button Control from Standard Toolbar - name this btnDownload and text "Load Image"
For this source code, you can get it below:
Default.aspx
<%
@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<
html xmlns="http://www.w3.org/1999/xhtml" >
<
head runat="server">
<title>Untitled Page</title>
</
head>
<
body>
<form id="form1" runat="server">
<div>
<h1>
This is an upload and download of images
</h1>
<hr />
<h2>
</h2>
<h2>
</h2>
<h2>
Upload
</h2>
<p>
Select a file to upload:
</p>
<p>
<asp:Image ID="imgUpload" runat="server" Height="150px" Width="140px" /> </p>
<p>
<asp:FileUpload ID="fuUpload" runat="server" Width="304px" /> </p>
<p>
<asp:Button ID="btnUpload" runat="server" Text="Upload" Width="119px" OnClick="btnUpload_Click" /> </p>
<p>
<asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="If you confirm, save"
Width="171px" /> </p>
<hr />
<h2>
Download
</h2>
<p>
<asp:Image ID="imgDownload" runat="server" Height="150px" Width="140px" /> </p>
<p>
<asp:Button ID="btnDownload" runat="server" Text="Load Image" OnClick="btnDownload_Click" /> </p>
<p>
</p>
<p>
</p>
</div>
</form>
</
body>
</
html>
5)
Cool. I have a nice layout. Let's move on to write my ConnectionManager class.
Create a class and place it inside App_Code folder.
Name this class as ConnectionManager.cs
Note: I will break it separately as it is quite fundamental. If you are a noob like me, there are comments in the code for you to read.
ConnectionManager.cs
// Developed by: Chua Wen Ching
// Reference:
// Obtains inputs from SgDotNet Community
// http://community.sgdotnet.org/forums/thread/25548.aspx
// Note: Not tested with performance
// Do let me know for further enquiries, chuawenching@yahoo.com
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Web.UI.HtmlControls;
///
<summary>
///
Summary description for ConnectionManager
///
</summary>
public
static class ConnectionManager
{
static ConnectionManager()
{
//
// TODO: Add constructor logic here
//
}
/// <summary>
/// Execute stored procedures to insert image
/// </summary>
/// <param name="imagePath"></param>
public static void writeImageToDB(string imagePath)
{
// Open Connection upon reading connection strings from web.config
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString()))
{
// "usp_WriteImages" is the stored procedure
SqlCommand command = new SqlCommand("usp_WriteImages", conn);
// Specify type of command - for this case Stored Procedure
command.CommandType =
CommandType.StoredProcedure;
// Add 1st parameter
// You need to pass in the picture path
SqlParameter pathParam = new SqlParameter();
pathParam.ParameterName =
"@varPicturePath";
pathParam.Value = imagePath;
pathParam.DbType =
DbType.String;
command.Parameters.Add(pathParam);
// Open Connection
conn.Open();
// Execute the stored procedure
command.ExecuteNonQuery();
// Close connection, cleans things up
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// Execute stored procedure to read image
/// </summary>
/// <returns></returns>
public static byte[] readImageFromDB()
{
SqlDataReader dr = null;
byte[] image = null;
// Open Connection upon reading connection strings from web.config
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString()))
{
// "usp_ReadImages" is the stored procedure
SqlCommand command = new SqlCommand("usp_ReadImages", conn);
command.CommandType =
CommandType.StoredProcedure;
// Open Connection
conn.Open();
// Get the return results from the stored procedure
// and pass into SqlDataReader
dr = command.ExecuteReader();
dr.Read();
// Return the read value and cast into byte array
image = (
byte[])dr.GetValue(0);
// Close datareader and connections, cleans things up
dr.Close();
conn.Close();
dr.Dispose();
conn.Dispose();
}
// Return the byte array to the caller
return image;
}
}
6) Time to write some events handling code in your main Default.aspx
7) Open your code behind which is Default.aspx.cs
Add additional namespaces as below:
using
System.IO;
using
System.Drawing;
using
System.Drawing.Imaging;
8)
btnUpload_Click event
Basically I want to achieve here is:
- Get the file anywhere on your machine
- Store it temporary in your local web server
- Store the location of the file within your Profile
/// <summary>
/// Upload to a temporary location
/// Load it to the image control
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpload_Click(object sender, EventArgs e)
{
// Check if the file is available
if (fuUpload.HasFile)
{
// Temporary location to store the images
// It is advisable to have this folder within your web application
string tempLocation = @"~\TempImages\" + fuUpload.FileName;
// Need the exact path to save this image.
// You cannot just load from the tempLocation.
// This path is also crucial when passing down to the stored procedure
string exactLocation = @"C:\Inetpub\wwwroot\DatabaseUploadDownload\TempImages\" + fuUpload.FileName;
// Save this image
fuUpload.SaveAs(exactLocation);
// Display the image
imgUpload.ImageUrl = tempLocation;
// Need to store into profile, if page postback
// fuUpload.FileName value will be empty
Profile.imagePath = exactLocation;
}
}
9)
Time to save your image. In this process I will be:
- Get the Profile path
- Pass this path into my ConnectionManager writeImageToDB function
- Delete this image from the tempImages folder
- Clear the Profile
- Set the imgUpload to a empty url
/// <summary>
/// This is the actual implementation of saving the file into the database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSave_Click(object sender, EventArgs e)
{
// Obtain values from Profiles
// You can pass around Profile.ImagePath if you want it to.
// I just want to reduce my time typing it.
string imagePath = Profile.imagePath;
// Write Image into database
ConnectionManager.writeImageToDB(imagePath);
// Remove Temporary image
// You don't need this image since it will be stored inside the database
File.Delete(imagePath);
// Clear Profile imagePath
Profile.imagePath =
string.Empty;
// Clear Image Control
imgUpload.ImageUrl =
string.Empty;
}
10)
Download the image from the database and display in the imgDownload
Note: Don't forget to read the comments.
protected void btnDownload_Click(object sender, EventArgs e)
{
using (MemoryStream mStream = new MemoryStream(ConnectionManager.readImageFromDB()))
{
string strDefaultFileName = "Default.jpg";
string strDefaultFileLocation = @"C:\Inetpub\wwwroot\DatabaseUploadDownload\TempImages\";
string strTempLocation = @"~\TempImages\";
byte[] imageFromDatabase = new byte[mStream.Length];
mStream.Read(imageFromDatabase, 0, imageFromDatabase.Length);
// I have to manually specify System.Drawing.Image, as it will get mix
// between System.Drawing.Image and System.Web.UI.WebControls
System.Drawing.
Image displayImage = System.Drawing.Image.FromStream(mStream);
// 1st, you need to specify the exact path, cannot use "~\tempImages\",
// it won't work, you will receive some GDI+ error
displayImage.Save(strDefaultFileLocation + strDefaultFileName,
ImageFormat.Jpeg);
// Alternative: If you want to display on the HTTP OutputStream
// Use this
// displayImage.Save(Response.OutputStream, ImageFormat.Jpeg);
// Display this image
imgDownload.ImageUrl = strTempLocation + strDefaultFileName;
}
}
11) Compile your webapp.
12) Trust me that you will see 2 errors. Why?
- No connectionstring inside your web config
- No profile properties in your web.config
Let's work on this:
a) ConnectionString
<
connectionStrings>
<
add name="TestConnectionString" connectionString="Data Source=MSROCKS;Initial Catalog=Test;Integrated Security=True;Pooling=False"
providerName="System.Data.SqlClient" />
</
connectionStrings>
b) Profile
- I set allow annonymous, so you will expect this
<
anonymousIdentification enabled="true" />
<
profile>
<
properties>
<
add name="imagePath" allowAnonymous="true" type="System.String" />
</
properties>
</
profile>
- If you say no annoymous, just write this
<profile>
<
properties>
<
add name="imagePath" type="System.String" />
</
properties>
</
profile>
If you read about my other post, you have to set a full type for imagePath property. You can't have the type="String". You have to place full path like this type="System.String"
13) Ola. Before you want to start running this examples. You have to configure a few things.
a) Make sure your aspnet account is within your new database.
b) Make sure your server roles \ bulk admin contains aspnet account. This is very important. By default aspnet does not have access to this bulk admin.
c) Make sure your aspnet account have read and write access to tempimages folder.
14) Time to run.
I guess this will be useful for everyone. Do correct me if I am wrong. I want to learn too like many others.
Cheers and have fun.