TL;DR so you stop putting this on hold: Visual Studio will not allow me to create a live connection to a database on an SQL server within a project, even though it can communicate with the server outside of projects. I want it to have a live connection so I can make a website that uses and allows access to the database.
I have Visual Studio 2017 Professional, and Microsoft SQL Server Management Studio 2017.
I have successfully connected a database from the server to Visual Studio such that I can edit the database in Visual Studio, and the server will reflect my changes. However, I cannot get a live connection to the server inside any project. When I import the database into a project, it just creates a copy that does not actually communicate with the server.
I'm thinking I need to create some sort of special object or change some settings in Visual Studio.
Do I need to switch to Enterprise for supported features? It seems rational that the professional edition should be sufficient for this, but if anyone knows how to solve this kind of issue with a different version of Visual Studio or how to create a website connected to a database server through another [hopefully affordable] program, I'm all ears. Thank you!
(This paragraph added later for clarity:) My goal is to create a website using Visual Studio which allows authorized users to edit the database. Unless doing so is a terrible idea, I want to start by making it possible to edit the database on the server using the website made in Visual Studio, and then add the security features. The current database on the server is placeholding junk until the system is satisfactorily set up. It sounds like it may be possible access the database using a website without directly putting it in the Visual Studio project, so I'm looking into the directions provided in the second comment.
The least error-filled solution I managed is as follows, based on @Symon's comment and link. I've tried several things for the first line, and being public void seems to case about 1/30th the errors as being anything else I've tried, which definitely doesn't mean it's actually the right answer. Items in square brackets are removed for security reasons. I entered the credentials for the database exactly as I did in the popup window to connect Visual Studio in general to the database on the server.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
public void heresanamespacethingy (string stp)
{
//GET STORED PROCEDURE HERE
string conStr = @"Data Source=[server name]; Initial Catalog=[database name]; User ID=[my user ID]; Password=[my password]";
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = stp;
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
}
The error is CS0116 A namespace cannot directly contain members such as fields or methods.
I'm using VS Community and can interact with my MS SQL Server just fine. The edition of Visual Studio that you are using isn't going to limit what you can do code-wise. The editions merely add extra features. I have never heard of anyone being limited in their projects because of the edition of Visual Studios that they are using.
As @Kfirprods stated, it may be very helpful for you to manage your Server/Databases with Microsoft SQL Server Management Studio 2017 instead of trying to use Visual Studios for everything.
The most common way that I've seen to get your live/current database information and data is to have your C# code call to the live data and pull it. I will have an example of C# calling a stored procedure below. The example only calls a stored procedure, showing how it is possible for you to use C# to connect to SQL Server and alter data. You can also send individual, inline queries through this method. Though, to avoid SQL injection (especially since it's a website) , it'd be better protection to use this method to call Stored Procedures and Views rather than sending inline queries.
This may be a good link to read up on for learning how to start using C# to connect to SQL Server. It's what I used to get started!
I have my SQL Server Management studio connected to my Server/Databases, and my Visual Studio is only concerned about my C# code. It helps keep things separated and organized (for me, anyway).
Here is a sample of a C# method that connects to a SQL Database, and runs a Stored Procedure:
public void CallSproc(string stp)
{
//GET STORED PROCEDURE HERE
string conStr = "Data Source=" + ConfigurationManager.AppSettings["DataSource"] + "Initial Catalog=" + ConfigurationManager.AppSettings["InitialCatalog"] + "Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = stp;
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
}
Code Explained:
ConfigurationManager.AppSettings[] points to my App.config file and keys (identifiers) where this data is stored. In case the current server is lost, the program doesn't require a re-compile. The config file just needs altered to point to the right location.
The variable stp is a string that is passed from another method which holds the name of the stored procedure (ex. "stpAddNumbers").
conStr is the connection string that tells the SqlConnection() call what Server and database to connect to.
conn.Open(); opens the connection to the SQL Server itself.
cmd.CommandType = CommandType.StoredProcedure; tells the code that you're calling for a stored procedure.
cmd.ExecuteNonQuery(); executes the stored procedure and the results are returned.
With the code you have shown along with the error message, you don't seem to have a namespace at all. To fix this, simply wrap your method in a namespace. Like so:
...
using System.Threading.Tasks;
namespace HeresANameSpace
{
class ThisIsAClass
{
public void HeresAMethod (string stp)
{
//GET STORED PROCEDURE HERE
string conStr = @"Data Source=[server name]; Initial Catalog=[database name]; User ID=[my user ID]; Password=[my password]";
using (SqlConnection conn = new SqlConnection(conStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = stp;
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}
//End of HeresAMethod()
}
//End of HeresAClass
}
//End of HeresANamespace
}
When starting from a blank Code file, you need to write in the namespace , classs and methods . A method goes inside of a class, a class goes inside of a namespace.