Images into sql server

• Download sourcecode.zip - 68.8 KB

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

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”

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.

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

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

5.- click next

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

Example: file://\\misti\generatorApp\*

(attention: the * is important)

7.- click next

8.- select Use existing permission set

9.- select FullTrust

10.- click Next

11.- click Finish

12.- Open your Command Windows (RUN)

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

14.- Execute “caspol -m -ag 1.2 -url urlname FullTrust”

(attention: the parameter FullTrust is case sensitive)