How to connect sql server express database to VS 2017

Question

I keep running into issues just trying to make a connection from my c# program in Visual Studio 2017 Express to a small local database with one table I created in Sql Server Express. I would like to connect it as a data source within Entity Framework in my solution (which is in c#). I've searched MSDN and this site for days but haven't found a way to solve this.

The issue now is that when I go through the Entity Data Model Wizard (existing database) and go to establish a New Connection, select "Microsoft SQL Server Database File (SqlClient)" and browse to my database -- the Test Connection generates the error:

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)". An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\MyDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm running Visual Studio as Administrator (this got past a prior issue that wouldn't even let me browse to the database without prompting an error message that I didn't have permission to open the database file).

I can view my database, its table, and its data in the SQL Server Object Explorer inside Visual Studio - regardless of whether I'm running it as Administrator or not. Not sure if that is significant. I'm new to all this, so sorry if I am missing something obvious.

Solution 1

Figured out my issue: in Visual Studio's "Add New Data Source" wizard, the option I kept choosing was "Microsoft SQL Server Database File", because its description is "Use this selection to attach a database file to a local Microsoft SQL Server instance (including Microsoft SQL Express) using the .NET Framework Data Provider for SQL Server."

However, and I'm not sure why, this was not the right selection.

I tried selecting "other" instead.

Then on the next page input ".\SQLExpress" as the Server Name (mentioned on connectionstrings.com/sql-server/ ...Thanks for the lead @ryguy72!)

Then, under "Connect to a database" my local list of databases popped up, including the one I had created already using SSMS.

Test Connection finally worked then!

Solution 2

Did you look here?

https://www.connectionstrings.com/sql-server/

Try it with the easiest possible scenario (no security).

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Here is a C# script that hits a SQL Server DB on my machine.

Excel to DGV:

        private void button1_Click(object sender, EventArgs e)
        {

            DataTable table = new DataTable();
            string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"",
            "C:\\Users\\Ryan\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls");
            using (OleDbConnection dbConnection = new OleDbConnection(strConn))
            {
                using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
                    dbAdapter.Fill(table);
                dataGridView1.DataSource = table;
                int rows = table.Rows.Count;
            }

            dataGridView1.AutoGenerateColumns = false;
            dataGridView1.Columns["FName"].DataPropertyName = table.Columns["FName"].ColumnName;
            dataGridView1.Columns["LName"].DataPropertyName = table.Columns["LName"].ColumnName;
            dataGridView1.Columns["Age"].DataPropertyName = table.Columns["Age"].ColumnName;
            dataGridView1.DataSource = table;

            //IF THE ORDER DOSEN'T MATTER
            //DataTable table = new DataTable();
            //string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls");
            //using (OleDbConnection dbConnection = new OleDbConnection(strConn))
            //{
            //    using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
            //        dbAdapter.Fill(table);
            //    dataGridView1.DataSource = table;
            //    int rows = table.Rows.Count;
            //}

        }

Excel to SQL Server:

        private void button3_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls;Extended Properties=Excel 8.0;");

            ExcelConnection.Open();

            string expr = "SELECT * FROM [Sheet1$]";
            OleDbCommand objCmdSelect = new OleDbCommand(expr, ExcelConnection);
            OleDbDataReader objDR = null;
            SqlConnection SQLconn = new SqlConnection();
            string ConnString = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
            SQLconn.ConnectionString = ConnString;
            SQLconn.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLconn))
            {

                bulkCopy.DestinationTableName = "tblTest";

                try
                {
                    objDR = objCmdSelect.ExecuteReader();
                    bulkCopy.WriteToServer(objDR);
                    ExcelConnection.Close();

                    //objDR.Close()
                    SQLconn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }

        }

Again...Excel to SQL Server:

private void button4_Click(object sender, EventArgs e)
{
    BindGrid();
}

protected void BindGrid()
{
    string path = "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to SQL Server Table\\Import_List.xls";
    string jet = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
    OleDbConnection conn = new OleDbConnection(jet);
    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
    DataTable dt = new DataTable();
    da.Fill(dt);

    dataGridView1.DataSource = dt;
    BulkUpload();
}

Finally...DGV to SQL Server:

private void button8_Click(object sender, EventArgs e)
{

    //SqlConnection connection = new SqlConnection("Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;");
    DataTable dt = (DataTable)dataGridView1.DataSource;
    string connection = "Data Source=Excel-PC;Initial Catalog=Northwind.MDF;Trusted_Connection=True;";
    using (var conn = new SqlConnection(connection))
    {
        conn.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.ColumnMappings.Add(0, "Fname");
            bulkCopy.ColumnMappings.Add(1, "Lname");
            bulkCopy.ColumnMappings.Add(2, "Age");

            bulkCopy.BatchSize = 10000;
            bulkCopy.DestinationTableName = "Import_List";
            bulkCopy.WriteToServer(dt.CreateDataReader());
        }
    }

}

Don't forget to set your references at the top!!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Data.SqlClient;

Comments

[Admin]

Thanks for the very thorough answer! I think the various options are based on copying data from one database program to another. In my case, the table I want to use is already populated properly and exists correctly in Sql Server... the problem is just getting my Visual Studio program to connect to it as a data source using the VS wizard. I appreciate too the connectionstring link, which I am looking over; does this imply that I will need to overcome the error message by using the wizard's "Code First" option? The error comes up even when I just try to add a new data source.

[Ahmad Tijani]

I ran into a similar issue. I was trying to create a datasource from the database. And Although I could open it SSMS, I couldn't open it on visual studio server explorer. The database connects fine but on expanding the tables, it says "Login failed for user Username". All i did to solve this was to change Integrated Security value to True in the connection. This can be accessed via the Advanced in the Connection Dialog. Hope this helps.