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;

}