View Report from Crystal report in asp.net using c# connect to SQL Server

 Hi guys! In this article show how to view report from crystal report in asp.net using c# connect to SQL Server 2008 on event page load.

 I want to display invoice page when page load that invoice was already designed in crystal report 11 which file's name is invoice.rpt.

 I have two functions: function for connect to database and function for load data to dataset. If you want to see more description about function for connect to database click here.

To make it work you need to import some namespaces into your page:

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportSource;

using    System.Data;

using    System.Data.SqlClient;

Function connect to database:  

public SqlConnection ConnectToDB()   {

           SqlConnection  sqlcon =  new  SqlConnection();
          // SqlConnection : Represents an open connection to a SQL Server database.
     
  sqlcon.ConnectionString="Server= UBEE_THAROTH ;Database=Drink; uid=sa; pwd=123;";
      try{
                    //open connection to SQL Server 
                    sqlcon.Open();
                    return cn;
            }catch (Exception ex){
                   // return null if can not connect to database in SQL Sever 2008 
                   return null;
        }

   }

Function for load data into dataset: 

public DataSet viewinvoice(SqlConnection sqlcon) {

    SqlCommand com;

    SqlDataAdapter da = new SqlDataAdapter();

    DataSet ds = new DataSet();           

    try

    {

        com = new SqlCommand("sp_reportInvoice", sqlcon);

        //sp_reportInvoice is stored procedure's  name 

        com.CommandType = CommandType.StoredProcedure;

        da.SelectCommand = com;

        da.Fill(ds);

        ds.Dispose();

        com.Dispose();

        da.Dispose();

        return ds;

      }

      catch

      {

        ds.Dispose();

        com.Dispose();

        da.Dispose();

         return null;

      }

}

On event page load:

protected void Page_Load(object sender, EventArgs e)

{

   SqlConnection sqlcon = new SqlConnection();

   DataSet ds = new DataSet();

    //calling function connection

   sqlcon = ConnectToDB();

   if (sqlcon != null)

    {

        ReportDocument report = new ReportDocument();              

        report.Load(Server.MapPath("bin\\invoice.rpt"));

         //i stored crystal report file in bin folder 

         report.FileName = Server.MapPath("bin\\invoice.rpt");

         report.SetDatabaseLogon("sa", "123", @"UBEE_THAROTH", "Drink");

         //load report from dataset

         ds = viewinvoice(sqlcon);//calling function viewinvoice

         report.SetDataSource(ds);

         CrystalReportViewer1.ReportSource = report;

      }

      else

      {

         Response.Write("Sorry ! please contact to administrator for this problem");

      }

}

Done :) ...hope you guy enjoy it ;)