一、连接层
先新建App.config
<configuration>
<appSettings>
<!-- Which provider? -->
<!-- <add key="provider" value="System.Data.OleDb" />-->
<add key="provider" value="System.Data.SqlClient" />
</appSettings>
<!-- Here are the connection strings -->
<connectionStrings>
<add name ="AutoLotSqlProvider" connectionString =
"Data Source=(local)\SQLEXPRESS;
Integrated Security=SSPI;Initial Catalog=AutoLot"/>
<add name ="AutoLotOleDbProvider" connectionString =
"Provider=SQLOLEDB;Data Source=(local)\SQLEXPRESS;
Integrated Security=SSPI;Initial Catalog=AutoLot"/>
</connectionStrings>
</configuration>
然后在代码中
string dp =
ConfigurationManager.AppSettings["provider"]; //获取Provider
string cnStr =
ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString; //获取连接字符串
DbProviderFactory df = DbProviderFactories.GetFactory(dp); //得到Factory
#region Use the factory!
using (DbConnection cn = df.CreateConnection()) //创建连接
{
Console.WriteLine("Your connection object is a: {0}", cn.GetType().Name);
cn.ConnectionString = cnStr; //配置连接字符串
cn.Open(); //打开连接
//得到命令对象
DbCommand cmd = df.CreateCommand();
Console.WriteLine("Your command object is a: {0}", cmd.GetType().Name);
cmd.Connection = cn;
cmd.CommandText = "Select * From Inventory";
//读取数据，得到一个数据读取器
using (DbDataReader dr = cmd.ExecuteReader())
{
Console.WriteLine("Your data reader object is a: {0}", dr.GetType().Name);
Console.WriteLine("\n***** Current Inventory *****");
while (dr.Read())
Console.WriteLine("-> Car #{0} is a {1}.",
dr["CarID"], dr["Make"].ToString());
}
}注：1、DBConnection的成员State是ConnnectionState枚举类型，有Broken，Closed，Connnecting,Executing,Fetching,Open 几种。
2、可以使用ConnenctionStringBuider来以编程的方式使用连接字符串，如：
SqlConnenctionStringBuider cnStrBuilder=new SqlConnenctionStringBuider(cnStr);
cnStrBuilder.ConnectionTimeout=10; //修改连接字符串
3、也可以使用参数建立命令对象
SqlCommand myCommand=new SqlCommand(strSQL,cn); #region Open / Close methods
public void OpenConnection(string connectionString)
{
sqlCn = new SqlConnection();
sqlCn.ConnectionString = connectionString;
sqlCn.Open();
}
public void CloseConnection()
{
sqlCn.Close();
}
#endregion
#region Insert method (no param-query)
public void InsertAuto(NewCar car)
{
// Format and execute SQL statement.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"('{0}', '{1}', '{2}', '{3}')", car.CarID, car.Make, car.Color, car.PetName);
// Execute using our connection.
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}
#endregion
#region 插入逻辑
public void InsertAuto(int id, string color, string make, string petName)
{
// Note the "placeholders" in the SQL query.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"(@CarID, @Make, @Color, @PetName)");
// This command will have internal parameters.
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
// Fill params collection.
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.Char;
param.Size = 10;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Color";
param.Value = color;
param.SqlDbType = SqlDbType.Char;
param.Size = 10;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PetName";
param.Value = petName;
param.SqlDbType = SqlDbType.Char;
param.Size = 10;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
}
#endregion
#region Delete method
public void DeleteCar(int id)
{
// Get ID of car to delete, then do so.
string sql = string.Format("Delete from Inventory where CarID = '{0}'",
id);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Exception error = new Exception("Sorry! That car is on order!", ex);
throw error;
}
}
}
#endregion
#region Update method
public void UpdateCarPetName(int id, string newPetName)
{
// Get ID of car to modify and new pet name.
string sql =
string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
newPetName, id);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
cmd.ExecuteNonQuery();
}
}
#endregion
#region Select methods
public DataTable GetAllInventoryAsDataTable()
{
// This will hold the records.
DataTable inv = new DataTable();
// Prep command object.
string sql = "Select * From Inventory";
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
SqlDataReader dr = cmd.ExecuteReader();
// Fill the DataTable with data from the reader and clean up.
inv.Load(dr);
dr.Close();
}
return inv;
}
public List<NewCar> GetAllInventoryAsList()
{
// This will hold the records.
List<NewCar> inv = new List<NewCar>();
// Prep command object.
string sql = "Select * From Inventory";
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
inv.Add(new NewCar
{
CarID = (int)dr["CarID"],
Color = (string)dr["Color"],
Make = (string)dr["Make"],
PetName = (string)dr["PetName"]
});
}
dr.Close();
}
return inv;
}
#endregion
#region Look up pet name
public string LookUpPetName(int carID)
{
string carPetName = string.Empty;
// Establish name of stored proc.
using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Input param.
SqlParameter param = new SqlParameter();
param.ParameterName = "@carID";
param.SqlDbType = SqlDbType.Int;
param.Value = carID;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
// Output param.
param = new SqlParameter();
param.ParameterName = "@petName";
param.SqlDbType = SqlDbType.Char;
param.Size = 10;
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
// Execute the stored proc.
cmd.ExecuteNonQuery();
// Return output param.
carPetName = (string)cmd.Parameters["@petName"].Value;
}
return carPetName;
}
#endregion
#region Tx method
// A new member of the InventoryDAL class.
public void ProcessCreditRisk(bool throwEx, int custID)
{
// First, look up current name based on customer ID.
string fName = string.Empty;
string lName = string.Empty;
SqlCommand cmdSelect = new SqlCommand(
string.Format("Select * from Customers where CustID = {0}", custID), sqlCn);
using (SqlDataReader dr = cmdSelect.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
fName = (string)dr["FirstName"];
lName = (string)dr["LastName"];
}
else
return;
}
// Create command objects that represent each step of the operation.
SqlCommand cmdRemove = new SqlCommand(
string.Format("Delete from Customers where CustID = {0}", custID), sqlCn);
SqlCommand cmdInsert = new SqlCommand(string.Format("Insert Into CreditRisks" +
"(CustID, FirstName, LastName) Values" +
"({0}, '{1}', '{2}')", custID, fName, lName), sqlCn);
// We will get this from the connection object.
SqlTransaction tx = null;
try
{
tx = sqlCn.BeginTransaction();
// 将命令加入到事务中
cmdInsert.Transaction = tx;
cmdRemove.Transaction = tx;
// Execute the commands.
cmdInsert.ExecuteNonQuery();
cmdRemove.ExecuteNonQuery();
// Simulate error.
if (throwEx)
{
throw new Exception("Sorry! Database error! Tx failed...");
}
// Commit it!
tx.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
// Any error will roll back transaction.
tx.Rollback();
}
}
#endregion主要是SqlTransaction
二、断开层
1、数据通过数据适配器而不是数据读取器获取，而且数据适配器对象通过DataSet在调用者和数据元之间传递数据，Dataset可以包含多个DataTable，而Datatable又是DataColumn和DataRow的集合。
DataSet包含三个强类型的集合，DataTableCollection，DataRelationCollection，PropertyCollection。
使用PropertyCollection：
DataSet carsInventoryDS = new DataSet("Car Inventory");
carsInventoryDS.ExtendedProperties["TimeStamp"] = DateTime.Now;
carsInventoryDS.ExtendedProperties["DataSetID"] = Guid.NewGuid();
carsInventoryDS.ExtendedProperties["Company"] = "Mikko’s Hot Tub Super Store";
2、DataColumn
//创建DataColumn
DataColumn carIDColumn = new DataColumn("CarID", typeof(int));
carIDColumn.Caption = "Car ID";
carIDColumn.ReadOnly = true;
carIDColumn.AllowDBNull = false;
carIDColumn.Unique = true;
carIDColumn.Unique = true;
//启用自增列
carIDColumn.AutoIncrement = true;
carIDColumn.AutoIncrementSeed = 0;
carIDColumn.AutoIncrementStep = 1;
把Datacolumn加入到DataTable
DataTable inventoryTable = new DataTable("Inventory");
inventoryTable.Columns.AddRange(new DataColumn[] { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn });
3、使用DataRow
carRow = inventoryTable.NewRow();
// Column 0 is the autoincremented ID field,
// so start at 1.
carRow[1] = "Saab";
carRow[2] = "Red";
carRow[3] = "Sea Breeze";
inventoryTable.Rows.Add(carRow);
4、将DataTable插入到DataSet
ds.Tables.Add(inventoryTable);
//反之，获取Datatable
foreach(Datatable dt in ds.Tables)
{
//处理。。。。
}
可以使用DataTableReader来处理Datatable
DataTableReader dtReader=dt.CreateDataReader();
6、DataSet和XML之间可以通过WriteXML()和ReadXML()相互转换。
7、
扫一扫
415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
