如何使用存储过程在水晶报表中显示数据？ [英] How can I use Stored procedure to display data in crystal report ?

问题描述

我正在尝试在水晶报表中显示数据，我尝试使用这个c＃代码，但我想使用存储过程：这是我的初始代码，没有存储过程：

  protected  DataTable EmployeeDetails（）
 {
  string  id = IDEmployee; 
 
  string  sql =   sql Query; 
 SqlDataAdapter dad =  new  SqlDataAdapter（sql，con）; 
 DataSetAttendance ds =  new  DataSetAttendance（）; 
 dad.Fill（ds.Tables [  MST_Employee]）; 
 DataTable employees = ds.Tables [  MST_Employee]; 
 返回员工; 
} 

  private   void  PermissionReportViewer_Load（ object  sender，EventArgs e）
 {
  try  
 {
 con.ConnectionString =  @  ConnectionString; 
 
 DataSet ds =  new  DataSet（）; 
 DataTable dt1 = EmployeeDetails（）。Copy（）; 
 
 ds.Tables.Add（dt1）; 
 Reports.PermissionReport myreport =  new  Reports.PermissionReport（）; 
 myreport.SetDataSource（ds）; 
 crystalReportViewer1.ReportSource = myreport; 
 crystalReportViewer1.Refresh（）; 
} 
  catch （Exception ex）
 {
 MessageBox.Show（ 错误 + ex.ToString（）， 错误，MessageBoxButtons.OKCancel，MessageBoxIcon.Error）; 
} 
} 

我的存储过程代码：

  protected  DataTable EmployeeDetails（）
 {
  string  id = IDEmployee; 
 
 con.Open（）; 
 SqlCommand sqlComm =  new  SqlCommand（  GetEmployeeDetails ，con）; 
 
 sqlComm.Parameters.AddWithValue（  @ ID，id）; 
 
 sqlComm.CommandType = CommandType.StoredProcedure; 
 
 SqlDataAdapter dad =  new  SqlDataAdapter（）; 
 dad.SelectCommand = sqlComm; 
 DataSetAttendance ds =  new  DataSetAttendance（）; 
 dad.Fill（ds，  MST_Employee）; 
 DataTable total = ds.Tables [  MST_Employee]; 
 con.Close（）; 
 返回总计; 
} 

但它不起作用!!请问任何解决方案？

解决方案

您好 -



您无需使用Data Adapater打开/关闭conn ;试试这个：

  protected  DataTable EmployeeDetails（）
 {
  string  id = IDEmployee; 
 SqlDataAdapter dad =  new  SqlDataAdapter（  GetEmployeeDetails ，con）; 
 dad.SelectCommand.CommandType = CommandType.StoredProcedure; 
 dad.SelectCommand.Parameters.Add（  @ ID，SqlDbType.NVarChar）。值= id; 
 
 DataTable total = ds.Tables [  MST_Employee]; 
 dad.Fill（total，  MST_Employee）; 
 返回总计; 
} 

谢谢，



Kuthuparakkal

i'm trying to display data in crystal report , I try this c# code but I want to use stored procedure : this is my initial code without stored procedure:

protected DataTable EmployeeDetails()
{
    string id = IDEmployee;
 
    string sql = "sql Query ";
    SqlDataAdapter dad = new SqlDataAdapter(sql, con);
    DataSetAttendance ds = new DataSetAttendance();
    dad.Fill(ds.Tables["MST_Employee"]);
    DataTable employees = ds.Tables["MST_Employee"];
    return employees;
}

private void PermissionReportViewer_Load(object sender, EventArgs e)
{
    try
    {
        con.ConnectionString = @"ConnectionString";      

        DataSet ds = new DataSet();
        DataTable dt1 = EmployeeDetails().Copy();

        ds.Tables.Add(dt1);
        Reports.PermissionReport myreport = new Reports.PermissionReport();
        myreport.SetDataSource(ds);
        crystalReportViewer1.ReportSource = myreport;
        crystalReportViewer1.Refresh();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error" +ex.ToString(), "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);        
    }
}

My code with stored procedure:

protected DataTable EmployeeDetails()
{
    string id = IDEmployee;

    con.Open();
    SqlCommand sqlComm = new SqlCommand("GetEmployeeDetails", con);

    sqlComm.Parameters.AddWithValue("@ID", id);
   
    sqlComm.CommandType = CommandType.StoredProcedure;

    SqlDataAdapter dad = new SqlDataAdapter();
    dad.SelectCommand = sqlComm;
    DataSetAttendance ds = new DataSetAttendance();
    dad.Fill(ds, "MST_Employee");
    DataTable total = ds.Tables["MST_Employee"];
    con.Close();
    return total;
}

but it doesn't work !! Any Solution please ?

解决方案

Hello -

You dont need to open/close conn with Data Adapater; try this:

protected DataTable EmployeeDetails()
{
    string id = IDEmployee;
    SqlDataAdapter dad = new SqlDataAdapter("GetEmployeeDetails", con);
    dad.SelectCommand.CommandType = CommandType.StoredProcedure;
    dad.SelectCommand.Parameters.Add("@ID", SqlDbType.NVarChar).Value = id;
    
    DataTable total = ds.Tables["MST_Employee"];
    dad.Fill(total, "MST_Employee");
    return total;
}

Thanks,

Kuthuparakkal

这篇关于如何使用存储过程在水晶报表中显示数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！