【茶包射手專欄】又是Parameters.Add闖的禍

private void TestODPClient(string cnStr, string cmdText, DateTime date)

{

    ODP.OracleCommand cmd = new ODP.OracleCommand(cmdText);

    cmd.Parameters.Add("pDate", date);

    RunTest(@"Add DateTime wo OracleDbType", cmd, cnStr);

    cmd = new ODP.OracleCommand(cmdText);

    cmd.Parameters.Add("pDate", ODP.OracleDbType.Date).Value = date;

    RunTest(@"Add DateTime w/ OracleDbType", cmd, cnStr);

    cmd = new ODP.OracleCommand(cmdText);

    cmd.CommandText = cmd.CommandText.Replace(":pDate",

        "TO_DATE('" + date.ToString("yyyyMMdd") + "', 'YYYYMMDD')");

    RunTest("Ad-Hoc SQL Style", cmd, cnStr);

    cmd = new ODP.OracleCommand(cmdText);

    cmd.CommandText = cmd.CommandText.Replace(":pDate", 

        "TO_DATE(:pDateStr, 'YYYYMMDD')");

    cmd.Parameters.Add("pDateStr", date.ToString("yyyyMMdd"));

    RunTest(@"Add String wo OracleDbType", cmd, cnStr);

    cmd = new ODP.OracleCommand(cmdText);

    cmd.CommandText = cmd.CommandText.Replace(":pDate", 

        "TO_DATE(:pDateStr, 'YYYYMMDD')");

    cmd.Parameters.Add("pDateStr", ODP.OracleDbType.Varchar2).Value = 

        date.ToString("yyyyMMdd");

    RunTest(@"Add String w/ OracleDbType", cmd, cnStr);

}

private void RunTest(string testName, ODP.OracleCommand cmd, string cnStr)

{

    using (ODP.OracleConnection cn = new 

        Oracle.DataAccess.Client.OracleConnection(cnStr))

    {

        cmd.Connection = cn;

        DataTable dt = new DataTable();

        cn.Open();

        Stopwatch sw = new Stopwatch();

        sw.Start();

        ODP.OracleDataReader dr = cmd.ExecuteReader();

        dt.Load(dr);

        sw.Stop();

        cn.Close();

        Response.Write(

            string.Format("<li>Test {0} Rows={1} Duration={2:#,0}ms",

                testName, dt.Rows.Count, sw.ElapsedMilliseconds));

    }

}