saving image in sql data base how?

Question

The C# code:

   SqlCommand NewUser = new SqlCommand("INSERT INTO [User] Values (@username,@password,@name,@lastname,@location,@profesion,@email,@gender,@money,@pro,@xp,@lv,@m1,@m2,@m3,@m4,@m5,@d1,@d2,@d3,@d4,@d5,@im);", c);
        NewUser.Connection = c;
        NewUser.Parameters.AddWithValue("@username", txtuser.Text);
        NewUser.Parameters.AddWithValue("@password", txtpass.Text);
        NewUser.Parameters.AddWithValue("@name", txtFName.Text);
        NewUser.Parameters.AddWithValue("@lastname", txtLName.Text);
        NewUser.Parameters.AddWithValue("@location", ddlcountry.SelectedItem.Text);
        NewUser.Parameters.AddWithValue("@profesion", txtprofession.Text);
        NewUser.Parameters.AddWithValue("@email", txtemail.Text);
        NewUser.Parameters.AddWithValue("@gender", rbgendere.SelectedItem.Text);
        NewUser.Parameters.AddWithValue("@money", 0);
        NewUser.Parameters.AddWithValue("@pro", DBNull.Value);
        NewUser.Parameters.AddWithValue("@xp", 0);
        NewUser.Parameters.AddWithValue("@lv", 1);
        NewUser.Parameters.AddWithValue("@m1", 0);
        NewUser.Parameters.AddWithValue("@m2", 0);
        NewUser.Parameters.AddWithValue("@m3", 0);
        NewUser.Parameters.AddWithValue("@m4", 0);
        NewUser.Parameters.AddWithValue("@m5", 0);
        NewUser.Parameters.AddWithValue("@d1", 0);
        NewUser.Parameters.AddWithValue("@d2", 0);
        NewUser.Parameters.AddWithValue("@d3", 0);
        NewUser.Parameters.AddWithValue("@d4", 0);
        NewUser.Parameters.AddWithValue("@d5", 0);
        NewUser.Parameters.AddWithValue("@im", );
        Session["CurentUserid"] = txtuser.Text;
        c.Open();
        NewUser.ExecuteNonQuery();
        c.Close();
        Session["Conect"] = (bool)true;
        Response.Redirect("Finish Had Member.aspx", true);

the table code:

   CREATE TABLE [dbo].[User] (
    [Username]  VARCHAR (100)   NOT NULL,
    [Pasword]   VARCHAR (100)   NOT NULL,
    [FName]     VARCHAR (MAX)   NOT NULL,
    [LName]     VARCHAR (MAX)   NOT NULL,
    [Location]  VARCHAR (MAX)   NOT NULL,
    [Profesion] VARCHAR (MAX)   NOT NULL,
    [email]     VARCHAR (MAX)   NOT NULL,
    [gender]    VARCHAR (MAX)   NOT NULL,
    [money]     INT             NOT NULL,
    [property]  VARCHAR (MAX)   NULL,
    [xp]        INT             NOT NULL,
    [level]     INT             NOT NULL,
    [mission1]  INT             NOT NULL,
    [mission2]  INT             NOT NULL,
    [mission3]  INT             NOT NULL,
    [mission4]  INT             NOT NULL,
    [mission5]  INT             NOT NULL,
    [did1]      INT             NOT NULL,
    [did2]      INT             NOT NULL,
    [did3]      INT             NOT NULL,
    [did4]      INT             NOT NULL,
    [did5]      INT             NOT NULL,
[image]     VARBINARY (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Username] ASC)
);

I want to save an image from the c# page into the data base this is the html:

<input runat="server" id="pho" type="file" name="file[]" multiple="" accept="image/*" />

how can i do it? i added all the insert code plss loke how can i save the image?

i update the code there is th full table

Try this : NewUser.Parameters.Add("@im", System.Data.SqlDbType.VarBinary); NewUser.Parameters["@im"] = image; — jdweng, May 30 '16 at 12:05
http://stackoverflow.com/questions/744589/how-do-you-store-a-picture-in-an-image-column/744603#744603 — Tewr, May 30 '16 at 12:08
The byte[] for the image. Make sure when reading the image from a file use UTF8 Encoding and not a string. Putting an image into a string will corrupt the image data. — jdweng, May 30 '16 at 12:17

Ruban J · Answer 1 · 2016-05-30T12:20:02.793

Here is a sample code for storing image to sql server :

SqlConnection conn = new SqlConnection(connectionString);

try
{
    int imageLength = uploadInput.PostedFile.ContentLength;
    byte[] picbyte = new byte[imageLength];
    uploadInput.PostedFile.InputStream.Read (picbyte, 0, imageLength);

    SqlCommand command = new SqlCommand("INSERT INTO ImageTable (ImageFile) VALUES (@Image)", conn);
    command.Parameters.Add("@Image", SqlDbType.Image);
    command.Parameters[0].Value = picbyte;

    conn.Open();
    command.ExecuteNonQuery();
    conn.Close();
}
finally
{
    if (conn.State != ConnectionState.Closed)
    {
        conn.Close();
    }
}

NOTE : uploadInput is a file input control, to upload image file to server. The code taken from an ASP.NET application.

Here is the insert script to an image typed column :

INSERT INTO ImageTable (ImageColumn)

SELECT ImageColumn FROM 
OPENROWSET(BULK N'C:\SampleImage.jpg', SINGLE_BLOB) 
AS ImageSource(ImageColumn);

EDIT : To know more on this topic, refer here

i dont succeed to do it with my database lots of errors someone can send an answer that will insert corectly with the image plss? — Oz Cohen, May 30 '16 at 12:25

Akshay · Answer 2 · 2016-05-31T08:44:46.090

Try this..

     Stream fs = FileUpload1.PostedFile.InputStream;
            BinaryReader br = new BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);

SqlCommand NewUser = new SqlCommand("INSERT INTO [User] Values (@username,@password,@name,@lastname,@location,@profesion,@email,@gender,@money,@pro,@xp,@lv,@m1,@m2,@m3,@m4,@m5,@d1,@d2,@d3,@d4,@d5,@im);", c);
        NewUser.Connection = c;
        NewUser.Parameters.AddWithValue("@username", txtuser.Text);
        NewUser.Parameters.AddWithValue("@password", txtpass.Text);
        NewUser.Parameters.AddWithValue("@name", txtFName.Text);
        NewUser.Parameters.AddWithValue("@lastname", txtLName.Text);
        NewUser.Parameters.AddWithValue("@location", ddlcountry.SelectedItem.Text);
        NewUser.Parameters.AddWithValue("@profesion", txtprofession.Text);
        NewUser.Parameters.AddWithValue("@email", txtemail.Text);
        NewUser.Parameters.AddWithValue("@gender", rbgendere.SelectedItem.Text);
        NewUser.Parameters.AddWithValue("@money", 0);
        NewUser.Parameters.AddWithValue("@pro", DBNull.Value);
        NewUser.Parameters.AddWithValue("@xp", 0);
        NewUser.Parameters.AddWithValue("@lv", 1);
        NewUser.Parameters.AddWithValue("@m1", 0);
        NewUser.Parameters.AddWithValue("@m2", 0);
        NewUser.Parameters.AddWithValue("@m3", 0);
        NewUser.Parameters.AddWithValue("@m4", 0);
        NewUser.Parameters.AddWithValue("@m5", 0);
        NewUser.Parameters.AddWithValue("@d1", 0);
        NewUser.Parameters.AddWithValue("@d2", 0);
        NewUser.Parameters.AddWithValue("@d3", 0);
        NewUser.Parameters.AddWithValue("@d4", 0);
        NewUser.Parameters.AddWithValue("@d5", 0);
        NewUser.Parameters.AddWithValue("@im", bytes);
        Session["CurentUserid"] = txtuser.Text;
        c.Open();
        NewUser.ExecuteNonQuery();
        c.Close();
        Session["Conect"] = (bool)true;
        Response.Redirect("Finish Had Member.aspx", true);

**

EDIT: ok, I tried it on my system. Below code inserts the image in binary format successfully.

**

SQL table

CREATE TABLE [dbo].[Image_table](   

    [Id] [int] IDENTITY(1,1) NOT NULL,
    [image]     VARBINARY (MAX) NULL
    )



protected void Button1_Click(object sender, EventArgs e)
    {
        Stream fs = FileUpload.PostedFile.InputStream;
        BinaryReader br = new BinaryReader(fs);
        Byte[] bytes = br.ReadBytes((Int32)fs.Length);

         string consString = ConfigurationManager.ConnectionStrings["connectionKey"].ConnectionString;
         SqlConnection con = new SqlConnection(consString);

            try
            { 
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into [dbo].[Image_table]  values (@img)";
                cmd.Parameters.Add("@img", SqlDbType.Image).Value = bytes;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }

    }

i dont think it will work i need to insert them all in one time? — Oz Cohen, May 30 '16 at 12:21
User will click on browse > Select the image file > then the control will come to first line of this code, and then the image will gets saved in DB one at a time, as you said you need to save an image in DB. Use FileUpload control. If you want to save multiple images, use a for loop. — Akshay, May 30 '16 at 12:27
ok but how can i exucate the first full insert without the img — Oz Cohen, May 30 '16 at 12:29
I edited the answer.. I assume @im is your image variable.. you need to save bytes as its value. — Akshay, May 30 '16 at 12:35
using System.IO; C'mon, you really need to read things and learn how visual studio works. — Akshay, May 30 '16 at 12:38
Does not workingl it is showing the same things at the data table data: 0x every time even if dont put image — Oz Cohen, May 30 '16 at 12:51

saving image in sql data base how?

2 Answers2

Linked