.NET データ処理に役立つ26のヒント（後編）

// Visual Studio 2003 code
ArrayList list = new ArrayList();
// implicit boxing, must convert value 
// type to reference type
list.Add(3);
list.Add(4);
list.Add(5.0);
int nFirst = (int)list[0];
int total = 0;
foreach (int val in list) 
    total = total + val;

// Visual Studio 2005 code
// define the stored data type in the placeholder 
List<int> aList = new List<int>();
aList.Add(3);
aList.Add(4);
// The following line generates a compile error
aList.Add(5.0);   
// no need for unboxing, value type stored in 
// List<int> as an int, not an object
int nFirst = aList[0];   
int total = 0;
foreach (int val in aList) 
    total = total + val;

// Visual Studio 2003 code - takes .2 seconds
ArrayList aBigList = new ArrayList();
DateTime dt1, dt2;
dt1 = DateTime.Now;
for (int nCtr = 0; nCtr < 1000000; nCtr++)
    aBigList.Add(nCtr);
int nSum = 0;
foreach (int nn in aBigList)
    nSum += nn;
dt2 = DateTime.Now;
TimeSpan ts = dt2.Subtract(dt1);
MessageBox.Show(ts.TotalSeconds.ToString());

// Visual Studio 2005 code - takes .04 seconds 
List<INT> aBigList = new List<INT>();
DateTime dt1, dt2;
dt1 = DateTime.Now;
for (int nCtr = 0; nCtr < 1000000; nCtr++)
    aBigList.Add(nCtr);
int nSum = 0;
foreach (int nn in aBigList)
    nSum += nn;
dt2 = DateTime.Now;
TimeSpan ts = dt2.Subtract(dt1);
MessageBox.Show(ts.TotalSeconds.ToString());

public T CalcMax<T> ( T compVal1, T compVal2)
       where T : IComparable 
{
    T returnValue = compVal2;
    if (compVal2.CompareTo(compVal1) < 0) 
        returnValue = compVal1;
    return returnValue;
}

double dMax = CalcMax<DOUBLE>(111.11,333.23);
int intMax =  CalcMax<INT>(2, 3);
string cMax = CalcMax<STRING>(&quot;Kevin&quot;, &quot;Steven&quot;);
DateTime dtMax = CalcMax<DATETIME> 
   (DateTime.Today, DateTime.Today.AddDays(1));

using System;
using System.Collections.Generic;
using System.Text;
namespace GenericsDemo{
    public class BaseballClass  
    {
        private string name;
        public string Name
        {  get  { return name;  }}
        private string team;
        public string Team
        { get { return team; } }
        public BaseballClass(string name, string team)
        {
            this.name = name;
            this.team = team;
        }
        public override string ToString()
        {
            return team + ", " + name;
        }
    }
}

List<BaseballClass> PopulateBaseballClass()
{
    List<BaseballClass> oBaseball = new List<BaseballClass>();
    oBaseball.Add(new BaseballClass("Kenny Rogers", "Tigers"));
    oBaseball.Add(new BaseballClass("Michael Young", "Rangers"));
    oBaseball.Add(new BaseballClass("Ken Griffey, Jr.", "Reds"));
    oBaseball.Add(new BaseballClass("Tom Glavine", "Mets"));
    oBaseball.Add(new BaseballClass("David Ortiz", "Red Sox"));
    oBaseball.Add(new BaseballClass("Derek Jeter", "Yankees"));
    oBaseball.Add(new BaseballClass("Roger Clemens", "Astros"));
    oBaseball.Add(new BaseballClass("Roy Oswalt", "Astros"));
    return oBaseball;
}

private void TestBaseballClass()
{
    List<BaseballClass> oBaseball = new List<BaseballClass>();
    oBaseball = this.PopulateBaseballClass();
    string cResults = "";
    foreach (BaseballClass oRecord in oBaseball)
        cResults += oRecord + "
";
    MessageBox.Show(cResults);
}

private void TestSortBaseballClass()
{
    List<BaseballClass> oBaseball = new List<BaseballClass>();
    oBaseball = this.PopulateBaseballClass();
    string cResults = "";
   
    // Anonymous delegate to sort on name
    oBaseball.Sort (delegate(BaseballClass f1, BaseballClass f2)
    {
        return f1.Name.CompareTo(f2.Name);
    }
   
    // Anonymous delegate to sort on Team, then Name
    oBaseball.Sort (delegate(BaseballClass f1, BaseballClass f2)
    {
        return f1.Name.Insert(0, f1.Team).CompareTo(
                                       f2.Name.Insert(0, f2.Team));
    }
}

// In-line anonymous method that compares 2 
// incoming values
oBaseball.Sort(delegate(BaseballClass f1, BaseballClass f2) {
    return f1.Name.CompareTo(f2.Name);
});
   
// A second anonymous method, inserts team 
// in front of name, to sort on two columns 
oBaseball.Sort(delegate(BaseballClass f1, BaseballClass f2) {
    return f1.Name.Insert(0, f1.Team).CompareTo( 
                                        f2.Name.Insert(0, f2.Team));
});

private void TestFindBaseballClass()
{
    List<BaseballClass> oBaseball = new List<BaseballClass>();
    oBaseball = this.PopulateBaseballClass();
    string cSearch = "Astros";
    BaseballClass oSingleFind =
        oBaseball.Find( delegate(BaseballClass f1) 
        {
            return f1.Team == cSearch;
        });
    MessageBox.Show("Single find is " + oSingleFind);
    string cResults = "";
    foreach (BaseballClass oRecord in
        oBaseball.FindAll( (delegate(BaseballClass f1) 
        {
            return f1.Team == cSearch;
        })))
        cResults += oRecord + "
";
    MessageBox.Show("Multiple find is " + oSingleFind);
}

string cSearch = "Astros";
   
// Perform a single search on the first hit
BaseballClass oSingleFind = oBaseball.Find(
    delegate(BaseballClass f1)
    { 
        return f1.Team == cSearch;
     });
   
// Perform a find all (almost like a filter)
string cResults = "";
foreach (BaseballClass oRecord in   
    oBaseball.FindAll((delegate(BaseballClass f1)
    { 
        return f1.Team == cSearch;
    })))
    cResults += oRecord + "
";

using System;
using System.Collections.Generic;
using System.Text;
namespace GenericsDemo
{
    class FindBaseballClass
    {
        private string team;
        public FindBaseballClass(string team)
        {
            this.team = team;
        }
        // Implement specific search logic - return true or false
        public bool TeamFind(BaseballClass oBaseball)
        {
            return oBaseball.Team == team;
        }
    }
}

private void TestFindBaseballClass()
{
   List<BaseballClass> oBaseball = new List<BaseballClass>();
   oBaseball = this.PopulateBaseballClass();
   FindBaseballClass oFind = new FindBaseballClass("Astros"); 
   BaseballClass oSingleFind = oBaseball.Find(  
      new Predicate<BaseballClass>(oFind.TeamFind));
   MessageBox.Show("Single find is " + oSingleFind);
   string cResults = "";
   foreach (BaseballClass oRecord in oBaseball.FindAll(
      new Predicate<BaseballClass>(oFind.TeamFind)))
      cResults += oRecord + "
";
   MessageBox.Show("Multiple find is " + oSingleFind);
}

public class SimpleInterface
{
   public interface IDocument
   {
      void GenerateDocument ();
   }
}
class SimpleClass1 : SimpleInterface.IDocument
{
   public SimpleClass1() 
   { 
   }
   public void GenerateDocument()
   {
      // Method code goes here!
   }
}
class SimpleClass2 : SimpleInterface.IDocument
{
   public SimpleClass2() 
   { 
   }
   public void GenerateDocument()
   {
      // Method code goes here!
   }
}
List<SimpleInterface.IDocument> oList = 
   new List<SimpleInterface.IDocument>();
SimpleClass1 oClass1 = new SimpleClass1();
SimpleClass2 oClass2 = new SimpleClass2();
oList.Add(oClass1);
oList.Add(oClass2);
foreach (SimpleInterface.IDocument oClass in oList)
   oClass.GenerateDocument();

// Create a new dictionary object with two strings
Dictionary<string, string> oDict = new Dictionary<string, string>();
oDict.Add("secure", "true");
oDict.Add("password", "kevin");
TcpChannel oChannel = new TcpChannel(oDict, null, null);

Collection<int> oCol = new Collection<int>();
oCol.Add(2);
oCol.Add(3);
oCol.Insert(0,1);

Collection<BaseballClass> oBaseballCollection = 
    new Collection<BaseballClass>();
oBaseballCollection.Add(new BaseballClass("Tom Glavine",
                                          "Mets"));
oBaseballCollection.Add(new BaseballClass("Derek Jeter",
                                          "Yankees"));
foreach (BaseballClass oBaseball in 
    oBaseballCollection)
MessageBox.Show(oBaseball.ToString());

public class SimpleDataAccess
{
    // Pass the following:
    // 1) An instance of a typed dataset you wish to fill
    // 2) The name of the stored procedure
    // 3) A List of SqlParameters 
    // Function will execute the stored procedure,  
    // fill the typed dataset with the results, and then return it
    public T ReadIntoDs<T> 
            (T oTypedDs, string cSP, List<SqlParameter> oParms) 
        where T:DataSet
    {
        SqlConnection oSqlConn = this.GetConnection();
        SqlDataAdapter oSqlAdapt = new SqlDataAdapter(cSP, oSqlConn);
        oSqlAdapt.SelectCommand.CommandType = 
        CommandType.StoredProcedure;
        foreach (SqlParameter oParm in oParmList)
            oSqlAdapter.SelectCommand.Parameters.Add(oParm);

        // A little bit of "elbow grease…
        // SQL Server returns tablenames as "Table", "Table1", etc
        // We need to map in the tablenames from the typed DS
        // So loop through the tables in the Typed DS, get the name,
        // and map it to the corresponding table that SQL will
        // use as the default name
        int nTableCtr = 0;
        foreach (DataTable Dt in dsTypedDs.Tables) {
            string cSource = "";   
            if (nTableCtr == 0)
                cSource = "Table";
            else
                cSource = "Table" + nTableCtr.ToString().Trim();
        oSqlAdapter.TableMappings.Add(
            cSource, Dt.TableName.ToString());
        nTableCtr++;
    }
        // End of "elbow grease" - we can fill the dataset
        // from the adapter, now that we’ve mapped our tablenames
        oSqlAdapter.Fill(dsTypedDs);
        return dsTypedDs;
    }

    public SqlConnection GetConnection() 
    {
        // New SqlConnection StringBuilder class
        SqlConnectionStringBuilder oStringBuilder = 
            new SqlConnectionStringBuilder();
        oStringBuilder.UserID = "UserID";
        oStringBuilder.Password = "PassWord";
        oStringBuilder.InitialCatalog = "InitialCatalog";
        oStringBuilder.DataSource = "DataSource";
        return new SqlConnection(oStringBuilder.ConnectionString);
    }
}

// Create an instance of a typed DataSet
dsAgingReport odsAgingReport = new dsAgingReport();

// Create an instance of our Data Access Method
SimpleDataAccess oDataAccess = new SimpleDataAccess();

// Use the list class to create a collection of sql parameters
List<SqlParameter> oParms = new List<SqlParameter>();
oParms.Add(new SqlParameter("@dAgingDate", DateTime.Today));
oParms.Add(new SqlParameter("@lShowDetails", true));

// Call ReadIntoDs, passing the typed DS, name of the stored proc
// and the list of parameters
odsAgingReport = oDataAccess.ReadIntoDs(odsAgingReport,
   "[dbo].[GetAgingReceivables]",oParms);

protected void ObjectDataSource1_Selected(
        object sender, ObjectDataSourceStatusEventArgs e)
{
    bzOrders.dsOrders odsOrders = 
        (dsOrders)e.ReturnValue;
    int nRows = odsOrders.dtOrders.Rows.Count;
    this.grdOrdersOnHold.Caption = 
        "Number of rows: " + nRows.ToString();
}

-- This will work in both SQL2000 and SQL2005
-- You can use COALESCE to query on only those search values
-- that are not NULL
DECLARE @city varchar(50), @state varchar(50), @zip varchar(50),
        @FirstName varchar(50), @LastName varchar(50), 
        @Address varchar(50)
SET @FirstName = ’Kevin’
SET @State = ’NY’
SELECT * FROM CUSTOMERS WHERE 
    FirstName = COALESCE(@FirstName,FirstName) AND
    LastName = COALESCE(@LastName,LastName) AND
    Address = COALESCE(@Address,Address) AND
    City = COALESCE(@City,City) AND
    State = COALESCE(@State,State) AND
    Zip = COALESCE(@Zip,Zip)

ROW_NUMBER() OVER (PARTITION BY CUSTOMERID 
ORDER BY  (UnitPrice  * Quantity) DESC) 
AS OrderRank

SELECT  CustomerID, OH.OrderID, OrderDate,  
    (UnitPrice  * Quantity) as Orderamount,
    ROW_NUMBER() OVER (PARTITION BY CUSTOMERID 
ORDER BY  (UnitPrice  * Quantity) desc ) AS OrderRank
FROM Orders OH
    JOIN [dbo].[Order Details] OD
ON OH.OrderID = OD.OrderID
WHERE  (UnitPrice * Quantity) > 500
ORDER BY CUSTOMERID, OrderAmount  DESC

-- Results, OrderRank partitioned (reset) on each Customer  
ALFKI   10692   1997-10-03 00:00:00.000   878.00   1
ALFKI   10835   1998-01-15 00:00:00.000   825.00   2
ANTON   10535   1997-05-13 00:00:00.000   1050.00   1
ANTON   10677   1997-09-22 00:00:00.000   936.90   2
ANTON   10535   1997-05-13 00:00:00.000   825.00   3
ANTON   10573   1997-06-19 00:00:00.000   820.00   4
ANTON   10573   1997-06-19 00:00:00.000   702.00   5
AROUT   10953   1998-03-16 00:00:00.000   4050.00   1
AROUT   10558   1997-06-04 00:00:00.000   1060.00   2
AROUT   10707   1997-10-16 00:00:00.000   780.00   3

CREATE FUNCTION [dbo].[GetCustOrders_GT_X]
   (@CustomerID AS varchar(10), @nThreshold AS decimal(14,2))
RETURNS @tOrders TABLE (OrderID int, CustomerID varchar(10), 
   OrderDate datetime, OrderAmount decimal(14,2))
AS
BEGIN
   INSERT INTO @tOrders
   SELECT  OH.OrderID, CustomerID, OrderDate,
      (UnitPrice  * Quantity) as Orderamount
   FROM Orders OH
   JOIN [dbo].[Order Details] OD
      ON OH.OrderID = OD.OrderID
   WHERE CustomerID =  @CustomerID AND 
             (UnitPrice  * Quantity)  > @nThreshold  
   ORDER BY OrderAmount  DESC
   RETURN
END
GO

DECLARE @nNumOrders int, @nMinAmt decimal(14,2)
SET @nNumOrders = 2
SET @nMinAmt = 5000.00
SELECT CustomerID FROM Customers 
WHERE  (SELECT COUNT(*) FROM 
     DBO.GetCustOrders_GT_X(CustomerID,@nMinAmt)) >=@nNumOrders

-- ResultsHUNGO
QUICK
ERNSH
SAVEA
RATTC

-- To get the actual orders > 5000 for these 5 customers, 
-- we can turn the query above into a derived table, and then 
-- use a CROSS APPLY
SELECT MaxOrders.* FROM
   (SELECT CustomerID FROM Customers
WHERE  (SELECT COUNT(*)  
FROM dbo.GetCustOrders_GT_X(CustomerID,@nThresholdAmt)) >= 
        @nNumOrders) as CustList
CROSS APPLY GetCustOrders_GT_X(CustomerID,@nThresholdAmt) 
AS MaxOrders

-- First Example of inserting XML into a table of columns
DECLARE @cXMLDoc XML
declare @hdoc int
SET @cXMLDoc = ’
<AddressType  >
   <AddressRecord AccountID = "1" 
      Street="31 Main Dr" City="Philly" State="PA" Zip="12345"/>
   <AddressRecord AccountID = "2" 
      Street="1 Wilson Dr" City="Newark" State="NJ" Zip="22222"/>
</AddressType>’

EXEC sp_xml_preparedocument @hdoc OUTPUT, @cXMLDoc
SELECT * FROM OPENXML (@hdoc, ’/AddressType/AddressRecord’,1)
WITH (AccountID int,Street varchar(100), City varchar(100), 
   State varchar(10), ZipCode varchar(13))

-- Second example of inserting XML into a table of columns
-- uses the Address tag to specify nested columns
DECLARE @cXMLDoc XML
declare @hdoc int
SET @doc = ’
<customer>
   <Customernum>48456</Customernum>
   <Firstname>Kevin</Firstname>
   <Lastname>Goff</Lastname>
   <Address>
      <city>Allentown</city>
      <state>PA</state>
   </Address>
</customer>’

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DECLARE @tTemp TABLE (Customernum int, Firstname char(50), 
   Lastname char(50), City char(50), State Char(10))
INSERT INTO @ttemp
SELECT Customernum,firstname,lastname,city,state
FROM OPENXML (@idoc, ’/customer’,2)
WITH (Customernum int,Firstname varchar(50),Lastname varchar(50),
   city varchar(50) ’./Address/city’, 
   state varchar(50) ’./Address/state’)

-- Performing a partial text search inside an XML column
declare @tTest table (address xml)
insert into @ttest values (’
  <Address >
    <AddrRecord AccountID = "1" Street="31 Main Dr" City="Newark" 
       State="NJ" Zip="11111" />
  </Address>’ )

insert into @ttest values (’
  <Address >
    <AddrRecord AccountID = "2" Street="1 Wilson Rd" City="Philly" 
      State="PA" Zip="22222"/>
  </Address>’ )
SELECT * FROM @ttest WHERE
   Address.exist(’/Address/AddrRecord [contains(@City,"hil")]’) = 1

INSERT @tTable EXEC <sp_mystoredproc> 

DECLARE @tUser TABLE (UserName varchar(100),Status char(10),UserPK int)
INSERT INTO @tUser exec  [dbo].[cgsValidateUserID] ’KGOFF’,’KGOFF’
SELECT * FROM @tUser