C#创建并连接数据库以及增删查改

引入命名空间

using System.Data.OleDb;
using System.IO;
using ADOX;

动态创建数据库

//数据库路径
String filePath = "C:\\Users\\18144\\OneDrive\\文档\\Access\\test2.accdb";
if (!File.Exists(filePath))
{
//数据库不存在
// 创建Access数据库
   ADOX.Catalog catalog = new ADOX.Catalog();
   catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\\Users\\18144\\OneDrive\\文档\\Access\\test2.accdb;Jet OLEDB:Database Password = 123456");
   //Data Source = .\\Data\\Student.accdb;  当前位置创建
    
   //释放Catalog对数据库文件的占用
   System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
   System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);

    return;
 }
else
{
    //数据库已存在
    MessageBox.Show("数据库文件已存在");         
}

创建数据表

//合并连接字符串
string DataLinkString = "Provider = Microsoft.Jet.OLEDB.4.0;";
DataLinkString += @"Data Source = C:\Users\18144\OneDrive\文档\Access\test2.accdb;";
DataLinkString += @"Jet OLEDB:Database Password = 123456";

//根据连接字符串，连接数据库
OleDbConnection AD = new OleDbConnection(DataLinkString);
//开启数据库
AD.Open();

//创建sql命令---删除表命令
OleDbCommand sql_cmd1 = new OleDbCommand("drop table Info", AD);
//执行sql命令
sql_cmd1.ExecuteNonQuery();
sql_cmd1.Dispose();


//创建sql命令----创建表命令
OleDbCommand sql_cmd2 = new OleDbCommand("create table Info (id char(15) primary key, name char(50))", AD);
//执行sql命令
sql_cmd2.ExecuteNonQuery();
sql_cmd2.Dispose();

//关闭数据库
AD.Close();
AD.Dispose();

执行sql命令

private void RunSQL(string sql_cmd)
{
    //void作用：运行sql代码//
    try
    {
        //合并连接字符串
        string DataLinkString = "Provider = Microsoft.Jet.OLEDB.4.0;";
        DataLinkString += @"Data Source = C:\Users\18144\OneDrive\文档\Access\test2.accdb;";
        DataLinkString += @"Jet OLEDB:Database Password = 123456";

        //根据连接字符串，连接数据库
        OleDbConnection conn = new OleDbConnection(DataLinkString);
        //开启数据库
        conn.Open();

        //创建sql命令
        OleDbCommand cmd = new OleDbCommand(sql_cmd, conn);
        //执行sql命令
        cmd.ExecuteNonQuery();
        cmd.Dispose();

        //关闭数据库
        conn.Close();
        conn.Dispose();
    }
    catch (Exception e)
    {
        MessageBox.Show(e.ToString(), "提示");
    }
}

增加

//sql增加命令
string insert = "insert into student values(" + id + ", '" + name + "', " + age + ");";
RunSQL(insert);

删除

//sql删除命令
string delete = "delete from student where id = " + id;
RunSQL(delete);

修改

//sql修改命令
string update = "update student set Name = '" + name + "', Age = " + age + " where Id = " + id;
RunSQL(update);

查询

         // 如果连接是关闭的，才进行打开
          if (odc.State == ConnectionState.Closed) {
              conn.Open();
          }
// sql操作语句
          string select = "select * from student where Age = " + age;

          // 创建设配器对象   【OleDbDataAdapter】
          OleDbDataAdapter dbDataAdapter = new OleDbDataAdapter(select, cmd);

          // 新建表对象
          DataTable dataTable = new DataTable();

          // 用设配器对象填充表对象
          dbDataAdapter.Fill(dataTable);

          // 将查找到的数据遍历输出
          foreach (DataRow dr in dataTable.Rows) {
              Console.WriteLine("id = {0} name = {1} age = {2}", dr[0], dr[1], dr[2]);
          }