Adding an sql table through VS2005

Hi,

Currently I am able to check for and add a column to an SQL database using this:

Dim connectionstring As String = My.Settings.ConnectionString

Dim eSQL As String = ""

Using connection As New System.Data.SqlClient.SqlConnection(connectionstring)

Dim cmd As New System.Data.SqlClient.SqlCommand(eSQL, connection)

connection.Open()

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND COLUMN_NAME = 'Company' ) " _

& "BEGIN " _

& "ALTER TABLE [Client] " _

& "ADD Company int " _

& "END "

cmd.CommandText = eSQL

cmd.ExecuteNonQuery()

End Using

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

hi,

GraemeP wrote:

Hi,

I would now like to add a completely new table with two columns to the same database. What syntax would I use?

eSQL = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Client' AND TABLE_TYPE = 'BASE TABLE' ) " _

& "BEGIN " _

& "CREATE TABLE [Client] (" _

& "Company int NOT NULL PRIMARY KEY," _

& "Name varchar(100) NOT NULL DEFAULT 'new company');" _

& "END;"

regards

|||Perfect - thanks!|||

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

|||

hi,

GraemeP wrote:

Hi,

I'm getting on fine adding tables and fields programmatically to an SQL database, however I now want to add a view in the same way.

Is this possible, if so would appreciate some sample code.

the very same pattern is not possible for views, as the CREATE VIE statement must be the first in it's own batch... typically you'll overcome this problem dropping the view, if existing like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE VIEW dbo.v1 AS

SELECT 1 AS [ColX];

GO

IF OBJECT_ID('dbo.v1') IS NOT NULL BEGIN

PRINT 'drop';

DROP VIEW dbo.v1;

END;

GO

CREATE VIEW dbo.v1 AS

SELECT 1 AS [ColX];

GO

DROP VIEW dbo.v1;

but this method has gotchas.. all permissions eventually set are gone when the objec is dropped..

regards