Summary of various Access operations in C #

Table operations:

Create a table:
Create table [table name]
(
[Automatic number field] int IDENTITY (1, 1) primary key,
[Field 1] nVarChar (50) default value null,
[Field 2] ntext null,
[Field 3] datetime,
[Field 4] money null,
[Field 5] int default 0,
[Field 6] Decimal (12, 4) default 0,
[Field 7] image null,
)

Delete table:
Drop table [table name]

Insert data:
Insert into [Table name] (Field 1, Field 2) VALUES (100, abc. NET)

Delete data:
Delete from [Table name] WHERE [field name]> 100

Update Data:
UPDATE [Table name] SET [Field 1] = 200, [Field 2] = abc. net where [Field 3] = HAIWA

New field:
Alter table [TABLE name] ADD [field name] NVARCHAR (50) NULL

Delete field:
Alter table [TABLE name] drop column [field name]

Modify Field:
Alter table [TABLE name] alter column [field name] NVARCHAR (50) NULL

Rename a table: (Access to rename a table, refer to the article: rename a table in Access database)
Sp_rename table name, new table name, OBJECT

New constraint:
Alter table [TABLE name] add constraint name CHECK ([CONSTRAINT field] <= 2000-1-1)

Delete constraints:
Alter table [TABLE name] drop constraint name

Create Default Value
Alter table [TABLE name] add constraint default name DEFAULT abc. net for [field name]

Delete default value
Alter table [TABLE name] drop constraint default name

 

The above part is reproduced from: PeterXu Source: Blog. CSDN Blog: http://blog.csdn.net/peterreg/
Copyright statement: original works can be reproduced. During reprinting, you must mark the original publication, author information, and this statement in hyperlink form. Otherwise, you will be held legally liable. Address: http://blog.csdn.net/peterreg/archive/2008/05.aspx

 

-------------------------------------- Gorgeous split line -----------------------------------------

Rename an Access table:

When the data volume is small, you can select:

Select * into newtable name from oldTable;

Drop table oldTableName;

 

--------------------------- Gorgeous split line -----------------------------------------

Data Type

Access Data Types

Alter table tb alter column aa Byte number [bytes]

Alter table tb alter column aa Long number [Long integer]

Alter table tb alter column aa Short number [integer]

Alter table tb alter column aa Single number [Single precision]

Alter table tb alter column aa Double number [Double Precision]

Alter table tb alter column aa Currency

Alter table tb alter column aa Char text

Alter table tb alter column aa Text (n) Text, where n indicates the field size

Alter table tb alter column aa Binary

Alter table tb alter column aa Counter automatic number

Alter table tb alter column aa Memo remarks

Alter table tb alter column aa Time date/Time

Alter table tb alter column aa bit?

------------------------------------- Gorgeous split line ------------------------------------

C # import excel into Access

First, export the excel file to the Datatable

String excelConnectionStr = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + this. tbExcelPath. Text +
"; Extended Properties = Excel 8.0; HDR = YES; IMEX = 1 ;";
String query = "SELECT * FROM [" + this. cbSheetName. SelectedItem. ToString () + "$]";
OleDbCommand oleCommand = new OleDbCommand (query, new OleDbConnection (excelConnectionStr ));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter (oleCommand );
DataSet myDataSet = new DataSet ();
Try
{
OleAdapter. Fill (myDataSet, this. tbNewDBTableName. Text );
}
Catch (Exception exx)
{
MessageBox. Show (exx. Message );
}
MyDataSet. Tables [0]. TableName = this. tbNewDBTableName. Text;
 

C # import Dataset or DataTable to Access

Export dataset or datatable as xml before importing data. If necessary, add a primary key.

 

String strXML = Environment. GetEnvironmentVariable ("Temp") + "// xmldata. xml /";
MyDataSet. WriteXml (strXML, XmlWriteMode. WriteSchema );
Microsoft. Office. Interop. Access. Application accessApp = new Microsoft. Office. Interop. Access. ApplicationClass ();
AccessApp. OpenCurrentDatabase (this. tbDatabasePath. Text, false ,"");
AccessApp. ImportXML (strXML, AcImportXMLOption. acStructureAndData );
AccessApp. closecurspondatabase ();

AccessApp. Quit (AcQuitOption. acQuitSaveAll );
System. Runtime. InteropServices. Marshal. ReleaseComObject (accessApp );
AccessApp = null;
GC. Collect ();
System. IO. File. Delete (strXML );

 

------------------------------------- Gorgeous split line ------------------------------------

Save dataset or datatable to the table:

Delete first, and then add:

 

OleDbConnection oleConn = new OleDbConnection (connectStr );
String SQL = "delete from" + curModelName + "where ID> 0 ";
OleDbCommand cmd = new OleDbCommand (SQL, oleConn );
Try
{
If (oleConn. State! = ConnectionState. Open)
{
OleConn. Open ();
& N