How to create a database from nothing?

[Jonas L]

I'm creating a application that should handle contacts, orders etc.
I want to distribute only the exe file and let the application take
care of creating and changing the database that is to be used.
Furthermore, I would also like to make it possible for the user to
select which sort of database to create and use, MSSQL, Oracel, Access
etc.

Is this possible? Or do I have distribute a databas file as well? My
first idea was to use OLEDB and let the user select which provider to
use, but I can't open a databas connection without a valid database
file.

Something simular to this..

Assume that the string aConnectionString would equal
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User
Id=admin;Password=;"
or "Provider=sqloledb;Data Source=Aron1;Initial
Catalog=pubs;Integrated Security=SSPI;"
or "Provider=MySQLProv;Data Source=mydb;User
Id=UserName;Password=asdasd;"

OleDbConnection oConnection = new OleDbConnection(aConnectionString);
OleDbCommand oCommand = new OleDbCommand("create database
aDbName",oConnection);
oConnection.Open();
oCommand.ExecuteNonQuery();
oConnection.Close();

Br
Jonas

[Cor Ligthert]

Jonas,

Complete sample in VBNET below, it cannot be that hard to set that in C#
language.

I hope this helps?

Cor

Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\db1.mdb")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
cmd = New OleDb.OleDbCommand("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
End Sub
End Class

Cor

[Cor Ligthert]

Patty,

Thanks normally I have above this sample

set a reference to COM adox ext 2.x for dll and security

That should be done for this.

That all.

Cor

[Scott Allen]

There is a KB article showing how to pop up the Data Link Properties
dialog to let the user select the database connection:

http://support.microsoft.com/default...b;EN-US;310083

--
Scott
http://www.OdeToCode.com

On Fri, 27 Aug 2004 08:57:07 -0700, Patty O'Dors
<Pa********@discussions.microsoft.com> wrote:

The more generic you make the SQL, the more likely it will be to work with
all database providers.
For instance,
create table
will work with either Oracle, SQL or Access - or pretty much anything else.
But some functions like "IIf", "CStr" will only work with Access.
Some things like clustered indexes will NOT work with Access.
Some things will only work with MSSQL and nothing else, and some things will
work with Oracle and nothing else.
So if you want them to be able to choose, let them know which they can
choose from, and make sure that you've tested it on *all* of those.
Then at the start of the application, you can pop up a "database connection"
dialog, (the purpose of which is to prompt the user for input and get back a
connection string), similar to the one in the administrative tools\data
sources applet in windows. How to do that is a subject for another post - I'm
unsure as to how you do it exactly but I'm pretty sure you can do it without
rolling your own.

[Jonas L]

Cor,

I tried the following code..

ADOX.Catalog catalog = new ADOX.CatalogClass();
catalog.Create(@"Provider=sqloledb;Data Source=Legolas;Initial
Catalog=Test;Integrated Security=SSPI;");
catalog.Create(@"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\mydb.mdb;User Id=admin;Password=;");

and only the second create works.. Is there any way to create a SQL
Server database or is there an error in the connectionstring?

I don't have access to a Oracel server so I havn't tested to create a
Oracel database.

Thanks!

Jonas
"Cor Ligthert" <no**********@planet.nl> wrote in message news:<uT**************@tk2msftngp13.phx.gbl>...

Patty,

Thanks normally I have above this sample

set a reference to COM adox ext 2.x for dll and security

That should be done for this.

That all.

Cor

[Jonas L]

Is it possible to create new databases with the Data Link Properties
dialog or can you only select existing databases?

Scott Allen <bitmask@[nospam].fred.net> wrote in message news:<gp********************************@4ax.com>. ..

There is a KB article showing how to pop up the Data Link Properties
dialog to let the user select the database connection:

http://support.microsoft.com/default...b;EN-US;310083

--
Scott
http://www.OdeToCode.com

On Fri, 27 Aug 2004 08:57:07 -0700, Patty O'Dors
<Pa********@discussions.microsoft.com> wrote:

The more generic you make the SQL, the more likely it will be to work with
all database providers.
For instance,
create table
will work with either Oracle, SQL or Access - or pretty much anything else.
But some functions like "IIf", "CStr" will only work with Access.
Some things like clustered indexes will NOT work with Access.
Some things will only work with MSSQL and nothing else, and some things will
work with Oracle and nothing else.
So if you want them to be able to choose, let them know which they can
choose from, and make sure that you've tested it on *all* of those.
Then at the start of the application, you can pop up a "database connection"
dialog, (the purpose of which is to prompt the user for input and get back a
connection string), similar to the one in the administrative tools\data
sources applet in windows. How to do that is a subject for another post - I'm
unsure as to how you do it exactly but I'm pretty sure you can do it without
rolling your own.

[Cor Ligthert]

Jonas,

This goes only for ms-Access, although you can discuss what is a database.
When there is an existing SQL server than you can of course use that Create
command to create a database in a existing SQL server.

I have understood that SQL express server will have in future posibilities
for easy installing.

I hope this helps?

Cor

I tried the following code..

ADOX.Catalog catalog = new ADOX.CatalogClass();
catalog.Create(@"Provider=sqloledb;Data Source=Legolas;Initial
Catalog=Test;Integrated Security=SSPI;");
catalog.Create(@"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\mydb.mdb;User Id=admin;Password=;");

[Scott Allen]

Just select an existing database...

--
Scott

On 28 Aug 2004 07:30:11 -0700, jo***@bootman.se (Jonas L) wrote:

Is it possible to create new databases with the Data Link Properties
dialog or can you only select existing databases?

Scott Allen <bitmask@[nospam].fred.net> wrote in message news:<gp********************************@4ax.com>. ..

There is a KB article showing how to pop up the Data Link Properties
dialog to let the user select the database connection:

http://support.microsoft.com/default...b;EN-US;310083

--
Scott
http://www.OdeToCode.com

On Fri, 27 Aug 2004 08:57:07 -0700, Patty O'Dors
<Pa********@discussions.microsoft.com> wrote:

>The more generic you make the SQL, the more likely it will be to work with
>all database providers.
>For instance,
>create table
>will work with either Oracle, SQL or Access - or pretty much anything else.
>But some functions like "IIf", "CStr" will only work with Access.
>Some things like clustered indexes will NOT work with Access.
>Some things will only work with MSSQL and nothing else, and some things will
>work with Oracle and nothing else.
>So if you want them to be able to choose, let them know which they can
>choose from, and make sure that you've tested it on *all* of those.
>Then at the start of the application, you can pop up a "database connection"
>dialog, (the purpose of which is to prompt the user for input and get back a
>connection string), similar to the one in the administrative tools\data
>sources applet in windows. How to do that is a subject for another post - I'm
>unsure as to how you do it exactly but I'm pretty sure you can do it without
>rolling your own.
>
>

--
Scott
http://www.OdeToCode.com

[Nick Malik]

You will have to test your code with any database that you want to support.
If you don't have SQL Server or Oracle yourself, you will have a difficult
time doing this.

Frequently, when I install an app on SQL Server, the install instructions
require me to create the database myself. That is typical. If you want to
create the database yourself, you can always use SQL DMO, but it will
increase the size of your app (to include the DMO library) and you can only
install the app on the machine where SQL Server is actually hosted. This is
what you are actually doing when you use MSDE (the small free version of SQL
Server available from Microsoft).

Better option is to require the user to create the database themselves (with
no tables) and give you a working connection string.

HTH,
--- Nick

"Jonas L" <jo***@bootman.se> wrote in message
news:7b*************************@posting.google.co m...

I'm creating a application that should handle contacts, orders etc.
I want to distribute only the exe file and let the application take
care of creating and changing the database that is to be used.
Furthermore, I would also like to make it possible for the user to
select which sort of database to create and use, MSSQL, Oracel, Access
etc.

Is this possible? Or do I have distribute a databas file as well? My
first idea was to use OLEDB and let the user select which provider to
use, but I can't open a databas connection without a valid database
file.

Something simular to this..

Assume that the string aConnectionString would equal
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User
Id=admin;Password=;"
or "Provider=sqloledb;Data Source=Aron1;Initial
Catalog=pubs;Integrated Security=SSPI;"
or "Provider=MySQLProv;Data Source=mydb;User
Id=UserName;Password=asdasd;"

OleDbConnection oConnection = new OleDbConnection(aConnectionString);
OleDbCommand oCommand = new OleDbCommand("create database
aDbName",oConnection);
oConnection.Open();
oCommand.ExecuteNonQuery();
oConnection.Close();

Br
Jonas