Create Dynamic SQL Server Database in asp.net

Question

How to create a dynamic database in asp.net in C#? Please help me. BTW I used 4-5 methods but didn't work for me.

One method is this:

String str;
SqlConnection myConn = new SqlConnection("Data Source=(LocalDB)\v11.0;Integrated security=True;"); //database=master

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();

    Label1.Text = "Database is created successfully";
}
catch (System.Exception ex)
{
    Label1.Text = ex.Message;
}
finally
{
    if (myConn.State == ConnectionState.Open)
    {
        myConn.Close();
    }
}

Explain didn't work. Did you get an error message? Did it delete all your databases? Give us something here. — Sean Lange, Apr 07 '15 at 14:30
I am having this error in all methods- A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) — Vishal Mittal, Apr 07 '15 at 14:30
I can' see your screen. Any chance you want to share what the error is? — Sean Lange, Apr 07 '15 at 14:31
Error is A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) — Vishal Mittal, Apr 07 '15 at 14:32
No I needed to know what the issue was. So if you can't create a connection because it can't find the server the first place to look is your connection string. Also, follow the instructions in the error message. Does your server allow remote connections? — Sean Lange, Apr 07 '15 at 14:36
@SeanLange I think connection string is OKAY but i am working with SQL server which is inbuilt in VS2013. I didn't install it separately. How to check remote connection — Vishal Mittal, Apr 07 '15 at 14:40
Here is your Full Project link [link](http://www.4shared.com/rar/PXsvYQBAba/DynamicDatabaseTrying.html) I used 4 methods in it... You can download it and see but i used VS2013 — Vishal Mittal, Apr 07 '15 at 14:40
Yes , this code worked perfectly . Have you ever connect to the master db successful? — Angus Chung, Apr 08 '15 at 04:28
LoL :D I work with the Sql server in windows application but not much in ASP.Net... okay .. @AngusChung Can you join me on Teamviewer.. May be you can solve it? and i m saying it again i have not installed any sql server Separately I m just using VS2013 inbuilt software. — Vishal Mittal, Apr 08 '15 at 04:39

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

Maybe you can try these way refer from Error creating an SQL Server Database in Visual Studio

SQL Server should be up and running
Enable TCP/IP in SQL Server Configuration
Open Port in Windows Firewall
Enable Remote Connection
Enable SQL Server Browser Service
Create exception of sqlbrowser.exe in Firewall
Recreate Alias

Hope it helps.

edited May 23 '17 at 11:56

Community

1
1

answered Apr 07 '15 at 15:35

Angus Chung

1,547
1
11
13

I think you didn't read mine comments. I just said that i haven't installed Sql Server separately so i don't have sql management studio and configuration tools. If from visual studio is there anything i can do please tell me. – Vishal Mittal Apr 07 '15 at 15:40
I used your code to create db from visual studio successfully. So,we should try to check if the remote connection is allowed. – Angus Chung Apr 07 '15 at 16:17

score 1 · Accepted Answer · answered Apr 07 '15 at 14:41

1

First of all you need to connect to master database on the server

new SqlConnection("Data Source=ServerName;Database=master;Integrated security=True;");

Plus your database size needs to be minimum 5MB it is required for SQL Server core functionality.

CREATE DATABASE MyDatabase ON PRIMARY  
(NAME       = MyDatabase_Data,  
FILENAME    = 'C:\MyDatabaseData.mdf',  
SIZE        = 5MB,    --<-- File size needs to be minimum 5MB 
MAXSIZE     = 10MB, 
FILEGROWTH  = 10%)  
LOG ON 
(NAME       = MyDatabase_Log,  
FILENAME    = 'C:\MyDatabaseLog.ldf',  
SIZE        = 1MB,  
MAXSIZE     = 5MB,  
FILEGROWTH  = 10%);

Otherwise you will get an error saying something like The database primary file must be at least 5 MB to accommodate a copy of the model database bla bla.....

answered Apr 07 '15 at 14:41

M.Ali

67,945
13
101
127

if you are not sure about, connect to sql server and execute `SELECT @@SERVERNAME` this will return the server name. – M.Ali Apr 07 '15 at 14:44
I didnt install Sql server separately. I am using which is inbuilt in VS2013. So what do you say? – Vishal Mittal Apr 07 '15 at 14:57
Didn't find. If anybody want me to join you on team viewer i can do that also. Please solve it. – Vishal Mittal Apr 07 '15 at 15:08
How can I check remote connection is allowed or not? – Vishal Mittal Apr 08 '15 at 02:51

score 0 · Answer 3 · answered Apr 08 '15 at 06:54

Thank You very much Angus Chung you are awesome man. he just helped me via Team viewer and told me wt was wrong... Actually i was using this "Data Source=(LocalDB)\v11.0;"

But it should be "Data Source=(LocalDB)\v11.0;" Well good catch.. Thank you..!!!

Create Dynamic SQL Server Database in asp.net

3 Answers3