C# Access database operation, export data to DMB file

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

A few days ago to do a gadget, today I have nothing to do, to do something in order

The 1.MdbOperate class, method access

public class MdbOperate

{

static Logger logger = new Logger();

static OleDbConnection connection;

static string mdbConn = ConfigurationManager.AppSettings["mdbConn"];//The web.config configuration  <add key="mdbConn" value="provider=Microsoft.ACE.OLEDB.12.0;Data Source="/> 


Download address: http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

#Create database region

/// <summary>

/// Create the mdb file

/// </summary>

/// <param name="filename">The database name full path</param>

public static void creatMDB(string filename)

{

try

{

ADOX.CatalogClass cat = new CatalogClass();//Microsoft.ACE.OLEDB.12.0

string str = mdbConn + filename + ";";

cat.Create(str);

cat = null;

}

catch (Exception ex)

{

logger.Error("Create the mdb file errors:" + ex.Message);

logger.Flush();

}

}

#endregion

#Region judging whether there is a table in the database

public static bool GetTables(OleDbConnection conn)

{

int result = 0;

DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

if (schemaTable != null)

{

for (Int32 row = 0; row <schemaTable.Rows.Count; row++)

{

string col_name = schemaTable.Rows[row]["TABLE_NAME"].ToString();

if (col_name == "mailtable")

{

result++;

}

}

}

if (result == 0)

return false;

return true;

}

#endregion

#region

/// <summary>

/// Create table

/// </summary>

/// <param name="mdbPath"></param>

/// <param name="tableName"></param>

/// <param name="mdbHead">MdbHead is a ArrayList, memory is table concrete column in the table. </param>

public static void CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)

{

try

{

ADOX.CatalogClass cat = new CatalogClass();

string sAccessConnection = mdbConn + mdbPath + ";Jet OLEDB:Engine Type=5";

connection = new OleDbConnection(sAccessConnection);

connection.Open();//Open the database connection

ADODB.Connection cn = new ADODB.Connection();

cn.Open(sAccessConnection, null, null, -1);

cat.ActiveConnection = cn;

//Create a new table

ADOX.TableClass table = new TableClass();

table.ParentCatalog = cat;

table.Name = tableName;

//Adding a column automatic growth

/* ADOX.ColumnClass column = new ColumnClass();

column.ParentCatalog = cat;

column.Type = ADOX.DataTypeEnum.adInteger;//Set the field type

column.Name = "indexno";

column.Properties["Jet OLEDB:Allow Zero Length"].Value = false;

column.Properties["AutoIncrement"].Value = true;

table.Columns.Append(column, DataTypeEnum.adInteger, 0);

*/

/*ADOX.ColumnClass column = new ColumnClass();

column.ParentCatalog = cat;

column.Type = ADOX.DataTypeEnum.adInteger;//Set the field type

column.Name = "indexno";

column.Properties["Jet OLEDB:Allow Zero Length"].Value = false;

table.Columns.Append(column, DataTypeEnum.adInteger, 0);*/

// Add a text field

int size = mdbHead.Count;

for (int i = 0; i <size; i++)

{

//Add a text field

ADOX.ColumnClass col2 = new ADOX.ColumnClass();

col2.ParentCatalog = cat;

col2.Name = mdbHead[i].ToString(); //The name of the column

col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;

table.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);

}

//ALTER TABLE user ALTER COLUMN userinfo Memo

//Set key

table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "indexno", "", "");

cat.Tables.Append(table); //This table is added to the database (very important), C# Access create table

table = null;

cat = null;

connection.Close();

}

catch (Exception ex)

{

logger.Error("Create table error:" + ex.Message);

logger.Flush();

}

}

#endregion

/// <summary>

/// Modify the content type for the note type (content field type is too long, can only use the note type)

/// </summary>

/// <param name="oleDBconn"></param>

/// <returns></returns>

public static bool UpdateType(OleDbConnection oleDBconn)

{

OleDbCommand updateCommand = oleDBconn.CreateCommand();

updateCommand.CommandText = "ALTER TABLE mailtable ALTER COLUMN content Memo";

int row = updateCommand.ExecuteNonQuery();//Here, don't know is it right? In this way, although the best returns 0 but found the file type is modified to note type

if (row > 0)

{

return true;

}

else

{

return false;

}

}

}

The 2 method calls, and export data to DMB file

if (!Directory.Exists(filePath))

{

Directory.CreateDirectory(filePath);

}

string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".mdb";

string fullpathMDB = Path.Combine(filePath, filename);

if (!File.Exists(fullpathMDB))

{

MdbOperate.creatMDB(fullpathMDB);

}

oleDBconn = new OleDbConnection(mdbConn + fullpathMDB);

oleDBconn.Open();

//To determine whether there mailtable database table

if (!MdbOperate.GetTables(oleDBconn))

{

string table_name = "mailtable";

ArrayList col_names = new ArrayList();

col_names.Add("indexno");

col_names.Add("outputtime");//Add column

col_names.Add("sender");

col_names.Add("email");

col_names.Add("tel");

col_names.Add("adds");

col_names.Add("zipcode");

col_names.Add("receiver");

col_names.Add("title");

col_names.Add("strIPAddr");

col_names.Add("time");

col_names.Add("content");

MdbOperate.CreateMDBTable(fullpathMDB, table_name, col_names);//Create table

oleDBconn.Close();

oleDBconn.Open();

}

//Query information and data to fill in to the new mailtable table

List<MailEntity> list = new List<MailEntity>();

int totalCount = 0;

list = DataBindFile(this.StartCalendar.Text.ToShortDateString(), this.EndCalendar.Text.ToShortDateString(), out totalCount, true);//Query data from the SQL database

try

{

if (list.Count > 0)

{

foreach (var item in list)

{

OleDbCommand insertCommand = oleDBconn.CreateCommand();

insertCommand.CommandText = "insert into mailtable(indexno,outputtime,sender,email,tel,adds,zipcode,receiver,title,strIPAddr,[time],content) values (?,?,?,?,?,?,?,?,?,?,?,?)";//Use? 'instead, placeholder is equivalent to c# in'{0},{1}...'

//Modify the fields for note type

bool m = MdbOperate.UpdateType(oleDBconn);

insertCommand.Parameters.Add("indexno", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[0].Value = item.indexno;

insertCommand.Parameters.Add("outputtime", OleDbType.DBDate, 500);

insertCommand.Prepare();

insertCommand.Parameters[1].Value = item.outputtime;

insertCommand.Parameters.Add("sender", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[2].Value = item.sender;

insertCommand.Parameters.Add("email", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[3].Value = item.email;

insertCommand.Parameters.Add("tel", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[4].Value = item.tel;

insertCommand.Parameters.Add("adds", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[5].Value = item.adds;

insertCommand.Parameters.Add("zipcode", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[6].Value = item.zipcode;

insertCommand.Parameters.Add("receiver", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[7].Value = item.receiver;

insertCommand.Parameters.Add("title", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[8].Value = item.title;

insertCommand.Parameters.Add("strIPAddr", OleDbType.Char, 500);

insertCommand.Prepare();

insertCommand.Parameters[9].Value = item.strIPAddr;

insertCommand.Parameters.Add("time", OleDbType.DBDate, 500);

insertCommand.Prepare();

insertCommand.Parameters[10].Value = item.time;

insertCommand.Parameters.Add("content", OleDbType.VarWChar, 8000);

insertCommand.Prepare();

insertCommand.Parameters[11].Value = item.content;

Int32 row = insertCommand.ExecuteNonQuery();

if (row > 0)

{

logger.Info(item.title.ToString() + "Export success");

logger.Flush();

}

}

}

}

catch (Exception ex)

{

logger.Error("Error exporting data to mdb file: " + ex.Message);

logger.Flush();

throw ex;

}


Note:'Microsoft.Jet.OLEDB.4.0'provider is not registered met The on the local machine  error, please download Access driver installation, before encountered this problem, download the driver installed a ha, OK. The local environment is Win7 64 +iis7+vs2010 server system, window Server2003 64 bit system, project compile time is any CPU.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Augustine at November 17, 2013 - 8:32 AM