Insert and Retrieve images to the Crystal Report using ASP.NET from database
In this article we will discuss that how to store values and image to SQL Database and how to retrieve the values and image from the database and show it into the crystal report using ASP.NET. This is not a complex job to insert binary value to the database and retrieve it. This was like a magic for me when I completed this task.
Step 1:
Create One Web Form
Step 2:
Design the form like this
Download design code of web form for your reference from below link
Code to design the web form
Step 3:
Add crystal report to the project and design like this
Step 4:
Design your database like this
Step 5:
Add reference of CrystalDecisions.CrystalReports.Engine.dll to the project and add the below namespace in class file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;
// Create an object of Sql Connection
SqlConnection con = new SqlConnection("your sql database connection string");
Step 6:
Write the below code on click event of Upload data button
protected void ButtonUpload_Click(object sender, EventArgs e)
{
string filePath = FileUpload1.PostedFile.FileName;
string filename = Path.GetFileName(filePath);
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
con.Open();
SqlCommand mycmd = con.CreateCommand();
SqlTransaction dBupdate;
dBupdate = con.BeginTransaction(IsolationLevel.Serializable);
mycmd.Connection = con;
mycmd.Transaction = dBupdate;
try
{
//insert the file into database
mycmd.CommandText = "INSERT INTO MemberDetails(Member_Name, Member_Designation, Member_Photo, Photo_Path) VALUES (@Name, @Designation, @Photo, @PhotoPath)";
int imglength = FileUpload1.PostedFile.ContentLength;
byte[] imgbytes = new byte[imglength];
string imgname = Path.GetFileName(FileUpload1.PostedFile.FileName);
string imgpath = "~/Member Photos/" + imgname;
mycmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text;
mycmd.Parameters.Add("@Designation", SqlDbType.VarChar).Value = txtDesignation.Text;
mycmd.Parameters.Add("@Photo", SqlDbType.Binary).Value = bytes;
mycmd.Parameters.Add("@PhotoPath", SqlDbType.VarChar).Value = imgpath;
mycmd.ExecuteNonQuery();
dBupdate.Commit();
//saving image to server location
FileUpload1.SaveAs(Server.MapPath("~/Member Photos/" + imgname));
txtName.Text = "";
txtDesignation.Text = "";
con.Close();
txtName.Focus();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>");
dBupdate.Rollback();
}
finally
{
con.Close();
}
}
Step 7:
Write the below code on click event of View Report button
protected void ViewReport_Click(object sender, EventArgs e)
{
try
{
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM MemberDetails", con);
da.Fill(dt);
if (dt.Rows.Count > 0)
{
ReportDocument RptDoc = new ReportDocument();
RptDoc.Load(Server.MapPath("~/ImageCrystalReport.rpt")); //your crystal Report name
RptDoc.SetDataSource(dt);
CrystalReportViewer1.ReportSource = RptDoc;
CrystalReportViewer1.DataBind();
}
}
catch (Exception ex)
{
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>");
}
finally
{
con.Close();
}
}
You are done. Now run your application and you will get the output like this
Click here to get source code
Follow