Click here to Skip to main content
Click here to Skip to main content

Images into sql server

, 20 Jan 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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

security1.JPG

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

security2.JPG

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)

security3.JPG

7.- click next

8.- select Use existing permission set

9.- select FullTrust

security4.JPG

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)

License

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

Share

About the Author

Augusto Humire Martinez
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

 
GeneralProblem when converting MemoryStream to Image PinmemberCode4Cafe20-May-07 1:09 
GeneralRe: Problem when converting MemoryStream to Image PinmemberAugusto Humire Martinez21-May-07 18:07 
GeneralRe: Problem when converting MemoryStream to Image PinmemberCode4Cafe23-May-07 1:15 
Generalworks fine on server but Pinmemberfarhan14016-May-07 2:43 
GeneralRe: works fine on server but PinmemberAugusto Humire Martinez20-Jan-08 8:01 
GeneralProblem with VB.NET PinmemberMCAST762-May-07 17:41 
GeneralRe: Problem with VB.NET PinmemberAugusto Humire Martinez2-May-07 20:33 
Generala better resource Pinmembercykophysh3918-Feb-07 13:01 
GeneralRe: a better resource PinmemberAugusto Humire Martinez18-Feb-07 14:29 
GeneralRe: a better resource Pinmembercykophysh3918-Feb-07 23:11 
Generalplz explain this article in a step by step process PinmemberMallikarjun Kalkere18-Feb-07 6:22 
GeneralRe: plz explain this article in a step by step process PinmemberAugusto Humire Martinez18-Feb-07 14:38 
GeneralI had simliar idea PinmemberSacha Barber17-Feb-07 22:46 
GeneralRe: I had simliar idea PinmemberAugusto Humire Martinez18-Feb-07 14:26 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 20 Jan 2008
Article Copyright 2007 by Augusto Humire Martinez
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid