locked
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
    Wednesday, May 30, 2007 2:54 PM

Answers

  • 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

    Friday, June 01, 2007 4:49 AM
  • 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 

    Friday, June 01, 2007 4:22 AM

All replies

  • 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 

    Friday, June 01, 2007 4:22 AM
  • 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

    Friday, June 01, 2007 4:49 AM
  • 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

    Tuesday, June 19, 2007 1:28 AM
  • 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

    Tuesday, June 19, 2007 2:01 AM
  • 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

    Tuesday, June 19, 2007 2:11 AM
  • I'm not expert at SQL Server Moblie.

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

    Thanks

    Tuesday, June 19, 2007 2:40 AM
  • 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

    Tuesday, June 19, 2007 2:48 AM
  •  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

    Tuesday, June 19, 2007 3:12 AM
  • What is maximum filestream read/write buffer size? Thanks. My English is bad, hope u help me.

     

    Monday, October 06, 2008 4:34 AM
  • 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 !!!!
    Friday, November 23, 2012 11:15 PM