CODEWITHSUNDEEP
imagesql-server-2005
vovkjn
vovkjn

Reputation: 99

How to get image from SQL Server

I have table called IMG and there are columns ID and Content that holds pictures.

How is it possible to get picture from there?

I googled for this problem and what all I got was vb.net, c# and php - mysql.

Maybe someone can say what is the best and easiest way to get picture (sample, copy/paste code, program)?

Thank you!

Upvotes: 0

Views: 11063

Answers (2)

vovkjn
vovkjn

Reputation: 99

after a lot of reading and work found the solution!

Here it is! Works 100%

      Dim conn, sql, a, filename
      Dim dir 
      dir = "c:\images\" //saves files into this directory
      Dim fileObj
      Set fileObj = CreateObject("Scripting.FileSystemObject")
      Set conn = CreateObject("ADODB.Connection")
      Dim strStream
      Set strStream = CreateObject("ADODB.Stream")
      Dim rstRecordset
      Set rstRecordset = CreateObject("ADODB.Recordset")
      conn.Open "Provider=SQLOLEDB;Data Source=yourserver;Integrated Security = SSPI","username","password"
      rstRecordset.Open "Select xxx, yyy, zzz from table (nolock) where xxx = '' order by xxx desc", conn    ', adOpenKeyset, adLockOptimistic
      Set fso = CreateObject("Scripting.FileSystemObject")

      While Not rstRecordset.EOF  
     filename = rstRecordset.Fields(0) 
     er = 0

Do
    er = er+1
Loop While (fso.FileExists(dir & filename & "_" & er  & ".JPG"))  

    filename = dir & filename & "_" & er  & ".JPG"
    strStream.Type = 1
    strStream.Open
    strStream.Write rstRecordset.Fields(2).Value
    strStream.SaveToFile filename
    strStream.Close
        rstRecordset.MoveNext
    Wend

Here it is!

Upvotes: 1

CloudyMarble
CloudyMarble

Reputation: 37566

try something like this (from d_r_w's answer):

SqlDataAdapter dataAdapter = new SqlDataAdapter(
   new SqlCommand("SELECT pic FROM imageTest WHERE pic_id = 1", 
     yourConnectionReference));

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);

if (dataSet.Tables[0].Rows.Count == 1)
{
    Byte[] data = new Byte[0];
    data = (Byte[])(dataSet.Tables[0].Rows[0]["pic"]);
    MemoryStream mem = new MemoryStream(data);
    yourPictureBox.Image= Image.FromStream(mem);
}

Take alook at the complete answer

More options to do this:

I found this post regards PHP but with MySQL(you need to change the connection to your server):

<?php 
// image.php - by Hermawan Haryanto &lt;[email protected]&gt; 
// Example PHP Script, demonstrating Storing Image in Database 
// Detailed Information can be found at http://www.codewalkers.com 

// database connection 
$conn = mysql_connect("localhost", "user", "password") 
  OR DIE (mysql_error()); 
@mysql_select_db ("hermawan", $conn) OR DIE (mysql_error()); 
$sql    = "SELECT * FROM image WHERE image_id=".$_GET["iid"]; 
$result = mysql_query ($sql, $conn); 
if (mysql_num_rows ($result)&gt;0) { 
  $row = @mysql_fetch_array ($result); 
  $image_type = $row["image_type"]; 
  $image = $row["image"]; 
  Header ("Content-type: $image_type"); 
  print $image; 
} 
?>

Upvotes: 1

Related Questions