Map structures/schema of all database tables from C#

Question

0.00/5 (No votes)

See more:

I have the following query which i am passing from front end for ex:-

SQL

CREATE TABLE [" + TableName + "]"
                                 + "("
                                 + "[Fname] [varchar](100) NOT NULL,"
                                 + "[Lname] [varchar](300) NOT NULL,"
                                 + "[CODE] [varchar](6) NULL,"
                                 + "[TYPE] [varchar](4) NULL,"
                                 + "[AMT] [float] NULL"
                                 + ") ON [PRIMARY]";

what i want to do is if the table doesnt exists in database i want to create it.but if it exists then i want to compare that tables structure with the query structure and if there is some difference i want to update the DB table.But without loosing the data of DB table.And i want to do it for all the tables of a particular database.
Any suggestions from where to start?How to achieve this task?Any Ideas?
Help Me..

Posted 8-Jun-14 21:43pm

pwavell

Updated 8-Jun-14 22:17pm

Nelek

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Answer 1 · 2014-06-08T21:54:00

Solution 1

Hello,

You can have a look at ActiveRecord Pattern implementations[^] described in this article.

Regards,

Posted 8-Jun-14 21:54pm

Prasad Khandekar

Comments

pwavell 9-Jun-14 4:49am

Any other way to achieve this?

cigwork · Answer 2 · 2014-06-09T08:54:00

You don't say what the DB is so I've assumed that it's SQL Server. If that is the case then the various information_schema views are your friend; that or you can use sysobjects if you're using an ancient version. They'll tell you pretty much everything you might want to know about a DB.

If you're using a.n.other DB then there's likely to be a similar feature.

C#

string connectionPath = @"Database=xxx;Server=yyy;User ID=zzz;Password=password;Network Library=dbmssocn;Connect Timeout=10;";

// Get the table's columns.
string getTableDef  = @"select * from information_schema.columns where table_name = 'myTable'";

/*
 Gets the following info. plus some other odds and ends.
 COLUMN_NAME, IS_NULLABLE, DATA_TYPE,
 CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
 NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX,
 NUMERIC_SCALE, DATETIME_PRECISION
*/

DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionPath))
{
  SqlDataAdapter da = new SqlDataAdapter(getTableDef , connection);
  da.Fill(ds);
  ds.WriteXml(xmlFilePath, XmlWriteMode.WriteSchema);

  DataView dv = new DataView(ds.Tables[0]);

  if (dv.count == 0) {
   // Create the table
  }
  else {
   // Compare the definition of each column held in the dataview with your
   // reference definition for the table and use:
   //         ALTER TABLE {my table name} ALTER COLUMN {my column name} or
   //         ALTER TABLE {my table name} ADD {my new column} or
   //         ALTER TABLE {my table name} DROP COLUMN {column to lose} as/if necessary.
  }

}