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

于 2011-01-13 15:22:00 发布
阅读量102 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/dreamszx/archive/2011/01/13/1934598.html
版权

本文分三部分

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

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

3.一些用法

 

1.数据抽象类

ExpandedBlockStart.gif 数据库抽象类代码
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数据库操作类

ExpandedBlockStart.gif 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数据库操作类

 

ExpandedBlockStart.gif 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.一些用法

 

ExpandedBlockStart.gif 执行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 " ;
    }

}

 

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

 

ExpandedBlockStart.gif 两个库之间拷数据(这里从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语句就断开数据库的连接？

欢迎提示改进意见

转载于:https://www.cnblogs.com/dreamszx/archive/2011/01/13/1934598.html

weixin_30740581
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值