search
HomeDatabaseMysql TutorialC#操作Access数据库,收集了3篇经典文章

【导言】因为开发制作一个小工具数据转移工具,需要用C#操作Access数据库。所以上网搜索了一下,有三篇文章内容比较好,特收录如下。 另有一份大礼送给网友:《ADO 程序员参考》 另一个链接:CSDN下载-ADO程序员参考中文版 一、创建数据库的基本方法: 原作

【导言】因为开发制作一个小工具“数据转移工具”,需要用C#操作Access数据库。所以上网搜索了一下,有三篇文章内容比较好,特收录如下。

另有一份大礼送给网友:《ADO 程序员参考》 

 另一个链接:CSDN下载-ADO程序员参考中文版

 

一、创建数据库的基本方法:

原作者迭失

microsoft ado ext.2.8
 
我想利用C#创建一个ACCESS数据库文件(A.mdb)。请问用什么办法可以实现。
A.mdb文件是原来没有的,程序需要创建一个然后往里面写数据!

1.
※新建工程
※进入解决方案->引用->添加引用

->选择-> OK
※编码
//命令行工程代码如下
using System;
using ADOX;
namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\AccessDB\\NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
Console.WriteLine("Database Created Successfully");
cat = null;
}
}
}
//asp.net代码如下
private void Page_Load(object sender, System.EventArgs e)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C://database//NewMDB.mdb;" +
"Jet OLEDB:Engine Type=5");
cat = null;
Response.Write("OK");
2.用sqlserver 的代码如下
SqlConnection conn=new SqlConnection("Server=lemoncat007;Uid=sa;Pwd=gtt");
conn.Open();
SqlCommand cmd=new SqlCommand("create database test",conn);
cmd.ExecuteNonQuery();
3 也可以创建一个Procedure 将创建数据库的语句写到里面然后执行
 

二、创建数据库,并且添表和添加字段

原文《用C#动态创建Access数据库》

记得以前要动态的创建Access数据库的mdb文件都是采用DAO,用VC开发,一大堆的API,很是麻烦。现在好像也鲜有人提起DAO。其实动态的创建mdb数据的最简单的方法还是ADOX。
      用ADOX创建access数据库方法很简单,只需要new一个Catalog对象,然后调用它的Create方法就可以了,如下:

C#操作Access数据库,收集了3篇经典文章ADOX.Catalog catalog = new Catalog();
C#操作Access数据库,收集了3篇经典文章catalog.Create(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;Jet OLEDB:Engine Type=5");
C#操作Access数据库,收集了3篇经典文章


      仅仅两行代码就搞定了。下来我主要介绍一下在c#中的实现细节。首先你要添加引用,在“Add reference”对话框里切换到Com页面,选择“Microsoft ADO Ext. 2.8 for DDL and Security”,然后点击OK。在文件的开头using ADOX名字空间。然后添加如上面所示的代码就可以成功的创建Access 数据库了,代码如下:

C#操作Access数据库,收集了3篇经典文章using System;
C#操作Access数据库,收集了3篇经典文章
using System.Collections.Generic;
C#操作Access数据库,收集了3篇经典文章
using System.Text;
C#操作Access数据库,收集了3篇经典文章
using ADOX;
C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章
namespace testADOX
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章    
class Program
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章    
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章        
static void Main(string[] args)
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章        
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章            ADOX.Catalog catalog 
= new Catalog();
C#操作Access数据库,收集了3篇经典文章            catalog.Create(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;Jet OLEDB:Engine Type=5");
C#操作Access数据库,收集了3篇经典文章        }

C#操作Access数据库,收集了3篇经典文章    }

C#操作Access数据库,收集了3篇经典文章}

C#操作Access数据库,收集了3篇经典文章


      创建了数据库文件是没有实际用处的,我们还要创建表。在创建表之前,我们必须连接目标数据库,用来连接数据的桥梁居然是ADO的Connection对象,所以我们不得不再次添加对ADO的应用,在添加引用对话框中切换到Com页面,选择“Microsoft ActiveX Data Objects 2.8 Library”,然后点击OK。下边是创建表的完整代码:

C#操作Access数据库,收集了3篇经典文章using System;
C#操作Access数据库,收集了3篇经典文章
using System.Collections.Generic;
C#操作Access数据库,收集了3篇经典文章
using System.Text;
C#操作Access数据库,收集了3篇经典文章
using ADOX;
C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章
namespace testADOX
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章    
class Program
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章    
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章        
static void Main(string[] args)
C#操作Access数据库,收集了3篇经典文章C#操作Access数据库,收集了3篇经典文章        
C#操作Access数据库,收集了3篇经典文章{
C#操作Access数据库,收集了3篇经典文章            ADOX.Catalog catalog 
= new Catalog();
C#操作Access数据库,收集了3篇经典文章            catalog.Create(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;Jet OLEDB:Engine Type=5");
C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章            ADODB.Connection cn 
= new ADODB.Connection();
C#操作Access数据库,收集了3篇经典文章            
C#操作Access数据库,收集了3篇经典文章            cn.Open(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb"nullnull-1);
C#操作Access数据库,收集了3篇经典文章            catalog.ActiveConnection 
= cn;
C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章            ADOX.Table table 
= new ADOX.Table();
C#操作Access数据库,收集了3篇经典文章            table.Name 
= "FirstTable";
C#操作Access数据库,收集了3篇经典文章
C#操作Access数据库,收集了3篇经典文章            ADOX.Column column 
= new ADOX.Column();
C#操作Access数据库,收集了3篇经典文章            column.ParentCatalog 
= catalog;
C#操作Access数据库,收集了3篇经典文章            column.Name 
= "RecordId";
C#操作Access数据库,收集了3篇经典文章            column.Type 
= DataTypeEnum.adInteger;
C#操作Access数据库,收集了3篇经典文章            column.DefinedSize 
= 9;
C#操作Access数据库,收集了3篇经典文章            column.Properties[
"AutoIncrement"].Value = true;
C#操作Access数据库,收集了3篇经典文章            table.Columns.Append(column, DataTypeEnum.adInteger, 
9);
C#操作Access数据库,收集了3篇经典文章            table.Keys.Append(
"FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, nullnull);
C#操作Access数据库,收集了3篇经典文章            table.Columns.Append(
"CustomerName", DataTypeEnum.adVarWChar, 50);
C#操作Access数据库,收集了3篇经典文章            table.Columns.Append(
"Age", DataTypeEnum.adInteger, 9);
C#操作Access数据库,收集了3篇经典文章            table.Columns.Append(
"Birthday", DataTypeEnum.adDate, 0);
C#操作Access数据库,收集了3篇经典文章            catalog.Tables.Append(table);
C#操作Access数据库,收集了3篇经典文章         
C#操作Access数据库,收集了3篇经典文章            cn.Close();
C#操作Access数据库,收集了3篇经典文章        }

C#操作Access数据库,收集了3篇经典文章    }

C#操作Access数据库,收集了3篇经典文章}

C#操作Access数据库,收集了3篇经典文章

      上面的代码中,创建了一个名为FirstTable的表,在表里加入了4个字段,并设置了一个主键。表里的字段分别输入4中不同的常用类型,第一个字段是一个自动增长的整数类型,这个类型比较特殊,你必须为这个字段设置ParentCatalog属性,并将“AutoIncrement”的属性值设为true.。Access里的Text类型对应的就是adVarWchar,而日期类型对应的是adDate。
键的设置如table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null)所示,如果是外键的话,你还必须要设置关联的表和关联的字段,也就是Append方法的后两个字段。
你也可以参照上边的代码创建索引和视图。

三、C#创建Access数据库的备注字段

 原文:http://www.hackpig.cn/post/168.html

 

具体的用C#创建方式网上一堆,主要是创建备注字段的时候遇到的问题。
用ADOX创建的,在工程里添加引用dll就不说了,错误的步骤如下(就少了一步):
-------------------------------------------------------------------
            //创建库
            ADOX.CatalogClass catLog = new ADOX.CatalogClass();
            catLog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ="+path+";");
           
            //创建表
            ADOX.TableClass tbl = new ADOX.TableClass();
            tbl.ParentCatalog = catLog;
            tbl.Name = "NewTable";

            //增加字段
           ADOX.ColumnClass c = new ADOX.ColumnClass();
           c.ParentCatalog = catLog;
           c.Name = list1[i];
           c.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
           tbl.Columns.Append(c, ADOX.DataTypeEnum.adLongVarWChar, 16);

            catLog.Tables.Append(tbl);
---------------------------------------------------------------------
Access的备注类型是Memo,不过在ADOX方式下,DataTypeEnum枚举里是没有这项的,adLongVarWChar就是备注,长度为16。这样创建,是不报错的,但是无论如何,建立出来的都是文本型的字段,最大长度限制是255,在内容很多的时候不够用,查了一堆,发现就是因为少了一句,正确的如下:

-------------------------------------
            //创建库
            ADOX.CatalogClass catLog = new ADOX.CatalogClass();
            catLog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source ="+path+";");
           
            //创建表
            ADOX.TableClass tbl = new ADOX.TableClass();
            tbl.ParentCatalog = catLog;
            tbl.Name = "NewTable";

            //增加字段
           ADOX.ColumnClass c = new ADOX.ColumnClass();
           c.ParentCatalog = catLog;
           c.Type = ADOX.DataTypeEnum.adLongVarWChar; //这句不能少,并且位置必须在其它属性前面,否则会报错。
           c.Name = list1[i];
           c.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
           tbl.Columns.Append(c, ADOX.DataTypeEnum.adLongVarWChar, 16);

            catLog.Tables.Append(tbl);
-------------------------------------

这样,就建立出来备注类型的字段了。

 

 

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

MySQL's Role: Databases in Web ApplicationsMySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

MySQL: Building Your First DatabaseMySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AM

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL: A Beginner-Friendly Approach to Data StorageMySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AM

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)