none
Load Images file based on the File name in SQL server RRS feed

  • Question

  • Hi ,

    I want to load Image files from Directory to SQL server Varbinary type based on File name,
    Here how i was develop and not able to insert the image binary data into SQL server.

    Code: 

            public void InsertImageinSQL(object sender, EventArgs e)
            {
                string filepath = ConfigurationManager.AppSettings["SourceLocation"];
                if (System.IO.File.Exists(filepath))
                {
                    foreach (string file in Directory.EnumerateFiles(filepath, "*.jpg"))
                    {
                        try
                        {
                            using (SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Constr"].ToString()))
                            {
                                string result = Path.GetFileName(file);
                                Conn.Open();
                                string sql = "Update [Employee].[dbo].[EmployeeImage]set [EmpImage] =(select * from OPENROWSET(BULK '@imagebinary', SINGLE_BLOB) IMG_DATA) and [EmployeeID]='@SpecialCode';";
                                SqlCommand cmd = new SqlCommand(sql, Conn);
                                SqlParameter param = cmd.Parameters.Add("@imagebinary", SqlDbType.VarBinary);
                                param.Value = file;
                                SqlParameter param1 = cmd.Parameters.Add("@SpecialCode", SqlDbType.NVarChar);
                                param1.Value = result;
                                int retVal = cmd.ExecuteNonQuery();
                                Console.WriteLine("{0, -2} returned by {1}", retVal, cmd.CommandText);

                            }

                        }
                        catch (System.IO.IOException ex)
                        {
                            Console.WriteLine(ex.Message);
                            return;
                        }
                    }
                }
                else 
                {
                    Console.WriteLine("No Image File found");
                 
                }
            }

    Thanks

    venkat


    Regards Venkat

    Wednesday, September 11, 2019 4:38 AM

All replies

  • If not requirement to use OPENROWSET, then I would read the content of the file to byte array in code and use that as parameter value. For example:

    byte[] fileData;
    
    using (FileStream fs = File.OpenRead(file))
    using (MemoryStream ms = new MemoryStream())
    {
        fs.CopyTo(ms);
        fileData = ms.ToArray();
    }
    
    string sql = "Update [Employee].[dbo].[EmployeeImage] set [EmpImage] = @imagebinary and [EmployeeID]='@SpecialCode';";
    SqlCommand cmd = new SqlCommand(sql, Conn);
    SqlParameter param = cmd.Parameters.Add("@imagebinary", SqlDbType.VarBinary);
    param.Value = fileData;
    SqlParameter param1 = cmd.Parameters.Add("@SpecialCode", SqlDbType.NVarChar);
    param1.Value = result;
    int retVal = cmd.ExecuteNonQuery();
    Wednesday, September 11, 2019 5:07 AM
  • using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Runtime.InteropServices;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;
    using System.Net.Mail;
    using System.Diagnostics;
    using System.Drawing;

    namespace Tiger.BT.SAC.EmpImage
    {
        class Program1
        {

            //string filepath = (ConfigurationSettings.AppSettings["SourceLocation1"]);
            //public byte[] imageToByteArray(System.Drawing.Image filepath)
            //{
            //    MemoryStream ms = new MemoryStream();
            //    filepath.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            //    return ms.ToArray();
            //}
            //public Image byteArrayToImage(byte[] byteArrayIn)
            //{
            //    MemoryStream ms = new MemoryStream(byteArrayIn);
            //    Image returnImage = Image.FromStream(ms);
            //    return returnImage;
            //}

            public static void Main (string[] args)
            {
                string filepath = ConfigurationManager.AppSettings["SourceLocation"];
                if (System.IO.File.Exists(filepath))
                {
                    foreach (string file in Directory.EnumerateFiles(filepath, "*.jpg"))
                    {
                        try
                        {
                            using (SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Constr"].ToString()))
                            {

                                byte[] fileData;

                                using (FileStream fs = File.OpenRead(file))
                                using (MemoryStream ms = new MemoryStream())
                                {
                                    fs.CopyTo(ms);
                                    fileData = ms.ToArray();
                                }

                                string result = Path.GetFileName(file);
                                Conn.Open();
                               // string sql = "Update [Employee].[dbo].[EmployeeImage]set [EmpImage] =(select * from OPENROWSET(BULK '@imagebinary', SINGLE_BLOB) IMG_DATA) where [EmployeeID]='@SpecialCode';";
                                string sql = "INSERT INTO  [Employee].[dbo].[EmployeeImage] ([EmployeeID],[EmpImage]) values(@imagebinary, @SpecialCode)";                                                  

                                SqlCommand cmd = new SqlCommand(sql, Conn);
                                SqlParameter param = cmd.Parameters.Add("@imagebinary", SqlDbType.VarBinary);
                                //param.Value = file;
                                param.Value = fileData;
                                SqlParameter param1 = cmd.Parameters.Add("@SpecialCode", SqlDbType.NVarChar);
                                param1.Value = result;
                                int retVal = cmd.ExecuteNonQuery();
                                Console.WriteLine("{0, -2} returned by {1}", retVal, cmd.CommandText);

                            }

                        }
                        catch (System.IO.IOException ex)
                        {
                            Console.WriteLine(ex.Message);
                            return;
                        }
                    }
                }
                else 
                {
                    Console.WriteLine("No Image File found");

                }
            }

            }
        }

    error : 

    The thread 0x13e8c has exited with code 259 (0x103).
    The thread 0x10170 has exited with code 259 (0x103).
    The thread 0x16558 has exited with code 0 (0x0).
    The thread 0x1185c has exited with code 259 (0x103).
    'Tiger.BT.Gaming.ExcelToCSV.vshost.exe' (CLR v4.0.30319: Tiger.BT.Gaming.ExcelToCSV.vshost.exe): Loaded 'C:\Venkat\Tiger.BT.Gaming.ExcelToCSV\Tiger.BT.Gaming.ExcelToCSV\bin\Debug\Tiger.BT.Gaming.ExcelToCSV.exe'. Symbols loaded.
    The thread 0x8b4 has exited with code 259 (0x103).
    The thread 0xe010 has exited with code 259 (0x103).
    The program '[24952] Tiger.BT.Gaming.ExcelToCSV.vshost.exe' has exited with code 0 (0x0).
    The program '[24952] Tiger.BT.Gaming.ExcelToCSV.vshost.exe: Program Trace' has exited with code 0 (0x0).

    Please help me on this..

    Thanks

    Venkat

                                           

    Regards Venkat

    Wednesday, September 18, 2019 1:35 AM
  • Hello,

    I have a code sample that does one image at a time which includes database scripts.

    https://code.msdn.microsoft.com/SQL-Server-insert-binary-0de8aef3?redir=0

    To make this work in a loop, create the connection, command then the parameters. In the loop set parameter values and do a ExecuteNonQuery or do the INSERT as follows.

    var statement = "INSERT INTO Table1 (FileContents,FileName) VALUES (@FileContents,@FileName);SELECT CAST(scope_identity() AS int);"; 

    Then use Convert.ToInt32(cmd.ExecuteScalar());  to insert and get the new primary key.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 18, 2019 2:11 AM
    Moderator
  • Thanks for share but here we are looking in folder and have multiple files .. we have to implement in Multithreading i think .. any way thanks for share.. :)

    Regards Venkat

    Wednesday, September 18, 2019 3:09 AM
  • Hi VENKAT

    Thank you for posting here.

    First, I want to mention that you could use Directory.Exists instead of File.Exits if you want to check if the directory is null.

    Second, about the error you provided, I think those are not errors. It is a common code when you finish your program.

    Third, I have updated the code you provided, which could insert the image binary data into SQL server.

     static void Main(string[] args)
            {
                string connectionstring = @"connectionstring";
                string filepath = @"D:\t"; 
                if (System.IO.Directory.Exists(filepath))
                {
                    foreach (string file in Directory.EnumerateFiles(filepath, "*.jpg"))
                    {
                        try
                        {
                            using (SqlConnection Conn = new SqlConnection(connectionstring))
                            {
    
                                byte[] fileData;
    
                                using (FileStream fs = File.OpenRead(file))
                                using (MemoryStream ms = new MemoryStream())
                                {
                                    fs.CopyTo(ms);
                                    fileData = ms.ToArray();
                                }
    
                                string result = Path.GetFileName(file);
                                Conn.Open();
                                
                                string sql = "insert into Employee (EmpImage,Emplfilename) values(@EmpImage,@Emplfilename)";
    
                                SqlCommand cmd = new SqlCommand(sql, Conn);
                                cmd.Connection = Conn;
                                SqlParameter param = cmd.Parameters.Add("@EmpImage", SqlDbType.VarBinary);
                                param.Value = fileData;
                                SqlParameter param1 = cmd.Parameters.Add("@Emplfilename", SqlDbType.NVarChar);
                                param1.Value = result;
                                int retVal = cmd.ExecuteNonQuery();
                                Console.WriteLine("{0, -2} returned by {1}", retVal, cmd.CommandText);
    
                            }
    
                        }
                        catch (System.IO.IOException ex)
                        {
                            Console.WriteLine(ex.Message);
                            return;
                        }
                    }
                }
                else
                {
                    Console.WriteLine("No Image File found");
    
                }
            }

    Database design:

    Result after inserting:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 19, 2019 7:07 AM
    Moderator
  • There are no errors shown in your output log, and you didn't show us the console output produced by your program, so we can't see anything.  Are you saying your program didn't work?

    Tim Roberts | Driver MVP Emeritus | Providenza & Boekelheide, Inc.

    Thursday, September 19, 2019 7:20 PM
  • Thanks Jack, it works and i work around that as per my requirement.. thank you very much.

    Regards Venkat

    Friday, September 20, 2019 1:15 AM
  • Hi 

    Thanks for the feedback.

    I am glad that your question has been solved. If so, please post "Mark as answer" to the appropriate answer. So that it will help other members to find the solution quickly if they face the similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 20, 2019 1:17 AM
    Moderator