SqlServerCE数据库类

 

using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Data.Common;

namespace Foundation
{
    /**//// <summary>
    /// 本类为：SQLCE数据库的一些基本操作。在使用前，先将数据库的连接字符串确认好，然后使用；
    /// 本类目的为了方便大家更好的使用SQLCE数据库；
    /// 本类为开源代码；
    /// 有使用本类所产生的一切后果由使用者自负
    /// </summary>
    public class DataBase
    {
        private String Server, dbName, UID, Password;
        private String SqlCeConnectionString;
        private SqlCeConnection con;        

        public DataBase()
        {
            // 初始化数据库连接字符串
            Server = "Input Correct string!";
            dbName = "Input Correct string!";
            UID = "Input Correct string!";
            Password = "Input Correct string!";
            SqlCeConnectionString = "user id=" + UID + ";password=" + Password + ";initial catalog=" + dbName + ";data source=" + Server + ";Connect Timeout=30";
        }
        /**//// <summary>
        /// 打开数据库连接。
        /// </summary>
        private void Open() 
        {
            if (con == null) 
            {
                con = new SqlCeConnection(SqlCeConnectionString);
                con.Open();
            }
        }

        /**//// <summary>
        /// 关闭数据库连接。
        /// </summary>
        public void Close() 
        {
            if (con != null)
            {
                con.Close();
                this.Dispose();
            }    
        }

        /**//// <summary>
        /// Release resources.
        /// 释放资源。
        /// </summary>
        public void Dispose() 
        {
            // make sure connection is closed
            if (con != null) 
            {
                con.Dispose();
                con = null;
            }                
        }

        /**//// <summary>
        /// 测试数据库连接是否成功
        /// </summary>
        /// <param name="server">server</param>
        /// <param name="database">database</param>
        /// <param name="uid">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>bool</returns>
        public bool TestConnection(string server, string database, string uid, string password)
        {
            try
            {    
                con = null;
                SqlCeConnectionString = "user id=" + uid + ";password=" + password + ";initial catalog=" + database + ";data source=" + server + " ";
                this.Open();
            }
            catch
            {                
                return false;
            }
            return true;
        }

        /**//// <summary>
        /// 创建command对象以便执行SqlCe语句。
        /// </summary>
        /// <param name="SqlCe">SqlCe Text.</param>        
        /// <returns>Command object.</returns>
        private SqlCeCommand CreateCommand(string SqlCe) 
        {
            // make sure connection is open
            Open();
            SqlCeCommand cmd = new SqlCeCommand(SqlCe, con);
            cmd.CommandType = CommandType.Text;
            return cmd;
        }

        /**//// <summary>
        /// 创建带Prameters的Command对象
        /// </summary>
        /// <param name="SqlCeCe">SqlCe语句</param>
        /// <param name="prams">SqlCeParameters参数</param>
        /// <returns>Command对象</returns>
        public SqlCeCommand CreateCommand(String SqlCe, SqlCeParameter[] prams)
        {
            Open();
            SqlCeCommand cmd = new SqlCeCommand(SqlCe,con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();            
            if (prams != null)
            {
                foreach (SqlCeParameter parameter in prams)
                    cmd.Parameters.Add(parameter);
            }
            return cmd;
        }

        /**//// <summary>
        /// 执行一个无返回的SqlCe语句
        /// </summary>                
        /// <param name="SqlCe">SqlCe语句</param>
        /// <returns>执行结果</returns>
        public bool QueryExec(string SqlCe)
        {                
            SqlCeCommand cmd = CreateCommand(SqlCe);
            try
            {                
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                return false;
            }
            finally
            {
                this.Close();                
            }
            return true;
        }

        /**//// <summary>
        /// 执行一个无返回的SqlCe语句，带parameters
        /// </summary>                
        /// <param name="SqlCe">SqlCe语句</param>
        /// <returns>执行结果</returns>
        public bool QueryExec(string SqlCe, SqlCeParameter[] parms)
        {                
            SqlCeCommand cmd = CreateCommand(SqlCe, parms);
            try
            {                
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                return false;
            }
            finally
            {
                this.Close();                
            }
            return true;
        }

        /**//// <summary>
        /// 执行一个插入记录操作，返回primary key
        /// </summary>                
        /// <param name="SqlCe">insert SqlCe语句</param>
        /// <returns>返回的primary key</returns>
        public String InsertExec(string SqlCe)
        {
            SqlCe += ";SELECT @@identity AS [@@IDENTITY];";
            SqlCeCommand cmd = CreateCommand(SqlCe);
            try
            {            
                return cmd.ExecuteScalar().ToString();
            }
            catch
            {
                return null;
            }
            finally
            {
                this.Close();                
            }
        }

        /**//// <summary>
        /// 执行一个插入记录操作，带parameters，返回primary key
        /// </summary>                
        /// <param name="SqlCe">insert SqlCe语句</param>
        /// <returns>返回的primary key</returns>
        public String InsertExec(string SqlCe, SqlCeParameter[] prams)
        {
            SqlCe += ";SELECT @@identity AS [@@IDENTITY];";
            SqlCeCommand cmd = CreateCommand(SqlCe, prams);
            try
            {            
                return cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {
                ex.Message.ToString();
                return null;
            }
            finally
            {
                this.Close();                
            }
        }

        /**//// <summary>
        /// 通过查询指定的SqlCe语句来获得一个返回值
        /// </summary>
        /// <param name="SqlCe">SqlCe语句</param>
        /// <returns>返回值</returns>
        public String QueryValue(string SqlCe)
        {
            SqlCeCommand cmd = CreateCommand(SqlCe);
            try
            {                
                return cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                return null;
            }
            finally
            {
                this.Close();
            }
        }    
        /**//// <summary>
        /// 通过查询指定的SqlCe语句来获得一个返回表，带parms
        /// </summary>
        /// <param name="SqlCe"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public DataTable QueryDataTable(string SqlCe,SqlCeParameter[] parms)
        {
            DataTable dataTable = new DataTable();
            SqlCeCommand cmd = CreateCommand(SqlCe,parms);
            try
            {            
                SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);
                dataAdapter.Fill(dataTable);
            }
            catch(Exception ex)
            {
                ex.Message.ToString();
                dataTable = null;
            }
            finally
            {
                this.Close();
            }
            return dataTable;
        }

        /**//// <summary>
        /// 通过查询指定的SqlCe语句来获得一个返回值，带parms
        /// </summary>
        /// <param name="SqlCe">SqlCe语句</param>
        /// <returns>返回值</returns>
        public String QueryValue(string SqlCe, SqlCeParameter[] prams)
        {
            SqlCeCommand cmd = CreateCommand(SqlCe, prams);
            try
            {                
                return cmd.ExecuteScalar().ToString();
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                return null;
            }
            finally
            {
                this.Close();
            }
        }
    
        /**//// <summary>
        /// 查询返回DATAREADER
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="error"></param>
        /// <param name="SqlCeConnectionString"></param>
        /// <param name="SqlCe">SqlCe语句</param>
        public SqlCeDataReader QueryDataReader(string SqlCe)
        {            
            try
            {
                SqlCeCommand cmd = CreateCommand(SqlCe);
                return cmd.ExecuteReader();        
            }
            catch
            {    
                return null;
            }
            finally
            {        
            }    
        }

        /**//// <summary>
        /// 查询返回DATASET
        /// </summary>
        /// <param name="SqlCe">SqlCe语句</param>
        /// <returns>DataSet对象</returns>
        public DataSet QueryDataSet (string SqlCe)
        {            
            SqlCeCommand cmd = CreateCommand(SqlCe);
            DataSet ds = new DataSet();            
            try
            {
                SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);                            
                dataAdapter.Fill(ds);
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                ds = null;
            }
            finally
            {
                this.Close();                
            }
            return ds;    
        }

        /**//// <summary>
        /// 通过SqlCe语句返回DataTable。
        /// </summary>
        /// <returns>DataTable</returns>
        public DataTable QueryDataTable(string SqlCe)
        {
            DataTable dataTable = new DataTable();
            SqlCeCommand cmd = CreateCommand(SqlCe);
            try
            {            
                SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);
                dataAdapter.Fill(dataTable);
            }
            catch(Exception ex)
            {    
                ex.Message.ToString();
                dataTable = null;
            }
            finally
            {
                this.Close();
            }
            return dataTable;
        }

        /**//// <summary>
        /// 包装输入参数。
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public SqlCeParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }        

        /**//// <summary>
        /// 包装输出参数。
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <returns>New parameter.</returns>
        public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }    

        public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size, object Value) 
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Value);
        }    

        /**//// <summary>
        /// 包装Command参数。
        /// </summary>
        /// <param name="ParamName">Name of param.</param>
        /// <param name="DbType">Param type.</param>
        /// <param name="Size">Param size.</param>
        /// <param name="Direction">Parm direction.</param>
        /// <param name="Value">Param value.</param>
        /// <returns>New parameter.</returns>
        public SqlCeParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 
        {
            SqlCeParameter param;

            if(Size > 0)
                param = new SqlCeParameter(ParamName, DbType, Size);
            else
                param = new SqlCeParameter(ParamName, DbType);

            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            else Value = param.Value;
            return param;
        }
    }
}