MS SQL Server - Create Database using C#

Author

Create Database using C#

orenbt78

2006-11-21, 7:13 pm

Hi,

I write software in C# that should create (by itself) a new database.
The SQL server should be installed on the customer's computers silently
(I still need to figure how to do this part)
How can my software create a database that only it can access? Do I
need to create a special user account from the software?
What user account do I use to open a connection to the SQL server in
the first place when I create the database (taking under consideration
that the SQL server was installed silently and as far as the user
concern he has no idea my software uses SQL server).

To summarize:
1. The install of my software should install and configure the SQL
server silently (appreciate any help here)
2. The software needs to access the local SQL server and create a
database that only it can access when the software runs under a simple
"USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
any idea?)

Thanks in advance,
Oren

David Browne

2006-11-21, 7:13 pm

"orenbt78" <orenbt78@googlemail.com> wrote in message
news:1164123542.578586.6280@j44g2000cwa.googlegroups.com...
> Hi,
>
> I write software in C# that should create (by itself) a new database.
> The SQL server should be installed on the customer's computers silently
> (I still need to figure how to do this part)
> How can my software create a database that only it can access? Do I
> need to create a special user account from the software?
> What user account do I use to open a connection to the SQL server in
> the first place when I create the database (taking under consideration
> that the SQL server was installed silently and as far as the user
> concern he has no idea my software uses SQL server).
>
> To summarize:
> 1. The install of my software should install and configure the SQL
> server silently (appreciate any help here)
> 2. The software needs to access the local SQL server and create a
> database that only it can access when the software runs under a simple
> "USER" account in Win2K/XP and not "ADMINISTRATOR" (my big problem -
> any idea?)
>
> Thanks in advance,
> Oren
>

See

Embedding SQL Server Express into Custom Applications
http://msdn.microsoft.com/library/d...qlexcustapp.asp

User Instances for Non-Administrators
http://msdn2.microsoft.com/en-us/library/ms143684.aspx

David

orenbt78

2006-11-26, 7:12 pm

Thanx for the reply.

The "Embedding SQL Server Express into Custom Applications" seems to be
very helpful.

However, I can't figure out yet how to solve my problem with creating
databases as a simply user.
I plan to create a new database every year and let the user to view
last year's data while I am updating this year's data. That means 2
connections at the same time.
In the "User Instances for Non-Administrators" document it says "One
user can only have one user instance". So how can I do that?

And how do I do it in C# code?

Thanks again,
Oren

David Browne

2006-11-26, 7:12 pm

"orenbt78" <orenbt78@googlemail.com> wrote in message
news:1164565596.943159.13410@14g2000cws.googlegroups.com...
> Thanx for the reply.
>
> The "Embedding SQL Server Express into Custom Applications" seems to be
> very helpful.
>
> However, I can't figure out yet how to solve my problem with creating
> databases as a simply user.
> I plan to create a new database every year and let the user to view
> last year's data while I am updating this year's data. That means 2
> connections at the same time.
> In the "User Instances for Non-Administrators" document it says "One
> user can only have one user instance". So how can I do that?
>
> And how do I do it in C# code?

An instance is an instance of the database engine. A single instance of the
database engine can have multiple databases.

And in C# you just send TSQL commands using a
System.Data.SqlClient.SqlCommand object. You can get the syntax for the
commands from SQL Server Books Online.

TDavid

orenbt78

2006-11-27, 7:12 pm

I have managed to create the database file (code below). I am not sure
it is the right way.
I would like to create a password for these database so only my
software will be able to control it (change data). how do i do that?

tmpConn.ConnectionString = "Data Source=(local); DATABASE =
master;Integrated Security=True; user instance=true";

sqlCreateDBQuery = " CREATE DATABASE " + DBParam.DatabaseName + " ON
PRIMARY "
+ " (NAME = " + DBParam.DataFileName +", "
+ " FILENAME = '" + DBParam.DataPathName +"', "
+ " SIZE = 5MB,"
+ " FILEGROWTH =" + DBParam.DataFileGrowth +") "
+ " LOG ON (NAME =" + DBParam.LogFileName +", "
+ " FILENAME = '" + DBParam.LogPathName + "', "
+ " SIZE = 1MB, "
+ " FILEGROWTH =" + DBParam.LogFileGrowth +") ";
SqlCommand myCommand = new SqlCommand(sqlCreate
DBQuery, tmpConn);
try
{
tmpConn.Open();
MessageBox. Show(sqlCreateDBQuer
y);
myCommand.ExecuteNonQuery();

MessageBox.Show("Database has been created successfully!", "Create
Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "Create Database",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
tmpConn.Close();
}

orenbt78

2006-11-29, 7:12 pm

anyone? any idea?

Sponsored Links