Import image from Excel sheet into SQL Server table C#

This code is help to insert image from Excel to sql Database

private void insert_Click(object sender, EventArgs e)
{
UInt64 ID = 0;
String Name = String.Empty;
String Designation = String.Empty;
String ProfilePicture = String.Empty;
String filePath = filePathText.Text;
Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel._Worksheet xlWorksheet = null;
Excel.Range xlRange = null;
String sqlConnectionString = "Your Connection String goes here";
String insertRecord = "INSERT_USER_RECORDS";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
SqlCommand sqlCommand = new SqlCommand(insertRecord, sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
if (filePath != null)
{
try
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(filePath);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{
MessageBox.Show((xlRange.Cells[row, col] as Microsoft.Office.Interop.Excel.Range).Value2.ToString());
// Check xlRange for Every run. And assign values to local variables. Here I just show the values using MsgBox
// If you get the Path of Image then call the function to Convert Image into byte
// Convert Image to Byte Function definition.
/* System.IO.FileStream fs = new System.IO.FileStream(ProfilePicture, System.IO.FileMode.Open);
Byte[] imageAsBytes = new Byte[fs.Length];
fs.Read(imageAsBytes, 0, imageAsBytes.Length);
fs.Close();
return imageAsBytes; */
}
sqlCommand.Parameters.Clear();
sqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = FirstName;
sqlCommand.Parameters.Add("@Designation", SqlDbType.NVarChar).Value = LastName;
sqlCommand.Parameters.Add("@ProfilePicture", SqlDbType.VarBinary).Value = imageAsBytes;
sqlCommand.Parameters.Add("@ID", SqlDbType.BigInt).Value = ID;
sqlCommand.ExecuteNonQuery();
}
MessageBox.Show(Path.GetFileName(filePath) + "is Successfully imported to SQL Server", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Release All objects and close the Connection to prevent the Excel file from lock.
sqlConnection.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(xlRange);
Marshal.FinalReleaseComObject(xlWorksheet);
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
}
else
{
MessageBox.Show("Please Select the Valid file to import");
}
}

This code is work fine and no matter of about the version of Excel

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pradeep Rawat5y ago
anyone can help me out of this scenerio .
+1
Pradeep Rawat5y ago
thanks for the reply, As per suggested code, instead of images they refred images of the path. how to write the images directly.
+2
Sanjay Kumar5y ago
This code is help to insert image from Excel to sql Database
private void insert_Click(object sender, EventArgs e)
{
UInt64 ID = 0;
String Name = String.Empty;
String Designation = String.Empty;
String ProfilePicture = String.Empty;
String filePath = filePathText.Text;
Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel._Worksheet xlWorksheet = null;
Excel.Range xlRange = null;
String sqlConnectionString = "Your Connection String goes here";
String insertRecord = "INSERT_USER_RECORDS";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
SqlCommand sqlCommand = new SqlCommand(insertRecord, sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnection.Open();
if (filePath != null)
{
try
{
xlApp = new Excel.Application();
xlWorkbook = xlApp.Workbooks.Open(filePath);
xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{
MessageBox.Show((xlRange.Cells[row, col] as Microsoft.Office.Interop.Excel.Range).Value2.ToString());
// Check xlRange for Every run. And assign values to local variables. Here I just show the values using MsgBox
// If you get the Path of Image then call the function to Convert Image into byte
// Convert Image to Byte Function definition.
/* System.IO.FileStream fs = new System.IO.FileStream(ProfilePicture, System.IO.FileMode.Open);
Byte[] imageAsBytes = new Byte[fs.Length];
fs.Read(imageAsBytes, 0, imageAsBytes.Length);
fs.Close();
return imageAsBytes; */
}
sqlCommand.Parameters.Clear();
sqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar).Value = FirstName;
sqlCommand.Parameters.Add("@Designation", SqlDbType.NVarChar).Value = LastName;
sqlCommand.Parameters.Add("@ProfilePicture", SqlDbType.VarBinary).Value = imageAsBytes;
sqlCommand.Parameters.Add("@ID", SqlDbType.BigInt).Value = ID;
sqlCommand.ExecuteNonQuery();
}
MessageBox.Show(Path.GetFileName(filePath) + "is Successfully imported to SQL Server", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//Release All objects and close the Connection to prevent the Excel file from lock.
sqlConnection.Close();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(xlRange);
Marshal.FinalReleaseComObject(xlWorksheet);
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlWorkbook);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
}
else
{
MessageBox.Show("Please Select the Valid file to import");
}
}
This code is work fine and no matter of about the version of Excel
-1
Kiran Mohanty5y ago
https://stackoverflow.com/questions/19074521/import-image-from-excel-sheet-into-sql-server-table-c-sharp
+1