CODEWITHSUNDEEP
c#sql-server-2008
Dan C.
Dan C.

Reputation: 559

SQL Command works in SSMS, not through C# SqlCommand

If I run the following in SSMS, the table gets created if it does not exist. However, in my SQLCommand code, it connects and sends the query through with no error but does not create the table if it doesn't exist. Any ideas?

string[] tables = new string[6];
    tables[0] += ("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AD_Authorization]') AND type in (N'U')) " + 
                  "CREATE TABLE [AD_Authorization]([ID] [bigint] IDENTITY(1,1) NOT NULL, " + 
                  "  [AD_Account] [varchar](255) NOT NULL, " + 
                  "  [AD_SID] [varchar](255) NOT NULL, " + 
                  "  [AD_EmailAddress] [varchar](255) NULL, " + 
                  "  [DateImported] [datetime] NOT NULL, " + 
                  "  [Active] [bit] NULL) ON [PRIMARY]");

 for (int i = 0; i <= 5; i++)
                        {
                            using (SqlConnection connection = new SqlConnection(ConnectionString))
                            {
                                using (SqlCommand cmd = new SqlCommand(tables[i], connection))
                                {
                                    connection.Open();
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery();
                                    connection.Close();
                                }
                            }
                        }

Upvotes: 0

Views: 318

Answers (3)

user3224240
user3224240

Reputation: 1

You can't use sys.objects via SQLCommand. Access is specifically blocked for security purposes in SQL Server. I'm not sure if this is a default setting, or something that cannot be overwritten.

Upvotes: 0

Dracorat
Dracorat

Reputation: 1194

Make sure that when you make the connection to the database, that you specify the correct database or that you ChangeDatabase the connection first. Otherwise, your objects will end up in the MASTER database.

Upvotes: 1

user596075
user596075

Reputation:

Try this:

SqlConnection DbConn = new SqlConnection(YourConnectionStringHere);
SqlCommand CreateTable = new SqlCommand();
CreateTable.Connection = DbConn;
CreateTable.CommandText = "IF NOT EXISTS 
    (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[AD_Authorization]') 
    AND type in (N'U')) 
        CREATE TABLE [AD_Authorization]
        (
            [ID] [bigint] IDENTITY(1,1) NOT NULL,
            [AD_Account] [varchar](255) NOT NULL,
            [AD_SID] [varchar](255) NOT NULL,
            [AD_EmailAddress] [varchar](255) NULL,
            [DateImported] [datetime] NOT NULL,[Active] [bit] NULL
        ) 
        ON [PRIMARY]";

try
{
    DbConn.Open();
    CreateTable.ExecuteNonQuery();
}
catch (Exception ex)
{
    DbConn.Dispose();
    // Handle your error
}

Upvotes: 1

Related Questions