How to Upload and Save Image in SQL Server as BLOB Data with Asp.Net

Question

I want to upload image and save in sql server as BLOB Data using .Net Web Api

Brahma Prakash Shukla · Answer

To upload and save an image in SQL Server as BLOB (Binary Large Object) data using ASP.NET, you can follow these steps: Create a table in your SQL Server database to store the image data. The table should have a column of type varbinary(MAX) to hold the binary image data. You can also include additional columns to store other relevant information about the image, such as filename, description, etc. Create an ASP.NET web form or MVC view with a file upload control to allow users to select the image file. Handle the file upload in the server-side code (C#) of your ASP.NET application. Here's an example of how you can handle the file upload and save the image as BLOB data: using System; using System.Data.SqlClient; using System.IO; protected void UploadButton_Click(object sender, EventArgs e) { if (ImageFileUpload.HasFile) { // Get the file name and extension string fileName = ImageFileUpload.FileName; string fileExtension = Path.GetExtension(fileName); // Create a byte array to hold the image data byte[] imageData = new byte[ImageFileUpload.PostedFile.ContentLength]; // Read the file data into the byte array using (Stream inputStream = ImageFileUpload.PostedFile.InputStream) { inputStream.Read(imageData, 0, imageData.Length); } // Save the image data to the database string connectionString = "your_connection_string_here"; string insertQuery = "INSERT INTO Images (FileName, ImageData) VALUES (@FileName, @ImageData)"; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(insertQuery, connection); command.Parameters.AddWithValue("@FileName", fileName); command.Parameters.AddWithValue("@ImageData", imageData); connection.Open(); command.ExecuteNonQuery(); } // Display a success message or perform any other actions UploadStatusLabel.Text = "Image uploaded successfully!"; } else { // Display an error message or perform any other actions UploadStatusLabel.Text = "Please select an image to upload."; } }

Prasad Raveendran · Answer

please review the sample code attached

Sandeep Kumar · Answer

how to save image in folder path

Prasad Raveendran · Answer

Please try with below updated code. DataTable DtPhoto = new DataTable(); DtPhoto.Columns.Add("FilePath", typeof(string)); List returnVal = new List (); var ID = HttpContext.Current.Request.Form["ID"]; var StationId = HttpContext.Current.Request.Form["StationId"]; var UnitId = HttpContext.Current.Request.Form["UnitId"]; var OutageTypeId = HttpContext.Current.Request.Form["OutageTypeId"]; var ZoneId = HttpContext.Current.Request.Form["ZoneId"]; var FinYear = HttpContext.Current.Request.Form["FinYear"]; var Photo_Tag = HttpContext.Current.Request.Form["PhotoTag"]; var httpRequest = HttpContext.Current.Request; var filePath = string.Empty; if (httpRequest.Files.Count > 0) { foreach (string file in httpRequest.Files) { Stream fs = httpRequest.Files[file].InputStream; BinaryReader br = new BinaryReader(fs); byte[] imagecode = br.ReadBytes((Int32)fs.Length); DtPhoto.Rows.Add(Convert.ToBase64String(imagecode)); } } try { using (SqlConnection con = new SqlConnection(strConnString)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = DBProcedure.SP_InsertUpdatePmsData; cmd.Parameters.Add("@PmsDataID ", SqlDbType.Int).Value = ID; cmd.Parameters.Add("@StationID", SqlDbType.Int).Value = StationId; cmd.Parameters.Add("@UnitID", SqlDbType.Int).Value = UnitId; cmd.Parameters.Add("@ZoneID", SqlDbType.Int).Value = ZoneId; cmd.Parameters.Add("@OutageTypeID", SqlDbType.Int).Value = OutageTypeId; cmd.Parameters.Add("@FinYear", SqlDbType.NVarChar).Value = FinYear; cmd.Parameters.Add("@PhotoTag", SqlDbType.NVarChar).Value = Photo_Tag; cmd.Parameters.Add("@ImageList", SqlDbType.Structured).Value = DtPhoto; cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = UserId; cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Value = 0; cmd.Parameters["@ReturnVal"].Direction = ParameterDirection.InputOutput; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); returnVal.Add(Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value.ToString())); } } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } return returnVal; After reading the image data as a byte array using BinaryReader , you are adding the byte array to the DtPhoto DataTable using DtPhoto.Rows.Add(imagecode) . However, the DataTable expects a string value for the "FilePath" column, not a byte array. If you intend to store the byte array in the "FilePath" column, you should convert it to a string before adding it to the DataTable. Modifed the code as follows: DtPhoto.Rows.Add(Convert.ToBase64String(imagecode));

Deepak Rawat · Answer

[HttpPost] public IHttpActionResult UploadImage() { try { HttpRequestMessage request = HttpContext.Current.Items["MS_HttpRequestMessage"] as HttpRequestMessage; if (!request.Content.IsMimeMultipartContent()) { throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType); } var provider = new MultipartFormDataStreamProvider(Path.GetTempPath()); var result = Request.Content.ReadAsMultipartAsync(provider).Result; var file = result.FileData.FirstOrDefault(); if (file != null) { string filePath = file.LocalFileName; byte[] imageData = File.ReadAllBytes(filePath); // Save the image to the database using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string query = "INSERT INTO Images (ImageData) VALUES (@ImageData)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@ImageData", imageData); command.ExecuteNonQuery(); } // Optionally, delete the temporary file File.Delete(filePath); return Ok("Image uploaded successfully"); } throw new HttpResponseException(HttpStatusCode.BadRequest); } catch (Exception ex) { return InternalServerError(ex); } }

Sandeep Kumar · Answer

My Code is Like but not working DataTable DtPhoto = new DataTable(); DtPhoto.Columns.Add("FilePath", typeof(System.String)); List returnVal = new List (); var ID = HttpContext.Current.Request.Form["ID"]; var StationId = HttpContext.Current.Request.Form["StationId"]; var UnitId = HttpContext.Current.Request.Form["UnitId"]; var OutageTypeId = HttpContext.Current.Request.Form["OutageTypeId"]; var ZoneId = HttpContext.Current.Request.Form["ZoneId"]; var FinYear = HttpContext.Current.Request.Form["FinYear"]; var Photo_Tag = HttpContext.Current.Request.Form["PhotoTag"]; var httpRequest = HttpContext.Current.Request; var filePath = string.Empty; if (httpRequest.Files.Count > 0) { foreach (string file in httpRequest.Files) { Stream fs = httpRequest.Files[file].InputStream; BinaryReader br = new BinaryReader(fs); byte[] imagecode = br.ReadBytes((Int32)fs.Length); //postedFile.SaveAs(filePath); DtPhoto.Rows.Add(imagecode); } } try { using (SqlConnection con = new SqlConnection(strConnString)) { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = DBProcedure.SP_InsertUpdatePmsData; cmd.Parameters.Add("@PmsDataID ", SqlDbType.Int).Value = ID; cmd.Parameters.Add("@StationID", SqlDbType.Int).Value = StationId; cmd.Parameters.Add("@UnitID", SqlDbType.Int).Value = UnitId; cmd.Parameters.Add("@ZoneID", SqlDbType.Int).Value = ZoneId; cmd.Parameters.Add("@OutageTypeID", SqlDbType.Int).Value = OutageTypeId; cmd.Parameters.Add("@FinYear", SqlDbType.NVarChar).Value = FinYear; cmd.Parameters.Add("@PhotoTag", SqlDbType.NVarChar).Value = Photo_Tag; cmd.Parameters.Add("@ImageList", SqlDbType.Structured).Value = DtPhoto; cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = UserId; cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Value = 0; /////// cmd.Parameters.Add("@ReturnPmsDataID", SqlDbType.Int).Value = 0; cmd.Parameters["@ReturnVal"].Direction = ParameterDirection.InputOutput; // cmd.Parameters["@ReturnPmsDataID"].Direction = ParameterDirection.InputOutput; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); returnVal.Add(Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value.ToString())); //returnVal.Add(Convert.ToInt32(cmd.Parameters["@ReturnPmsDataID"].Value.ToString())); } } } catch (Exception ex) { } return returnVal;

Rajkiran Swain · Answer

public class ImageModel { public byte[] ImageData { get; set; } } public class ImageController : ApiController { [HttpPost] public IHttpActionResult UploadImage(ImageModel model) { // Save the image data to the database SaveImageDataToDatabase(model.ImageData); return Ok("Image uploaded successfully"); } private void SaveImageDataToDatabase(byte[] imageData) { // Establish a connection to your SQL Server database using (SqlConnection connection = new SqlConnection("your_connection_string")) { // Create a SQL command to insert the image data into the table string query = "INSERT INTO YourTable (ImageData) VALUES (@ImageData)"; using (SqlCommand command = new SqlCommand(query, connection)) { // Add the image data as a parameter command.Parameters.AddWithValue("@ImageData", imageData); // Open the connection and execute the command connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } } }