i would like to get the following information of a database please suggests ways

Question

My need is to write a code, which

• creates a db
• creates four tables
• creates primary keys
• creates foreign keys
• and constraints like type int or boolean or string etc

Yes I know w3c shools has the sql codes, but the problem is I first need to detect if these things exists or not one by one.

And this is for me a great problem.

I tried to work with sql exceptions, but it does not provide way to categorize the exceptions --like databasethereexception--tablealreadythereEXCEPTION..

So please provide some coded examples or links for the above purpose,

note: yes I can google, but it is full full full of examples and codes, it gets too confusing, so hoping for straight professional examples please

Also a sample of the type of code i am working with

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Making_DB : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //check or make the db
        MakeDB();
        CheckDB();
    }

    public void CheckDB()
    {
        try
        {
            string Data_source = @"Data Source=A-63A9D4D7E7834\SECOND;";
            string Initial_Catalog = @"Initial Catalog=master;";
            string User = @"User ID=sa;";
            string Password = @"Password=two";

            string full_con = Data_source + Initial_Catalog + User + Password;

            SqlConnection connection = new SqlConnection(full_con);

            connection.Open();

            SqlDataAdapter DBcreatingAdaptor = new SqlDataAdapter();
            DataSet ds2 = new DataSet();
            SqlCommand CheckDB = new SqlCommand("select * from sys.databases where name = 'my_db'", connection);
            DBcreatingAdaptor.SelectCommand = CheckDB;
            DBcreatingAdaptor.Fill(ds2);
            GridView1.DataSource = ds2;
            GridView1.DataBind();   // do not forget this//
            Response.Write("<br />WORKING(shows zero if db not there) checking by gridview rows: " + GridView1.Rows.Count.ToString());
            Response.Write("<br />NOT WORKING(keeps on showing one always!) checking by dataset tables: " + ds2.Tables.Count.ToString());
            DBcreatingAdaptor.Dispose();
            connection.Close();

            //Inaccesible due to protection level. Why??
            //SqlDataReader reader = new SqlDataReader(CheckDB, CommandBehavior.Default);
        }//try
        catch (Exception e)
        {
            Response.Write("   checking::    " +  e.Message);
        }//catch
    }//check db

    public void MakeDB()
    {
        try
        {
            string Data_source = @"Data Source=A-63A9D4D7E7834\SECOND;";
            //string Initial_Catalog = @"Initial Catalog=replicate;";
            string User = @"User ID=sa;";
            string Password = @"Password=two";

            string full_con = Data_source + User + Password;

            SqlConnection connection = new SqlConnection(full_con);

            connection.Open();

            //SqlCommand numberofrecords = new SqlCommand("SELECT COUNT(*) FROM dbo.Table_1", connection);
            SqlCommand CreateDB = new SqlCommand("CREATE DATABASE my_db", connection);

            //DataSet ds2 = new DataSet();

            SqlDataAdapter DBcreatingAdaptor = new SqlDataAdapter();
            DBcreatingAdaptor.SelectCommand = CreateDB;
            DBcreatingAdaptor.SelectCommand.ExecuteNonQuery();

            //check for existance
            //select * from sys.databases where name = 'my_db'

            DataSet ds2 = new DataSet();
            SqlCommand CheckDB = new SqlCommand(" select * from sys.databases where name = 'my_db'", connection);
            DBcreatingAdaptor.SelectCommand = CheckDB;
            //DBcreatingAdaptor.SelectCommand.ExecuteReader();
            DBcreatingAdaptor.Fill(ds2);
            GridView1.DataSource = ds2;

            //if not make it
        }//try
        catch (Exception e)
        {
            Response.Write("<br /> createing db error:  " + e.Message);
        }//catch
    }//make db
}

Thank you so much answerers, this forum has helped me a lot, I would start a bounty for this question, but I have lot more to come :-) which would deserve a bounty

Answer 1

I am very sure of the cross-over between this and the other question - however, it sounds to me like you are approaching this from the wrong end.

I would write this as a TSQL script, making use of EXEC to avoid problems with the checker, for example:

USE [master]
if not exists ( ... database ...)
begin
    print 'creating database...'   
    exec ('...create database...')
end
GO
USE [database]
if not exists( ... check schema tables for 1st thing ... )
begin
    print 'Creating 1st thing...'
    exec ('...create 1st thing...')
end
if not exists( ... check schema tables for 2nd thing ... )
begin
    print 'Creating 2nd thing...'
    exec ('...create 2nd thing...')
end
if not exists( ... check schema tables for 3rd thing ... )
begin
    print 'Creating 3rd thing...'
    exec ('...create 3rd thing...')
end

Then you can gradually extend this script as your schema changes, and all you need to do is re-run the script for it to update the database.

Answer 2

May be it is not directly you want, but for easy database creation & population from .Net review usage of wide migrate tool. My preference (Migrator.NET) but full review can be found there: http://flux88.com/blog/net-database-migration-tool-roundup/

Answer 3

As I've already mentioned in my comment - I would NEVER write out directly to the Response stream from a function like this! Pass back a string with an error message or something - but do NOT write out to the stream or screen directly.

You should use the best practice of wrapping SqlConnection and SqlCommand into using(...){.....} blocks to make sure they get properly disposed. Also, populating a gridview from within this code is really bad - you're mixing database access (backend) code and UI frontend code - really really bad choice. Why can't you just pass back the data table and then bind it in the UI front end code to the grid??

public DataTable CheckDB()
{
    DataTable result = new DataTable();

    try
    {
        string connectionString = 
          string.Format("server={0};database={1};user id={2};pwd={3}"
                        "A-63A9D4D7E7834\SECOND", "master", "sa", "two"); 

        string checkQuery = "SELECT * FROM sys.databases WHERE name = 'my_db'";

        using(SqlConnection _con = new SqlConnection(connectionString))
        using(SqlCommand _cmd = new SqlCommand(checkQuery, _con))
        {
            SqlDataAdapter DBcreatingAdaptor = new SqlDataAdapter(_cmd);
            DBcreatingAdaptor.Fill(_result);
        }
    }//try
    catch (SqlException e)
    {
         // you can inspect the SqlException.Errors collection and 
         // get **VERY** detailed description of what went wrong,
         // including explicit SQL Server error codes which are 
         // unique to each error
    }//catch

    return result;
}//check db

Also - you're doing the MakeDB() method way too complicated - why a table adapter?? All you need is a SqlCommand to execute your SQL command - you already have a method that checks that a database exists.

public void MakeDB()
{
    try
    {
        string connectionString = 
          string.Format("server={0};database={1};user id={2};pwd={3}"
                        "A-63A9D4D7E7834\SECOND", "master", "sa", "two"); 

        string createDBQuery = "CREATE DATABASE my_db";

        using(SqlConnection _con = new SqlConnection(connectionString))
        using(SqlCommand _cmd = new SqlCommand(createDBQuery, _con))
        { 
            _con.Open();
            _cmd.ExecuteNonQuery();
            _con.Close();
        }
    }//try
    catch (SqlException e)
    {
       // check the detailed errors 
       // error.Number = 1801 : "database already exists" (choose another name)
       // error.Number = 102: invalid syntax (probably invalid db name)
       foreach (SqlError error in e.Errors)
       {
          string msg = string.Format("{0}/{1}: {2}", error.Number, error.Class, error.Message);
       }
    }//catch
}//make db