How can I create a SQL Server database file (.mdf) programmatically from C#?

Question

I'm new to here. Recently I was ordered by my boss to make a SQL Server database from a Winforms app. I'm trying to create database programmatically. But every time I'm getting an error message.

Is it possible to create a SQL Server database file (.mdf) programmatically ?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace mdf_creator
{
    public partial class Form1 : Form
    {
        public Form1()
        {
           InitializeComponent();
        }

        private void btnCreateDatabase_Click(object sender, EventArgs e)
        {
            String str;
            SqlConnection myConn = new SqlConnection("Data Source =(LocalDB)\\MSSQLLocalDB; Integrated security =SSPI;database=master");
        str = "CREATE DATABASE ss ON PRIMARY " +
            "(NAME = ss_Data, " +
            "FILENAME = 'D:\\ss.mdf', " +
            "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = ss_Log, " +
            "FILENAME = 'D:\\ss.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();
                }
        }
    }
}

Where is the problem with my code?

Access is denied is pretty helpful. You don't have permission to create a file where you are trying to create a file. — LarsTech, Jun 17 '16 at 16:37
Looks like you don't have access to your D:\ drive. Your error message says "Access is denied". — Krazy Dev, Jun 17 '16 at 16:40
If you are running as administrator and you still cannot access the drive, then it likely has nothing to do with the project or your code, and is an issue with your permissions to that drive itself within Windows. — Justin, Jun 17 '16 at 16:57

score 2 · Answer 1 · answered Jun 17 '16 at 16:39

2

The error message indicates that the SQL server was not able to create a file on the 'D:' drive. Make sure that the user the SQL server runs as has the necessary mappings and access rights to that drive.

answered Jun 17 '16 at 16:39

GWLlosa

23,995
17
79
116

I tried to save database to rest of the drive. but it shows same error message. – Sumon Tito Jun 17 '16 at 17:30
It's not YOU who needs the permission. It's the SQL server. Look in your services.msc and see what user SQL server is running as. THAT is the user that needs permission. – GWLlosa Jun 17 '16 at 17:38

score 0 · Answer 2 · answered Jun 17 '16 at 16:39

Yes.

You can use SQL Server Managments Objects

//Connect to the local, default instance of SQL Server.   
Server srv = new Server();  

// Define a Database object variable by supplying the server and the 
// database name arguments in the constructor.   
Database db = new Database(srv, "Test_SMO_Database");  

//Create the database on the instance of SQL Server.   
db.Create();  

//Reference the database and display the date when it was created.   
db = srv.Databases["Test_SMO_Database"];  
Console.WriteLine(db.CreateDate);

I'm very new to programming . I don't understand your answer sir . — Sumon Tito, Jun 17 '16 at 17:40

score 0 · Answer 3 · edited May 23 '17 at 11:58

I would suggest firstly running Visual Studio as administrator. Right click the Visual Studio icon and 'Run as Administrator'.

I would also suggest implementing the answer here: How to request administrator permissions when the program starts?

This is so your application will require administrator access on the machine it is running on, which it will generally need if it is creating/deleting files.

Edit: If you still cannot access the drive when Visual Studio is running as administrator, then it is likely the Window's permissions on that drive, rather than any issue with your project.

How can I create a SQL Server database file (.mdf) programmatically from C#?

3 Answers3