asp.net读取Access数据库。

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

注：数据库（表名 job id 工作id ,job工作字段）

数据库放在app_data文件中。名称为database.mdb

如果用codesmith生成，选择的数据库连接类型如下图：

项目结构图：

webconfig配置如下：（在configuration中添加）

<connectionStrings>
    <add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\database.mdb" providerName="System.Data.OleDb"/>
 </connectionStrings>

View Code

OleDbHelper.cs类：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;

    public class OleDbHelper
    {
        //从配置文件中读取连接字符串
        private static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        
        #region 增删改
        public static int ExecuteNonQuery(string commandText, CommandType commandType, params OleDbParameter[] commandParameters)
        {
            int row = 0;
            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                string os = null;
                PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                row = cmd.ExecuteNonQuery();
            }
            return row;
        }
        #endregion

        #region 返回实体集
        public static OleDbDataReader ExecuteReader(string commandText, CommandType commandType, params OleDbParameter[] commandParameters)
        {
            OleDbDataReader dr = null;
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                conn.Close();
                throw;
            }
            return dr;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// 执行Sql Server存储过程
        /// 注意：不能执行有out 参数的存储过程
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="spName">存储过程名</param>
        /// <param name="parameterValues">对象参数</param>
        /// <returns>执行结果对象</returns>
        public static object ExecuteScalar(string commandText, CommandType commandType,params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();

            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                PrepareCommand(cmd, commandType, conn, commandText, commandParameters);
                object val = cmd.ExecuteScalar();
                return val;
            }
        }


        #endregion

        #region Private Method
        /// <summary>
        /// 设置一个等待执行的OleDbCommand对象
        /// </summary>
        /// <param name="cmd">OleDbCommand 对象，不允许空对象</param>
        /// <param name="conn">OleDbConnection 对象，不允许空对象</param>
        /// <param name="commandText">Sql 语句</param>
        /// <param name="cmdParms">OleDbParameters  对象,允许为空对象</param>
        private static void PrepareCommand(OleDbCommand cmd, CommandType commandType, OleDbConnection conn, string commandText, OleDbParameter[] cmdParms)
        {
            //打开连接
            if (conn.State != ConnectionState.Open)
                conn.Open();

            //设置OleDbCommand对象
            cmd.Connection = conn;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;

            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        #region 返回一个表的数据
        public static DataSet ExcuteDataSet(string cmdText, params OleDbParameter[] pars)
        {
            using (OleDbConnection con = new OleDbConnection(ConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                PrepareCommand(cmd, CommandType.Text, con, cmdText, pars);
                DataSet ds = new DataSet();
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    da.Fill(ds, "Authorities");
                    return ds;
                }
            }
        }
        #endregion 
}

View Code

jobs.cs类：

using System;
using System.Collections.Generic;
using System.Web;

namespace WebTestAccess
{
    public class jobs
    {
        public jobs() { }

        public jobs(string job)
        {
            this.job = job;
        }

        public jobs(int id, string job)
        {
            this.id = id;
            this.job = job;
        }
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        private string job;

        public string Job
        {
            get { return job; }
            set { job = value; }
        }
    }
}

View Code

jobservice.cs类：

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

namespace WebTestAccess
{
    public class jobservice
    {
        public List<jobs> getjobsall()
        {
            List<jobs> jobss = new List<jobs>();
            string sql = "select * from jobs";
            using(OleDbDataReader dr=OleDbHelper.ExecuteReader(sql,CommandType.Text))
            {
                while(dr.Read())
                {
                    jobs job = new jobs(
                            Convert.ToInt32(dr["id"]),
                            dr["job"].ToString()
                        );
                    jobss.Add(job);
                }
            }
            return jobss;
        }
       
        //id为自增。
        public int addjob(jobs job)
        {
            int result = -1;
            string sql = string.Format("insert into jobs(job) values('{0}')",job.Job);
            OleDbParameter para = new OleDbParameter("@job", job.Job);
            result = OleDbHelper.ExecuteNonQuery(sql, CommandType.Text, para);
            return result;
        }
    }
}