VS2005数据库操作示例

来源：互联网发布：vmware mac 下载编辑：程序博客网时间：2024/05/18 11:23

//**************************************************
// *
// * 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);