Microsoft Application Blocks系列 (1)
介紹與使用資料存取應用程式模組(DAAB)
文／沈炳宏

-----box-----
#程式1
SqlConnection myConnection = new SqlConnection(connectionString);
string strSQL = @"SELECT ProductName, UnitPrice, UnitsInStock 
		    FROM Products
		    WHERE CategoryID = @CatID
		    ORDER BY UnitPrice";
SqlCommand myCommand = new SqlCommand(strSQL, myConnection);
SqlParameter catParam = new SqlParameter("@CatID", categoryID);
myCommand.Parameters.Add(catParam);
myConnection.Open();
SqlDataReader reader = 
        myCommand.ExecuteReader(CommandBehavior.CloseConnection);
dgProducts.DataSource = reader;
dgProducts.DataBind();
reader.Close();
myConnection.Close();
-----end-----

-----box-----
#程式2
C#：using Microsoft.ApplicationBlocks.Data;
VB.NET：Imports Microsoft.ApplicationBlocks.Data
ASP.NET In-Line Code：<%@ Import Namespace="Microsoft.ApplicationBlocks.Data" %>
-----box-----

-----box-----
#程式3
ExecuteReader(connectionString,commandType, 
              query, parameters)
-----box-----

-----box-----
#程式4
SqlConnection myConnection = new SqlConnection(connString);
const string strSQL = "SELECT CategoryID, CategoryName 
                  FROM Categories ORDER BY CategoryName";
SqlCommand myCommand = new SqlCommand(strSQL, myConnection);
myConnection.Open();
SqlDataReader reader =  myCommand.ExecuteReader(CommandBehavior.CloseConnection);
-----box-----

-----box-----
#程式5
const string strSQL = "SELECT CategoryID, CategoryName 
               FROM Categories ORDER BY CategoryName";
SqlDataReader reader = SqlHelper.ExecuteReader(
                connString, CommandType.Text, strSQL);
-----box-----

-----box-----
#程式6
SELECT ... FROM Products
WHERE CategoryID = @CategoryID
-----box-----

-----box-----
#程式7
const string strSQL = @"SELECT ... 
				FROM Products
				WHERE CategoryID = @CategoryID
				ORDER BY UnitPrice";
dgProducts.DataSource = SqlHelper.ExecuteDataset(connString,
CommandType.Text, strSQL, new SqlParameter("@CategoryID", value));
-----box-----

-----box-----
#程式8
dgProducts.DataSource = 
       SqlHelper.ExecuteDataset(connString,
				"getProducts", value);
-----box-----

-----box-----
#程式9
dgProducts.DataSource = SqlHelper.ExecuteDataset(connString,
		  CommandType.Text, query, 
          new SqlParameter("@ParamName1", value1),
		  new SqlParameter("@ParamName2", value2), 
		  …
		  new SqlParameter("@ParamNameN", valueN));
-----box-----

-----box-----
#程式10
dgProducts.DataSource = 
     SqlHelper.ExecuteDataset(connString,
     "getProducts", value1, value2, …, valueN); 
-----box-----

-----box-----
#程式11
case "SqlServer":
assembly = ConfigurationSettings.AppSettings["SqlServerHelperAssembly"];
type = ConfigurationSettings.AppSettings["SqlServerHelperType"];
break;
case "OleDb":
assembly = ConfigurationSettings.AppSettings["OleDbHelperAssembly"];
type = ConfigurationSettings.AppSettings["OleDbHelperType"];
break;
case "Odbc":
assembly = ConfigurationSettings.AppSettings["OdbcHelperAssembly"];
type = ConfigurationSettings.AppSettings["OdbcHelperType"];
break;
case "Oracle":
assembly = ConfigurationSettings.AppSettings["OracleHelperAssembly"];
type = ConfigurationSettings.AppSettings["OracleHelperType"];
break;
-----box-----

-----box-----
#程式12
private void LoadCustomers()
		{
			//選擇資料庫使用組件
			AdoHelper helper = GetAdoHelper();
			//選擇資料庫連線字串
			string cs = GetConnectionString();

			DataSet ds = new DataSet();
			//透過AdoHelper類別的FillDataset方法載入資料到//Dataset中
			helper.FillDataset( cs, CommandType.Text, 
					"Select * from Customer", ds, new string[] { "Customers" } );

			customerGrid.DataSource = ds;
			customerGrid.DataMember = "Customers";
		}
-----box-----