sql server msde: Sample code to create a SQL Server Database in C# -- sql server msde

You're in the

sql server msde

group:

Sample code to create a SQL Server Database in C#

Re: Sample code to create a SQL Server Database in C#

Andrea Montanari
5/22/2005 12:00:00 AM

sql server msde:

hi,
[quoted text, click to view]

if you mean you can create databases and dbobjects without che SQL Server
Client Tools like Enteprise Manager, then yes... you can execute
Transact-SQL DDL code via Ado.Net like following trivial VB.Net code..
Module Module1
Sub Main()

CreateDatabase("SCM")

End Sub

Private Sub CreateDatabase(ByVal DbName As String)
Dim sqlCmd As String
sqlCmd = String.Format("CREATE DATABASE [{0}]" & vbCrLf & _
vbTab & "ON (NAME = N'{0}_data'," & vbCrLf & _
vbTab & vbTab & "FILENAME = N'C:\Programmi\Microsoft SQL
Server\MSSQL\Data\{0}_data.mdf' ," & vbCrLf & _
vbTab & vbTab & "SIZE = 2," & vbCrLf & _
vbTab & vbTab & "FILEGROWTH = 10%" & vbCrLf & _
vbTab & ")" & vbCrLf & _
vbTab & "LOG ON (NAME = N'{0}_log'," & vbCrLf & _
vbTab & vbTab & "FILENAME = N'C:\Programmi\Microsoft SQL
Server\MSSQL\Data\{0}_log.ldf' ," & vbCrLf & _
vbTab & vbTab & "FILEGROWTH = 10%" & vbCrLf & _
vbTab & ")", DbName)

Debug.WriteLine(sqlCmd)

Dim Conn As New System.Data.SqlClient.SqlConnection("Data
Source=(Local);Initial Catalog=master;Integrated Security=SSPI;")
Conn.Open()

Dim Cmd As New System.Data.SqlClient.SqlCommand
With Cmd
.CommandTimeout = 10
.CommandType = CommandType.Text
.CommandText = sqlCmd
.Connection = Conn
End With
Try
Cmd.ExecuteNonQuery()
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
Cmd.Dispose()
Cmd = Nothing
Conn.Dispose()

Conn = New System.Data.SqlClient.SqlConnection(String.Format("Data
Source=(Local);Initial Catalog={0};Integrated Security=SSPI;", DbName))
Conn.Open()
CreateTables(Conn, DbName)
Conn = Nothing

End Sub
Private Sub CreateTables(ByRef Conn As
System.Data.SqlClient.SqlConnection, ByVal DbName As String)

Dim sqlCmd As String

sqlCmd = "CREATE TABLE dbo.SomeTable (" & vbCrLf & _
vbTab & "[ID] int NOT NULL IDENTITY PRIMARY KEY ," & vbCrLf & _
vbTab & "[Name] varchar(10) NOT NULL " & vbCrLf & _
vbTab & ")"

Debug.WriteLine(sqlCmd)

Dim Cmd As New System.Data.SqlClient.SqlCommand
With Cmd
.CommandTimeout = 10
.CommandType = CommandType.Text
.CommandText = sqlCmd
.Connection = Conn
End With
Try
Cmd.ExecuteNonQuery()
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
Cmd.Dispose()
Cmd = Nothing

End Sub
End Module

it uses a Windows NT trusted connection to connect to the (Local) instance
referencing the [master] database... obviously the Winndows account must be
member of dbcreator server role in order to have "CREATE DATABASE"
permission... you can modify the connection string providing a standard SQL
Server authenticated connection using a sysadmin or dbcreator member as
well...
it then create a table object... hardcoded schema, but you should read
dbobjects schema from an external DDL file and execute each batch as soon as
you reach a "GO" batch terminator... as usually this is the way to go..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Re: Sample code to create a SQL Server Database in C#

Greg Low [MVP]
5/22/2005 12:00:00 AM

Hi SCM,

No, you don't need IIS. You can do all these things with the MSDE on its
own. IIS is used as one of the options if you need to do replication from
outside your network.

HTH,

--
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com

[quoted text, click to view]

Sample code to create a SQL Server Database in C#

SCM
5/22/2005 3:45:01 AM

Hi,

I'm a new programmer in using ADO.Net.

I have to write a simple program to create a SQL server Database on a
Windows 2003 Server with MSDE 2000 installed only.

Is it possible to do so by just creating a Database without installed any
SQL Server 2000?

After I have created the SQL Database, can I generate an XSD schema on SQL
Server MSDE by using C#?

Any sample code to create a SQL Database without SQL Server 2000 installed?

Re: Sample code to create a SQL Server Database in C#

SCM
5/22/2005 9:47:01 AM

Hi Andrea,

Thanks for your information. It's very helpful.

One more things that I would like to know is do I need to install IIS in
order to create, read, insert, delete SQL DB in Windows2003 server with MSDE
installed only?
Because of the security reason, I should not install IIS in Windows2003
Server.

--
Regards,
SCM
Programmer

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server msde group.

home · blog · groups

Questions? Comments? Contact the dn