DbConnection：代表資料庫的連接。
DbConnection.ConnectionString：取得或設定用來開啟連接的字串。
DbConnection.Database：取得開啟連接後，目前資料庫的名稱或開啟連接之前，連接字串中指定的資料庫名稱。
DbConnection.DataSource：取得要連接的資料庫伺服器的名稱。
DbConnection.State：取得描述連接狀態的字串。
DbConnection.CreateDbCommand()：建立並傳回DbCommand與目前連接相關聯的物件。
DbCommand：表示要對資料來源執行的SQL指令或預存程序。
DbCommand.CommandText：取得或設定要針對資料來源執行的SQL指令。
DbCommand.CommandType：指定指令的屬性是SQL指令或預存程序。
DbCommand.Connection：取得或設定使用的DbCommand。
DbCommand.DbParameterCollection：取得DbParameter集合物件。
DbCommand.ExecuteNonQuery()：執行非查詢SQL指令。
DbCommand.ExecuteReader()：執行SQL查詢指令並傳回DbDataReader。
DbCommand.ExecuteScalar()：執行SQL查詢指令並傳回第一個資料列的第一個資料行中查詢所傳回的結果集。
DbDataAdapter：代表一組SQL指令和一個資料庫連接，用來填入DataSet並更新資料來源。
DbDataAdapter.SelectCommand：取得或設定用來查詢資料的DbCommand。
DbDataAdapter.InsertCommand：取得或設定用來新增資料的DbCommand。
DbDataAdapter.UpdateCommand：取得或設定用來更新資料的DbCommand。
DbDataAdapter.DeleteCommand：取得或設定用來刪除資料的DbCommand。
DbDataAdapter.Fill(DataSet)：將查詢結果填入DataSet。
DbDataAdapter.Fill(DataTable)：將查詢結果填入DataTable。
DbDataAdapter.Update(DataSet)：透過DataSet，更新資料庫中的值
DbDataAdapter.Update(DataTable)：透過DataTable，更新資料庫中的值
DbDataAdapter.Update(DataRow[])：透過DataRow，更新資料庫中的值
DataSet一旦連接到資料來源並透過DataAdapter取得資料後，就會立刻離線，並且把獲得的資料放在主機的記憶體裡面，等待日後應用。
DbProviderFactories：表示用於建立DbProviderFactory類別之一組靜態方法。
DbProviderFactories.GetFactory(String InvariantName)：傳回 DbProviderFactory 的執行個體。
DbProviderFactories.GetFactoryClasses()：查詢所有可用的InvariantName，回傳DataTable型別。
DbProviderFactory：表示用於建立資料來源之提供者。
DbProviderFactory.CreateCommand()：傳回提供者類別實作的DbCommand。
DbProviderFactory.CreateConnection()：傳回提供者類別實作的DbConnection。
DbProviderFactory.CreateDataAdapter()：傳回提供者類別實作的DbDataAdapter。
ADO.NET存取資料庫完整程式碼：
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace Quest.Models.DbHelper
{
public class GenericDbHelper : IDisposable
{
protected string HostName;
protected string DBName;
protected string connectionString;
protected string providerInvariantName;
protected DbProviderFactory dbfactory;
public GenericDbHelper()
{
HostName = "localhost";
DBName = "MyDB";
//MSSQL 連線字串
connectionString = string.Format(@"Data Source={0};Initial Catalog={1};User ID=xxxx;Password=xxxxxxxxx", HostName, DBName);
providerInvariantName = "System.Data.SqlClient";
//MySQL 連線字串
//connectionString = string.Format(@"Server={0};Database={1};UID=xxxx;Password=xxxxxxx", HostName, DBName);
//providerInvariantName = "MySql.Data.MySqlClient";
dbfactory = DbProviderFactories.GetFactory(providerInvariantName);
}
// 取得DbConnection
public virtual DbConnection CreateConnection()
{
var dbConnection = dbfactory.CreateConnection();
dbConnection.ConnectionString = connectionString;
return dbConnection;
}
// 取得DbCommand
public virtual DbCommand CreateCommand()
{
return dbfactory.CreateCommand();
}
// 取得DbCommandBuilder
public DbCommandBuilder CreateCommandBuilder()
{
return dbfactory.CreateCommandBuilder();
}
// 取得DbDataAdapter
public virtual DbDataAdapter CreateDataAdapter()
{
return dbfactory.CreateDataAdapter();
}
// 取得DbParameter
public virtual DbParameter CreateParameter()
{
return dbfactory.CreateParameter();
}
// 取得DbParameter
public virtual DbParameter CreateParameter(string ParameterName, object Value, DbType dbType)
{
DbParameter dbParameter = dbfactory.CreateParameter();
dbParameter.ParameterName = ParameterName;
dbParameter.Value = Value;
dbParameter.DbType = dbType;
return dbParameter;
}
// 取得DbParameter
public virtual DbParameter CreateParameter(string ParameterName, object Value)
{
DbParameter dbParameter = dbfactory.CreateParameter();
dbParameter.ParameterName = ParameterName;
dbParameter.Value = Value;
return dbParameter;
}
// 執行查詢語法，回傳DataTable
public virtual DataTable ExcuteQuery(string sql, params DbParameter[] values)
{
return ExcuteQuery(sql, CommandType.Text, values);
}
// 執行查詢語法，回傳DataTable
public virtual DataTable ExcuteQuery(string sql, CommandType commandType, params DbParameter[] values)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = commandType;
dbCommand.CommandText = sql;
if (values != null && values.Length > 0)
{
dbCommand.Parameters.AddRange(values);
}
DataTable dt = new DataTable();
using (DbDataAdapter dbAdapter = dbfactory.CreateDataAdapter())
{
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dt);
dbConnection.Close();
return dt;
}
}
}
}
// 執行查詢語法，回傳DataTable
public virtual DataTable ExcuteQuery(DbCommand dbCommand)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
dbCommand.Connection = dbConnection;
DataTable dt = new DataTable();
using (DbDataAdapter dbAdapter = dbfactory.CreateDataAdapter())
{
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dt);
dbConnection.Close();
return dt;
}
}
}
// 執行查詢語法，回傳DbDataReader
public virtual DbDataReader ExecuteReader(string sql, params DbParameter[] values)
{
return ExecuteReader(sql, CommandType.Text, values);
}
// 執行查詢語法，回傳DbDataReader
public virtual DbDataReader ExecuteReader(string sql, CommandType commandType, params DbParameter[] values)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = commandType;
dbCommand.CommandText = sql;
if (values != null && values.Length > 0)
{
dbCommand.Parameters.AddRange(values);
}
return dbCommand.ExecuteReader();
}
}
}
// 執行查詢語法，回傳DbDataReader
public virtual DbDataReader ExecuteReader(DbCommand dbCommand)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
dbCommand.Connection = dbConnection;
return dbCommand.ExecuteReader();
}
}
// 執行查詢語法，回傳object
public virtual object ExecuteScalar(string sql, params DbParameter[] values)
{
return ExecuteScalar(sql, CommandType.Text, values);
}
// 執行查詢語法，回傳object
public virtual object ExecuteScalar(string sql, CommandType commandType, params DbParameter[] values)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = commandType;
dbCommand.CommandText = sql;
if (values != null && values.Length > 0)
{
dbCommand.Parameters.AddRange(values);
}
return dbCommand.ExecuteScalar();
}
}
}
// 執行查詢語法，回傳object
public virtual object ExecuteScalar(DbCommand dbCommand)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
dbCommand.Connection = dbConnection;
return dbCommand.ExecuteScalar();
}
}
// 執行單筆SQL異動語法
public virtual bool ExcuteNonQuery(string sql, bool isTran, params DbParameter[] values)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = sql;
if (values != null && values.Length > 0)
{
dbCommand.Parameters.AddRange(values);
}
//啟用交易
if (isTran)
{
using (DbTransaction tran = dbConnection.BeginTransaction())
{
dbCommand.Transaction = tran;
try
{
dbCommand.ExecuteNonQuery();
tran.Commit();
dbConnection.Close();
return true;
}
catch (Exception)
{
tran.Rollback();
dbConnection.Close();
return false;
}
}
}
//不啟用交易
else
{
try
{
dbCommand.ExecuteNonQuery();
dbConnection.Close();
return true;
}
catch (Exception)
{
dbConnection.Close();
return false;
}
}
}
}
}
// 執行單筆SQL異動語法
public virtual bool ExcuteNonQuery(DbCommand dbCommand, bool isTran = true)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
dbCommand.Connection = dbConnection;
//啟用交易
if (isTran)
{
using (DbTransaction tran = dbConnection.BeginTransaction())
{
try
{
dbCommand.Transaction = tran;
dbCommand.ExecuteNonQuery();
tran.Commit();
dbConnection.Close();
return true;
}
catch (Exception)
{
tran.Rollback();
dbConnection.Close();
return false;
}
}
}
//不啟用交易
else
{
try
{
dbCommand.ExecuteNonQuery();
return true;
}
catch (Exception)
{
dbConnection.Close();
return false;
}
}
}
}
// 執行多筆SQL異動語法
public virtual bool ExcuteNonQuery(IEnumerable dbCommands, bool isTran = true)
{
using (DbConnection dbConnection = CreateConnection())
{
dbConnection.Open();
//啟用交易
if (isTran)
{
using (DbTransaction tran = dbConnection.BeginTransaction())
{
try
{
foreach (var dbCommand in dbCommands)
{
dbCommand.Connection = dbConnection;
dbCommand.Transaction = tran;
dbCommand.ExecuteNonQuery();
}
tran.Commit();
dbConnection.Close();
return true;
}
catch (Exception)
{
tran.Rollback();
dbConnection.Close();
return false;
}
}
}
//不啟用交易
else
{
try
{
foreach (var dbCommand in dbCommands)
{
dbCommand.Connection = dbConnection;
dbCommand.ExecuteNonQuery();
}
return true;
}
catch (Exception)
{
dbConnection.Close();
return false;
}
}
}
}
// 執行單筆局部SQL異動語法，由外部決定如何交易處理
public virtual void ExcutePartialNonQuery(DbConnection dbConnection, DbTransaction tran, string sql, params DbParameter[] values)
{
ExcutePartialNonQuery(dbConnection, tran, sql, CommandType.Text, values);
}
// 執行單筆局部SQL異動語法，由外部決定如何交易處理
public virtual void ExcutePartialNonQuery(DbConnection dbConnection, DbTransaction tran, string sql, CommandType commandType, params DbParameter[] values)
{
using (DbCommand dbCommand = dbfactory.CreateCommand())
{
dbCommand.Connection = dbConnection;
dbCommand.Transaction = tran;
dbCommand.CommandType = commandType;
dbCommand.CommandText = sql;
if (values != null && values.Length > 0)
{
dbCommand.Parameters.AddRange(values);
}
ExcutePartialNonQuery(dbCommand);
}
}
// 執行單筆局部SQL異動語法，由外部決定如何交易處理
public virtual void ExcutePartialNonQuery(DbCommand dbCommand)
{
dbCommand.ExecuteNonQuery();
}
// 執行多筆局部SQL異動語法，由外部決定如何交易處理
public virtual void ExcutePartialNonQuery(IEnumerable dbCommands)
{
foreach (var dbCommand in dbCommands)
{
dbCommand.ExecuteNonQuery();
}
}
#region IDisposable Support
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (dbfactory != null)
{
dbfactory = null;
}
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
}
} 
沒有留言:
張貼留言