Best Architecture To Deal With Dynamic Data Stores RRS feed

  • Question

  • User-817646753 posted

    Hi there

    I am developing an ASP.NET/MVC web application using SQL Server as the database. The requirement is that users need to be able to save their own custom data. So the user would need a page to setup their user defined table structure (i.e. I want column A to be a Date, column B to be currency, etc.) Afterwards, they can then go in and perform CRUD operations on their own custom data store.

    My proposed solution to persist this data is to create the tables dynamically in the SQL Server database using SQL (e.g. CREATE TABLE statements etc.) But it rather feels like I am somewhat reinventing the wheel, as this functionality already exists in SQL Management Studio. Is there an API or existing class library that I can utilise to save development time? 

    I am happy to consider any advice about this, as the last thing that I want to do is spend lots of time coding a solution that is already available, or if there is an easier way to tackle this problem.

    Thanks in advance for any help.

    Monday, August 3, 2015 11:23 PM

All replies

  • User-821857111 posted

    The "API" is called DDL (Data Definition Language) which is the CREATE TABLE etc statements that you have already mentioned. SSMS is just a visual wrapper over these commands. If you want to programmatically alter a database schema, that is the way to do it.

    Tuesday, August 4, 2015 2:07 AM
  • User-817646753 posted

    Hi, and thanks for the reply. This is the way forward then. I just wondered whether there was an existing wrapper API out there.

    Thursday, August 6, 2015 12:07 AM