ADO.NET資料庫通路使用範例（三）複用代碼

例一、資料通路層的建立

使用SqlConnection，SqlCommand，SqlDataReader的類進行資料庫操作時，需要編寫大量機械的代碼，使用編寫資料庫通路類程式集，可以将這些重複的工作進行封裝和複用。提高效率。

建立一個類庫，生成AutoLotDAL（DataAccessLayer資料通路層）類庫項目。

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data;

namespace AutoLotDAL

{

public class InventoryDAL

{

private SqlConnection sqlCn = new SqlConnection();

public void OpenConnection(string conString)

{

sqlCn.ConnectionString = conString;

sqlCn.Open();

}

public void CloseConnection()

{

sqlCn.Close();

}

public void InsertAuto(int id, string color, string make, string petName)

{

string sql = string.Format("Insert into Inventory" +

"(CarID,Make,Color,PetName) values" +

"('{0}','{1}','{2}','{3}')", id, make, color, petName);

using (SqlCommand cmd = new SqlCommand(sql, sqlCn))

{

cmd.ExecuteNonQuery();

}

}

public void UpdateCarPetName(int id, string newPetName)

{

string sql = string.Format("Update Inventory set PetName='{0}' where CarID='{1}'", newPetName

, id);

using (SqlCommand cmd = new SqlCommand(sql, sqlCn))

{

cmd.ExecuteNonQuery();

}

}

public DataTable GetAllInventory()

{

DataTable inv = new DataTable();

string sql = "Select * from Inventory";

using (SqlCommand cmd = new SqlCommand(sql, sqlCn))

{

SqlDataReader sdr = cmd.ExecuteReader();

//使用DataTable 的load方法将SqlDataReader讀入到DataTable中

inv.Load(sdr);

sdr.Close();

}

return inv;

}

}

}

使用範例，首先添加引用，然後編寫

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.Common;

using System.Data;

using System.Configuration;

using AutoLotDAL;

namespace DataProviderFactory

{

class Program02

{

static void Main(string[] args)

{

Console.WriteLine("*****使用Provder Factory示例******");

SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder();

conStringBuilder.InitialCatalog = "AutoLot";

conStringBuilder.DataSource = @"(local)/sql2005";

conStringBuilder.IntegratedSecurity = true;

conStringBuilder.ConnectTimeout = 30;

InventoryDAL inv = new InventoryDAL();

inv.OpenConnection(conStringBuilder.ConnectionString);

DataTable dt = inv.GetAllInventory();

foreach (DataRow row in dt.Rows)

{

foreach (DataColumn dc in dt.Columns)

{

Console.Write(row[dc].ToString()+"/t");

}

Console.WriteLine();

}

inv.CloseConnection();

Console.ReadLine();

}

}

}

例二、使用參數化查詢改寫InsertAuto

改寫AutoLotDAL.cs檔案中

……

public void InsertAuto(int id, string color, string make, string petName)

{

string sql = string.Format("Insert into Inventory" +

"(CarID,Make,Color,PetName) values" +

"('{0}','{1}','{2}','{3}')", id, make, color, petName);

using (SqlCommand cmd = new SqlCommand(sql, sqlCn))

{

cmd.ExecuteNonQuery();

}

}

public void InsertAutoUsingParameters(int id, string color, string make, string petName)

{

string sql = string.Format("Insert into Inventory" +

"(CarID,Make,Color,PetName) values" +

"(@CarID,@Make,@Color,@PetName)");

using (SqlCommand cmd = new SqlCommand(sql,sqlCn))

{

SqlParameter param = new SqlParameter();

param.ParameterName = "@CarID";

param.Value = id;

param.SqlDbType = SqlDbType.Int;

cmd.Parameters.Add(param);

param = new SqlParameter();

param.ParameterName = "@Make";

param.Value = make;

param.SqlDbType = SqlDbType.NVarChar;

param.Size = 10;

cmd.Parameters.Add(param);

param = new SqlParameter();

param.ParameterName = "@Color";

param.Value = color;

param.SqlDbType = SqlDbType.NVarChar;

param.Size = 10;

cmd.Parameters.Add(param);

param = new SqlParameter();

param.ParameterName = "@PetName";

param.Value = petName;

param.SqlDbType = SqlDbType.NVarChar;

param.Size = 10;

cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

}

}

……

使用Main示例

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data.Common;

using System.Data;

using System.Configuration;

using AutoLotDAL;

namespace DataProviderFactory

{

class Program02

{

static void Main(string[] args)

{

Console.WriteLine("*****使用Provder Factory示例******");

SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder();

conStringBuilder.InitialCatalog = "AutoLot";

conStringBuilder.DataSource = @"(local)/sql2005";

conStringBuilder.IntegratedSecurity = true;

conStringBuilder.ConnectTimeout = 30;

InventoryDAL inv = new InventoryDAL();

inv.OpenConnection(conStringBuilder.ConnectionString);

Console.WriteLine("******在插入資料之前*****");

PrintInventory(inv);

Console.WriteLine("******在插入資料1之後*****");

inv.InsertAuto(12, "Black", "BMW", "zz");

PrintInventory(inv);

Console.WriteLine("******在插入資料2之後*****");

inv.InsertAutoUsingParameters(11, "Black", "BMW", "zz");

PrintInventory(inv);

inv.CloseConnection();

Console.ReadLine();

}

private static void PrintInventory(InventoryDAL inv)

{

DataTable dt = inv.GetAllInventory();

foreach (DataRow row in dt.Rows)

{

foreach (DataColumn dc in dt.Columns)

{

Console.Write(row[dc].ToString() + "/t");

}

Console.WriteLine();

}

}

}

}

例三、使用存儲過程進行資料查詢

在AutoLotDAL中添加一個GetPetName方法，用于傳回一個指定CarID的PetName，注意存儲過程調用後的output傳回類型，可以通過SqlCommand的Parameters索引獲得其值。注意參數名稱，大小寫必須和存儲過程的大小寫一緻。

public string LookUpPetName(int carID)

{

string carPetName = string.Empty;

using (SqlCommand cmd = new SqlCommand("GetPetName", sqlCn))

{

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

param.SqlDbType = SqlDbType.Int;

param.Value = carID;

param.ParameterName = "@carID";

cmd.Parameters.Add(param);

param = new SqlParameter();

param.SqlDbType = SqlDbType.Char;

param.Size = 10;

param.ParameterName = "@petName";

param.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

//傳回輸出參數

carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();

}

return carPetName;

}