SqlExpress & C# problem in an ASP.NET web site

  • Thread starter Thread starter Lloyd Dupont
  • Start date Start date
L

Lloyd Dupont

I'm trying to create a SqlServer express database file on the fly in a web
application..
(mhh.... maybe I should try to get something running in a desktop
application first?)

Anyway I want to connect to a database file and, if it doesn't exist, create
the DB on the fly.

Actuall I'm trying to create the connection like that:
I'm trying to create a connection like that:
===
public static SqlConnection GetConnection(string filename)
{
string connString = @"Data
Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|{0}.mdf;Integrated
Security=True;User Instance=True";
connString = string.Format(connString, filename);
SqlConnection sqlc = new SqlConnection(filename); // <= exception here
return sqlc;
}
===
It fails with "Format of the initialization string does not conform to
specification starting at index 0."

mmh..
is it because the file doesn't exists?
How am I to connect to SqlServer to run the SqlSever creation script if my
file doesn't exist?

Should I embed an empty database as a resource in my control library if I
want to automatically create the database?
Or is there an other way? (I have the creation Sql script ready, I just
wonder how to run it)....
 
You cannot attach something that does not exist. You can create it, however.
Something like:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDB_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

blah! blah! blah!

I would create a DB creator class to create the string to fire it off. I
would also consider setting all of the properties necessary, ala:

ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDB] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDB] SET READ_WRITE
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL
GO
ALTER DATABASE [MyDB] SET MULTI_USER
GO
ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM
GO

etc.

After this, you can create a connection string to this new database. Oh, one
more thing - this is against master.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks Cowboy!

hmm..
when you said connect to the master, what kind of connection string should I
use?

Cowboy (Gregory A. Beamer) - MVP said:
You cannot attach something that does not exist. You can create it,
however.
Something like:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH =
1024KB )
LOG ON
( NAME = N'MyDB_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH =
10%)
GO

blah! blah! blah!

I would create a DB creator class to create the string to fire it off. I
would also consider setting all of the properties necessary, ala:

ALTER DATABASE [MyDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyDB] SET ARITHABORT OFF
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyDB] SET READ_WRITE
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL
GO
ALTER DATABASE [MyDB] SET MULTI_USER
GO
ALTER DATABASE [MyDB] SET PAGE_VERIFY CHECKSUM
GO

etc.

After this, you can create a connection string to this new database. Oh,
one
more thing - this is against master.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


Lloyd Dupont said:
I'm trying to create a SqlServer express database file on the fly in a
web
application..
(mhh.... maybe I should try to get something running in a desktop
application first?)

Anyway I want to connect to a database file and, if it doesn't exist,
create
the DB on the fly.

Actuall I'm trying to create the connection like that:
I'm trying to create a connection like that:
===
public static SqlConnection GetConnection(string filename)
{
string connString = @"Data
Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|{0}.mdf;Integrated
Security=True;User Instance=True";
connString = string.Format(connString, filename);
SqlConnection sqlc = new SqlConnection(filename); // <= exception
here
return sqlc;
}
===
It fails with "Format of the initialization string does not conform to
specification starting at index 0."

mmh..
is it because the file doesn't exists?
How am I to connect to SqlServer to run the SqlSever creation script if
my
file doesn't exist?

Should I embed an empty database as a resource in my control library if I
want to automatically create the database?
Or is there an other way? (I have the creation Sql script ready, I just
wonder how to run it)....
Click to expand...
Click to expand...
 
Back
Top