Load Images file based on the File name in SQL server

[VENKAT YADAV]

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

Continue reading...