ADO.NET參數詳解

　　這個問題以前總是遇到但是每次都是找到解決方法就沒有具體找原因昨天再次遇到參數問題才想著一定要搞清楚了
    ADONET中不同數據提供者所用參數格式如下
    Provider             Named/Positional            Parameter Marker
    SqlClient            Named                       @parmname
    OracleClient         Named                       parmname (or parmname)
    OleDb                Positional                  ?
    Odbc                 Positional                   ?

　　在DbCommandCommandType=CommandTypeText時DbParameter就要符合上述要求如SqlParameter則要使用參數名來決定參數值而oledb則是根據參數順序來決定參數值但是當DbCommandCommandType=CommandTypeStoreProcedure時則可以采用采用占位符或名字來確定參數值
    這種不統一使得要寫出跨越各種數據提供者的程序變得復雜了同時要實現統一的數據層當然還要考慮不同數據庫的sql語句區別（所以要盡量采用標准的sql語句）本來提供了很好的類結構(DbConnection DbCommand等以Db開頭的類)來實現這種很重要的功能可是這些原因使得我們還是要自己寫不少代碼
    下面是一段示例代碼

　　//create proc TestParam
    //@courseid varchar()
    //as
    //select * from course where courseid=@courseid

　　using System;
    using SystemCollectionsGeneric;
    using SystemDataCommon;
    using SystemDataOleDb;
    using SystemDataSqlClient;
    using SystemText;

　　namespace ADOParameterTest
    {
        class Program
        {
            enum ConnectionType : byte { Sql Ole };

　　static string sqlConnectionString = Data Source=CTECLLY;Initial Catalog=examdb;Integrated Security=True;
        static string oleConnectionString = Provider=sqloledb;Data Source=CTECLLY;Initial Catalog=examdb;User Id=sa;Password=sa;

　　static DbConnection GetConnection(ConnectionType ct){
            if (ct == ConnectionTypeSql)
           
            {
                return new SqlConnection(sqlConnectionString);
            }
            else
            {
                return new OleDbConnection(oleConnectionString);
            }
        }

　　static void TestOleCommandText()
        {
            ConsoleWriteLine(TestOleCommandText);
            using(DbConnection con = GetConnection(ConnectionTypeOle))
            {
                conOpen();
                DbCommand cmd = conCreateCommand();
                //cmdCommandText = declare @courseid as varchar(); set @courseid=;select * from course where courseid=@courseid;
                cmdCommandText = select * from course where courseid=?;
                //odbc&oledb只能是?做占位符這時跟參數名無關跟參數順序是相關的
                DbParameter p = cmdCreateParameter();
                pParameterName = courseid;
                pValue = ;
                cmdParametersAdd(p);
                //下面被注釋的參數設置方式也是對的可以注釋上面四行而采用下面的語句
                //記住參數名是沒有意義的順序決定參數
                //cmdParametersAdd(new OleDbParameter(@courseid ));
                ShowResult(cmdExecuteReader());
            }
        }

　　static void TestOleStoredProcedure()
       
         {
            ConsoleWriteLine(TestOleStoredProcedure);
            using (DbConnection con = GetConnection(ConnectionTypeOle))
            {
                conOpen();
                DbCommand cmd = conCreateCommand();
                cmdCommandType = SystemDataCommandTypeStoredProcedure;
                cmdCommandText = TestParam;
                ConsoleWriteLine(用@param做占位符);
                //odbc&oledb只能是?做占位符這時跟參數名無關跟參數順序是相關的
                DbParameter p = cmdCreateParameter();
                pParameterName = @courseid;
                pValue = ;
                cmdParametersAdd(p);
                //下面被注釋的參數設置方式也是對的可以注釋上面四行而采用下面的語句
                //記住參數名是沒有意義的順序決定參數
                //cmdParametersAdd(new OleDbParameter(@courseid ));
                ShowResult(cmdExecuteReader());

　　ConsoleWriteLine(用?做占位符);
                pParameterName = ;
                ShowResult(cmdExecuteReader());

　　}
        }

        static void TestSqlCommandText()
        {
            ConsoleWriteLine(TestSqlCommandText);
            using (DbConnection con = GetConnection(ConnectionTypeSql))
            {
           
            conOpen();
                DbCommand cmd = conCreateCommand();
                //sql只能是@param做占位符跟oledb相反跟參數名有關跟參數順序無關
                //oracle只能用:param做占位符跟參數名有關跟參數順序無關
                cmdCommandText = select * from course where courseid=@courseid;
                DbParameter p = cmdCreateParameter();
                pParameterName = @courseid;
                pValue = ;
                cmdParametersAdd(p);
                //下面被注釋的參數設置方式也是對的可以注釋上面四行而采用下面的語句
                //記住只有參數名有意義順序無關
                //cmdParametersAdd(new OleDbParameter(@courseid ));
                ShowResult(cmdExecuteReader());
            }
        }

　　static void TestSqlStoredProcedure()
        {
            ConsoleWriteLine(TestSqlStoredProcedure);
            using (DbConnection con = GetConnection(ConnectionTypeSql))
            {
                conOpen();
                DbCommand cmd = conCreateCommand();
                //sql只能是@param做占位符跟oledb相反跟參數名有關跟參數順序無關
                //oracle只能用:param做占位符跟參數名有關跟參數順序無關
                cmdCommandType = SystemDataCommandTypeStoredProcedure;
                cmdCommandText = TestParam;

　　ConsoleWriteLine(用@param做占位符);
               
                         DbParameter p = cmdCreateParameter();
                pParameterName = @courseid;
                pValue = ;
                cmdParametersAdd(p);
                //下面被注釋的參數設置方式也是對的可以注釋上面四行而采用下面的語句
                //記住只有參數名有意義順序無關
                //cmdParametersAdd(new OleDbParameter(@courseid ));
                ShowResult(cmdExecuteReader());

　　ConsoleWriteLine(用?做占位符);
                pParameterName = ;
                ShowResult(cmdExecuteReader());
            }
        }

　　static void ShowResult(DbDataReader reader)
        {
            int count = readerFieldCount;
            while(readerRead())
            {
                ConsoleWriteLine();
                for(int i=; i<count; i++)
                {
                    ConsoleWriteLine(stringFormat(Filed[{}]={} i readerGetValue(i)ToString()));
                }
            }
            readerClose();
            ConsoleWriteLine();
        }

　　static void Main(string[] args)
        {
            try
            {
                TestOleCommandText();

　　TestSqlCommandText();
                TestOleStoredProcedure();
                TestSqlStoredProcedure();
            }
            catch (Exception ex)
            {
                ConsoleWriteLine(exMessage);
            }
            ConsoleReadLine();
        }
    }
}

From:http://tw.wingwit.com/Article/program/net/201311/12944.html