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:
you want. like that add 2 columns or more
select CrystalReport from templates.
select FieldExplorer.
it and finish.
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.
two fields in the designer of the crystal report.
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();
}
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>