Create Sales Invoice Report using Crystal Report in C# windows application
Introduction:
Today in this article, I will explain How to create sales invoice/receipt using crystal report in c# windows application with the stored procedure.
Crystal Reports is a great business objects tool available in Visual Studio to build reports. Crystal Reports is used for building simple to complex reports including daily reports as sales reports, and stock reports.
Follow these steps in order to implement “Create sales invoice using crystal report in c# windows application”
Step1: Create New Project.
Go to File > New > Project > Windows > Windows form Application> Enter Application Name > Select your project location > then click to ok
Step2: Create a table and Stored procedure in the database.
In this example, I have used following table for creating an invoice using crystal report.
Customer:
CREATE TABLE [dbo].[tbl_Customer] (
[Customer_id] INT IDENTITY (1, 1) NOT NULL,
[First_Name] NVARCHAR (MAX) NULL,
[Last_Name] NVARCHAR (MAX) NULL,
[Billing_Address] NVARCHAR (MAX) NULL,
[Postal_Code] NVARCHAR (MAX) NULL,
[Mobile_Number] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_tbl_Customer] PRIMARY KEY CLUSTERED ([Customer_id] ASC)
);
Invoice Product Detail:
CREATE TABLE [dbo].[tbl_Sales_Invoice_Product] (
[Sr_no] INT IDENTITY (1, 1) NOT NULL,
[InvoiceID] NVARCHAR (50) NULL,
[ProductID] INT NULL,
[ProductName] NVARCHAR (MAX) NULL,
[Quantity] INT NULL,
[Amount] FLOAT (53) NULL,
[Discountper] FLOAT (53) NULL,
[Total_Amount] FLOAT (53) NULL,
PRIMARY KEY CLUSTERED ([Sr_no] ASC)
);
Invoice detail:
CREATE TABLE [dbo].[tbl_Sales_Invoice_Info] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[InvoiceID] NVARCHAR (50) NOT NULL,
[InvoiceDate] DATE NULL,
[Customer_id] INT NULL,
[Grand_Total] FLOAT (53) NULL,
[Total_paid] FLOAT (53) NULL,
[Balance] FLOAT (53) NULL,
PRIMARY KEY CLUSTERED ([InvoiceID] ASC)
);
Now I have to create the stored procedure for fetching record:
Get Customer Detail
Create proc Sp_Customer_id
@Cust_id int
as
begin
Select * from tbl_Customer where Customer_id=@Cust_id
End
Get Invoice product Detail
CREATE proc Sp_InvoiceProduct_InvoiceID
@InvoiceID nvarchar(50)
as
begin
Select * from tbl_Sales_Invoice_Product where InvoiceID=@InvoiceID
end
Get Invoice Detail
CREATE proc Sp_Sales_InvoiceInfo_invid
@Invoice_id nvarchar(50)
as
begin
Select * from tbl_Sales_Invoice_info where InvoiceID=@Invoice_id
endRun above script in MS SQL Server and click to execute button.
Step5: Create a class for Database operation.
Here in this example, I have used Ado.net as database operation so we need to create a class for all database operations. Here I have created ‘db.cs’ class.
Now write the following code snippet given below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace InvoiceWindows
{
class db
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=BillingWdb;Integrated Security=True");
public DataSet Invoice_product(int invid)
{
SqlCommand com = new SqlCommand("Sp_InvoiceProduct_InvoiceID", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@InvoiceID", invid);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public DataSet Customer_info(int cust_id)
{
SqlCommand com = new SqlCommand("Sp_Customer_id", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Cust_id", cust_id);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public DataSet Inv_info(int invid)
{
SqlCommand com = new SqlCommand("Sp_Sales_InvoiceInfo_invid", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Invoice_id", invid);
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
Step6: Add Class for Invoice Detail.
You can add a class for binding the Invoice details. Here I have added ‘InvoiceDetail.cs’
Now write the following code snippet given below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace InvoiceWindows
{
class InvoiceDetail
{
public string ProductID { get; set; }
public string ProductName { get; set; }
public string Quantity { get; set; }
public string Amount { get; set; }
public string Discountper { get; set; }
public int totalamount { get; set; }
}
}
Step7: Add Crystal report
Now I have to add Crystal report in a project with following procedure.
Go to Solution Explorer > Add New Item. Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.
Select Report type from Crystal report gallery
Create the following format in Crystal Report
Step8: Add Windows Form for display crystal report
Go to Solution Explorer > Right Click >Add > New Item > Windows Form
Now write the following code snippet given below.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace InvoiceWindows
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
db dblayer = new db();
private void Form1_Load(object sender, EventArgs e)
{
List<InvoiceDetail> _List = new List<InvoiceDetail>();
DataSet ds = dblayer.Invoice_product(28);
foreach (DataRow dr in ds.Tables[0].Rows)
{
_List.Add(new InvoiceDetail
{
ProductID=dr["ProductID"].ToString(),
ProductName = dr["ProductName"].ToString(),
Quantity = dr["Quantity"].ToString(),
Amount = dr["Amount"].ToString(),
Discountper = dr["Discountper"].ToString(),
totalamount = Convert.ToInt32(dr["Total_Amount"])
});
}
DataSet ds2 = dblayer.Customer_info(2);
foreach (DataRow dr in ds2.Tables[0].Rows)
{
invoice1.SetDataSource(_List);
invoice1.SetParameterValue("pCustomer",dr["First_Name"].ToString()+" "+dr["Last_Name"].ToString());
invoice1.SetParameterValue("pAddress",dr["Billing_Address"].ToString());
invoice1.SetParameterValue("pPostalCode",dr["Postal_Code"].ToString());
invoice1.SetParameterValue("pPhoneNumber",dr["Mobile_Number"].ToString());
invoice1.SetParameterValue("pOrder_id",28);
DataSet ds3 = dblayer.Inv_info(28);
foreach (DataRow dr1 in ds3.Tables[0].Rows)
{
invoice1.SetParameterValue("pDate",dr1["InvoiceDate"].ToString());
invoice1.SetParameterValue("Grand_total",dr1["Grand_Total"].ToString());
invoice1.SetParameterValue("pTotal_Paid",dr1["Total_paid"].ToString());
invoice1.SetParameterValue("pBalance",dr1["Balance"].ToString());
}
crystalReportViewer1.ReportSource=invoice1;
}
}
}
}
Step 10: Run Application
Now it’s time to run the application and got following output
f you have any problem and face any difficulty then Share your experiences with us in the comments below!
Like this post? Don’t forget to share it!
