form1.cs

连接access数据库

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;


namespace access_data
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void 导入AccessToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Open a file dialog for selecting access documents
           openFileDialog1.Filter = "Microsoft Office Access(*.mdb)|*.mdb";
            //openaccessfile.ShowDialog();

            //Exit if no access document is selected
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {

                string AccessFilePath = openFileDialog1.FileName;

                if (AccessFilePath == "")
                    return;

                else
                {
                    //设置数据库连接
                    string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
                    strConnection += AccessFilePath;
                    OleDbConnection objConnection = new OleDbConnection(strConnection);
                    if (objConnection.State.ToString() == "Closed")
                        objConnection.Open();
                    //设置数据库操作命令
                    OleDbDataAdapter sda = new OleDbDataAdapter("select * from student ", objConnection);
                    OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(sda);            
                    //创建一个DataSet
                    //ds = new DataSet();
                    //执行oledataadapter，并将结果填充到DataSet中
                    sda.Fill(ds,"student");
                    //获取数据行数
                    iRowCount = ds.Tables["student"].Rows.Count;
                    //获取数据列数
                    iColumnsCount = ds.Tables["student"].Columns.Count;
                  /*  DataRow dr = ds.Tables["student"].NewRow();
                    dr["ID"] = 215478456;// ds.Tables["studentinfo"].Rows[0]["ID"];
                    dr["NAME"] = "jadljfdadj";// ds.Tables["studentinfo"].Rows[0]["NAME"];
                    ds.Tables["student"].Rows.Add(dr);
                    int errorNum = sda.Update(ds,"student");
                    if (errorNum == 1)
                        MessageBox.Show("添加成功");
                    else
                        MessageBox.Show("添加失败");*/
                    objConnection.Close();
                    dataGridView1.DataSource = ds.Tables["student"].DefaultView;
                    this.导出AccessToolStripMenuItem.Enabled = true;




                }
            }
          


        }

        private void 导出AccessToolStripMenuItem_Click(object sender, EventArgs e)
        {
          //Open a file dialog for selecting access documents
           saveFileDialog1.Filter = "Microsoft Office Access(*.mdb)|*.mdb";
            
            //openaccessfile.ShowDialog();

            //Exit if no access document is selected
          if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
             
//
                string CreateAccessFilePath = saveFileDialog1.FileName;
                if (File.Exists(CreateAccessFilePath))
                    File.Delete(CreateAccessFilePath);
//
               if (CreateAccessFilePath == "")
                   return;

               else
                {
                    //创建数据库
                    string createfilepath = "Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=" +CreateAccessFilePath + "";//;Jet OLEDB:Engine Type=5
                    ADOX.CatalogClass catalog = new ADOX.CatalogClass();
                    catalog.Create(createfilepath);
                    ADOX.TableClass tbl = new ADOX.TableClass();
                    tbl.ParentCatalog = catalog;
                    tbl.Name = "studentinfo";
                    ADOX.ColumnClass col = new ADOX.ColumnClass();
                    col.ParentCatalog = catalog;
                    col.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
                    col.Name = "id";
                    col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                    col.Properties["AutoIncrement"].Value = false;
                    tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);

                    //增加一个文本字段
                    ADOX.ColumnClass col2 = new ADOX.ColumnClass();
                    col2.ParentCatalog = catalog;
                    col2.Name = "name";
                    col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                    tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarChar, 25);

                    //设置主键
                    tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "id", "", "");
                    catalog.Tables.Append(tbl);
                   // catalog = null;
                   
                   
                    
                    //设置数据库连接
                    string connectfilepath = "Provider=Microsoft.Jet.OLEDB.4.0 ; Data Source=" + CreateAccessFilePath;
                    OleDbConnection SconnStr = new OleDbConnection(connectfilepath);
                    if (SconnStr.State.ToString() == "Closed")
                        SconnStr.Open();
                    //设置数据库操作命令
                    OleDbDataAdapter sda1 = new OleDbDataAdapter("select id,name from studentinfo order by id",SconnStr);
                    OleDbCommandBuilder myBuilder = new OleDbCommandBuilder(sda1);            
                    sda1.Fill(ds1, "info1");
                 //   DataRow dr2 = ds1.Tables["info1"].NewRow();
                    //dr1["id"] = 20;//ds.Tables["studentinfo"].Rows[0]["ID"];
                    //dr1["name"] = "jasdljfaj";//ds.Tables["studentinfo"].Rows[0]["NAME"];
                 //   ds1.Tables["info1"].Rows.Add(dr2);
                  //  int errorNum = sda1.Update(ds1,"info1");
                        Int32[] studid ={123,456,125};
                        string[] studname ={ "dfasdfdg", "adhsfhdk", "hasdfhk" };
                        DataRow dr2 = ds1.Tables["info1"].NewRow();
                       // dr2["id"] =0; ;//ds.Tables["studentinfo"].Rows[0]["ID"];
                       // dr2["name"] = " ";//ds.Tables["studentinfo"].Rows[0]["NAME"];
                        ds1.Tables["info1"].Rows.Add(dr2);
                        sda1.Update(ds1, "info1");

                        for (int i = 0; i < 3; i++)
                        {
                            DataRow dr1 = ds1.Tables["info1"].NewRow();
                            dr1["id"] = ds.Tables["student"].Rows[i]["ID"];
                            dr1["name"] = ds.Tables["student"].Rows[i]["NAME"];
                            ds1.Tables["info1"].Rows.Add(dr1);
                            int errorNum = sda1.Update(ds1, "info1");
                            if (errorNum == 1)
                                MessageBox.Show("添加成功");
                            else
                                MessageBox.Show("添加失败");

                        }
                    }
            }
        }
    }
}