Click here to Skip to main content
15,868,217 members
Articles / Programming Languages / C#
Article

Images into sql server

Rate me:
Please Sign up or sign in to vote.
2.47/5 (11 votes)
20 Jan 2008CPOL3 min read 78.1K   1.7K   26   14
It examples show how insert any image into sql server and perharps other types
Screenshot - database.png

Introduction

A day I became a question myself, how insert any image to a sql server, and I decided read more about it, and I reached the following conclusión.

Only I need a field with image type (sql server 2000) or varbinary type (sql server 2005).

Then next code will be explained step by step, but if you has any question consults without problems.

using System.Drawing.Imaging;
using System.IO;
using System.Data.SqlClient;

those lines allow us work with images, get access to Input Output classes and manage files or directories or streams that we will use to do our target, connect to sql server natively, respectively.

caution: firstly we need run this srcipt in sql server, it allows us create a database and a table that support any images

   /*--EN SQL SERVER...
   CREATE DATABASE dbimages;
   go
   use dbimages;
   go
   CREATE TABLE Tabla
   (
       Id INT IDENTITY(1,1) PRIMARY KEY,
       Descripcion VARCHAR(200) unique,
       Imagen IMAGE
   );
*/

after to have inserted your images, you will see something similar to this

Screenshot - database.png

the images are really stored thus. so as a sequence of hexadecimal numbers.

well, we need insert a image there, within my sql server


How can insert it?

we need to follow a sequence so
myFile.jpg -> Conver to Image -> Convert to MemoryStream -> Convert to byte[] -> insert to database

this code get a Image object from Image property from PictureBox Control, here we see how a Image object is
transform to memoryStream type next to byte array and return it

private byte[] GetArrayFromImagen(Image imagen)
        {
            MemoryStream ms = new MemoryStream();
            imagen.Save(ms, ImageFormat.Jpeg);
            byte[] matriz = ms.ToArray();

            return matriz;
        }


when has already obtained a byte array insert that sequence of bytes within our table so as show next code

private void GuardarImagen(byte[] matriz)
        {
            this.cmd.CommandText = "insert into tabla(DESCRIPCION, IMAGEN) " +
                    "VALUES(@DESCRIPCION, @IMAGEN)";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("Descripcion", this.NombreDeArchivoCorto).SqlDbType = SqlDbType.VarChar;
            cmd.Parameters.AddWithValue("Imagen", matriz).SqlDbType = SqlDbType.Image;
            cmd.ExecuteNonQuery();
        }


it method uses the previous methods

private void GuardarImagen(Image imagen)
        {
            byte[] matriz = this.GetArrayFromImagen(imagen);
            this.GuardarImagen(matriz);
        }


ready!!!

now,


How can I get my Image from sql Server?

it sequence is similar but other way around
myFieldSqlServer -> Convert to byte[] -> Convert to MemoryStream -> Convert to Image -> to give this to a PictureBox

we requested through a query our image and it method return our array

private byte[] GetArrayFromDB(string NombreDeArchivo)
        {
            this.cmd.CommandText = "select imagen from tabla where descripcion = @Descripcion";
            this.cmd.Parameters.Clear();
            this.cmd.Parameters.AddWithValue("Descripcion", NombreDeArchivo).SqlDbType = SqlDbType.VarChar;
            byte[] matriz = (byte[])this.cmd.ExecuteScalar();

            return matriz;
        }


now it array is used so as a parameter to this method, it convert to MemoryStream and next to Image Object
now that we have got it,we can set it image to a image property of PictureBox Control

private Image GetImagenFromArray(byte[] matriz)
        {
            MemoryStream memoryStream = new MemoryStream();
            memoryStream.Write(matriz, 0, matriz.Length);
            Image imagen = Image.FromStream(memoryStream);

            return imagen;
        }


ready!!!
mmmm...


how can I get insert a file within sql server?

we too can get a image from a file directly and insert to database, the idea es similar only that now it will be from
a file
so:
now we provided a paramater that contain a path at file in the filesystem, next the file is converted to stream
through FileStream Class and next the process continuos just as the previous one.

private byte[] GetArrayFromFile(string Ruta)
{
    FileStream fs = new FileStream(this.NombreDeArchivoLargo, FileMode.OpenOrCreate, FileAccess.Read);
    byte[] matriz = new byte[fs.Length];
    fs.Read(matriz, 0, System.Convert.ToInt32(fs.Length));
    fs.Close();

    return matriz;
}


mmmmm....


how can I save a image from sql server to File?

we only need a array contained with sequence of bytes from sql server and a path where save our file

private void GetFileFromArray(byte[] matriz, string FileName)
{
    FileStream fs = new FileStream(Application.StartupPath + "\\" + FileName,
        FileMode.OpenOrCreate, FileAccess.Write);
    fs.Write(matriz, 0, matriz.Length);
    fs.Flush();
    fs.Close();
}

ready.

so as have been working with images Files, we too can work with otrer file types, *.doc, *.fla, *.mdb, *.etc . the idea is equal.

Topic (How can I execute any .exe from other pc?)

1.- Open “Start/Settings/Control Panel/Microsoft .NET Framework 2.0 Configuration”<o:p>

2.- Expand the node Runtime Security Policy, next the node Machine, next the node Code Groups, next the node All Code, and select LocalIntranet_Zona.<o:p>

3.- On the right panel click on Add a Child Code Group

<o:p>security1.JPG

4.- Select Create a new code group, insert the name to the new code group

security2.JPG <o:p>

5.- click next <o:p>

6. Now settings the condition type for this to URL and write the path of shared folder, it is using the next format file://\\nameserver\sharedFolder\* where \\nameserver\sharedFolder is the name of the shared folder <o:p>

Example: file://\\misti\generatorApp\*<o:p>

(attention: the * is important)

security3.JPG<o:p>

7.- click next<o:p>

8.- select Use existing permission set<o:p>

9.- select FullTrust

security4.JPG<o:p>

10.- click Next <o:p>

11.- click Finish<o:p>

12.- Open your Command Windows (RUN)<o:p>

13.- go to the next path “C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx”<o:p>

14.- Execute “caspol -m -ag 1.2 -url urlname FullTrust”<o:p>

(attention: the parameter FullTrust is case sensitive)<o:p>

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Peru Peru
One of the things that like me is to be creative being and to be able to offer solutions and don't only create a software components, I can say that of all the languages that I have touched, I like C#.
Greetings to all.

Comments and Discussions

 
Generalplz explain this article in a step by step process Pin
Mallikarjun Kalkere18-Feb-07 5:22
Mallikarjun Kalkere18-Feb-07 5:22 
GeneralRe: plz explain this article in a step by step process Pin
Augusto Humire Martinez18-Feb-07 13:38
Augusto Humire Martinez18-Feb-07 13:38 
GeneralI had simliar idea Pin
Sacha Barber17-Feb-07 21:46
Sacha Barber17-Feb-07 21:46 
GeneralRe: I had simliar idea Pin
Augusto Humire Martinez18-Feb-07 13:26
Augusto Humire Martinez18-Feb-07 13:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.