访问Access2007的封装类DbAccess

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;

/// <summary>
/// DbAccess 的摘要说明
/// </summary>
public class DbAccess:Page
    {
        string ConnString;
        OleDbConnection conn ;
        OleDbCommand cmd;
        public DbAccess(string datapath)
        {
           
            ConnString = "provider=Microsoft.Jet.OleDb.4.0;Data Source="+HttpContext.Current.Request.PhysicalApplicationPath.ToString()+"//"+datapath;
 
            conn = new OleDbConnection();
            conn.ConnectionString=ConnString;
            cmd = new OleDbCommand();
            cmd.Connection=conn;
           
        }
        public DbAccess()
        {

            ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath.ToString()+"//App_Data//data.accdb" + ";Persist Security Info=False";
     
            conn = new OleDbConnection();
            conn.ConnectionString=ConnString;
            cmd = new OleDbCommand();
            cmd.Connection=conn;
           
        }
        /// <summary>
        /// 获取数据存在DataTable中
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql)
        {
            cmd.CommandText=sql;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand=cmd;
            //DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 获取一个值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetOne(string sql)
        {
            string obj="";
            conn.Open();
            cmd.CommandText=sql;
            try
            {
                obj = Convert.ToString(cmd.ExecuteScalar());
                obj = (obj==null)?(""):(obj);
               
            }
            catch(Exception ex)
            {
                HttpContext.Current.Response.Write("<script>alert('"+ex.Message.ToString()+"')</script>");
            }
            conn.Close();
            return obj;
        }
        public DataSet GetSet(string sql)
        {
            cmd.CommandText=sql;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand=cmd;
            //DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 事物处理
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns></returns>
        public bool GetStateTran(params string[] sqls)
        {
           
            conn.Open();
            OleDbTransaction tran = conn.BeginTransaction();
            //tran.
            cmd.Transaction=tran;
            for(int i=0;i<sqls.Length;i++)
            {
                try
                {
                   cmd.CommandText=sqls[i];
                   cmd.ExecuteNonQuery();

                }
                catch(Exception ex)
                {
                   tran.Rollback();
                    HttpContext.Current.Response.Write(ex.Message.ToString());
                   return false;
                }
            }

            tran.Commit();
            conn.Close();
            return true;
        }
        public bool GetState(string sql)
        {
            bool suc = false;
            conn.Open();
            cmd.CommandText=sql;
            try
            {
                int count = cmd.ExecuteNonQuery();
                if(count>0)
                    suc=true;
            }
            catch(Exception ex)
            {
                HttpContext.Current.Response.Write("<script>alert('"+ex.Message.ToString()+"')</script>");
            }
            conn.Close();
            return suc;

        }
    /*    public bool ReturnState(string sql)
        {
            string String = "provider=Microsoft.Jet.OleDb.4.0;Data Source="+Server.MapPath("data/shuhua.mdb");
            OleDbConnection connR = new OleDbConnection();
            connR.ConnectionString=String;
            OleDbcommand cmdR = new OleDbCommand();
            cmdR.Connection=connR;
            bool suc = false;
            connR.Open();
            cmdR.CommandText=sql;
            try
            {
                int count = cmdR.ExecuteNonQuery();
                if(count>0)
                    suc=true;
            }
            catch(Exception ex)
            {
                HttpContext.Current.Response.Write("<script>alert('"+ex.Message.ToString()+"')</script>");
            }
            connR.Close();
            return suc;

        }*/
        //分页
        /*public DataTable GetPerData(string sql,int cur)
        {
            cmd.CommandText=sql;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand=cmd;
            DataSet ds = new DataSet();
            int zon = cur*8;
           
            da.Fill(ds,zon,8,"per");
           
            return ds.Tables["per"];
       
        }
        */
        //根据提供的页数分页
        public DataTable GetPerData(string sql,int cur,int percount)
        {
            cmd.CommandText=sql;
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand=cmd;
            DataSet ds = new DataSet();
            int zon = cur*percount;
           
            da.Fill(ds,zon,percount,"per");
           
            return ds.Tables["per"];
        }
        #region script处理
        public void Alert(string str,string loc)
        {
            StringBuilder  sb = new StringBuilder();
            sb.Append("<script language='javascript'>alert('");
            sb.Append(str);
            sb.Append("');");
            sb.Append(loc);
            sb.Append("</script>");
            HttpContext.Current.Response.Write(sb.ToString());
        }
        public void Alert(string str)
        {
            StringBuilder  sb = new StringBuilder();
            sb.Append("<script language='javascript'>alert('");
            sb.Append(str);
            sb.Append("');");
           
            sb.Append("</script>");
            HttpContext.Current.Response.Write(sb.ToString());
        }
        /// <summary>
        /// 脚本信息
        /// </summary>
        /// <param name="str"></param>
        public void Script(string str)
        {
            StringBuilder  sb = new StringBuilder();
            sb.Append("<script language='javascript'>");
            sb.Append(str);
       
           
            sb.Append("</script>");
            HttpContext.Current.Response.Write(sb.ToString());
        }
        #endregion
        #region 字符串处理
        /// <summary>
        /// 字符串处理
        /// </summary>
        /// <param name="str">old string</param>
        /// <param name="len"></param>
        /// <returns></returns>
        public string  Sub(string str,int len)
        {
            if(str.Length>len)
            {
                str = str.Substring(0,len)+"…";
            }
            return str;
        }
        #endregion
        #region 数据表
        /// <summary>
        /// 数据表处理
        /// </summary>
        /// <param name="dtc">原表</param>
        /// <param name="topn">前几条</param>
        /// <returns></returns>
        public DataTable GetTop(DataTable dtc,int topn)
        {
       
            DataTable dt = new DataTable();
            #region　复制列
            for(int j=0;j<dtc.Columns.Count;j++)
            {
                DataColumn dc = new DataColumn(dtc.Columns[j].ColumnName,typeof(string));
                dt.Columns.Add(dc);
            }
            #endregion
            #region 复制行
            for(int i=0;i<topn;i++)
            {
                if(i<dtc.Rows.Count)
                   
                {
                    DataRow dr = dt.NewRow();
                    for(int j=0;j<dtc.Columns.Count;j++)
                    {
                        dr[j]=dtc.Rows[i][j].ToString();
                           
                    }
                    dt.Rows.Add(dr);
                }
                else
                {

                }

            }
            #endregion
            return dt;
        }
        #endregion

    }