There are a lot of applications that use external images for their websites. However, if you store images in a database then they get backed up with your database, they are portable with your database, and it just may prevent someone from tampering with the images. Anyway, if you have links—like ~/images/picture.jpg”—and you want to load those from your website into a database then the following code will get the job done. You of course will have to change your connection string and update the database column references.
Listing 1 Contains a down and dirty loop that is intended to run from a Website. You could easily change it to a console application by fiddling with the path information. Listing 2 contains the implementation of the UpdateImage stored procedure. Just substitute your column names, and Listing 3 contains the script for schema used for the example.
If you are an old hand you probably could have figured this out in a minute. Its not very robust, but it represents code you probably only need to run one time any way. Just rip it out of the Page_Load after it runs. On an interesting note: when I used literal SQL for the update it kept trying to write ‘System.Byte[]’ to the BookImage column. It seems like string.Format was struggling with stream.ToArray. What is cooler is the demo I created that used the converted database images, of course, you’ll have to wait for the book (Professional DevExpress ASP.NET Controls) for that.
Listing 1: When the web page loads the links are read in the outer loop and the images are loaded in the inner loop; down and dirty just like I said.
protected void Page_Load(object sender, EventArgs e)
{
// run once
string connectionString =
ConfigurationManager.ConnectionStrings["BooksConnectionString"].ConnectionString;
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
const string sql =
"SELECT ID, CoverImageLink FROM BOOKS";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = (int)reader["ID"];
string link = (string)reader["CoverImageLink"];
string path = Server.MapPath(link);
if(File.Exists(path))
{
System.Drawing.Image bitmap = Bitmap.FromFile(path);
MemoryStream stream = new MemoryStream();
bitmap.Save(stream, ImageFormat.Jpeg);
byte[] data = stream.ToArray();
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
connection2.Open();
SqlCommand command2 = new SqlCommand("UpdateImage", connection2);
command2.CommandType = CommandType.StoredProcedure;
command2.Parameters.AddWithValue("@ID", id);
command2.Parameters.AddWithValue("@BookImage", data);
command2.ExecuteNonQuery();
}
}
}
}
}
Listing 2: The UpdateImage stored procedure (which you can run in Visual studio or SQL Sever Management Studio.
CREATE PROCEDURE UpdateImage
@ID int,
@BookImage image
AS
BEGIN
UPDATE BOOKS
SET
BookImage = @BookImage
WHERE ID = @ID
END
Listing 3: The schema (SQL script) that describes the database I ran the code in listing 1 against.
USE [Books]
GO
/****** Object: Table [dbo].[Books] Script Date: 05/20/2009 15:23:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Books](
[ID] [int] NOT NULL,
[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Author] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Publisher] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISBN] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hyperlink] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Tip] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Language] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublicationDate] [datetime] NULL,
[CoverImageLink] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BookImage] [image] NULL,
[Visible] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]