大家可以帮忙推荐个c#对access数据库操作的列子吗?

Question

同标题!我在坛里一直没搜到经典的,拜拜浪费了有限的几点积分.

Answer 1

using System;
using System.Data;
using System.Data.OleDb;namespace zBirthdayReminderLib
{
public class BaseDao
{
private OleDbDataAdapter adapter;
    private OleDbConnection conn;
    private OleDbCommand command;    public bool modify(zContacter contacter, int id)
    {
        bool ret = false;
        string sql = "update contacter";
        sql += " set name = '" + contacter.Name + "', ";
        sql += "cl_year = " + contacter.ChineseLunarBirthday.Year + ", ";
        sql += "cl_month = '" + contacter.ChineseLunarBirthday.Month + "', ";
        sql += "cl_day ='" + contacter.ChineseLunarBirthday.Day + "', ";
        sql += "ge_year =" + contacter.GregorianBirthday.Year + ", ";
        sql += "ge_month =" + contacter.GregorianBirthday.Month + ", ";
        sql += "ge_day = " + contacter.GregorianBirthday.Day + " ";
        sql += "where id = " + id;
        conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
        command = new OleDbCommand(sql, conn);
        try
        {
            conn.Open();
            command.ExecuteNonQuery();
            if (conn != null)
            {
                conn.Close();
            }
            ret = true;
        }
        catch (System.Exception e)
        {
            Console.WriteLine(sql);
            Console.WriteLine(e.Message);
            ret = false;
        }
        return ret;
    }    public bool delete(int id)
    {
        bool ret = false;
        string sql = "delete * from contacter where id = " + id;
        conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
        command = new OleDbCommand(sql, conn);
        try
        {
            conn.Open();
            command.ExecuteNonQuery();
            if (conn != null)
            {
                conn.Close();
            }
            ret = true;
        }
        catch (System.Exception e)
        {
            Console.WriteLine(sql);
            Console.WriteLine(e.Message);
            ret = false;
        }
        return ret;
    }    public bool insert(zContacter contacter)
    {
        bool ret = false;
        string sql = "insert into contacter (name, cl_year, cl_month, cl_day, ge_year, ge_month, ge_day) values ( ";
        sql += "'" + contacter.Name + "',";
        sql += contacter.ChineseLunarBirthday.Year + ", " ;
        sql += "'" + contacter.ChineseLunarBirthday.Month + "', " ;
        sql += "'" + contacter.ChineseLunarBirthday.Day + "', ";
        sql += contacter.GregorianBirthday.Year + ", ";
        sql += contacter.GregorianBirthday.Month + ", ";
        sql += contacter.GregorianBirthday.Day + " )";
        conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
        command = new OleDbCommand(sql, conn);
        try
        {
            conn.Open();
            command.ExecuteNonQuery();
            if (conn != null)
            {
                conn.Close();
            }
            ret = true;
        }
        catch (System.Exception e)
        {
            Console.WriteLine(sql);
            Console.WriteLine(e.Message);
            ret = false;
        }
        return ret;
    } public  DataSet QueryAll()
{
string sql = "select * from contacter where name is not null";
        conn = new OleDbConnection(ConstDefine.DB_CONN_STRING);
command = new OleDbCommand(sql);
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
adapter = new OleDbDataAdapter();
adapter.SelectCommand = command;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
            return new DataSet();
}        return ds;
}
}
}其中数据库连接字符串定义如下private static string DB_FILE
{
get
{
return System.AppDomain.CurrentDomain.BaseDirectory + "\\data.mdb";
}
}
public static string DB_CONN_STRING
{
get
{
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConstDefine.DB_FILE;
}
}

Answer 2

大哥,能给个关于access的项目吗?没有做这个数据库的项目

Answer 3

public class DataHelper
    {
        // 连接数据源
        public SqlConnection con = null;
        public OleDbConnection conn = null;        /// <summary>
        /// 数据库连接类型
        /// </summary>
        public enum dbType
        {
            /// <summary>
            /// SQL数据库
            /// </summary>
            sql,
            /// <summary>
            /// access数据库
            /// </summary>
            access
        }
         //连接数据类型
        dbType cType;        public DataHelper(string conStr,dbType type)
        {
             this.cType = type;
            if (type == dbType.sql)
                con = new SqlConnection(conStr);
            else if (type == dbType.access)
                conn = new OleDbConnection(conStr);
        }         /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet returnDS(string sql)
        {            DataSet ds = new DataSet();
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    adapter.Fill(ds);
                }
            }
            catch (Exception e)
            {                
                ds = null;
                throw (e);
            }
            finally
            {
                this.Close();
            }            return ds;        }        /// <summary>
        /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="sRecord">开始记录数</param>
        /// <param name="mRecord">最大记录数</param>
        /// <param name="strTableName">表名</param>
        /// <returns>DataSet</returns>
        public DataSet returnDS(string sql, int sRecord, int mRecord, string strTableName)
        {            DataSet ds = new DataSet();
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds, sRecord, mRecord, strTableName);                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    adapter.Fill(ds, sRecord, mRecord, strTableName);
                }
            }
            catch (Exception e)
            {
                ds = null;
                throw (e);
               
            }
            finally
            {
                this.Close();
            }            return ds;        }        /// <summary>
        /// 对数据库的增，删，改的操作
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否成功</returns>
        public bool OperateDB(string sql)
        {
            bool succeed = false;
            int cnt = 0;
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    cnt = cmd.ExecuteNonQuery();                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    cnt = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                if (cnt > 0)
                {
                    succeed = true;
                }
                this.Close();
            }            return succeed;
        }        /// <summary>
        /// 获得该SQL查询返回的第一行第一列的值，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回的第一行第一列的值</returns>
        public string getValue(string sql)
        {
            string str = null;
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    this.Open();
                    str = cmd.ExecuteScalar().ToString();                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    this.Open();
                    str = cmd.ExecuteScalar().ToString();
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            finally
            {
                this.Close();
            }            return str;
        }
        /// <summary>
        ///   获得该SQL查询返回DataTable，如果没有查询到则返回NULL
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public DataTable getTable(string sql)
        {
            DataTable tb = null;
            DataSet ds = this.returnDS(sql);
            if (ds != null)
            {
                tb = ds.Tables[0];
            }
            return tb;
        }        /// <summary>
        ///  获得该SQL查询填充到 指定DataTable
        /// </summary>
        /// <param name="tb">指定的Table</param>
        /// <param name="sql"> SQL 语句</param>
        /// <returns></returns>
        public DataTable FillTable(DataTable tb, string sql)
        {            DataSet ds = tb.DataSet;
            if (ds == null)
            {
                ds = new DataSet();
                ds.Tables.Add(tb);
            }           
            try
            {
                if (cType == dbType.sql)
                {
                    SqlCommand cmd = new SqlCommand(sql, con);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds,tb.TableName);                }
                else if (cType == dbType.access)
                {
                    OleDbCommand cmd = new OleDbCommand(sql, conn);
                    cmd.CommandTimeout = 20;
                    this.Open();
                    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    adapter.Fill(ds, tb.TableName);
                }
            }
            catch (Exception e)
            {
                ds = null;
                throw (e);
            }
            finally
            {
                this.Close();
            }
            if (ds != null)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
         
        }
        
        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private void Open()
        {
            if (cType == dbType.sql)
            {
                if (con.State == System.Data.ConnectionState.Closed)
                {
                    con.Open();
                }
                else if (con.State == System.Data.ConnectionState.Broken)
                {
                    con.Close();
                    con.Open();
                }
            }
            else if (cType == dbType.access)
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                else if (conn.State == System.Data.ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
            }
        }        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (cType == dbType.sql)
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            else if (cType == dbType.access)
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }    }

Answer 4

产用三层架构实现cms系统源码里面有操作access和ms sql server两种数据库的源代码！