[RESOLVED] Creating a table (ifnotexists) at startup of my vb.net app [vb.net]

Hi,

I am trying to find out the way on how to create a table at the startup when running my app ...

This should only happen if the table does not yet exists.

I have looked into tsql and stored procedures.

I understand that I will have to add a file to my project in the solution explorer ...

I understand that it will look similar to :

CREATE TABLE tblName ( [UID] int, [Name] varchar(64), [Address] varchar(255) )


What i dont understand is ...

1) Which item should i add to my project ... ive seen various things been used but am not sure whats correct? .sql? .txt? ...

2) How would I find out whether the table exists or not?

3) How do I run this file?


I hope someone can enlighten me ...

Many thanks in advance,

Rob

I am using VS2005.

So do you have a database already? A table is not a file, a table is part of a database so in order to be able to create a table you need to already have a database. This could be a Microsoft Access database, an SQL Server database, an Oracle database etc etc

Hi Chris,

I have got a db allready ... in my case a SQL SERVER 2005 db.

I need to programmatically create a table when loading my bespoke software within that existing database if this table does not yet exist.

Right ok then, well the first thing you need is an SQLConnection object then.
You use this like so:

vb Code:

'Use this at the very top of your form, above all other code
Imports System.Data.SqlClient
 
'Declare the necessary variables
Dim conn As SqlConnection
    Dim mycommand As SqlCommand
    Dim connstring As String
 
'Here is an example sub that you could call, or you could place the code in an event handler
    Private Sub CreateMyTable()
        connstring = "Data Source=SERVERNAME_HERE;Initial Catalog=DATABASE_HERE;User Id=USERNAME_HERE;Password=PASSWORD_HERE;"
        conn = New SqlConnection(connstring)
        mycommand = New SqlCommand("CREATE TABLE tblName ( [UID] int, [Name] varchar(64), [Address] varchar(255)")
        conn.Open()
        mycommand.ExecuteNonQuery()
        conn.Close()
 
    End Sub

Obviously you need to replace bits in the ConnString variable so that it has your Server name, database name etc in.
Now, all that code above is doing is creating a new table, its not checking to see if the table already exists. To do that I would use a DataAdapter, do a bit of searching for SQLDataAdapter and see what you can come up with. Post back ere if your still really stuck.

If you are having a problem with the Connection String - look here: http://www.connectionstrings.com/

Just found this as well - apparently you can use IF Not Exist statements in SQL commands, so you wouldnt need to use a data adapter or anything. You would just change the SQLCommand Text.
Here is the example syntax I found:

Code:

---

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

---

Thanks Chris ...

I did know about the data adapter ... but thanks for showing that up again ...

Thats what I have been making use of during the past months ...

Thanks for showing up that does not exists rule ... didnt know about that.

The reason why I would like to avoid to having these create statements within my code is to keep the program seperate from the data ... as I have read that this is the best practises way to do things ... so hence im giving it a shot ... but have had no success so far.

So preferably i would imagine I have a .sql file within my app added to my solution and have the pure sql create table statement there ...

I am just not sure how anyone else has gone about this?

Thanks again Chris for your very quick/informative replies.

Appreciate it allot.

I'm not too sure about the .sql files, I've never tried to use something like that.
I know what you mean about wanting to keep your program seperate from the SQL commands/data but maybe running a Stored Procedure from the SQL database would be acceptable?
This way you would write your SQL statements (CREATE TABLE etc etc) in a Stored Procedure in the SQL database itself and then just call it from your VB program if necessary.
I dont know if that helps?

I think what I will do is the following:

I will add an item to my current project - in the solution explorer

The item I will add will be a text file (.txt) but I will rename it to .sql just for personnal preference.

I will make use of sqlcommands as you have shown in a previous post but will replace the statement with the read output from my .sql file having used a streamreader of some sort ...

I will add this code in my createtable method which I have placed within my own sql class.

Then all i will do is create a new instance of my sql class and load the createtable method at the mainform load event.

I think that could be just the way ... logically that makes sense to me ...


I have looked into sprocs but for this scenario I will have to create the table from the app ... rather then from the db - so sprocs in this case may not be very usefull to me.

What do you think Chris or anyone else?

Thanks.

If it was me, I would just do it with a Stored Procedure in the database or just through the VB.NET program directly. However I dont know much about best practices when working with SQL so it would be good if someone else could confirm what the preferred method would be in this case.

Quote:

---

Originally Posted by chris128

If it was me, I would just do it with a Stored Procedure in the database or just through the VB.NET program directly. However I dont know much about best practices when working with SQL so it would be good if someone else could confirm what the preferred method would be in this case.

---

When you say 'through the vb.net program directly' do you mean using the sqlcommand object?


So far I have got my .sql file which holds the t-sql statement to be used within my app.

which looks like this:

Code:

---

CREATE TABLE tblAA
(
[AAID] float not null,
[Type] varchar(3)null,
[Date] datetime null ,
[Invoice] varchar(25)null,
[Unallocated] varchar(255)not null,
[Supplier] varchar(10)null,
[BookingRef] float not null,
[AuditInv] varchar(255)null,
[AuditPay] varchar(255)not null,
[Update] int not null
)

---

I am working on the createtable method.

Now im looking to read this file and insert the text within a dimensioned sqlcommand string variable (strSQLCommand).

thx

Well if you really want to do it that way, then you will need to use something like this to read the data in from the text file:

Code:

---

Dim sqlcommandtext As String = My.Computer.FileSystem.ReadAllText("path_to_.sql_file")

---

Then you can use that variable in your VB.NET code like so (assuming you are using the code I posted previously)

Code:

---

mycommand = New SqlCommand(sqlcommandtext)
conn.Open()
mycommand.ExecuteNonQuery()
conn.Close()

---

However, doing it this way you still need to determine whether or not the table exists. If you dont want to use the IF NOT EXIST method in the SQL statement itself then you will need to use VB.NET code to determine this. This can be done either with a DataAdapter or using various other methods.

I tried using the IF NOT EXISTS in the sql statement but it does not seem to like it ...

this is what i tried:

Code:

---

CREATE TABLE [IF NOT EXISTS] tblAA
(
[AAID] float not null,
[Type] varchar(3)null,
[Date] datetime null ,
[Invoice] varchar(25)null,
[Unallocated] varchar(255)not null,
[Supplier] varchar(10)null,
[BookingRef] float not null,
[AuditInv] varchar(255)null,
[AuditPay] varchar(255)not null,
[Update] int not null
)

---

it gives me the following error: Line 1: Incorrect syntax near 'tblAA'.


I can't see whats wrong with that ...

Take out the square brackets so its just: CREATE TABLE IF NOT EXISTS tblAA

Quote:

---

Originally Posted by chris128

Take out the square brackets so its just: CREATE TABLE IF NOT EXISTS tblAA

---

Unfortunately, this hasnt solved the problem.


I get the following errors:

Incorrect syntax near the keyword 'IF'.

Line 1: Incorrect syntax near 'tblAA'.


This is what i used:

Code:

---

CREATE TABLE IF NOT EXISTS tblAA
(
[AAID] float not null,
[Type] varchar(3)null,
[Date] datetime null ,
[Invoice] varchar(25)null,
[Unallocated] varchar(255)not null,
[Supplier] varchar(10)null,
[BookingRef] float not null,
[AuditInv] varchar(255)null,
[AuditPay] varchar(255)not null,
[Update] int not null
)

---

That (at least as far as I know) (CREATE TABLE IF NOT EXISTS tblAA) is not a valid SQL create table statement.

You could do something like this:

sql Code:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAA]
GO
Craete Table tblAA (
 Fieldlist here
)

Except, I'd change it to a If NOT EXISTS..... create table.... and remove the DROP TABLE.


-tg

-tg

Yes. I didn't change to the not exsist condition

Quote:

---

Originally Posted by GaryMazzone

That (at least as far as I know) (CREATE TABLE IF NOT EXISTS tblAA) is not a valid SQL create table statement.

You could do something like this:

sql Code:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAA]
GO
Craete Table tblAA (
 Fieldlist here
)

---

Oh right, I found a few examples of people using it so I assumed it worked...

Heres an example from http://support.sas.com/techsup/technote/create402.sql

Quote:

---

CREATE TABLE IF NOT EXISTS allocation (
allocation_id int NOT NULL,
currency varchar(255) binary default NULL,
source_actg_period int NOT NULL,
source_frequency varchar(255) binary default NULL,
source_analysis varchar(255) binary default NULL,
source_id int,
offset longblob NOT NULL,
PRIMARY KEY (allocation_id)
) TYPE=InnoDB;

---

Quote:

---

Originally Posted by techgnome

Except, I'd change it to a If NOT EXISTS..... create table.... and remove the DROP TABLE.


-tg

---

And change

Code:

---

Craete TABLE tblAA

---

:) :P

this is what i understood from the above messages ...

to come to the following result:

Code:

---

BEGIN
IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'COMPONENT_EVENT') and type = 'U')

CREATE TABLE tblAA (
         
[AAID] float not null,
[Type] varchar(3)null,
[Date] datetime null ,
[Invoice] varchar(25)null,
[Unallocated] varchar(255)not null,
[Supplier] varchar(10)null,
[BookingRef] float not null,
[AuditInv] varchar(255)null,
[AuditPay] varchar(255)not null,
[Update] int not null
)
     
END
GO

---

I will try this out ...

thx chris & gary & tech for your expertise ...

Quote:

---

Originally Posted by chris128

Oh right, I found a few examples of people using it so I assumed it worked...

Heres an example from http://support.sas.com/techsup/technote/create402.sql

---

I did find similar examples too ... where it is used


CREATE TABLE IF NOT EXISTS tbl_name ...

but for some reason or another it just wont like it ...

Aha! This is what you need to use instead:

Code:

---

IF NOT EXISTS (select * from dbo.sysobjects where name = 'testingtbl')
BEGIN
Create Table testingtbl (
ExampleField1 varchar(5),
ExampleField2 varchar(10)
)
END

---

Obviously you need to replace 'testingtbl' with the name of the table you want to create etc


Here is an example of using this in VB.NET assuming that the file C:\test.sql has the code above written in it and assuming you have imported system.data.sqlclient:

Code:

---

Dim myconn As SqlConnection
Dim mycommandtext As String = My.Computer.FileSystem.ReadAllText("C:\test.sql")
Dim myconnstring As String = "Data Source=YOUR_SERVERNAME\YOUR_INSTANCE;Database=YOUR_DATABASENAME;User Id=XXX;Password=XXX;"
Dim mycommand As SqlCommand

myconn = New SqlConnection(myconnstring)

mycommand = New SqlCommand(mycommandtext, myconn)
myconn.Open()
mycommand.ExecuteNonQuery()
myconn.Close()

---

GREAT GREAT GREAT!


I will update this thread later on once I have cleaned up everything within my code.

I am also now researching into reading embedded files as this sql file will be embedded within the application.


Rep has been left to all.

Thanks allot for your help.

Rob

No problem :) I learnt a thing or two from trying to find out how to do this :)

Quote:

---

Originally Posted by Devdude

I did find similar examples too ... where it is used


CREATE TABLE IF NOT EXISTS tbl_name ...

but for some reason or another it just wont like it ...

---

It's a MySQL thing..... a few other DBMSs may implement it as well.,... but SQL Server doesn't. That link that was posted earlier was a creation script for a InnoDB database, not SQL Server. Not all SQL is created equal.

-tg

Quote:

---

Originally Posted by chris128

No problem :) I learnt a thing or two from trying to find out how to do this :)

---

Thats great!

Have you ever made use of storing embedded files within your app and then using them?

I am finding out all about it ... so this may be to your intrest ...


:)

Yeah I realised after searching for a while that most of the examples mentioned MySQL. Thankfully though there is a way to accomplish the same thing in T-SQL :)

Quote:

---

Originally Posted by techgnome

It's a MySQL thing..... a few other DBMSs may implement it as well.,... but SQL Server doesn't. That link that was posted earlier was a creation script for a InnoDB database, not SQL Server. Not all SQL is created equal.

-tg

---

Aaaaaah .... that explains it ... call me ignorent but I was not even aware that SQL is not created equal for both mysql and sql server? I thought the whole point was to create a universal language that could apply to all Relational Database Management Systems ...


Many thanks for bringing this to my attention!

Well, there's SQL, then there's SQL.... the S in SQL is "Structured" not standard... but there is a standard, which nearly all DMSs implement.... but then they add their own stuff to it, Like the CREATE TABLE IF NOT EXISTS...

-tg