How to Create CrystalReport by Querying SQL Dynamically.


This article is about how to dynamically create the CrystalReports from visual Studio using database and Dataset (.xsd files) by Designing the own Templates. Here I gave the step by step flow so that one can easily follow the steps and can create the crystalreport very easily.

How to Create CrystalReport by Querying SQL Dynamically.


Steps to Follow:


  • Create a New Project in VisualStudio.

  • Add New Item of type Dataset to the project which name like MyDataSet.xsd

  • Now it open the Designer there rightclick and Select Add and then DataTable.

  • Add columns by right clicking on datatable and select Add Column and Rename it as
    you want. like that add 2 columns or more

  • Now Add a CrystalReport into the VisualStudioProject from reporting section and
    select CrystalReport from templates.

  • Now goto FieldExplorer if not visible then Right click on the CrystalReport and
    select FieldExplorer.

  • Now in FieldExplorer you can see DatabaseFields rightclick on it and select DatabaseExpert.

  • There select Add New Connection and select NEW ADO.NET (XML). DoubleClick

  • Now that window it asks for the path of the .xsd file which you created first. Select
    it and finish.

  • Now in the window a database icon shape with name you created(DataSet1 given while
    creating DataSet file) will display select and press the > symbol button in the
    middle. it takes the database objecty to the rightside panel. After that select
    ok.

  • Now you can see the two columns created by expanding it. now drag and drop those
    two fields in the designer of the crystal report.

  • Now goto .aspx page and add CrystalReportViewer from toolbox.

  • Now goto .cs and write the below code





  • Code Explanation.

  • Create a Connection and apply select statement and retrive the data from database.

  • protected void Page_Load(object sender, EventArgs e)
    {
    string sqlstmt = "";
    SqlConnection con = new SqlConnection();
    con.ConnectionString = @"Data Source=SW-HARIKISHORE\SQLEXPRESS;Initial Catalog=sample;User ID=sa;Password=xxxx";
    sqlstmt = getSql(); //here getSql will modify the data see below for it.
    SqlDataAdapter da = new SqlDataAdapter(sqlstmt, con);
    DataSet ds = new DataSet();
    da.Fill(ds);
    objrpt.SetDataSource(ds.Tables[0]);

    CrystalReportViewer1.ReportSource = objrpt; //binding the dataset to the report.
    CrystalReportViewer1.RefreshReport();
    }

  • Now for assigning the data to the report we will make use of "ReportDefinition.ReportObjects"

  • public string getSql()
    {
    string SQLStmt = null;
    string SelectFields = null;
    string FromTable = null;
    int SelectIndex = 0;
    int StartIndex = 0;
    string[] Fileds = null;
    string[] Separator = { "," };
    int i = 0;
    TextObject rptText; //text fields in the report mean columns we added first

    SQLStmt = "select username,password from userlogin"; //sql statement we write
    SQLStmt = SQLStmt.ToUpper();

    SelectIndex = SQLStmt.IndexOf("SELECT");//getting the select index from sql statement
    StartIndex = SQLStmt.IndexOf("FROM");//getting the from index from sql statement
    SelectIndex = SelectIndex + 6;
    SelectFields = SQLStmt.Substring(SelectIndex, (StartIndex - SelectIndex));//getting fields written in sqlStatement here username and password
    FromTable = SQLStmt.Substring(StartIndex, SQLStmt.Length - StartIndex);// separting from statement.

    Fileds = SelectFields.Split(',');//we got fields here
    SelectFields = "";
    for (i = 0; i <= Fileds.Length - 1; i++)
    {
    if (i > 0)
    {
    SelectFields = SelectFields + ", " + Fileds[i].ToString() + " AS COLUMN" + (i + 1);
    SelectFields.Trim();

    rptText = (TextObject)objrpt.ReportDefinition.ReportObjects[i];
    rptText.Text = Fileds[i].ToString();
    }
    else
    {
    SelectFields = SelectFields + Fileds[i].ToString() + " AS COLUMN" + (i + 1); //modifying sql statement
    SelectFields.Trim();

    rptText = (TextObject)objrpt.ReportDefinition.ReportObjects[i];//getting the textobject of the report first one because i=0 in starting.
    rptText.Text = Fileds[i].ToString();//assigning the text here to the textfields in report.
    }
    }
    SQLStmt = "SELECT " + SelectFields + " " + FromTable;
    return SQLStmt;
    }



and aspx code will look like this.



<div>
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>


Attachments

  • How to Create CrystalReport by Querying SQL Dynamically. (44258-6542-How-Create-CrystalReport-by-Querying-SQL-Dynamically.rar)
  • Comments

    No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: