MVC 4 – Reporting und gespeicherte Prozedur mit SqlParameters

Ich benutze MVC 4 und Entity Framework, um eine Webanwendung zu entwickeln. Ich habe eine Reporting-function, die auf gespeicherte Prozedur basiert. Hier ist, wie ich einen Bericht mache:

public static DataSet ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters) { var connectionString = ((EntityConnection)db.Connection).StoreConnection.ConnectionString; var ds = new DataSet(); using (var conn = new SqlConnection(connectionString)) { using (var cmd = conn.CreateCommand()) { cmd.CommandText = storedProcedureName; cmd.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (var parameter in parameters) { cmd.Parameters.Add(parameter); } } using (var adapter = new SqlDataAdapter(cmd)) { adapter.Fill(ds); } } } return ds; } public static byte[] GenerateReport(string templatePath, string storedProcedure, string extension, IEnumerable<SqlParameter> storedProcedureParameters) { BuSIMaterialEntities db = new BuSIMaterialEntities(); ReportViewer personReportViewer = new ReportViewer(); //Parameters ReportParameter[] reportParameters = new ReportParameter[3]; reportParameters[0] = new ReportParameter("Title", "Reporting", true); reportParameters[1] = new ReportParameter("Address", "Avenue de Tervuren, 268", true); reportParameters[2] = new ReportParameter("PostalCode", "B-1150 Brussels", true); //Report Template personReportViewer.ProcessingMode = ProcessingMode.Local; personReportViewer.LocalReport.ReportPath = templatePath; //Source DataSet ds = BuSIMaterial.Utils.Services.ExecuteStoredProcedure(db, storedProcedure, storedProcedureParameters); ReportDataSource dsList = new ReportDataSource("TableData", ds.Tables[0]); personReportViewer.LocalReport.DataSources.Clear(); personReportViewer.LocalReport.DataSources.Add(dsList); //Report type personReportViewer.LocalReport.SetParameters(reportParameters); byte[] byteArray = personReportViewer.LocalReport.Render(extension); return byteArray; } 

Alles funktioniert gut, wenn ich keine Parameter verwenden muss:

 public ActionResult PersonReport() { byte[] byteArray = BuSIMaterial.Utils.Services.GenerateReport((HttpContext.server.MapPath("..") + "\\Reporting\\Templates" + "AllPersonsReport.rdlc"), "GetAllPersons", "PDF", null); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment; Filename=\"" + DateTime.Now + " - BuSI's Employees" + "\""); Response.AddHeader("Content-Transfer-Encoding", "Binary"); Response.BinaryWrite((byte[])byteArray); Response.Flush(); Response.End(); return RedirectToAction("Index"); } 

Die Sache ist, ich habe eine gespeicherte Prozedur, die einen Parameter erfordert:

 ALTER PROCEDURE dbo.GetPersonDetails ( @Id_Person int ) AS BEGIN SET NOCOUNT ON SELECT p.FirstName, p.LastName, p.NumNat, pc.PhoneNumber, pc.SimNumber, vfc.Number, vfcc.Name, pa.StartDate, pa.EndDate, pr.PurchaseDate, pr.CatalogPrice, (prc.Name + ' - ' + prt.Model + ' ' + pr.SerialNumber) AS Allocation FROM bm_Persons AS p INNER JOIN bm_PhoneCards AS pc ON p.Id_Person = pc.Id_Person INNER JOIN bm_VehicleFuelCards AS vfc ON vfc.Id_Person = p.Id_Person INNER JOIN bm_VehicleFuelCardCompanies AS vfcc ON vfcc.Id_VehicleFuelCardCompany = vfc.Id_VehicleFuelCardCompany INNER JOIN bm_ProductAllocations AS pa ON pa.Id_Person = p.Id_Person INNER JOIN bm_Products AS pr ON pr.Id_Product = pa.Id_Product INNER JOIN bm_ProductType AS prt ON prt.Id_ProductType = pr.Id_ProductType INNER JOIN bm_ProductCompanies AS prc ON prc.Id_ProductCompany = prt.Id_ProductCompany WHERE p.Id_Person = @Id_Person; END 

Was ich tun möchte, ist, eine Methode (wie oben) zu erstellen, aber anstatt null für den Parameter SqlParameters zu übergeben, möchte ich eine ID bereitstellen. Wie soll ich das machen?

   

Sende Id_Person als sql-Parameter, geben Sie den id-Wert als int-Wert an

 byte[] byteArray = BuSIMaterial.Utils.Services.GenerateReport( (HttpContext.server.MapPath("..") + "\\Reporting\\Templates" + "AllPersonsReport.rdlc"), "GetAllPersons", "PDF", new[] {new SqlParameter("Id_Person", id)}); 

Sie können einen SqlParameter wie folgt erstellen.

 SqlParameter idParameter = new SqlParameter("@id", SqlDbType.Int, 4); idParameter.Value = 123; IEnumerable<SqlParameter> myParameters = new[] { idParameter }; byte[] byteArray = BuSIMaterial.Utils.Services.GenerateReport((HttpContext.server.MapPath("..") + "\\Reporting\\Templates" + "AllPersonsReport.rdlc"), "GetAllPersons", "PDF", myParameters);