(原创)一个方便的.net数据库操作类(很容易扩展，以支持多种数据库)

本文分三部分

1.数据抽象类(只有抽象，没有实现，由继承类去实现，以支持多种数据库)

2.mssql和sqlce数据库操作类的代码

3.一些用法

 

1.数据抽象类

数据库抽象类代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace TestSQL
{
    abstract public class SqlHelper
    {
        public abstract IDbConnection DBConn { get;}  //事务的时候用到

         public abstract IDbConnection GetDBConn();
        public abstract Boolean TestConn();

        public abstract int ExecSQL(string SqlStr);
        public abstract int ExecSQL(string SqlStr, params object[] ParaValues);

        public abstract DataSet DoSelect(string SqlStr);
        public abstract DataSet DoSelect(string SqlStr, params object[] ParaValues);

        public abstract DataTable DoSelectToTable(string SqlStr, string tablename);
        public abstract DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues);

        public abstract IDataReader ExecReader(string SqlStr);
        public abstract IDataReader ExecReader(string SqlStr, params object[] ParaValues);

        public abstract object GetSingle(string SQLString);
        public abstract object GetSingle(string SQLString, params object[] ParaValues);

        public abstract int ExecuteSqlTran(List<String> SQLStringList);

        public abstract bool ColumnExists(string tableName, string columnName);
        public abstract bool TabExists(string TableName);

        #region 公共操作类
        
        #region 取得最大id+1
        public int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        #endregion

        #region 是否存在记录(不带参数)
        public bool HasRecord(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 是否存在记录(带参数)
        public bool HasRecord(string strSql, params object[] ParaValues)
        {
            object obj = GetSingle(strSql, ParaValues);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #endregion
 
    }
}

 

 

2.mssql数据库操作类

mssql数据库操作类代码
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Collections;

namespace TestSQL
{
    public class MssqlDal: SqlHelper
    {
        private SqlConnection F_Conn;        
         private string connStr;

        public MssqlDal(string DBConnStr)
        {
            connStr = DBConnStr;
            F_Conn = GetDBConn() as SqlConnection;
        }

        public override IDbConnection DBConn 
        {
            get { return F_Conn; }
        } 

        #region   连接数据库
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns>IDbConnection</returns>
        public override IDbConnection GetDBConn()
        {
            F_Conn = new SqlConnection(connStr);
            try
            {
                if (ConnectionState.Closed == F_Conn.State)
                {
                    F_Conn.Open();
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                //NLSSysCtrl.NKDbgPrintfW(ex.ToString() + "\r\n");
                MessageBox.Show("数据库连接失败："+ex.Message);
            }
            return F_Conn;
        }
        #endregion

        #region   测试连接数据库
        /// <summary>
        /// 测试连接数据库
        /// </summary>
        /// <returns>Boolean</returns>
        public override Boolean TestConn()
        {
            using (SqlConnection Conn = new SqlConnection(connStr))
            {
                try
                {
                    Conn.Open();
                    return true;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("数据库连接失败：" + ex.Message);
                    return false;
                }
            }
        }
        #endregion

        #region   执行SQL语句
        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>返回影响行数</returns>
        public override int ExecSQL(string SqlStr)
        {
            using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn))
            {
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("执行SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <param name="ParaValues">传入的参数值</param>
        /// <returns>返回影响行数</returns>
        public override int ExecSQL(string SqlStr, params object[] ParaValues)
        {

            using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn))
            {
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("执行SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return 0;
                }
            }
        }
        #endregion

        #region    返回数据集
        /// <summary>
        /// 返回数据集(不带参数)
        /// </summary>
        /// <param name="SqlStr">需要查询的SQL语句</param>
        /// <returns>DataSet</returns>
        public override DataSet DoSelect(string SqlStr)
        {
            try
            {
                SqlDataAdapter F_DataApt = new SqlDataAdapter(SqlStr, F_Conn);
                DataSet F_DataSet = new DataSet();
                F_DataApt.Fill(F_DataSet);
                return F_DataSet;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                return null;
            }
        }

        /// <summary>
        /// 返回数据集(带参数)
        /// </summary>
        /// <param name="SqlStr">需要查询的SQL语句</param>
        /// <param name="ParaValues">传入的参数值</param>
        /// <returns>DataSet</returns>
        public override DataSet DoSelect(string SqlStr, params object[] ParaValues)
        {
            using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues) )
            {
                try
                {
                    SqlDataAdapter F_DataApt = new SqlDataAdapter();
                    F_DataApt.SelectCommand = cmd;
                    DataSet F_DataSet = new DataSet();
                    F_DataApt.Fill(F_DataSet);
                    return F_DataSet;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }
        #endregion

        #region    返回DataTable
        /// <summary>
        /// 返回DataTable (不带参数)
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="tablename">自定义的表名</param>
        /// <returns>DataTable</returns>
        public override DataTable DoSelectToTable(string SqlStr, string tablename)
        {
            DataTable P_tbl;   //声明一个DataTable对象
            try
            {
                SqlDataAdapter F_DataApt = new SqlDataAdapter(SqlStr, F_Conn);
                P_tbl = new DataTable(tablename);
                F_DataApt.Fill(P_tbl);   //将表中对象放入P_tbl中
                return P_tbl;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                return null;
            }
        }

        /// <summary>
        /// 返回DataTable (带参数)
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ParaValues">参数数组</param>
        /// <param name="tablename">自定义的表名</param>
        /// <returns>DataTable</returns>
        public override DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues)
        {
            using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues))
            {
                DataTable P_tbl;   //声明一个DataTable对象
                try
                {
                    SqlDataAdapter F_DataApt = new SqlDataAdapter();
                    F_DataApt.SelectCommand = cmd;
                    P_tbl = new DataTable(tablename);
                    F_DataApt.Fill(P_tbl);   //将表中对象放入P_tbl中
                    return P_tbl;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }

        #endregion

        #region   返回SqlDataReader类型数据

        /// <summary>
        /// 返回SqlDataReader类型数据(不带参数)
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>SqlDataReader</returns>
        public override IDataReader ExecReader(string SqlStr)
        {
            using (SqlCommand cmd = new SqlCommand(SqlStr, F_Conn))
            {
                SqlDataReader P_Dr;
                try
                {
                    P_Dr = cmd.ExecuteReader();
                    return P_Dr;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }

        /// <summary>
        /// 返回SqlDataReader类型数据(带参数)
        /// </summary>
        /// <param name="ParaValues">参数数组</param>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>SqlDataReader</returns>
        public override IDataReader ExecReader(string SqlStr, params object[] ParaValues)
        {
            using (SqlCommand cmd = CreateSqlCommand(SqlStr, ParaValues))
            {
                SqlDataReader P_Dr;
                try
                {
                    P_Dr = cmd.ExecuteReader();
                    return P_Dr;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }
        #endregion

        #region 执行一条计算查询结果语句，返回查询结果（object）。
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString)
        {
            using (SqlCommand cmd = new SqlCommand(SQLString, F_Conn))
            {
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString, params object[] ParaValues)
        {
            using (SqlCommand cmd = CreateSqlCommand(SQLString, ParaValues))
            {
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
            }
        }
        #endregion

        #region   执行多条Sql语句(带事务)
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public override int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = F_Conn;
                SqlTransaction tx = F_Conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        #endregion

        #region   准备sql语句
        /// <summary>
        /// 返回SqlDataReader类型数据(不带参数)
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <param name="values">参数数组</param>
        /// <returns>SqlCommand实例</returns>
        private SqlCommand CreateSqlCommand(string SqlStr, object[] values)
        {
            SqlCommand cmd = new SqlCommand(SqlStr, F_Conn);  //声明SqlCommand对象
            //从Sql语句中循环取得参数，并放到arrlist中
            ArrayList arrlist = new ArrayList();
            int j = 0;
            Boolean Find = false;
            for (int i = 0; i < SqlStr.Length; i++)  
            {
                if (SqlStr[i] == '@')
                {
                    j = i;
                    Find = true;
                }
                if ((SqlStr[i] == ' ' || SqlStr[i] == ')' || SqlStr[i] == ',' || i == SqlStr.Length - 1) && Find == true) //参数结尾标志
                {
                    if (i == SqlStr.Length - 1 && SqlStr[i] != ')') arrlist.Add(SqlStr.Substring(j, i - j+1));
                    else arrlist.Add(SqlStr.Substring(j, i - j));
                    Find = false;
                }
            }
            //赋值给参数
            if (arrlist.Count == values.Length)
            {
                for (int k = 0; k < arrlist.Count; k++)
                {
                    //cmd.Parameters.Add(arrlist[k], null);
                    //cmd.Parameters[k].Value = values[k];
                    //上面两名等同这句 
                    cmd.Parameters.AddWithValue(arrlist[k].ToString(), values[k]);
                }
            }
            else throw new Exception("参数的个数和传入值的个数不匹配！");
            return cmd;
        }

        #endregion

        #region 表是否存在
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public override bool TabExists(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 字段是否存在
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public override bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }
        #endregion

    }
}

 

 

3.sqlce数据库操作类

 

sqlce数据库操作类代码
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
using System.Collections;

//在引用添加System.Data.SqlServerCe;
namespace TestSQL
{
    public class SqlceDal : SqlHelper
    {
        SqlCeConnection F_Conn;        
        public string SqlceConn;

        public SqlceDal(string DBConnStr)
        {
            SqlceConn = DBConnStr;
            F_Conn = GetDBConn() as SqlCeConnection;
        }

        public override IDbConnection DBConn
        {
            get { return F_Conn; }
        }

        #region   连接数据库
        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns>IDbConnection</returns>
        public override IDbConnection GetDBConn()
        {
            F_Conn = new SqlCeConnection(SqlceConn);
            try
            {
                if (ConnectionState.Closed == F_Conn.State)
                {
                    F_Conn.Open();
                }
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                MessageBox.Show("数据库连接失败：" + ex.Message);
            }
            return F_Conn;
        }
        #endregion

        #region   测试连接数据库
        /// <summary>
        /// 测试连接数据库
        /// </summary>
        /// <returns>Boolean</returns>
        public override Boolean TestConn()
        {
            using (SqlCeConnection Conn = new SqlCeConnection(SqlceConn))
            {
                try
                {
                    Conn.Open();
                    return true;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("数据库连接失败：" + ex.Message);
                    return false;
                }
            }
        }
        #endregion

        #region   执行SQL语句
        /// <summary>
        /// 执行不带参数的SQL语句
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>返回影响行数</returns>
        public override int ExecSQL(string SqlStr)
        {
            using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn))
            {
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("执行SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行带参数的SQL语句
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <param name="ParaValues">传入的参数值</param>
        /// <returns>返回影响行数</returns>
        public override int ExecSQL(string SqlStr, params object[] ParaValues)
        {

            using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn))
            {
                try
                {
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("执行SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return 0;
                }
            }
        }
        #endregion

        #region    返回数据集
        /// <summary>
        /// 返回数据集(不带参数)
        /// </summary>
        /// <param name="SqlStr">需要查询的SQL语句</param>
        /// <returns>DataSet</returns>
        public override DataSet DoSelect(string SqlStr)
        {
            try
            {
                SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(SqlStr, F_Conn);
                DataSet F_DataSet = new DataSet();
                F_DataApt.Fill(F_DataSet);
                return F_DataSet;
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                return null;
            }
        }

        /// <summary>
        /// 返回数据集(带参数)
        /// </summary>
        /// <param name="SqlStr">需要查询的SQL语句</param>
        /// <param name="ParaValues">传入的参数值</param>
        /// <returns>DataSet</returns>
        public override DataSet DoSelect(string SqlStr, params object[] ParaValues)
        {
            using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues))
            {
                try
                {
                    SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter();
                    F_DataApt.SelectCommand = cmd;
                    DataSet F_DataSet = new DataSet();
                    F_DataApt.Fill(F_DataSet);
                    return F_DataSet;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }
        #endregion

        #region    返回DataTable
        /// <summary>
        /// 返回DataTable (不带参数)
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="tablename">自定义的表名</param>
        /// <returns>DataTable</returns>
        public override DataTable DoSelectToTable(string SqlStr, string tablename)
        {
            DataTable P_tbl;   //声明一个DataTable对象
            try
            {
                SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter(SqlStr, F_Conn);
                P_tbl = new DataTable(tablename);
                F_DataApt.Fill(P_tbl);   //将表中对象放入P_tbl中
                return P_tbl;
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                return null;
            }
        }

        /// <summary>
        /// 返回DataTable (带参数)
        /// </summary>
        /// <param name="SqlStr">sql语句</param>
        /// <param name="ParaValues">参数数组</param>
        /// <param name="tablename">自定义的表名</param>
        /// <returns>DataTable</returns>
        public override DataTable DoSelectToTable(string SqlStr, string tablename, params object[] ParaValues)
        {
            using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues))
            {
                DataTable P_tbl;   //声明一个DataTable对象
                try
                {
                    SqlCeDataAdapter F_DataApt = new SqlCeDataAdapter();
                    F_DataApt.SelectCommand = cmd;
                    P_tbl = new DataTable(tablename);
                    F_DataApt.Fill(P_tbl);   //将表中对象放入P_tbl中
                    return P_tbl;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }

        #endregion

        #region   返回SqlDataReader类型数据

        /// <summary>
        /// 返回SqlDataReader类型数据(不带参数)
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>SqlDataReader</returns>
        public override IDataReader ExecReader(string SqlStr)
        {
            using (SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn))
            {
                SqlCeDataReader P_Dr;
                try
                {
                    P_Dr = cmd.ExecuteReader();
                    return P_Dr;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }

        /// <summary>
        /// 返回SqlDataReader类型数据(带参数)
        /// </summary>
        /// <param name="ParaValues">参数数组</param>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <returns>SqlDataReader</returns>
        public override IDataReader ExecReader(string SqlStr, params object[] ParaValues)
        {
            using (SqlCeCommand cmd = CreateSqlCommand(SqlStr, ParaValues))
            {
                SqlCeDataReader P_Dr;
                try
                {
                    P_Dr = cmd.ExecuteReader();
                    return P_Dr;
                }
                catch (System.Data.SqlServerCe.SqlCeException ex)
                {
                    MessageBox.Show("查询SQL语句失败：" + ex.Message + "\n" + SqlStr);
                    return null;
                }
            }
        }
        #endregion

        #region 执行一条计算查询结果语句，返回查询结果（object）。
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString)
        {
            using (SqlCeCommand cmd = new SqlCeCommand(SQLString, F_Conn))
            {
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlServerCe.SqlCeException e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public override object GetSingle(string SQLString, params object[] ParaValues)
        {
            using (SqlCeCommand cmd = CreateSqlCommand(SQLString, ParaValues))
            {
                try
                {
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlServerCe.SqlCeException e)
                {
                    throw e;
                }
            }
        }
        #endregion

        #region   执行多条Sql语句(带事务)
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public override int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlCeCommand cmd = new SqlCeCommand())
            {
                cmd.Connection = F_Conn;
                SqlCeTransaction tx = F_Conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        #endregion

        #region   准备sql语句
        /// <summary>
        /// 返回SqlDataReader类型数据(不带参数)
        /// </summary>
        /// <param name="SqlStr">要执行的SQL语句</param>
        /// <param name="values">参数数组</param>
        /// <returns>SqlCommand实例</returns>
        private SqlCeCommand CreateSqlCommand(string SqlStr, object[] values)
        {
            SqlCeCommand cmd = new SqlCeCommand(SqlStr, F_Conn);  //声明SqlCommand对象
            //从Sql语句中循环取得参数，并放到arrlist中
            ArrayList arrlist = new ArrayList();
            int j = 0;
            Boolean Find = false;
            for (int i = 0; i < SqlStr.Length; i++)
            {
                if (SqlStr[i] == '@')
                {
                    j = i;
                    Find = true;
                }
                if ((SqlStr[i] == ' ' || SqlStr[i] == ')' || SqlStr[i] == ',' || i == SqlStr.Length - 1) && Find == true) //参数结尾标志
                {
                    if (i == SqlStr.Length - 1 && SqlStr[i] != ')') arrlist.Add(SqlStr.Substring(j, i - j+1));
                    else arrlist.Add(SqlStr.Substring(j, i - j));
                    Find = false;
                }
            }
            //赋值给参数
            if (arrlist.Count == values.Length)
            {
                for (int k = 0; k < arrlist.Count; k++)
                {
                    //cmd.Parameters.Add(arrlist[k], null);
                    //cmd.Parameters[k].Value = values[k];
                    //上面两名等同这句 
                    cmd.Parameters.AddWithValue(arrlist[k].ToString(), values[k]);
                }
            }
            else throw new Exception("参数的个数和传入值的个数不匹配！");
            return cmd;
        }

        #endregion

        #region 表是否存在
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public override bool TabExists(string TableName)
        {
            string strsql = "select count(*) from information_schema.tables where table_name = '" + TableName + "'";
            object obj = GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 字段是否存在
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public override bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from information_schema.columns where table_name = '" + TableName + "' and column_name='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }
        #endregion

    }
}

 

 

其它的数据操作类也参考上面的来写.

 

3.一些用法

 

执行SQL语句 
private void button1_Click(object sender, EventArgs e)
{
    //生成mssql 实例
    SqlHelper mssql = new MssqlDal("Data Source = 192.168.3.104;Initial Catalog = s7100;USER ID = sa;PASSWORD =");
    //生成sqlce 实例
    string sqlcePath = Path.Combine(Application.StartupPath, "checkgun.sdf");
    SqlHelper sqlce = new SqlceDal(@"Data Source=" + sqlcePath);


    //使用mssql数据库
    //object[] paraValues ={ 15, "新品" };
    //IDataReader dr = mssql.ExecReader("select * from warebase where wbid>@wbid and wbtype=@type", paraValues);

    //下面这句跟上面2句的效果一样
    IDataReader dr = mssql.ExecReader("select * from warebase where wbid>@wbid and wbtype=@type", 15, "新品");
    while (dr.Read()) 
    {
      txtScript.Text = txtScript.Text + dr["wbcname"] + "\r\n"; 
    }
    dr.Close();  //务必要释放

    DataSet ds = sqlce.DoSelect("select * from warebase");
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        txtScript.Text = txtScript.Text + ds.Tables[0].Rows[i]["wbcname"].ToString() + "\r\n";
    }

     //使用sqlce数据库
    DataSet ds = sqlce.DoSelect("select * from globarea where gacode>@code", 1002);
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
        txtScript.Text = txtScript.Text + ds.Tables[0].Rows[i]["ganame"].ToString() + "\r\n";
    }

    DataTable dt = sql.DoSelectToTable("select * from globarea", "gatable");
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        txtScript.Text = txtScript.Text + dt.Rows[i]["ganame"].ToString() + "\r\n";
    }

}

 

范例二:可抽出用于不同数据库间导数据

 

两个库之间拷数据(这里从mssql拷数据到sqlce)
private void button2_Click(object sender, EventArgs e)
        {
            //生成mssql 实例
            SqlHelper mssql = new MssqlDal("Data Source = 192.168.3.104;Initial Catalog = s7100;USER ID = sa;PASSWORD =");
            //生成sqlce 实例
            string sqlcePath = Path.Combine(Application.StartupPath, "checkgun.sdf");
            SqlHelper sqlce = new SqlceDal(@"Data Source=" + sqlcePath);



            //sqlce.ExecSQL("delete from localpara");
            //IDataReader dr = mssql.ExecReader("select * from localpara");
            //或者
            // IDataReader dr = mssql.ExecReader("select lparaid, lparacode, lparaname, lparavalue from localpara");
            //或者
             IDataReader dr = mssql.ExecReader("select dcid lparaid,dccode lparacode,dcname lparaname,dcvalues lparavalue from dbconfig");
            try
            {
                DataTable scheamTable = dr.GetSchemaTable();  //取得表信息

                // 生成 Sqlce 数据插入 SQL 语句
                StringBuilder sbFields = new StringBuilder();
                StringBuilder sbParams = new StringBuilder();
                string field, param;
                DataRow schemaRow;
                for (int i = 0; i < scheamTable.Rows.Count; i++)
                {
                    if (i != 0)
                    {
                        sbFields.Append(", ");
                        sbParams.Append(", ");
                    }

                    schemaRow = scheamTable.Rows[i];
                    field = string.Format("[{0}]", schemaRow["ColumnName"]); //字段名称
                    param = "@" + ((string)schemaRow["ColumnName"]).Replace(" ", "_"); //参数名称
                    sbFields.Append(field);
                    sbParams.Append(param);
                }
                string insertSql = string.Format("INSERT INTO [{0}]({1}) VALUES({2})", "localpara", sbFields, sbParams);

                // 执行数据导入
                object[] values;
                while (dr.Read())
                {
                    values = new object[dr.FieldCount];
                    dr.GetValues(values);
                    sqlce.ExecSQL(insertSql, values);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入失败" + "\r\n" + "错误信息：" + ex.ToString() + "\r\n");
            }
            finally
            {
                if (dr != null && dr.IsClosed == false)
                {
                    dr.Close();
                    dr.Dispose();
                }
            }

        }


后记：

1.这里提供个思路，功能和效率还可以再改进

2.有个疑问：是否每执行一条SQL语句就断开数据库的连接？

欢迎提示改进意见

posted on 2011-01-13 15:22  凌风的博客  阅读(2601)  评论(2)  编辑  收藏  举报
Copyright © 2024 凌风的博客
Powered by .NET 8.0 on Kubernetes Powered by: 博客园