CODEWITHSUNDEEP
c#sql-serverdatabase
Rashid
Rashid

Reputation: 293

How to create a Microsoft SQL Server database by using ADO.NET?

How to create a Microsoft SQL Server database by using ADO.NET and Visual C# .NET programmatically?

I need to create databases programmatically.

I use this code but after creating database, I cannot add a table to this DB due to permission problem

string str = "CREATE DATABASE MyDatabase ON PRIMARY (NAME = MyDatabase_Data, FILENAME = 'C:\\MyDatabaseData.mdf', SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = MyDatabase_Log, FILENAME = 'C:\\MyDatabaseLog.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)";

SqlCommand myCommand = new SqlCommand(str, myConn);

try 
{
    myConn.Open();
    myCommand.ExecuteNonQuery();
    MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
    if (myConn.State == ConnectionState.Open)
    {
         myConn.Close();
    }
}

my code for Creating Table

string path="";// new DB path

string constring = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + path + ";Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True";

string qry="create table table1 ( ID int, name varchar(20) )";
SqlConnection con = new SqlConnection(constring);       
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();

Upvotes: 1

Views: 2844

Answers (3)

Paulo Fernando
Paulo Fernando

Reputation: 187

According with your code, you created the database, so ... Before you call the command to create the table, Did you anytime add an "USE DATABASE [DataBaseName]" in your string variable? In my case, I've had to add this code to create the tables. Of course, you must note for the user's permissions of the connection string commented above.

Upvotes: 1

YvesR
YvesR

Reputation: 6222

You created a database but you did not set the permissions to it. So after database creation add:

var userName = "sa"; // username that is used in the connection
var str = String.Format("USE MyDatabase; EXEC sp_changedbowner '{0}'", userName);

Do a ExecuteNonQuery with this as well and then you have the rights to add tables, etc.

EDIT

Btw: Sidenote: I would just execute 

USE master; IF ( NOT(SELECT IsNull(db_id('myDBname'),-1)>0) ) CREATE DATABASE 'myDBname'

and let the SQL server do the rest. Easier to create like this, but it is just another way how to do it.

Upvotes: 0

Thinkeye
Thinkeye

Reputation: 655

Based on the error description, following steps may work. I assume that your SQL server runs on the same machine as your test program.

  1. Create a worldwide writable directory (C:\mydbdir).
  2. Let the DB file be created there (C:\mydbdir\MyDatabaseData.mdf)
  3. Try to create the tables in the DB

As ta.speot.is noted, you are (very likely) missing write permissions to C:\ for SQL server process.

Upvotes: 0

Related Questions