c#: Dynamic DAL with changing table structure

I am开发者_开发百科 facing one problem. I am working on a project which has requirement of dynamically populating Grid control to add, update and remove records of specific table.

database is not finalized yet. so what i want is, if i add new column to a table and run the application. that grid should contain newly added column so that i can add new row. update or delete existing row.

I have crated DAL using LINQ to SQL but that is not covering my requirement. I want Get name of tables from database and show them in dropdown list.

after selecting table name. grid should populate with all the columns. so that i can add/update/delete records.

So what exactly is your problem, you want to know how to get the table list from the database? If it is so, and if you're using SQL Server, you could run a select like that :

select name from sysobjects where xtype = 'U'

You can explore this system tables : sysobjects and syscolumns, they store the metadata information on the database.

From what I can gather, your best bet would be taking a different approach than Linq to SQL. You are looking for a UI which directly reflects your domain and can be generated automatically / dynamically. Two methods come to mind:

1. You can leverage MS Dynamic-Data which is an ASP.NET WebForms-based technology. You wire it up directly to a database or Entity Framework model. It generates the grids for all CRUD operations. It detects relationships via foreign keys and can generate the tables with links to one another. It's very customizable. Dynamic Data

2. There is another architectural pattern called "Naked Objects". This requires rich, well-designed domain and aggregate roots. The UI should be 100% generated from this domain model. See the videos on this site to get a great example.

One example I can give you is, recently, our team has been divided - some working on an SOA application which integrates with our main product. Our developer resources are all focused on the task at hand writing WCF services, architecture, database engineering, ASP.NET, etc etc. We needed an internal application which we could use to administer the new SOA application. We could not dedicate another group of guys to build out a new application.

By using Dynamic Data, we had the entire administration app up and running off our EF 4 model in no time. It's doing everything we needed and minimal resources were exerted.

Use ADO.NET Entity Framework for your DAL than using LINQ TO SQL.

Well I solved this problem by getting table info from database schema.

build table to grid on run time which auto generates all columns.

created insert/update/delete query on fly by getting columns name from grid column name.

Happy coding:)