Search code examples
c#sql-servervisual-studiosql-server-ce

trouble with initial setup and connection to microsoft sql server from Visual Studio

I am on a Windows 10 machine, which has Visual Studio 2015. I am now trying to do projects with a database on the same machine. I seem to have SQL server ( I am really new to Databases ) Here is why I think I have a Database, which I can use: 

C:\Users\zohal>wmic product get name,version | findstr "SQL"
Microsoft SQL Server Compact 4.0 SP1 x64 ENU                                                                    4.0.8876.1

When I try to connect to a database from Visual Studio I do the following: click on Project > Add New Data Source new Window Comes up select Database new Window Comes up select Dataset the only option available, new Window Comes up select NewConnection new Window Comes up There are six options select Micorsoft SQL Server new Window Comes up but the pulldown is blank. I looked in the services section of Computer Management application, and do not see a SQL service running. I want to know how do I connect to this SQL server and does it work for what I want to do, which is to run .Net C# code with SQL statements?

Update........... Guys, thank you for the content and advice. I have tried both SQL compact and SQL Express, and ran into the following problems. I wonder if you can help, or perhaps I'll create a new post. I have tried everything I could think of. 1) I tried to use the SQL compact. Got as far as "Creating a SQL server Compact Database". When I right click on the "App_Data", I just don't see an option of "SQL Server Compact 4.0 Local Database" as it stated in the procedures. Here is an image of what I see. I did a search online for this type of issue, and the solution was to install a tools package. I did that. In fact I ran the Visual Studio install file, and chose the modify option and added all the tools, and support packages I could think of. I rebooted the machine also; no change. I also ended up installing SQLExpress, and followed this link to set it up, but when I click on a new connection option I do not see the SQL Express option in the server name pull down as it states in the procedure. I basically get as far as creating the project and three steps into the procedure. I can put the projects on Git if that helps. My immediate goal is to create a table and query it. Here the code in my project: 

var query = from c in db.Customers
                        where c.City == "Nantes"
                        select new { c.City, c.CompanyName };

Right now, I get an error under "c" and "db" as I have not created a connection. While I don't know how to do that yet, I do know that is not a huge task. But if I can not connect to a Database, this does not help me much. I did find the right assemblies and added them to the beginning of my project. I am talking about 

using System.Linq.Expressions;
using System.Data.SqlServerCe;

Just tell me what to do. Should post again or can help me?

Update: Thank you. The MVC .Net Core MVC procedures worked. Thank you so much.

Solution

  • SQL Server Compact is not a full fledged database service. It is a way to store data in a file and run sql like queries against it.

    Here is a short walk through of how to connect to a sql compact database: https://msdn.microsoft.com/en-us/library/gg606540(v=vs.100).aspx

    If you want to use an actual sql server you can download and install SQL Server Express for free. Here is a walk through for that: https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

    Both of the linked examples use Entity Framework to connect which is an ORM. There are many ways to connect to a database in C#. But since you mentioned running sql statements directly you may want to look into Dapper.Net which is a mirco ORM that let's you run SQL statements and map the results back to objects.