CODEWITHSUNDEEP
c#ms-accessif-statement
SiRaZz
SiRaZz

Reputation: 43

Check if two tables exist in database

I have database pavadinimas.mdf, which contains two tables: Vehicle and Repairs. I want to check if both tables exist in database. So, far I managed to check if one table exist, but how to check if both exist, if not create them.

Here is my code:

string tblnm = "Vehicle";
            SqlConnection conn;
            using (conn = new SqlConnection(connection))
            {
                conn.Open();           
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
                       WHERE TABLE_NAME='" + tblnm + "') SELECT 1 ELSE SELECT 0"; ;
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                int x = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
                if (x == 2)
                {
                    MessageBox.Show("Lentelės yra");
                }
                else
                {
                    MessageBox.Show("Lenteliu nėra.Sukuriama");
                }

I also have code which should create table. Here is code:

string table1 = "Repairs";
                 SqlConnection conn;
                 conn = new SqlConnection(connection);
                     conn.Open();
                     string createString = "CREATE TABLE [dbo].['" + table1 + "'](" + "[VIN] [nvarchar](50)," + "[Taisymas] [nvarchar](50)," + "[Kaina] [decimal](18, 2))";
                       SqlCommand sqlCmd = new SqlCommand(createString, conn);
                       sqlCmd.ExecuteNonQuery();
                       conn.Close();

But this code don't create table in my database. Then I call this method, it is saying that table already exist, but when I check tables in database it's nothing, empty...

Upvotes: 0

Views: 606

Answers (3)

Hambone
Hambone

Reputation: 16377

How about using a parameter and looping through the tables?

conn.Open();
var cmd = new System.Data.SqlClient.SqlCommand(
    @"SELECT count (*) FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = @TABLE_NAME",
    conn);
cmd.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar);

List<String> tables = new List<string>() { "Vehicles", "Repairs" };

foreach (string tableName in tables)
{
    cmd.Parameters[0].Value = tableName;
    int x = Convert.ToInt32(cmd.ExecuteScalar());

    if (x == 0)
        CreateTable(tableName, conn);
}
conn.Close();

-- EDIT --

CreateTable method was added above, and the code would look something like this. Caveat -- this is EXTREMELY brute force, but in the absence of other information, is is one way to accomplish the task, as I best understand your issue.

private void CreateTable(String TableName, System.Data.SqlClient.SqlConnection conn)
{
    StringBuilder sql = new StringBuilder(@"create table [");
    sql.Append(TableName);
    sql.AppendLine(@"] (");

    switch (TableName)
    {
        case "Vehicle":
            sql.AppendLine("[VIN] varchar(100),");
            sql.AppendLine("[Manufacturer] varchar(100),");
            sql.AppendLine("[Model] varchar(100),");
            sql.AppendLine("[Year] integer");
            break;
        case "Repair":
            sql.AppendLine("[VIN] varchar(100),");
            sql.AppendLine("[Correction] varchar(100),");
            sql.AppendLine("[Price] decimal");
            break;
    }

    sql.Append(")");

    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(
        sql.ToString, conn);

    try
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("Created Table " + TableName);
    }
    catch (Exception ex)
    {
        MessageBox.Show("Oops, I did it again");
    }
}

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

Wrap it in a for loop

for(int i = 0; i < 2; i++){
  if (i = 0)
  {
    string tblnm = "Vehicle";
  }
  else
  {
    string tblnm = "Repairs";
  }

        SqlConnection conn;
        using (conn = new SqlConnection(connection))
        {
            conn.Open();           
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandText = @"IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 
                   WHERE TABLE_NAME='" + tblnm + "') SELECT 1 ELSE SELECT 0"; ;
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
            int x = Convert.ToInt32(cmd.ExecuteScalar());
            conn.Close();
            if (x == 2)
            {
                MessageBox.Show("Lentelės yra");
            }
            else
            {
                MessageBox.Show("Lenteliu nėra.Sukuriama");
            }
}

Upvotes: 1

Blindy
Blindy

Reputation: 67352

Are you looking for something similar to:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='tbl1') AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='tbl2') SELECT 1 ELSE SELECT 0

Upvotes: 3

Related Questions