private string ConvertSQLCE35ConnectionStringToOLEDB(string ce35ConnectionString)
{
StringBuilder oledbConnectionString = new StringBuilder();
PFDataAccessObjects.PFDatabase db = null;
string databasePath = string.Empty;
string databasePassword = string.Empty;
try
{
db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLServerCE35);
db.ConnectionString = ce35ConnectionString;
databasePath = db.GetPropertyValue("DatabasePath").ToString();
databasePassword = db.GetPropertyValue("DatabasePassword").ToString();
oledbConnectionString.Length = 0;
oledbConnectionString.Append("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;");
oledbConnectionString.Append("Data Source=");
oledbConnectionString.Append(databasePath);
oledbConnectionString.Append(";");
if (databasePassword.Trim().Length > 0)
{
oledbConnectionString.Append("SSCE:Database Password='******';");
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
;
}
return(oledbConnectionString.ToString());
}
} //end method
private void LoadSQLServerCE35SchemaTree()
{
queryBuilder.MetadataContainer.BeginUpdate();
PFDataAccessObjects.PFDatabase db = null;
string sql = string.Empty;
try
{
queryBuilder.MetadataContainer.Items.Clear();
//get list of tables and add them to the schema tree
db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLServerCE35);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES";
DataTable datatableList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataRow row in datatableList.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
MetadataObject table = queryBuilder.MetadataContainer.AddTable(tableName);
sql = "select * from " + tableName + " where 0=1";
Console.WriteLine(sql);
DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataColumn col in columnList.Columns)
{
MetadataField metadataField = table.AddField(col.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(col.DataType);
metadataField.Nullable = col.AllowDBNull;
metadataField.ReadOnly = col.ReadOnly;
if (col.MaxLength != -1)
{
metadataField.Size = col.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in columnList.PrimaryKey)
{
if (col == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
//close and reopen connection to free up space used by temp tables in the .NET provider
db.CloseConnection();
db.OpenConnection();
}
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to load SQLCE 3.5 Schema Tree failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
db.Connection.Close();
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
queryBuilder.MetadataContainer.EndUpdate();
}
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
queryBuilder.InitializeDatabaseSchemaTree();
}
} //end method
private void LoadUltraLiteSchemaTree()
{
queryBuilder.MetadataContainer.BeginUpdate();
MetadataItem schemaRoot = null;
PFDataAccessObjects.PFDatabase db = null;
try
{
queryBuilder.MetadataContainer.Items.Clear();
queryBuilder.InitializeDatabaseSchemaTree();
if (queryBuilder.MetadataContainer.Items.Count == 1)
{
schemaRoot = queryBuilder.MetadataContainer.Items[0];
}
else
{
schemaRoot = queryBuilder.MetadataContainer.AddDatabase("UltraLiteDb");
}
//MetadataObject table = queryBuilder.MetadataContainer.AddTable("TestTable");
//MetadataObject table = schemaRoot.AddTable("TestTable");
//get list of tables and add them to the schema tree
db = new PFDataAccessObjects.PFDatabase(PFDataAccessObjects.DatabasePlatform.SQLAnywhereUltraLite);
db.ConnectionString = this.ConnectionString;
db.OpenConnection();
DataTable datatableList = db.Connection.GetSchema(ULMetaDataCollectionNames.Tables);
foreach (DataRow row in datatableList.Rows)
{
string tableName = row["table_name"].ToString();
MetadataObject table = schemaRoot.AddTable(tableName);
string sql = "select * from " + tableName + " where 0=1";
Console.WriteLine(sql);
DataTable columnList = db.RunQueryDataTable(sql, CommandType.Text);
foreach (DataColumn col in columnList.Columns)
{
MetadataField metadataField = table.AddField(col.ColumnName);
// setup field
metadataField.FieldType = TypeToDbType(col.DataType);
metadataField.Nullable = col.AllowDBNull;
metadataField.ReadOnly = col.ReadOnly;
if (col.MaxLength != -1)
{
metadataField.Size = col.MaxLength;
}
// detect the field is primary key
foreach (DataColumn pkColumn in columnList.PrimaryKey)
{
if (col == pkColumn)
{
metadataField.PrimaryKey = true;
}
}
}
//close and reopen connection to free up space used by temp tables in the UL .NET provider
db.CloseConnection();
db.OpenConnection();
}
//MetadataField field = table.AddField("Fld1");
//field.FieldTypeName = "nvarchar";
//field.Size = 30;
//field = table.AddField("Fld2");
//field.FieldTypeName = "int";
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("Attempt to load UltraLite Schema Tree failed.");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
throw new System.Exception(_msg.ToString());
}
finally
{
if (db != null)
{
db.Connection.Close();
if (db.IsConnected)
{
db.CloseConnection();
}
}
db = null;
queryBuilder.MetadataContainer.EndUpdate();
}
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 1;
queryBuilder.InitializeDatabaseSchemaTree();
}
