Ad:

C# Discussion Thread
Marked Solved
Views: 1218

Nov 5th, 2009

Retrieveing binary data out of the database

Expand Post »

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:

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();
        }

Similar Threads

Login and retrieve user data from database in ASP.NET
update sql server data database in c# in C#
Storing Binary Data in MySQL in MySQL
converting binary data into image in asp.net(vb code) in ASP.NET
Reading binary data from SPROC..help please... in C#

Mitja Bonca

Reputation Points: 537

Solved Threads: 398

Postaholic

Offline

2,010 posts
since May 2009

Permalink

Nov 5th, 2009

Re: Retrieveing binary data out of the database

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:

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:

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 5th, 2009

Re: Retrieveing binary data out of the database

Click to Expand / Collapse Quote 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:
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:

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.

Mitja Bonca

Reputation Points: 537

Solved Threads: 398

Postaholic

Offline

2,010 posts
since May 2009

Permalink

Nov 6th, 2009

Re: Retrieveing binary data out of the database

So fix the error....

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();

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 6th, 2009

Re: Retrieveing binary data out of the database

Thank you, will remember for the next time.

Mitja Bonca

Reputation Points: 537

Solved Threads: 398

Postaholic

Offline

2,010 posts
since May 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Enable Macros

Next Thread in C# Forum Timeline: C# Multi-form pre-render?

DaniWeb Message

Cancel Changes

User Name
Password