How to create a table programically

Question

0.00/5 (No votes)

See more:

Hi,

I need to create a duplicate database and its tables and constraints by reading from an existing database programmatically.
I have created the database programmatically. But I need to create those tables in a particular order. In my existing database there are lots of related tables.So I need to create parent table first, then its child tables.

Suppose 2 tables "Table1" and "Table2" in database DB1.
Suppose Table2 is the parent of Table1. That is Table1 using a foreign key which is the primary key of Table2.
So I need to create Table2 first. But while I am using

Database objDatabase = objServer.Databases["DB2"];

And looping through objDatabase.tables I am getting the order Table1, Table2

This is my problem. Please help me

Thanks in advance.

Posted 22-Feb-12 22:22pm

kutz 2

Updated 22-Feb-12 23:13pm

lukeer

v2

Add a Solution

1 solution

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)

Bojjaiah · Answer 1 · 2012-02-22T22:27:00

Solution 1

hi
try this
before creating table validate like this

string validate="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" + tablename + "_GroupMaster'";
after validate like this to create
string s=   "create table " + tablename + "_GroupMaster(Slno int identity(1,1),GroupId Char(7) null,GroupName varchar(150) null,Status Char(1) null,MasterId notnull)";

Posted 22-Feb-12 22:27pm

Bojjaiah

Updated 22-Feb-12 22:29pm

v3

Comments

kutz 2 23-Feb-12 4:39am

I didn't get u.
Suppose 2 table Table1,Table2 is there under a database DB1.Suppose Table2 is the parent table of Table1.That is Table1 using a foreign key which is the primary key of Table2.So I need to create Table2 first.But while I am using
Database objDatabase = objServer.Databases["DB2"];

And looping through objDatabase.tables I am getting the order Table1,Table2

This is my problem.

So I am asking how can I solve my problem with your solution.

Could you please clarify

Thanks in advance

Bojjaiah 23-Feb-12 5:34am

<pre tag="c#">//validate table2 exist or not
SqlConnection objConnection = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString());
SqlCommand objCommand = new SqlCommand();
SqlDataReader dr;
objConnection.Open();
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Clear();
objCommand.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" + Table2 + "_Test'";
dr = objCommand.ExecuteReader();
if (!dr.HasRows)
{
//here create table2
dr.Close();
objCommand.Connection = objConnection;
objCommand.CommandType = CommandType.Text;
objCommand.Parameters.Clear();
objCommand.CommandText = "create table " + Table2 + "_Test(Slno int identity(1,1),ClassId Char(7) null,ClassName varchar(150) null,Status Char(1) null)";
val = objCommand.ExecuteNonQuery();

}
dr.Close();

//validate table2 exist or not because table2 is the paretn of table1
SqlConnection objConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString());
SqlCommand objCommand1 = new SqlCommand();
SqlDataReader dr1;
objConnection1.Open();
objCommand1.Connection = objConnection;
objCommand1.CommandType = CommandType.Text;
objCommand1.Parameters.Clear();
objCommand1.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" + Table1 + "_Test'";
dr1 = objCommand1.ExecuteReader();
if (!dr1.HasRows)
{
//here create table1
dr1.Close();
objCommand1.Connection = objConnection1;
objCommand1.CommandType = CommandType.Text;
objCommand1.Parameters.Clear();
objCommand1.CommandText = "create table " + Table1 + "_Test(No int identity(1,1),ClassId Char(7) null,ClassName varchar(150) null,Status Char(1) null,Slno int Notnull)";
val = objCommand1.ExecuteNonQuery();

}
dr1.Close();
</pre>