自由手記

public partial class Form1 : Form
{
    private void Form1_Load(object sender, EventArgs e)
    {
        // Set the processing mode for the ReportViewer to Local
        reportViewer1.ProcessingMode = ProcessingMode.Local;

        LocalReport localReport = reportViewer1.LocalReport;

        localReport.ReportPath = "Sales Order Detail.rdlc";

        DataSet dataset = new DataSet("Sales Order Detail");

        string salesOrderNumber = "SO43661";

        // Get the sales order data
        GetSalesOrderData(salesOrderNumber, ref dataset);

        // Create a report data source for the sales order data
        ReportDataSource dsSalesOrder = new ReportDataSource();
        dsSalesOrder.Name = "SalesOrder";
        dsSalesOrder.Value = dataset.Tables["SalesOrder"];

        localReport.DataSources.Add(dsSalesOrder);

        // Get the sales order detail data
        GetSalesOrderDetailData(salesOrderNumber, ref dataset);

        // Create a report data source for the sales order detail 
        // data
        ReportDataSource dsSalesOrderDetail =
            new ReportDataSource();
        dsSalesOrderDetail.Name = "SalesOrderDetail";
        dsSalesOrderDetail.Value =
            dataset.Tables["SalesOrderDetail"];

        localReport.DataSources.Add(dsSalesOrderDetail);

        // Create a report parameter for the sales order number 
        ReportParameter rpSalesOrderNumber = new ReportParameter();
        rpSalesOrderNumber.Name = "SalesOrderNumber";
        rpSalesOrderNumber.Values.Add("SO43661");

        // Set the report parameters for the report
        localReport.SetParameters(
            new ReportParameter[] { rpSalesOrderNumber });

        // Refresh the report
        reportViewer1.RefreshReport();
    }

    private void GetSalesOrderData(string salesOrderNumber,
                                   ref DataSet dsSalesOrder)
    {
        string sqlSalesOrder =
            "SELECT SOH.SalesOrderNumber, S.Name AS Store, " +
            "       SOH.OrderDate, C.FirstName AS SalesFirstName, " +
            "       C.LastName AS SalesLastName, E.Title AS " +
            "       SalesTitle, SOH.PurchaseOrderNumber, " +
            "       SM.Name AS ShipMethod, BA.AddressLine1 " +
            "       AS BillAddress1, BA.AddressLine2 AS " +
            "       BillAddress2, BA.City AS BillCity, " +
            "       BA.PostalCode AS BillPostalCode, BSP.Name " +
            "       AS BillStateProvince, BCR.Name AS " +
            "       BillCountryRegion, SA.AddressLine1 AS " +
            "       ShipAddress1, SA.AddressLine2 AS " +
            "       ShipAddress2, SA.City AS ShipCity, " +
            "       SA.PostalCode AS ShipPostalCode, SSP.Name " +
            "       AS ShipStateProvince, SCR.Name AS " +
            "       ShipCountryRegion, CC.Phone AS CustPhone, " +
            "       CC.FirstName AS CustFirstName, CC.LastName " +
            "       AS CustLastName " +
            "FROM   Person.Address SA INNER JOIN " +
            "       Person.StateProvince SSP ON " +
            "       SA.StateProvinceID = SSP.StateProvinceID " +
            "       INNER JOIN Person.CountryRegion SCR ON " +
            "       SSP.CountryRegionCode = SCR.CountryRegionCode " +
            "       RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " +
            "       LEFT OUTER JOIN  Person.Contact CC ON " +
            "       SOH.ContactID = CC.ContactID LEFT OUTER JOIN" +
            "       Person.Address BA INNER JOIN " +
            "       Person.StateProvince BSP ON " +
            "       BA.StateProvinceID = BSP.StateProvinceID " +
            "       INNER JOIN Person.CountryRegion BCR ON " +
            "       BSP.CountryRegionCode = " +
            "       BCR.CountryRegionCode ON SOH.BillToAddressID " +
            "       = BA.AddressID ON  SA.AddressID = " +
            "       SOH.ShipToAddressID LEFT OUTER JOIN " +
            "       Person.Contact C RIGHT OUTER JOIN " +
            "       HumanResources.Employee E ON C.ContactID = " +
            "       E.ContactID ON SOH.SalesPersonID = " +
            "       E.EmployeeID LEFT OUTER JOIN " +
            "       Purchasing.ShipMethod SM ON SOH.ShipMethodID " +
            "       = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" +
            "        S ON SOH.CustomerID = S.CustomerID " +
            "WHERE  (SOH.SalesOrderNumber = @SalesOrderNumber)";

        SqlConnection connection = new
            SqlConnection("Data Source=(local); " +
                          "Initial Catalog=AdventureWorks; " +
                          "Integrated Security=SSPI");

        SqlCommand command =
            new SqlCommand(sqlSalesOrder, connection);

        command.Parameters.Add(
            new SqlParameter("SalesOrderNumber",
            salesOrderNumber));

        SqlDataAdapter salesOrderAdapter = new
            SqlDataAdapter(command);

        salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder");
    }

    private void GetSalesOrderDetailData(string salesOrderNumber,
                           ref DataSet dsSalesOrder)
    {
        string sqlSalesOrderDetail =
            "SELECT  SOD.SalesOrderDetailID, SOD.OrderQty, " +
            "        SOD.UnitPrice, CASE WHEN " +
            "        SOD.UnitPriceDiscount IS NULL THEN 0 " +
            "        ELSE SOD.UnitPriceDiscount END AS " +
            "        UnitPriceDiscount, SOD.LineTotal, " +
            "        SOD.CarrierTrackingNumber, " +
            "        SOD.SalesOrderID, P.Name, P.ProductNumber " +
            "FROM    Sales.SalesOrderDetail SOD INNER JOIN " +
            "        Production.Product P ON SOD.ProductID = " +
            "        P.ProductID INNER JOIN " +
            "        Sales.SalesOrderHeader SOH ON " +
            "        SOD.SalesOrderID = SOH.SalesOrderID " +
            "WHERE   (SOH.SalesOrderNumber = @SalesOrderNumber) " +
            "ORDER BY SOD.SalesOrderDetailID";

        using (SqlConnection connection = new
            SqlConnection("Data Source=(local); " +
                          "Initial Catalog=AdventureWorks; " +
                          "Integrated Security=SSPI"))
        {

            SqlCommand command =
                new SqlCommand(sqlSalesOrderDetail, connection);

            command.Parameters.Add(
                new SqlParameter("SalesOrderNumber",
                salesOrderNumber));

            SqlDataAdapter salesOrderDetailAdapter = new
                SqlDataAdapter(command);

            salesOrderDetailAdapter.Fill(dsSalesOrder,
                "SalesOrderDetail");
        }
    }
}