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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string dbfile = AppDomain.CurrentDomain.BaseDirectory + "Database1.mdb";
            DataTable table = new DataTable();
            OleDbCommand cmd;
            OleDbParameter[] parameters;

            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbfile))
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter())
                {
                    /***    init    ***/

                    //Select
                    cmd = adapter.SelectCommand = new OleDbCommand("Select UserID,UserName,Age FROM MZ_MembersTB", conn);

                    //Insert
                    cmd = adapter.InsertCommand = new OleDbCommand("Insert INTO MZ_MembersTB(UserName,Age) VALUES(@UserName,@Age)", conn);
                    parameters = new OleDbParameter[]{ //参数名称对access数据库没有意义,只与顺序有关系
                        new OleDbParameter("@UserName", OleDbType.VarChar),
                        new OleDbParameter("@Age", OleDbType.Integer)
                    };
                    parameters[0].SourceColumn = "UserName";
                    parameters[1].SourceColumn = "Age";
                    ParametersAdd(cmd, parameters);

                    //Update
                    cmd = adapter.UpdateCommand = new OleDbCommand("Update MZ_MembersTB SET UserName=@UserName Where UserID=@UserID", conn);
                    parameters = new OleDbParameter[]{
                        new OleDbParameter("@UserName", OleDbType.VarChar),
                        new OleDbParameter("@UserID", OleDbType.Integer)
                    };
                    parameters[0].SourceColumn = "UserName";
                    parameters[1].SourceColumn = "UserID";
                    parameters[1].SourceVersion = DataRowVersion.Original;
                    ParametersAdd(cmd, parameters);

                    //Delete
                    cmd = adapter.DeleteCommand = new OleDbCommand("Delete FROM MZ_MembersTB Where UserID=@UserID", conn);
                    parameters = new OleDbParameter[]{
                        new OleDbParameter("@UserID", OleDbType.Integer)
                    };
                    parameters[0].SourceColumn = "UserID";
                    parameters[0].SourceVersion = DataRowVersion.Original;
                    ParametersAdd(cmd, parameters);

                    /***    fill    ***/

                    adapter.Fill(table);

                    /***    e.g    ***/

                    //Insert
                    DataRow row = table.NewRow();
                    row["UserName"] = "张三";
                    row["Age"] = "29";
                    table.Rows.Add(row);
                    //Update
                    table.Rows[1]["UserName"] = "李四";
                    table.Rows[1]["Age"] = 19;
                    //Delete
                    table.Rows[0].Delete(); //Delete只将行状态标识为Deleted,并未删除,不能使用Remove

                    for (int i = 0; i < table.Rows.Count; i++)
                        Console.WriteLine(table.Rows[i].RowState);

                    adapter.Update(table);
                }
            }

            Console.ReadKey();
        }

        static void ParametersAdd(OleDbCommand cmd, OleDbParameter[] parameters)
        {
            for (int i = 0; i < parameters.Length; i++)
            {
                cmd.Parameters.Add(parameters[i]);
            }
        }
    }

}