How to dynamically map a datatable to a SQL table

Question

Hopefully I can explain what I am trying to do.

I am writing a system to take data stored in Sharepoint lists and push them into SQL tables. This is being done so the data from the lists can be joined with other data and reported on.

I need the system to be quite flexible so I want to store the mapping between the lists and SQL and then create any of the SQL that is missing.

So I would first have to check if the SQL table I want exists and if not create it. Then check all the columns I expect and create an missing ones then populate the table with the list data.

Getting the list data is no problem to me and it isn't a problem for me to store by configuration information.

My issue is I'm not sure what .NET features to use when talking to the database. I was looking into the entity framework and LINQ but these seem to need fixed tables which I don't have.

I am also looking at using the enterprise libraries (4.1) as I use these for event logging.

Ideally what I want to be able to do is build a datatable and then "compare" it to a SQL table and have the system update it as required.

Does any thing like this exist and what approach would you use.

He meant out of 5 questions you asked, you only accepted 2 answers. So, you only got 2/5 chances to accept an answer in this thread. — Holystream, Feb 10 '11 at 17:34
I have now gone through my old questions and accepted the correct answer — Buzzby, Feb 10 '11 at 17:52

score 2 · Answer 1 · edited May 23 '17 at 12:17

2

These may help get you started :-

Codeplex - SPListSync

Synchronize information with other lists or SQL Server table based on a linked column. This can be helpfull when having list with companies and another list with contacts. The company-information (e.g. Business phone and address) can be copied to the linked contacts.

Exporting Data from SharePoint 2007 Lists to SQL Server via SSIS

SO - Easiest way to extract SharePoint list data to a separate SQL Server table?

Commercial

edited May 23 '17 at 12:17

Community

1
1

answered Feb 10 '11 at 17:32

Ryan

23,871
24
86
132

thanks i'll take a look the SQList product looks interesting for some of my requirments. I still need some help on what the best .NET technology will be to take a datatable or dataset and push this into SQL as some of what i am doing it processing the data before it goes into SQL – Buzzby Feb 10 '11 at 17:50

score 1 · Answer 2 · answered Jul 26 '11 at 16:15

You need to bit Study SQL Server Management Objects, through which you can directly interact with SQL Server very easily. Through this you can create New Table, Stored Procedure etc and also check pre-existance of any object.

Talking to Database like this was never so easy...

How to dynamically map a datatable to a SQL table

2 Answers2