dbmanagement.cs

C#代码创建Access MDB数据库,无需Access环境.

//******************************************************************************************************************
//     
// MS Access DB Management v1.0
//
//******************************************************************************************************************
//
//
//  The idea of developing this application came when I was building my web site that is using
//  MS Access as back-end database. I was actually developing the site in our office and it happens
//  that there are no MS Access installed in the workstations. I wasn't able to create tables through VS2005
//  either so I created a database application that is capable of creating MDB and execute scripts such as.
//  table creation and deletion.
//
//  This application consumes ADOX and ADODB. 
//  In the COM tab of the Add Reference dialog box, locate the Microsoft ADO Ext. 2.7 or 2.8 for DDL and Security: 
//
//
//
//	- Rhialyn Nomorosa, .NET Developer
//    http://www.rhialyn.somee.com
//
//

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

using System.Reflection;
using System.IO;
using ADOX;
using ADODB;

namespace Rhialyn.MySite.DBManagement
{
	public partial class DBManagement : Form
	{
		public DBManagement()
		{
			InitializeComponent();
		}

		private void DBManagement_Load(object sender, EventArgs e)
		{
			txtDBName.Text = GetAppPath() + "TestTableName.mdb";
			txtTableScript0.Text = "Create Table TestTableNameA(TestTableID Counter Primary Key,TestTableText VarChar(20), TestTableNameMemo Memo, TestTableNameDate DateTime);";
			txtTableScript1.Text = "Create Table TestTableNameB(TestTableID Counter Primary Key,TestTableText VarChar(20), TestTableNameMemo Memo, TestTableNameDate DateTime);";
		}

		/// <summary>
		/// Used to retrieve the path where executable is located. 
		/// Serves as a default location for the Access Database
		/// </summary>
		/// <returns></returns>
		private string GetAppPath()
		{
			Module[] modules = Assembly.GetExecutingAssembly().GetModules();
			string aPath = Path.GetDirectoryName(modules[0].FullyQualifiedName);

			if ((aPath != "") && (aPath[aPath.Length - 1] != '\\'))
			{
				aPath += '\\';
			}
			return aPath;
		}

		private void btnCreateDB_Click(object sender, EventArgs e)
		{
			if (txtDBName.Text.EndsWith(".mdb"))
			{
				if (File.Exists(txtDBName.Text))
				{
					if (MessageBox.Show("DB already exists! Overwrite?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
					{
						this.CreateDB();
					}
				}
				else
				{
					this.CreateDB();
				}
			}
			else
			{
				MessageBox.Show("Please specify database name!");
			}
		}

		private void btnCreateTable_Click(object sender, EventArgs e)
		{
			if (File.Exists(txtDBName.Text))
			{
				this.CreateTable(0);
				this.CreateTable(1);
			}
			else
			{
				if (MessageBox.Show("DB doesn't exists! Create?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
				{
					this.CreateDB();
					this.CreateTable(0);
					this.CreateTable(1);
				}
			}
		}

		private void btnBrowse_Click(object sender, EventArgs e)
		{
			folderBrowserDialogMDF.ShowDialog();
			txtDBName.Text = folderBrowserDialogMDF.SelectedPath;
		}

		/// <summary>
		/// Utilizes ADOX and ADODB to connect to Access database
		/// </summary>
		private void CreateDB()
		{
			try
			{
				CatalogClass catDatabase = null;
				string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + txtDBName.Text;
				catDatabase = new CatalogClass();
				catDatabase.Create(strDatabase);
				catDatabase = null;
				MessageBox.Show("A new Microsoft JET database has been created");
			}
			catch (Exception ex)
			{
				MessageBox.Show("Error encountered: " + ex.Message);
			}
		}

		/// <summary>
		/// Utilizes ADOX and ADODB to connect to Access database and execute scripts
		/// </summary>
		private void CreateTable(int nIndex)
		{
			ConnectionClass ObjConnection = new ConnectionClass();
			try
			{
				object objAffected = null;
				string strStatement = ""; 
				if (nIndex == 0)
				{
					strStatement=txtTableScript0.Text;
				}
				else 
				{
					strStatement = txtTableScript1.Text;
				}
				string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + @txtDBName.Text;
				ObjConnection.Open(strConnection, null, null, -1);
				ObjConnection.Execute(strStatement, out objAffected, 0);
				ObjConnection.Close();

				MessageBox.Show("The script executed successfully!");
			}
			catch (Exception ex)
			{
				MessageBox.Show("Error encountered: " + ex.Message);
			}
		}
	}
}