Retrieveing binary data out of the database

[Mitja Bonca]

1. How to retrieve stored file our of the database?

have stored (for example) a pdf file into database, as a data type of varbinary(MAX) - binary data. How can I get this file back, like it was before?

Important things I have stored for the particular file are:

- ID

- File name

- File type (pdf, word, ... document)

- and of course the content (which is now in binary data)

2. One more small thing:

I would like to get the ID from a Method1 which retrieves the ID, into another Method2 (in the same class), where I will use this ID as a variable.

I have this code for examle:

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            int IDDocument; //How to get into IDDocument an ID value from the GetID method (from int Doc_ID)
            GetID(IDDocument);  //I got an error here: Use of unassigned local variable 'IDDocument
            string SQL = "SELECT Content FROM Documents WHERE DocumentID = '" + IDDocoment + "'";
            //...
        }

        private void GetID(int Doc_ID)
        {
            string DocID = "SELECT DocumentID FROM Documents WHERE Name = '" + listBox1.SelectedItem + "'";
            SqlCommand cmd = new SqlCommand(DocID, sqlConn);
            cmd.CommandType = CommandType.Text;
            sqlConn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                Doc_ID = reader.GetInt32(0);
            }
            reader.Close();
            sqlConn.Close();
        }

[sknake]

PDF, XLS, DOC, DOCX, it doesn't matter. The image is stored as binary on the SQL server. You just need to pull down the byte[] array and store it to disk with the proper file extension. Here is an example:

//Download image
    private void button2_Click(object sender, EventArgs e)
    {

      const string connStr = "Data Source=apex2006sql;Initial Catalog=ServManLeather;Integrated Security=True;";
      const string query = "Select ImgNumber, FacilityImg From FacilityImg Where ImgNumber = 1000";
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
              //File.WriteAllBytes(@"C:\picture.bmp", (byte[])dt.Rows[0]["FacilityImg"]);
              using (MemoryStream ms = new MemoryStream((byte[])dt.Rows[0]["FacilityImg"]))
              {
                ms.Position = 0;
                ms.Seek(0, SeekOrigin.Begin);
                pictureBox1.Image = System.Drawing.Image.FromStream(ms);
                ms.Flush();
                ms.Close();
              }
            }
          }
        }
      }
    }

I notice in your code you are not using using() blocks or calling .Dispose() on classes that implement IDisposable . You should read up on that to prevent leaking unmanaged resources ;)

For you next question you need to use an out parameter to indicate that the value will be set in the called method. Example:

private void button1_Click(object sender, EventArgs e)
    {
      int IDDocument;
      GetID(out IDDocument);
      MessageBox.Show(IDDocument.ToString());
    }


    private void GetID(out int Doc_ID)
    {
      Doc_ID = 2;
    }

[Mitja Bonca]

For you next question you need to use an out parameter to indicate that the value will be set in the called method. Example:

private void button1_Click(object sender, EventArgs e)
    {
      int IDDocument;
      GetID(out IDDocument);
      MessageBox.Show(IDDocument.ToString());
    }


    private void GetID(out int Doc_ID)
    {
      Doc_ID = 2;
    }

Ok, but I do not have just Doc_ID = 2; in my GetID method.
I have this:

string DocID = "SELECT DocumentID FROM Documents WHERE Name = '" + listBox1.SelectedItem + "'";  
            SqlCommand cmd = new SqlCommand(DocID, sqlConn);  
            cmd.CommandType = CommandType.Text;  
            sqlConn.Open();  
            SqlDataReader reader = cmd.ExecuteReader();  
            if (reader.Read())  
            {  
                Doc_ID = reader.GetInt32(0);  
            }  
            reader.Close();  
            sqlConn.Close();

In my case I got an error:
The out parameter 'Doc_ID' must be assigned to before control leaves the current method.

[sknake]

So fix the error....

string DocID = "SELECT DocumentID FROM Documents WHERE Name = '" + listBox1.SelectedItem + "'";  
            SqlCommand cmd = new SqlCommand(DocID, sqlConn);  
            cmd.CommandType = CommandType.Text;  
            sqlConn.Open();  
            SqlDataReader reader = cmd.ExecuteReader();  
            if (reader.Read())  
            {  
                Doc_ID = reader.GetInt32(0);  
            }  
            else
            {
                 Doc_ID = -1;
             }
            reader.Close();  
            sqlConn.Close();

[Mitja Bonca]

Thank you, will remember for the next time. :)