DaniWeb Forum Index > Software Development > C#

Retrieveing binary data out of the database

Please support our C# advertiser: Programming Forums - DaniWeb Sister Site

Thread Solved

•

Join Date: May 2009

Posts: 74

Reputation: Mitja Bonca is an unknown quantity at this point

Solved Threads: 0

Mitja Bonca Mitja Bonca is offline

Offline

Junior Poster in Training

Retrieveing binary data out of the database

Nov 5th, 2009

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
        }
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();
        }

•

Join Date: Feb 2009

Posts: 3,460

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Offline

.NET Enthusiast

Nov 5th, 2009

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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
    //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();
              }
            }
          }
        }
      }
    }
    //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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
    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;
    }
    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;
    }

Last edited by sknake; Nov 5th, 2009 at 4:48 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: May 2009

Posts: 74

Reputation: Mitja Bonca is an unknown quantity at this point

Solved Threads: 0

Mitja Bonca Mitja Bonca is offline

Offline

Junior Poster in Training

Nov 5th, 2009

•

Originally Posted by sknake

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

C# Syntax (Toggle Plain Text)
    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;
    }
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:

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 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();
 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.

Last edited by Mitja Bonca; Nov 5th, 2009 at 6:19 pm.

•

Join Date: Feb 2009

Posts: 3,460

Reputation: sknake has much to be proud of

Solved Threads: 629

sknake

Offline

.NET Enthusiast

Nov 6th, 2009

So fix the error....

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
 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();
 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();