SiRaZz SiRaZz - 14 days ago 4
C# Question

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...

Answer

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");
    }
}