void RunExtraUpdaters(DBTable[] tables, ConnectionProviderSql store, bool dontCreateIfFirstTableNotExist)
{
foreach (var schemaUpdater in schemaUpdaters)
{
schemaUpdater.Update(store, new DataStoreUpdateSchemaEventArgs(dontCreateIfFirstTableNotExist, tables));
}
}
private void UpdateColumnSize(IEnumerable <DBTable> tables, ConnectionProviderSql sqlDataStore)
{
foreach (var table in tables)
{
DBTable actualTable = null;
foreach (var column in table.Columns.Where(col => col.ColumnType == DBColumnType.String))
{
if (actualTable == null)
{
actualTable = new DBTable(table.Name);
sqlDataStore.GetTableSchema(actualTable, false, false);
}
DBColumn dbColumn = column;
var actualColumn = actualTable.Columns.Find(col => string.Compare(col.Name, sqlDataStore.ComposeSafeColumnName(dbColumn.Name), true) == 0);
if (NeedsAltering(column, actualColumn))
{
if ((actualColumn.Size < column.Size) || (column.Size == DevExpress.Xpo.SizeAttribute.Unlimited))
{
var sql = GetSql(table, sqlDataStore, column);
System.Diagnostics.Trace.WriteLineIf(new System.Diagnostics.TraceSwitch("XPO", "").TraceInfo, sql);
sqlDataStore.ExecSql(new Query(sql));
}
}
}
}
}
static void Main()
{
ConnectionProviderSql provider =
(ConnectionProviderSql)XpoDefault.GetConnectionProvider(SQLiteConnectionProvider.GetConnectionString(@"CustomCommand.sqlite"),
AutoCreateOption.DatabaseAndSchema);
provider.RegisterCustomFunctionOperator(new GetMonthFunction());
XPDictionary dict = new ReflectionDictionary();
dict.CustomFunctionOperators.Add(new GetMonthFunction());
XpoDefault.DataLayer = new SimpleDataLayer(dict, provider);
CreateData();
using (Session session = new Session()) {
XPView view = new XPView(session, typeof(Order));
view.AddProperty("Month", "custom('GetMonth', OrderDate)");
foreach (ViewRecord prop in view)
{
Console.WriteLine(prop["Month"]);
}
var list = from o in new XPQuery <Order>(session)
where o.OrderName == "Chai"
select new { Month = GetMonthFunction.GetMonth(o.OrderDate) };
foreach (var item in list)
{
Console.WriteLine(item.Month);
}
}
Console.WriteLine("done\npress any key to exit ..");
Console.ReadKey();
}
string GetSql(DBTable table, ConnectionProviderSql sqlDataStore, DBColumn column) {
return string.Format(CultureInfo.InvariantCulture,
"alter table {0} alter column {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column));
}
private void UpdateColumnSize(IEnumerable <DBTable> tables, ConnectionProviderSql sqlDataStore)
{
foreach (var table in tables.Where(table => !HashSet.Contains(table.Name)))
{
HashSet.Add(table.Name);
DBTable actualTable = null;
foreach (var column in table.Columns.Where(col => col.ColumnType == DBColumnType.String))
{
if (actualTable == null)
{
actualTable = new DBTable(table.Name);
sqlDataStore.GetTableSchema(actualTable, false, false);
}
DBColumn dbColumn = column;
var actualColumn = actualTable.Columns.Find(col => String.Compare(col.Name, sqlDataStore.ComposeSafeColumnName(dbColumn.Name), StringComparison.OrdinalIgnoreCase) == 0);
if (NeedsAltering(column, actualColumn))
{
if ((actualColumn.Size < column.Size) || (column.Size == SizeAttribute.Unlimited))
{
var sql = GetSql(table, sqlDataStore, column);
Trace.WriteLineIf(new TraceSwitch("XPO", "").TraceInfo, sql);
sqlDataStore.ExecSql(new Query(sql));
}
}
}
}
}
public static void CreateColumn(this ConnectionProviderSql connectionProviderSql, XPMemberInfo xpMemberInfo, bool throwUnableToCreateDBObjectException = false)
{
var dbColumnType = GetDbColumnType(xpMemberInfo);
var column = new DBColumn(xpMemberInfo.Name, false, null, xpMemberInfo.MappingFieldSize, dbColumnType);
CallSchemaUpdateMethod(connectionProviderSql, sql => CreateColumnCore(xpMemberInfo, throwUnableToCreateDBObjectException, sql, column), throwUnableToCreateDBObjectException);
connectionProviderSql.CreateForeignKey(xpMemberInfo, throwUnableToCreateDBObjectException);
}
public static void CreateForeignKey(this ConnectionProviderSql connectionProviderSql, XPMemberInfo xpMemberInfo,
bool throwUnableToCreateDBObjectException = false)
{
if (xpMemberInfo.HasAttribute(typeof(AssociationAttribute)))
{
CallSchemaUpdateMethod(connectionProviderSql, CreateForeighKey(xpMemberInfo), throwUnableToCreateDBObjectException);
}
}
void BindProducts()
{
SimpleDataLayer dataLayer = (SimpleDataLayer)session1.DataLayer;
ConnectionProviderSql provider = (ConnectionProviderSql)dataLayer.ConnectionProvider;
DBTable[] tables = provider.GetStorageTables("Products");
gridControl2.DataSource = new XPCollection(session1, Program.AddClass(session1.Dictionary, tables[0]));
}
string GetSql(DBTable table, ConnectionProviderSql sqlDataStore, DBColumn column) {
return string.Format(CultureInfo.InvariantCulture,
"alter table {0} {3} {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column),
(sqlDataStore is BaseOracleConnectionProvider || sqlDataStore is MySqlConnectionProvider) ? "modify" : "alter column");
}
string GetSql(DBTable table, ConnectionProviderSql sqlDataStore, DBColumn column)
{
return(string.Format(CultureInfo.InvariantCulture,
"alter table {0} {3} {1} {2}",
sqlDataStore.FormatTableSafe(table),
sqlDataStore.FormatColumnSafe(column.Name),
sqlDataStore.GetSqlCreateColumnFullAttributes(table, column, true),
(sqlDataStore is BaseOracleConnectionProvider || sqlDataStore is MySqlConnectionProvider) ? "modify" : "alter column"));
}
static void InitializeDataLayer(IDbConnection connection)
{
ConnectionProviderSql prov = (ConnectionProviderSql)XpoDefault.GetConnectionProvider(connection, AutoCreateOption.None);
XPDictionary dict = new ReflectionDictionary();
DBTable[] tables = prov.GetStorageTables("Categories");
AddClass(dict, tables[0]);
XpoDefault.DataLayer = new SimpleDataLayer(dict, prov);
}
static void CreateColumnCore(XPMemberInfo xpMemberInfo, bool throwUnableToCreateDBObjectException, ConnectionProviderSql sql,
DBColumn column) {
try {
sql.CreateColumn(xpMemberInfo.Owner.Table, column);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
throw;
}
}
static void CallSchemaUpdateMethod(ConnectionProviderSql connectionProviderSql, Action<ConnectionProviderSql> action, bool throwUnableToCreateDBObjectException ) {
var autoCreateOption = connectionProviderSql.AutoCreateOption;
connectionProviderSql.SetFieldValue("_AutoCreateOption", AutoCreateOption.SchemaOnly);
try {
action.Invoke(connectionProviderSql);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
throw;
}
finally {
connectionProviderSql.SetFieldValue("_AutoCreateOption", autoCreateOption);
}
}
public void Update(ConnectionProviderSql connectionProviderSql, DataStoreUpdateSchemaEventArgs dataStoreUpdateSchemaEventArgs) {
if (connectionProviderSql is AccessConnectionProvider)
return;
lock (connectionProviderSql.SyncRoot) {
if (!connectionProviderSql.CanCreateSchema)
return;
try {
if (dataStoreUpdateSchemaEventArgs.UpdateSchemaResult == UpdateSchemaResult.SchemaExists)
UpdateColumnSize(dataStoreUpdateSchemaEventArgs.Tables, connectionProviderSql);
} catch (System.Exception e) {
System.Diagnostics.Trace.TraceError(e.ToString());
}
}
}
static void CallSchemaUpdateMethod(ConnectionProviderSql connectionProviderSql, Action<ConnectionProviderSql> action, bool throwUnableToCreateDBObjectException ) {
var autoCreateOption = connectionProviderSql.AutoCreateOption;
var fieldInfo = typeof (DataStoreBase).GetField("_AutoCreateOption", BindingFlags.NonPublic | BindingFlags.Instance);
if (fieldInfo == null) throw new NullReferenceException("fieldInfo");
fieldInfo.SetValue(connectionProviderSql, AutoCreateOption.SchemaOnly);
try {
action.Invoke(connectionProviderSql);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
throw;
}
finally {
fieldInfo.SetValue(connectionProviderSql, autoCreateOption);
}
}
static void CallSchemaUpdateMethod(ConnectionProviderSql connectionProviderSql, Action <ConnectionProviderSql> action, bool throwUnableToCreateDBObjectException)
{
var autoCreateOption = connectionProviderSql.AutoCreateOption;
connectionProviderSql.SetFieldValue("_AutoCreateOption", AutoCreateOption.SchemaOnly);
try {
action.Invoke(connectionProviderSql);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
{
throw;
}
}
finally {
connectionProviderSql.SetFieldValue("_AutoCreateOption", autoCreateOption);
}
}
private void UpdateColumnSize(IEnumerable<DBTable> tables, ConnectionProviderSql sqlDataStore) {
foreach (var table in tables) {
DBTable actualTable = null;
foreach (var column in table.Columns.Where(col => col.ColumnType == DBColumnType.String)) {
if (actualTable == null) {
actualTable = new DBTable(table.Name);
sqlDataStore.GetTableSchema(actualTable, false, false);
}
DBColumn dbColumn = column;
var actualColumn = actualTable.Columns.Find(col => string.Compare(col.Name, sqlDataStore.ComposeSafeColumnName(dbColumn.Name), true) == 0);
if (NeedsAltering(column, actualColumn)) {
if ((actualColumn.Size < column.Size) || (column.Size == DevExpress.Xpo.SizeAttribute.Unlimited)) {
var sql = GetSql(table, sqlDataStore, column);
System.Diagnostics.Trace.WriteLineIf(new System.Diagnostics.TraceSwitch("XPO", "").TraceInfo, sql);
sqlDataStore.ExecSql(new Query(sql));
}
}
}
}
}
public void Update(ConnectionProviderSql connectionProviderSql, DataStoreUpdateSchemaEventArgs dataStoreUpdateSchemaEventArgs)
{
if (connectionProviderSql == null || Disabled)
{
return;
}
using (((IDisposable)((AsyncLockHelper)connectionProviderSql.GetPropertyValue("LockHelper")).CallMethod("Lock"))) {
if (!connectionProviderSql.CanCreateSchema)
{
return;
}
try {
if (dataStoreUpdateSchemaEventArgs.UpdateSchemaResult == UpdateSchemaResult.SchemaExists)
{
UpdateColumnSize(dataStoreUpdateSchemaEventArgs.Tables, connectionProviderSql);
}
} catch (Exception e) {
Trace.TraceError(e.ToString());
}
}
}
public void Update(ConnectionProviderSql connectionProviderSql, DataStoreUpdateSchemaEventArgs dataStoreUpdateSchemaEventArgs)
{
if (connectionProviderSql == null || connectionProviderSql is AccessConnectionProvider || Disabled)
{
return;
}
lock (connectionProviderSql.SyncRoot) {
if (!connectionProviderSql.CanCreateSchema)
{
return;
}
try {
if (dataStoreUpdateSchemaEventArgs.UpdateSchemaResult == UpdateSchemaResult.SchemaExists)
{
UpdateColumnSize(dataStoreUpdateSchemaEventArgs.Tables, connectionProviderSql);
}
} catch (System.Exception e) {
System.Diagnostics.Trace.TraceError(e.ToString());
}
}
}
void RunExtraUpdaters(DBTable[] tables, ConnectionProviderSql store, bool dontCreateIfFirstTableNotExist) {
foreach (var schemaUpdater in schemaUpdaters) {
schemaUpdater.Update(store, new DataStoreUpdateSchemaEventArgs(dontCreateIfFirstTableNotExist, tables));
}
}
static void CreateColumnCore(XPMemberInfo xpMemberInfo, bool throwUnableToCreateDBObjectException, ConnectionProviderSql sql,
DBColumn column)
{
try {
sql.CreateColumn(xpMemberInfo.Owner.Table, column);
}
catch (UnableToCreateDBObjectException) {
if (throwUnableToCreateDBObjectException)
{
throw;
}
}
}
