Clearing a Database through OpenAccess

[Roman Katsman]

Hello,

I am evaluating your product for the purpuse of finding the best ORM tool for our company needs.
I have encountered a bit of a pickle, as I cannot find any supported way to empty a database from existing data,
both the Create and Update schema methods do not actually touch the existing data in a database,
so if for instance I need to test different modes of Forward Mapping, all the resulting data remains in the MS SQL Database.

So my exact question is, what do you suggest as a way to clear the database you connect to from all data ?

[Alexander]

Hello Roman Katsman,

It is not necessary to empty the database. You can use the following code to update the schema when the mapping is changed:

db = Database.Get("DatabaseConnection1");
if (!db.GetSchemaHandler().DatabaseExists())
{
    db.GetSchemaHandler().CreateDatabase();
}

string script = db.GetSchemaHandler().CreateUpdateDDLScript(null);

if (!String.IsNullOrEmpty(script))
{
    db.GetSchemaHandler().ExecuteDDLScript(script);
}

If the existing schema does not correspond to the current mapping, an update script will be generated and executed. Hope that helps.

Sincerely yours,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Michael Josiah]

I cant seem to get this example script running. I keep getting the error message that the scriptfile property or sql statement must be set. I have purposefully added a new table to my db and altered an existing field in a table. I ran the example script above and its not generate the required ddl script. Am I missing something here?

[Alexander]

Hi Michael Josiah,

Please make sure that the project containing this code has the UpdateDatabase property set to True.
If this does not solve the problem, please provide us with the exact exception message that you get and its stack trace, so we can try to reproduce it locally.

Greetings,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Michael Josiah]

OK let me get this right. What I want to do is create code whereby when I click a button the code will automatically check the current database against the schema and if there is a difference update the database to match the schema. Now the way I am attempting this is as following

1) I have a class library project called DateModel. It contains all the table classes that I generated via reverse mapping.

2) I have a web application which references the project that has the DataModel. This project as been ORM enabled. I have now basically used the following code to try and update my database from the schema (currently trying to print the sql)

  Dim db = ObjectScopeProvider1.Database

            If db.GetSchemaHandler.DatabaseExists = False Then
                db.GetSchemaHandler.CreateDatabase()
            End If

            Dim script = db.GetSchemaHandler.CreateUpdateDDLScript(Nothing)
            Response.Write(script)

The script is always an empty string, even though I make the effort to delete tables, add new fields and basically make sure the db is very different to the schema. Is there something I am missing here? You mentioned setting the property to UpdateDatabase. Is this the property for the DataModel project (based on my setup). If so it says that it will update my database on compile however I want to update my database at runtime.

 

[Alexander]

Hello Michael Josiah,

Thank you for clarifying. Since you are using reverse mapping, the mapping in the App.config file contains a node (in each class configuration) that denies the modification of the tables. This is done because usually when the schema-first approach is used, such changes are not necessary and probably not wanted. However, you can override this by removing the following line:

<extension key="db-do-not-create-table" value="true" />

from each class node. Setting it to false should have the same effect as well.
Hope that helps.

Greetings,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Michael Josiah]

OK we are nearly there

I have updated the APP.CONFIG as you have suggested and now I see the SQL. However the SQL looks horribly wrong and scares the hell out of me. It seemed to strip all the foreign key relationships, crash out whilst weirdly trying to update the timestamp columns on each table and it completely missed the additional table I added to the database which does not exist on the schema.

What is unbelievable is that it crashed out in the middle of the upgrade leaving my database semi complete. So I lost the foreign key relationships and still have the rouge test table that I added. This is crazy guys. Basically I have a software that I roll out to my customers. Sometimes I make database changes to my dev db and I want to roll the changes out to all the customers in an automated way. The way I see it I can just give them an updated version of the APP which has the latest copy of the db schema. Once the application is running it will notice the difference in the schema and update the database. Currently if I had rolled this out with OpenAccess my customers would loose data, still have legacy tables, have no new tables all this translating to totally screwed up application and angry customers.

Am I wrong in assuming that OpenAccess can pretty much do what this software http://www.perpetuumsoft.com/SQL-Schema-Sync-API.aspx can do? Or I am I totally wrong.

[Alexander]

Hello Michael Josiah,

We found several issues and started fixing them. They are observed in a mixed approach like the one you are trying to achieve (initial reverse mapping and then forward mapping). The essence of the issue is that the information in the App.config and reversemapping.config files is not completely synchronized, thereby the forward mapping process (DDL script) tries to update the database with some default settings that do not correspond to the existing database. Unfortunately the DDL scripts are not transactional and if something goes wrong during the update, the changes cannot be easily rolled back.
The fixes for the problems found should be available in the next build of the product.

Best wishes,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Michael Josiah]

I appreciate this. This will be a very useful feature as it will mean that I can make sure all my customers databases are always in Sync with the latest version. Will you be adding the execution of the ddl script within a transaction? I think this is pretty essential as any errors should be rolled back. Last thing I want is to break my customers database.

Just another quick question. If I want to develop a solution which exposes a webservice to thirdparty applications, how would I go about building the architecture for this. Keeping in mind the thirdparty applications will not have Telerik. Is there a best practise setup for this? I want to use OpenAccess on my end but third party apps should be able to interact with my webservice without the use of OpenAccess.

Thanks

[Alexander]

Hi Michael Josiah,

The problem with the DDL scripts is that not all database backends support executing them in a transaction. However, we will have this in mind for the feature versions.

Regarding your second question, the best approach would be to use proxy objects that wrap the persistent classes and are not OpenAccess dependent. A demo of such implementation using WCF could be found in our Code library. Hope that helps.

Sincerely yours,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Michael Josiah]

Hi

Thanks for your response. So if not all databases support transactions could you do the following instead

1) Take a snapshot of the database and save it as a SQL file

2) Allow the developer to execute this snapshot file

Would this be a viable workaround? This way developers can generate a snapshot first, so if something goes wrong they can execute the snapshot file. This would be a very good addition to OpenAccess. What are your thoughts on this?

Thanks

[Alexander]

Hi Michael Josiah,

Implementing such functionality would require a considerable effort and I am not sure if it would work well for large databases with existing data. I think the best way would be to backup the database manually before performing the DDL operations and if they fail, restore the database from the backup. There should be some third party APIs that provide this feature. I am afraid Telerik OpenAccess ORM will not support such functionality in the near future.

Greetings,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.