detecting if database there or not CONFUSIONS, please help and improve this code

Question

my aim is to create a db, and check if the db is there or not, at pageload

the confusions are stated beneath "BOLD TEXT" i do not see why its not clear but still as requested i adding more explanations

working fine code

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());

PROBLEM--HELP IN CORRECTING THIS PLEASE-- here the datatable shows 1 alwys even if the database does not exist, why does it do that?

            Response.Write("<br />NOT WORKING(keeps on showing one always!) checking by dataset tables: " + ds2.Tables.Count.ToString());
            DBcreatingAdaptor.Dispose();
            connection.Close();

PROBLEM--HELP IN CORRECTING THIS PLEASE well the error is inaccesible due to protection level? what protection everything here is public..

            //Inaccesible due to protection level. Why??
            //SqlDataReader reader = new SqlDataReader(CheckDB, CommandBehavior.Default);
        }//try

working fine code

        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 + Initial_Catalog + User + Password;
            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
}

Answer 1

This line looks... suspect:

SqlDataReader reader = new SqlDataReader(CheckDB, CommandBehavior.Default);

Should this simply be (since CheckDB is a command):

SqlDataReader reader = CheckDB.ExecuteReader(CommandBehavior.Default);

Note also that a few using statements would help avoid problems when exceptions happen; for example, to read all the data (as a test):

using(SqlDataReader reader = CheckDB.ExecuteReader(CommandBehavior.Default)) {
    do {
        while(reader.Read()) {}
    } while (reader.NextResult());
}

Beyond that, there is such a mix of UI and data-access code that it is hard to see your intent. I would strongly advocate a bit more separation of concerns.