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.

Answer 1

After standard (w/o additive configuration) installation of SQL Express Server will be active only Shared memory protocol, you can check by SQL Server 2014 Configuration Manager

It's enough for local connection (testing) from VS

"...Connections to Microsoft SQL Server from a client running on the same computer use the shared memory protocol. Shared memory has no configurable properties. Shared memory is always tried first, and cannot be moved from the top position of the Enabled Protocols list in the Client Protocols Properties list. The Shared Memory protocol can be disabled, which is useful when troubleshooting one of the other protocols." learn.microsoft.com

For testing connection from VS, you can use the Add Connection from Data Connection Data Source = "MS SQL Server"

Main problem you must set right Server name (by default not correct) <servername>\<instancename> or alias (localhost\<instancename> or .\<instancename>). Instance name you set when installing SQL server (commonly - SQLEXPRESS). You can check right the "Server Name" by SQL Server Management Studio (this programm can find it more simply vs VS). Field "database name" leave empty and simple press "Test Connection" (answer from server must be immediately, if you set all right).

P.S> By this SQL you can check which protocol use for your connection: SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Answer 2

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!

Answer 3

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;