public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.mdb"));
if (File.Exists(path))
File.Delete(path);
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5");
CSConfig.SetDB(new CSDataProviderAccess(path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID COUNTER PRIMARY KEY,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID counter PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price double NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID counter PRIMARY KEY,
[Date] datetime NOT NULL DEFAULT DATE()+TIME(),
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID counter primary key,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID counter primary key,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID guid NOT NULL PRIMARY KEY,
Name text(50) NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblColdData (Name text(50) NULL)");
cat.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
ADOX.Column column = new ADOX.Column();
column.Name = "ColdDataID";
column.Type = ADOX.DataTypeEnum.adGUID;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = false;
column.Properties["Fixed Length"].Value = true;
column.Properties["Jet OLEDB:AutoGenerate"].Value = true;
column.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
cat.Tables["tblColdData"].Columns.Append(column, ADOX.DataTypeEnum.adGUID, 0);
CSDatabase.ExecuteNonQuery("ALTER TABLE tblColdData ADD CONSTRAINT PK_COLD_DATA PRIMARY KEY (ColdDataID)");
}
// --------------------- Helper methods ---------------------
// --------------------- General methods --------------------
/*****
* Purpose: To create a new database
*
* Parameter list:
* string name the name of the new database
*
* Return value:
* int 1 on success, 0 otherwise
*****/
public int CreateNewDB(string name)
{
int index;
string newDB;
try
{
if (name.Length == 0 && dbName.Length == 0) // No DB name given?
{
return(0);
}
index = name.LastIndexOf('.');
if (index == -1) // No secondary file name?
{
dbName += ".mdb"; // Assume Access DB
name = dbName;
}
combinedName = Path.Combine(pathName, name); // Where to put it
ADOX.CatalogClass myCat = new ADOX.CatalogClass();
newDB = CONNECTSTRING + combinedName + ";" + CONNECTSTRINGPART2;
myCat.Create(newDB);
myCat = null;
}
catch (Exception ex)
{
#if DEBUG
// Don't put user I/O in a class except for debugging
MessageBox.Show("Error: " + ex.Message);
#endif
return(0);
}
return(1);
}
/// <summary>
/// 构造函數
/// </summary>
/// <param name="mdbPath">檔案路徑</param>
/// <param name="deleteWhenExists">如果檔案已經存在,是否刪除舊檔案</param>
public AccessDao(string mdbPath, bool deleteWhenExists)
{
this.mdbPath = mdbPath + ".mdb";
this.strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.mdbPath + ";Jet OLEDB:Engine Type=5";
//如果已存在檔案
if (File.Exists(this.mdbPath))
{
//刪除
if (deleteWhenExists)
{
File.Delete(this.mdbPath);
}
//不刪除
else
{
return;
}
}
//新增資料檔案
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(this.strConn);
//釋放資源
Marshal.ReleaseComObject(cat);
cat = null;
}
/// <summary>
/// 建立mdb数据库文件
/// </summary>
/// <param name="pathandname">完整路径</param>
public static void CreateMdbFile(string pathandname)
{
ADOX.CatalogClass cls = new ADOX.CatalogClass();
cls.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + pathandname + ";" +
"Jet OLEDB:Engine Type=5");
cls = null;
}
public static void createDatabase(string databaseFileName)
{
string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseFileName + ";Jet OLEDB:Engine Type=5";
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(strAccessConn);
cat = null;
}
/// <summary>
/// 新建mdb表，mdbHead是一个ArrayList，存储的是table表中的具体列名
/// </summary>
/// <param name="mdbPath"></param>
/// <param name="tableName"></param>
/// <param name="mdbHead"></param>
/// <returns></returns>
private static bool CreateMDBTable(string tableName, ArrayList mdbHead)
{
//ADODB需添加com程序集
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
string sAccessConnection
= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbpath;
ADODB.Connection cn = new ADODB.Connection();
cn.Open(sAccessConnection, null, null, 0);
cat.ActiveConnection = cn;
//新建一个表
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;
tbl.Name = tableName;
int size = mdbHead.Count;
for (int i = 0; i < size; i++)
{
//增加一个文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
ADOX.IndexClass ind2 = new ADOX.IndexClass();
col2.ParentCatalog = cat;
col2.Name = mdbHead[i].ToString();//列的名称
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col2.DefinedSize = 50;
if (i == 0)
{
// col2.Type = ADOX.DataTypeEnum.adSingle;
// col2.Type = ADOX.DataTypeEnum.adInteger;
// col2.Properties["AutoIncrement"].Value = true;
tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "通道");
}
col2.SortOrder = ADOX.SortOrderEnum.adSortAscending;
// col2.DefinedSize = 20;
// col2.Type = ADOX.DataTypeEnum.adGUID;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adWChar, 50);
// tbl.Indexes.Append(col2 as );
// tbl.Columns.Append(col2, ADOX.KeyTypeEnum.adKeyPrimary, 500);
}
cat.Tables.Append(tbl); //这句把表加入数据库(非常重要)
tbl = null;
cat = null;
cn.Close();
return(true);
}
catch (Exception t)
{
MessageBox.Show(t.Message);
return(false);
}
}
private void CreateDnbInfoWithADOX(string MdbPath)
{
ADOX.CatalogClass Adc = new ADOX.CatalogClass();
if (CreateDir(MdbPath) == false)
{
return;
}
Adc.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MdbPath + ";Jet OLEDB:Engine Type=5");
Adc = null;
}
protected override void Dispose(bool disposeManagedResources)
{
if (disposeManagedResources)
{
// dispose any managed resources here...
}
// dispose any unmanaged resources here...
Marshal.ReleaseComObject(CatalogClass);
Marshal.FinalReleaseComObject(CatalogClass);
CatalogClass = null;
}
/// <summary>
/// Creates database file.
/// </summary>
/// <param name="filePath">Export file path.</param>
/// <returns>Created database.</returns>
private ADOX.Catalog _CreateDatabase(string filePath)
{
Debug.Assert(!string.IsNullOrEmpty(filePath));
string connectionString = _GetConnectionString(filePath);
// create empty database
var catalog = new ADOX.CatalogClass();
catalog.Create(connectionString);
return(catalog);
}
public static void createDatabase(string databaseFileName)
{
if (!File.Exists(databaseFileName))
{
string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseFileName + ";Jet OLEDB:Engine Type=5";
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(strAccessConn);
cat = null;
}
else
{
MessageBox.Show(databaseFileName + " already exists. Cannot create!!!", "File Already Exists!!!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public bool CreateDatabase(string DatabaseName, string FilePath)
{
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(GetStringCreateDB(FilePath + "\\" + DatabaseName + ".mdb"));
Marshal.ReleaseComObject(cat);
cat = null;
GC.Collect();
return true;
}
catch
{
return false;
}
}
public bool CreateDatabase(string DatabaseName, string FilePath)
{
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(GetStringCreateDB(FilePath + "\\" + DatabaseName + ".mdb"));
Marshal.ReleaseComObject(cat);
cat = null;
GC.Collect();
return(true);
}
catch
{
return(false);
}
}
public static bool CreateDB(string filename)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
try
{
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Jet OLEDB:Engine Type=5");
}
catch
{
return(false);
}
cat = null;
return(true);
}
private ObservableCollection <NodeViewModel> GetGenericDatabaseObjectsAsync()
{
ObservableCollection <NodeViewModel> databaseObjects = new ObservableCollection <NodeViewModel>();
ADODB.Connection connection = null;
try
{
connection = new ADODB.ConnectionClass();
connection.Open(this.connectionString, "", "", (int)ADODB.ConnectOptionEnum.adConnectUnspecified);
ADOX.Catalog catalog = new ADOX.CatalogClass();
catalog.ActiveConnection = connection;
var tableContainer = new ComplexNodeViewModel("Tables");
var viewContainer = new ComplexNodeViewModel("Views");
PopulateTablesAndViews(catalog, tableContainer, viewContainer);
var storedProcedureContainer = new ComplexNodeViewModel("Stored Procedures");
PopulateStoredProcedures(catalog, connection, storedProcedureContainer);
databaseObjects.Add(tableContainer);
databaseObjects.Add(viewContainer);
databaseObjects.Add(storedProcedureContainer);
}
catch (Exception exception)
{
databaseObjects.Clear();
databaseObjects.Add(new SimpleNodeViewModel(exception.Message));
}
finally
{
if (null != connection)
{
try
{
connection.Close();
}
catch { }
}
}
return(databaseObjects);
}
public static void CreateLocalDbFile()
{
ADOX.CatalogClass catalog = new ADOX.CatalogClass( );
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath + ";Jet OLEDB:Engine Type=5");
CreateTable( );
}
catch (Exception err)
{
throw err;
}
finally
{
if (catalog.ActiveConnection != null)
{
catalog.ActiveConnection = null;
}
}
}
long m_lastOID; // highest inclusive OID
/// <summary>
/// Encapsulates a Microsoft Access database file
/// </summary>
/// <param name="file">Required file path</param>
/// <param name="types">Required map of string identifiers and types</param>
/// <param name="instances">Optional map of instance identifiers and objects (specified if saving)</param>
public FormatMDB(
string file,
Dictionary <string, Type> types,
Dictionary <long, SEntity> instances)
{
this.m_file = file;
this.m_typemap = types;
this.m_instances = instances;
if (m_instances == null)
{
this.m_instances = new Dictionary <long, SEntity>();
}
string connectionstring =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + this.m_file + ";" +
"Jet OLEDB:Engine Type=5;";
// create database if it doesn't exist
bool bNew = false;
if (!System.IO.File.Exists(file))
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(connectionstring);
bNew = true;
}
this.m_connection = new OleDbConnection(connectionstring);
this.m_connection.Open();
// build schema
if (bNew)
{
foreach (Type t in this.m_typemap.Values)
{
InitType(t);
}
}
}
long m_lastOID; // highest inclusive OID
/// <summary>
/// Encapsulates a Microsoft Access database file
/// </summary>
/// <param name="file">Required file path</param>
/// <param name="types">Required map of string identifiers and types</param>
/// <param name="instances">Optional map of instance identifiers and objects (specified if saving)</param>
public FormatMDB(
string file,
Dictionary<string, Type> types,
Dictionary<long, SEntity> instances)
{
this.m_file = file;
this.m_typemap = types;
this.m_instances = instances;
if (m_instances == null)
{
this.m_instances = new Dictionary<long, SEntity>();
}
string connectionstring =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + this.m_file + ";" +
"Jet OLEDB:Engine Type=5;";
// create database if it doesn't exist
bool bNew = false;
if (!System.IO.File.Exists(file))
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(connectionstring);
bNew = true;
}
this.m_connection = new OleDbConnection(connectionstring);
this.m_connection.Open();
// build schema
if(bNew)
{
foreach (Type t in this.m_typemap.Values)
{
InitType(t);
}
}
}
public override bool CreateDatabase(BaseLoginInfo loginInfo)
{
LoginInfo_Oledb myInfo = loginInfo as LoginInfo_Oledb;
Debug.Assert(myInfo != null);
bool result = false;
try
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source={0};" +
"Jet OLEDB:Engine Type=5", myInfo.Database));
result = true;
}
catch (Exception ee)
{
throw ee;
}
return(result);
}
/// <summary>
/// 创建mdb
/// </summary>
/// <param name="mdbPath"></param>
/// <returns></returns>
private static bool CreateMDBDataBase()
{
try
{
//ADOX需添加com程序集,CatalogClass须将ADO属性的嵌入互操作类型置为false.
// FileHelper.CreateDirectory(Directory.GetCurrentDirectory() + "/AccessFile/");
if (!File.Exists(@mdbpath))
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbpath + ";");
cat = null;
return(true);
}
else
{
return(false);
}
}
catch (Exception ei)
{
MessageBox.Show(ei.Message);
return(false);
}
}
public ADOX_CatalogClass()
{
CatalogClass = new ADOX.CatalogClass();
}
private void run()
{
if (!File.Exists(filePath))
{
//创建数据库
ADOX.CatalogClass Student = new ADOX.CatalogClass();
Student.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=2327085154;");
//新建一个表[user]
ADOX.TableClass user = new ADOX.TableClass();
user.ParentCatalog = Student;
user.Name = "usb";
//增加一个自动增长的字段ID
ADOX.ColumnClass ID = new ADOX.ColumnClass();
ID.ParentCatalog = Student;
ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
ID.Name = "ID";
ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
ID.Properties["AutoIncrement"].Value = true;
user.Columns.Append(ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段username
ADOX.ColumnClass username = new ADOX.ColumnClass();
username.ParentCatalog = Student;
username.Name = "U盘名";
username.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(username, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段U——ID
ADOX.ColumnClass U_ID = new ADOX.ColumnClass();
U_ID.ParentCatalog = Student;
U_ID.Name = "U盘序列号";
U_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(U_ID, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段 密文password
ADOX.ColumnClass U_password = new ADOX.ColumnClass();
U_password.ParentCatalog = Student;
U_password.Name = "U盘密文";
U_password.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(U_password, ADOX.DataTypeEnum.adLongVarChar, 20);
//增加一个文本字段 密文路径
ADOX.ColumnClass fileName = new ADOX.ColumnClass();
fileName.ParentCatalog = Student;
fileName.Name = "密文路径";
fileName.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(fileName, ADOX.DataTypeEnum.adVarChar, 20);
//把表加进数据库
Student.Tables.Append(user);
System.Runtime.InteropServices.Marshal.ReleaseComObject(user);
//新建一个表[mail]
ADOX.TableClass mail = new ADOX.TableClass();
mail.ParentCatalog = Student;
mail.Name = "Email";
//增加一个文本字段mail
ADOX.ColumnClass mail_name = new ADOX.ColumnClass();
mail_name.ParentCatalog = Student;
mail_name.Name = "Emial";
mail_name.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
mail.Columns.Append(mail_name, ADOX.DataTypeEnum.adVarChar, 20);
Student.Tables.Append(mail);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mail);
//新建一个表[Log]
ADOX.TableClass Log = new ADOX.TableClass();
Log.ParentCatalog = Student;
Log.Name = "Log";
//增加一个自动增长的字段ID
ADOX.ColumnClass log_ID = new ADOX.ColumnClass();
log_ID.ParentCatalog = Student;
log_ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
log_ID.Name = "ID";
log_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
log_ID.Properties["AutoIncrement"].Value = true;
Log.Columns.Append(log_ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段 日期
ADOX.ColumnClass date = new ADOX.ColumnClass();
date.ParentCatalog = Student;
date.Name = "日期";
date.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(date, ADOX.DataTypeEnum.adDate, 20);
//增加一个文本字段 type
ADOX.ColumnClass type = new ADOX.ColumnClass();
type.ParentCatalog = Student;
type.Name = "操作";
type.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(type, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段 方式
ADOX.ColumnClass 方式 = new ADOX.ColumnClass();
方式.ParentCatalog = Student;
方式.Name = "方式";
方式.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
Log.Columns.Append(方式, ADOX.DataTypeEnum.adVarChar, 20);
Student.Tables.Append(Log);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Log);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Student);
user = null;
mail = null;
Log = null;
Student = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
/// <summary>
/// Mose Like Excel
/// Just different in Connection String
/// </summary>
/// <param name="csvFile"></param>
/// <param name="sdfFile"></param>
/// <param name="p"></param>
/// <param name="p_4"></param>
/// <returns></returns>
internal static bool ConvertCSVToSdf(string csvFile, string sdfFile, bool NeedCopyData, string targetDbPwd, bool isFirstRowIsColumnName)
{
bool result = false;
if (!File.Exists(csvFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.CSV).X_Handler;
srcEngine.Open(new LoginInfo_CSV()
{
Database = csvFile, IsFirstRowIsColumnName = isFirstRowIsColumnName
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
List <string> tableList = srcEngine.GetTableListInDatabase();
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
//IF the ce database not existed ,then create it .
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = "", IsEncrypted = false, CurOpenMode = OpenMode.ReadWrite
});
List <string> targetDbList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDbList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
string strconnection = CoreEA.ConnSTR.DbConnectionString.TxtFile.OleDb_DelimitedColumns(csvFile, true);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("{0} {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
throw new Exception(string.Format("Create table {0} error", tableName));
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
}
return(result);
}
public static bool DataTableExportToAccess(DataTable dt, string filename, String tabName, ref String reMsg)
{
if (dt.Rows.Count <= 0)
{
reMsg = "目前无数据不需要导出";
return(false);
}
if (!File.Exists(filename))
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";");
cat = null;
}
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
StringBuilder sb = new StringBuilder();
string connString = String.Empty;
connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filename);
sb.Append("CREATE TABLE " + tabName + " (");
String colName = String.Empty;
String colNames = String.Empty;
String colNamePramas = String.Empty;
String colType = String.Empty;
for (int i = 0; i < cols; i++)
{
colName = dt.Columns[i].ColumnName.ToString();
colType = dt.Columns[i].DataType.ToString();
colType = NetDataTypeToDataBaseType(colType);
if (i == 0)
{
sb.Append(colName + " " + colType);
colNames += colName;
colNamePramas += "@" + colName;
}
else
{
sb.Append(", " + colName + " " + colType);
colNames += "," + colName;
colNamePramas += ",@" + colName;
}
}
sb.Append(" )");
if (colNames == String.Empty)
{
reMsg = "数据集的列数必须大于0";
return(false);
}
using (OleDbConnection objConn = new OleDbConnection(connString))
{
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = sb.ToString();
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
reMsg = "在Excel中创建表失败，错误信息：" + e.Message;
return(false);
}
sb.Remove(0, sb.Length);
sb.Append(" insert into " + tabName + " (" + colNames + ") values(" + colNamePramas + " )");
objCmd.CommandText = sb.ToString();
OleDbParameterCollection param = objCmd.Parameters;
for (int i = 0; i < cols; i++)
{
colType = dt.Columns[i].DataType.ToString();
colName = dt.Columns[i].ColumnName.ToString();
if (colType == "System.String")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.VarChar));
}
else if (colType == "System.DateTime")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Date));
}
else if (colType == "System.Boolean")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Boolean));
}
else if (colType == "System.Decimal")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Decimal));
}
else if (colType == "System.Double")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Double));
}
else if (colType == "System.Single")
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Single));
}
else
{
param.Add(new OleDbParameter("@" + colName, OleDbType.Integer));
}
}
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < param.Count; i++)
{
param[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
}
reMsg = "数据成功导出";
return(true);
}
/// <summary>
/// 新增table, 欄位都是字串類型的(2000字節)
/// </summary>
/// <param name="tableName">表名稱</param>
/// <param name="list">欄位名稱集合</param>
/// <param name="needID">是否創建自動增長的主鍵(名稱為oid)</param>
/// <returns>bool:操作訊息(true:成功,false:失敗)</returns>
public bool CreateTable(string tableName, List <string> list, bool needID)
{
//初始化
bool recode = false;
ADOX.CatalogClass cat = null;
ADOX.TableClass table = null;
try
{
// 資料檔案
cat = new ADOX.CatalogClass();
//引用Connection
ADODB.Connection cn = new ADODB.Connection();
cn.Open(strConn, null, null, -1);
cat.ActiveConnection = cn;
// 新增表
table = new ADOX.TableClass();
table.ParentCatalog = cat;
table.Name = tableName;
if (needID)
{
//先增加一個自動增長的欄位
ADOX.ColumnClass id_col = new ADOX.ColumnClass();
id_col.ParentCatalog = cat;
//設置欄位類型
id_col.Type = ADOX.DataTypeEnum.adInteger;
//這欄位名稱設為“oid”
id_col.Name = "oid";
id_col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
id_col.Properties["AutoIncrement"].Value = true;
//表裡面增加一個字段
table.Columns.Append(id_col, ADOX.DataTypeEnum.adInteger, 0);
//釋放資源
if (id_col != null)
{
Marshal.ReleaseComObject(id_col);
id_col = null;
}
}
//逐個加入欄位,但都是字串類型的
foreach (String key in list)
{
//初始化
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Name = key;
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
table.Columns.Append(col, ADOX.DataTypeEnum.adLongVarChar, 2000);
//釋放資源
if (col != null)
{
Marshal.ReleaseComObject(col);
col = null;
}
}
// 添加表
cat.Tables.Append(table);
recode = true;
}
//捕捉例外
catch
{
}
finally
{
//釋放資源
if (table != null)
{
Marshal.ReleaseComObject(table);
//清空
table = null;
}
//釋放資源
if (cat != null)
{
Marshal.ReleaseComObject(cat);
cat = null;
}
// 垃圾回收
GC.WaitForPendingFinalizers();
GC.Collect();
}
//操作訊息
return(recode);
}
/// <summary>
/// Accesss to sqlce
/// </summary>
/// <param name="p"></param>
/// <param name="sdfFile"></param>
public static bool SyncMdbToSdf(string mdbFile, string sdfFile, bool NeedCopyData, string targetDbPwd)
{
bool result = false;
if (!File.Exists(mdbFile))
{
"ImportData_FileNotFound".GetFromResourece().Notify();
return(false);
}
ICoreEAHander srcEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.OleDb).X_Handler;
srcEngine.Open(new LoginInfo_Oledb()
{
Database = mdbFile
});
if (!srcEngine.IsOpened)
{
"ImportData_ReadError".GetFromResourece().Notify();
return(false);
}
//Filter system table
List <string> tableList = new List <string>();
foreach (string item in srcEngine.GetTableListInDatabase())
{
if (!item.StartsWith("MSys"))
{
tableList.Add(item);
}
}
if (tableList == null)
{
"ImportData_NoTable".GetFromResourece().Notify();
return(false);
}
ICoreEAHander destEngine = new CoreEA.CoreE(CoreE.UsedDatabaseType.SqlCE35).X_Handler;
if (!File.Exists(sdfFile))
{
if (!destEngine.CreateDatabase(new LoginInfo_SSCE()
{
DbName = sdfFile, IsEncrypted = false, IsCaseSensitive = false
}))
{
"ImportData_CreateSSCEFileFailure".GetFromResourece().Notify();
return(false);
}
}
destEngine.Open(new LoginInfo_SSCE()
{
DbName = sdfFile, Pwd = targetDbPwd
});
List <string> targetDBList = destEngine.GetTableListInDatabase();
try
{
foreach (string tableName in tableList)
{
//Don't import table which name has existed.
if (targetDBList.Contains(tableName))
{
continue;
}
string sqlCeTableName = tableName;
//if (Properties.Settings.Default.IsAllowAutoParseInvalidCharsInTableName)
//{
// sqlCeTableName= sqlCeTableName.Replace(" ", "");
//}
string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}", mdbFile);
ADODB.Connection conn = new ADODB.ConnectionClass();
//conn.ConnectionString = strconnection;
conn.Open(strconnection, "Admin", "", 0);
//Prepare to retrive schema info from access via COM
ADOX.Catalog catelog = new ADOX.CatalogClass();
catelog.let_ActiveConnection(conn);
ADOX.Table tempTable = catelog.Tables[tableName];
//Start Generate the Create Sdf table command
string tempCreateTableCmd = string.Empty;
tempCreateTableCmd = String.Format("CREATE TABLE [{0}] ", sqlCeTableName);
string tempSechma = string.Empty;
for (int i = 0; i < tempTable.Columns.Count; i++)
{
Debug.WriteLine("Source Field Name ------>" + tempTable.Columns[i].Name);
tempSechma += String.Format("[{0}] {1},",
tempTable.Columns[i].Name,
CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
tempTable.Columns[i].DefinedSize)
);
}
tempSechma = tempSechma.Substring(0, tempSechma.Length - 1);
tempCreateTableCmd = String.Format("{0} ({1})", tempCreateTableCmd, tempSechma);
if (destEngine.DoExecuteNonQuery(tempCreateTableCmd) != -1)
{
return(false);
}
if (NeedCopyData)
{
CopyTable(srcEngine.GetConnection(), (SqlCeConnection)destEngine.GetConnection(),
string.Format("Select * from [{0}]", tableName), sqlCeTableName);
}
}
result = true;
}
catch (Exception ee)
{
ee.HandleMyException();
//((SqlCeDatabase)destEngine.DbHandler).CloseSharedConnection();
}
return(result);
}
public OleDbModel(String server, Int32 port, String username, String password, String database)
{
//connectionString = String.Format("server={0}; port={1}; user id={2}; password={3}; database={4}; Max Pool Size=50; Min Pool Size=5; Pooling=True; Reset Pooled Connections=False; Cache Server Configuration=True;", server, port, username, password, database);
filename = Path.Combine(Directory.GetCurrentDirectory(), @"config\data\vsg.mdb");
connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename;
this.database = database;
OleDbConnection connection = new OleDbConnection(connectionString);
try
{
connection.Open();
}
catch (OleDbException ex)
{
// error codes from http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html
switch (ex.ErrorCode)
{
case -2147467259: // unknown / bad DB, create the database for them
{
ADOX.CatalogClass mdb = new ADOX.CatalogClass();
try
{
mdb.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Jet OLEDB:Engine Type=5");
ADOX.Table componentTable = new ADOX.Table();
componentTable.Name = "ComponentTable";
mdb.Tables.Append(componentTable);
ADOX.Table linkTable = new ADOX.Table();
linkTable.Name = "LinkTable";
mdb.Tables.Append(linkTable);
ADOX.Table parameterTable = new ADOX.Table();
parameterTable.Name = "ParameterTable";
mdb.Tables.Append(parameterTable);
}
catch (Exception e)
{
// Let the user know what went wrong.
MessageBox.Show(e.Message, "File error");
}
finally
{
mdb = null;
}
break;
}
default:
{
connectionString = String.Empty;
database = String.Empty;
throw new Exception(ex.Message);
}
}
}
finally
{
connection.Close();
}
}
public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.mdb"));
if (File.Exists(path))
{
File.Delete(path);
}
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5");
CSConfig.SetDB(new CSDataProviderAccess(path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID COUNTER PRIMARY KEY,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID counter PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price double NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID counter PRIMARY KEY,
[Date] datetime NOT NULL DEFAULT DATE()+TIME(),
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID counter primary key,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID counter primary key,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID guid NOT NULL PRIMARY KEY,
Name text(50) NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblColdData (Name text(50) NULL)");
cat.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
ADOX.Column column = new ADOX.Column();
column.Name = "ColdDataID";
column.Type = ADOX.DataTypeEnum.adGUID;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = false;
column.Properties["Fixed Length"].Value = true;
column.Properties["Jet OLEDB:AutoGenerate"].Value = true;
column.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
cat.Tables["tblColdData"].Columns.Append(column, ADOX.DataTypeEnum.adGUID, 0);
CSDatabase.ExecuteNonQuery("ALTER TABLE tblColdData ADD CONSTRAINT PK_COLD_DATA PRIMARY KEY (ColdDataID)");
}
public static void CreateDatabase(string connectionString)
{
try
{
// Create DB in form of .mdb file
ADOX.CatalogClass catalog = new ADOX.CatalogClass();
catalog.Create(connectionString);
ADOX.Table table = new ADOX.Table();
table.Name = "Files"; // Table name
// Id column
ADOX.ColumnClass idCol = new ADOX.ColumnClass()
{
Name = "Id",
ParentCatalog = catalog,
Type = ADOX.DataTypeEnum.adInteger,
};
idCol.Properties["AutoIncrement"].Value = true;
// Name column
ADOX.ColumnClass nameCol = new ADOX.ColumnClass()
{
Name = "Name",
ParentCatalog = catalog,
Type = ADOX.DataTypeEnum.adVarWChar,
DefinedSize = 16,
};
// FileData column (BLOBs)
ADOX.ColumnClass fileCol = new ADOX.ColumnClass()
{
Name = "FileData",
ParentCatalog = catalog,
Type = ADOX.DataTypeEnum.adLongVarBinary
};
// Add columns to Files table
table.Columns.Append(idCol);
table.Columns.Append(nameCol);
table.Columns.Append(fileCol);
// Add table to .mdb catalog
catalog.Tables.Append(table);
// Close the connection
ADODB.Connection con = (ADODB.Connection)catalog.ActiveConnection;
if (con != null && con.State != 0)
con.Close();
}
catch (Exception ex) { throw ex; }
}
/// <summary>
/// Creates database file.
/// </summary>
/// <param name="filePath">Export file path.</param>
/// <returns>Created database.</returns>
private ADOX.Catalog _CreateDatabase(string filePath)
{
Debug.Assert(!string.IsNullOrEmpty(filePath));
string connectionString = _GetConnectionString(filePath);
// create empty database
var catalog = new ADOX.CatalogClass();
catalog.Create(connectionString);
return catalog;
}
public Form1()
{
InitializeComponent();
label1.BackColor = Color.Transparent;
label2.BackColor = Color.Transparent;
label3.BackColor = Color.Transparent;
if (!File.Exists(filePath))
{
//创建数据库
ADOX.CatalogClass Student = new ADOX.CatalogClass();
Student.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Database Password=2327085154;");
//新建一个表[user]
ADOX.TableClass user = new ADOX.TableClass();
user.ParentCatalog = Student;
user.Name = "user";
//增加一个自动增长的字段ID
ADOX.ColumnClass ID = new ADOX.ColumnClass();
ID.ParentCatalog = Student;
ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
ID.Name = "ID";
ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
ID.Properties["AutoIncrement"].Value = true;
user.Columns.Append(ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段username
ADOX.ColumnClass username = new ADOX.ColumnClass();
username.ParentCatalog = Student;
username.Name = "用户名";
username.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(username, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段password
ADOX.ColumnClass password = new ADOX.ColumnClass();
password.ParentCatalog = Student;
password.Name = "密码";
password.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
user.Columns.Append(password, ADOX.DataTypeEnum.adVarChar, 20);
//把表加进数据库
Student.Tables.Append(user);
System.Runtime.InteropServices.Marshal.ReleaseComObject(user);
//新建表[student]
ADOX.TableClass student = new ADOX.TableClass();
student.ParentCatalog = Student;
student.Name = "student";
//增加一个自动增长的字段ID
ADOX.ColumnClass _ID = new ADOX.ColumnClass();
_ID.ParentCatalog = Student;
_ID.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
_ID.Name = "ID";
_ID.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
_ID.Properties["AutoIncrement"].Value = true;
student.Columns.Append(_ID, ADOX.DataTypeEnum.adInteger, 0);
//增加一个文本字段xueHao
ADOX.ColumnClass xueHao = new ADOX.ColumnClass();
xueHao.ParentCatalog = Student;
xueHao.Name = "学号";
xueHao.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(xueHao, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段name
ADOX.ColumnClass name = new ADOX.ColumnClass();
name.ParentCatalog = Student;
name.Name = "姓名";
name.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(name, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段iD
ADOX.ColumnClass iD = new ADOX.ColumnClass();
iD.ParentCatalog = Student;
iD.Name = "身份证号";
iD.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(iD, ADOX.DataTypeEnum.adVarChar, 20);
//增加一个文本字段math
ADOX.ColumnClass math = new ADOX.ColumnClass();
math.ParentCatalog = Student;
math.Type = ADOX.DataTypeEnum.adDouble;
math.Name = "高数成绩";
math.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(math, ADOX.DataTypeEnum.adDouble, 20);
//增加一个文本字段Eglish
ADOX.ColumnClass Eglish = new ADOX.ColumnClass();
Eglish.ParentCatalog = Student;
Eglish.Type = ADOX.DataTypeEnum.adDouble;
Eglish.Name = "英语成绩";
Eglish.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(Eglish, ADOX.DataTypeEnum.adDouble, 20);
//增加一个文本字段C
ADOX.ColumnClass C = new ADOX.ColumnClass();
C.ParentCatalog = Student;
C.Type = ADOX.DataTypeEnum.adDouble;
C.Name = "C语言成绩";
C.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
student.Columns.Append(C, ADOX.DataTypeEnum.adDouble, 20);
//把表加进数据库
Student.Tables.Append(student);
System.Runtime.InteropServices.Marshal.ReleaseComObject(student);
System.Runtime.InteropServices.Marshal.ReleaseComObject(Student);
user = null;
student = null;
Student = null;
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
