How to Save and Get files in DataBase with C#.net

Question

MySqlCommand command = con.CreateCommand(); command.CommandText = "SELECT image FROM image WHERE id=11"; con.Open(); byte[] imagedata=(byte[])(command.ExecuteScalar()); con.Close(); MemoryStream mem = new MemoryStream(imagedata); pictureBox1.Image = Image.FromStream(mem); i have some problem here thatparameter is not valid exception .Please tell me any one how to retrieve a image saved in database and tell me how to convert a pdf to format to store in database

Answer 1

Hi, Narayanc1

Usually, we save big files dependant from the database and remain their path in the database. (it doesn't need any convertion)

And also we can save the files into binary.(it need to do convertion)

 

So I should explain the second one,

First, in sql server, you must set the field as binary type(including varbinary(MAX), binary).

Then, in your program, you can convert the specific file into byte array like:

Code Snippet

FileStream st = new FileStream(@"C:\filename.jpg", FileMode.Open);
            byte[] buffer = new byte[st.Length];
            st.Read(buffer, 0, (int)st.Length);
            st.Close();

 Note above is just an example to show how it works, when you deal with a big file integer may be too short, in which case you should read the file more than once into the byte array.

 

Finally, pass the byte array into the database:

Code Snippet

SqlConnection conn = new SqlConnection("...");
            SqlCommand cmd = new SqlCommand("UPDATE SomeTable SET image=@image WHERE ID = 1", conn);
            cmd.Parameters.AddWithValue("@image", buffer);
            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();

 

The sample just simply assign an image to a cell which id equal 1.

 

Thanks 

Answer 2

Further more, if you want to save other sorts of files, you may add a column to specify the file sort for convertion.

To get out the file data, the process is to read the binary data out, and then convert it into stream:

Code Snippet

        SqlConnection connection = new SqlConnection ("...");
        connection.Open ();
        SqlCommand command = new
          SqlCommand ("select PdfFile from Table", connection);
        byte[] buffer = (byte[]) command.ExecuteScalar ();
        connection.Close();
        FileStream fs = new FileStream(@"C:\test.pdf", FileMode.Create);
        fs.Write(buffer, 0, buffer.Length);
        fs.Close();

 

Thanks

Answer 3

Hi,

 

you were mentioning that if deal with a big file integer, i shld read the file more than once into the byte array rite?

 

How do I go abt doing tt??

 

can provide sample codes

 

Thanks

Johnson

Answer 4

Hi, Johnson

Sometimes the interger type is not big enough to save all data of the FileStream, So we gonna once read a data block into the buffer.

Something like the following

Code Snippet

long length = st.Length;

int times = 0;

byte[] buffer = new byte[length];

while(length > 1000)

{// once read 1000 bytes for example
   st.Read(buffer, 1000*times, 1000);

  length -= 1000;

  times++;

}

st.Read(buffer, 1000*times, length);

 

Thanks

Answer 5

ok got it...

 

thanks.

 

Do u have any idea if these codes can work for saving and retrieving files in SQL Mobile Database?

 

cos i encounter error when trying to save the file to sql mobile database. in my sql mobile database, i am using the datatype image instead of varbinary to store my pdf files. is tt correct?

 

Pls advise.

 

Thanks

Johnson

Answer 6

I'm not expert at SQL Server Moblie.

You can ask such a question at SQL Server Compact/Mobile Edition related forum.

Thanks

Answer 7

Hi Figo,

 

as u were saying we haf to read the buffer more than once if reading big int file rite??

 

wat abt writing the file?? do we have to incorporate the multiple write of the file if the file is big??

 

if yes do we use the same multiple read codes as the write?

 

Pls advise.

 

Thanks

Johnson

Answer 8

Johnson565581 wrote:

Hi Figo,   as u were saying we haf to read the buffer more than once if reading big int file rite??   wat abt writing the file?? do we have to incorporate the multiple write of the file if the file is big??   if yes do we use the same multiple read codes as the write?   Pls advise.   Thanks Johnson

Hi, Johnson

Very similar to the reading case:

Code Snippet

long length = buffer.Length;

int times = 0;

while(length > 1000)

{// once write 1000 bytes for example
   fs.Write(buffer, 1000*times, 1000);

  length -= 1000;

  times++;

}

fs.Write(buffer, 1000*times, length);

 

Thanks

Answer 9

What is maximum filestream read/write buffer size? Thanks. My English is bad, hope u help me.

 

Answer 10

Further more, if you want to save other sorts of files, you may add a column to specify the file sort for convertion.

To get out the file data, the process is to read the binary data out, and then convert it into stream:

Code Snippet

        SqlConnection connection = new SqlConnection ("...");
        connection.Open ();
        SqlCommand command = new
          SqlCommand ("select PdfFile from Table", connection);
        byte[] buffer = (byte[]) command.ExecuteScalar ();
        connection.Close();
        FileStream fs = new FileStream(@"C:\test.pdf", FileMode.Create);
        fs.Write(buffer, 0, buffer.Length);
        fs.Close();

Thanks

Thanks !!!!