//**************************************************
// *
// * Author : Sophia Wang
// *
// * Date : 20070423
// *
// * Purpose : 数据库控制器
// *
//**************************************************
using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
namespace Library {
public class DBController {
private static string _connectionString = ConfigurationManager.ConnectionStrings["InfoConnection"].ConnectionString;
private static bool _hasConnection = false;
private static OdbcConnection _sharedConnection;
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns></returns>
private static OdbcConnection CreateConnection() {
if (!_hasConnection) {
_hasConnection = true;
_sharedConnection = new OdbcConnection(_connectionString);
}
else {
if (_sharedConnection == null) {
_sharedConnection = new OdbcConnection(_connectionString);
}
_sharedConnection.ConnectionString = _connectionString;
}
return _sharedConnection;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public static void CloseConnection() {
if (_sharedConnection != null) {
_sharedConnection.Close();
}
}
/// <summary>
/// 执行ExecuteNonQuery
/// </summary>
/// <param name="commandText">查询文本</param>
public static void ExecuteNonQuery(string commandText) {
using (OdbcConnection connection = CreateConnection()) {
connection.Open();
OdbcCommand cmd = new OdbcCommand(commandText, connection);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行ExecuteNonQuery
/// </summary>
/// <param name="commandText">查询文本</param>
/// <param name="parameters">参数集合</param>
public static void ExecuteNonQuery(string commandText, DataParameterCollection parameters) {
using (OdbcConnection connection = CreateConnection()) {
connection.Open();
OdbcCommand cmd = new OdbcCommand(commandText, connection);
foreach (OdbcParameter parameter in parameters.DataParameters) {
cmd.Parameters.Add(parameter);
}
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行ExecuteReader
/// </summary>
/// <param name="commandText">查询文本</param>
/// <returns></returns>
public static OdbcDataReader ExecuteReader(string commandText) {
OdbcConnection connection = CreateConnection();
connection.Open();
OdbcCommand cmd = new OdbcCommand(commandText, connection);
return cmd.ExecuteReader();
}
/// <summary>
/// 不带参数的查询
/// </summary>
/// <param name="commandText">查询语句</param>
/// <returns>DataTable</returns>
public static DataTable LoadData(string commandText) {
DataTable table = new DataTable();
using (OdbcConnection connection = CreateConnection()) {
connection.Open();
OdbcDataAdapter adapter = new OdbcDataAdapter(commandText, connection);
adapter.Fill(table);
adapter.Dispose();
}
return table;
}
/// <summary>
/// 带参数的查询
/// </summary>
/// <param name="commandText">查询文本</param>
/// <param name="parameters">参数集合</param>
/// <returns>DataTable</returns>
public static DataTable LoadData(string commandText, DataParameterCollection parameters) {
DataTable table = new DataTable();
using (OdbcConnection connection = CreateConnection()) {
connection.Open();
OdbcCommand selectCommand = new OdbcCommand(commandText, connection);
foreach (OdbcParameter param in parameters.DataParameters) {
selectCommand.Parameters.Add(param);
}
OdbcDataAdapter adapter = new OdbcDataAdapter(selectCommand);
adapter.Fill(table);
selectCommand.Dispose();
adapter.Dispose();
}
return table;
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="parameterName">参数名</param>
/// <param name="type">类型</param>
/// <param name="size">大小</param>
/// <param name="sourceColumn">数据源字段名</param>
/// <returns>OdbcParameter</returns>
public static OdbcParameter CreateParameter(string parameterName,OdbcType type,int size,string sourceColumn) {
return new OdbcParameter(parameterName, type, size, sourceColumn);
}
}
}
****************************************************************************************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;
namespace Library {
public class DataParameterCollection {
public DataParameterCollection() {
}
private List<OdbcParameter> dataParameters;
public List<OdbcParameter> DataParameters {
get {
if (dataParameters == null)
dataParameters = new List<OdbcParameter>();
return dataParameters;
}
set {
dataParameters = value;
}
}
}
}
***************************************************************************************
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="InfoConnection" providerName="System.Data.Odbc" connectionString ="Dsn=North;uid=sa;pwd=sa"/>
</connectionStrings>
</configuration>
***************************************************************************************
应用
不带参数的查询***************************************
string statement = "select * from Customers";
dtgvData.DataSource = DBController.LoadData(statement);
带参数的查询***************************************
string statement = "select * from Customers where CustomerID = ? and City = ?";
DataParameterCollection parameters = new DataParameterCollection();
parameters.DataParameters.Add(DBController.CreateParameter("@CustomerID", OdbcType.Char, 5, "CustomerID"));
parameters.DataParameters[0].Value = "ALFKI";
parameters.DataParameters.Add(DBController.CreateParameter("@City", OdbcType.Char, 15, "City"));
parameters.DataParameters[1].Value = "Berlin";
dtgvData.DataSource = DBController.LoadData(statement, parameters);
DataReader读数据***************************************
string statement = "select * from Customers";
IDataReader reader = DBController.ExecuteReader(statement);
StringBuilder builder = new StringBuilder();
try {
while (reader.Read()) {
builder.Append(reader.GetString(0)).Append("***");
}
}
finally {
reader.Close();
DBController.CloseConnection();
}
textBox1.Text = builder.ToString();
DataCommand写数据***************************************
string statement = "Update Customers set City ='China' where CustomerID = 'ALFKI'";
DBController.ExecuteNonQuery(statement);
DataCommand带参数写数据***************************************
string statement = "Update Customers set City =? where CustomerID = ?";
DataParameterCollection parameters = new DataParameterCollection();
parameters.DataParameters.Add(DBController.CreateParameter("@City", OdbcType.Char, 15, "City"));
parameters.DataParameters[0].Value = "Berlin";
parameters.DataParameters.Add(DBController.CreateParameter("@CustomerID", OdbcType.Char, 5, "CustomerID"));
parameters.DataParameters[1].Value = "ALFKI";
DBController.ExecuteNonQuery(statement,parameters);