CODEWITHSUNDEEP
c#sqlado.net
Raducu Mihai
Raducu Mihai

Reputation: 343

Interact with databases from ADO.NET

Hi there I try to learn some ADO.NET. I'm working in Visual Studio 2015 using C#. I made a Database with some table in it which are stored on my local machine. Here is my C# code trying to connect to the database:

string constring = "Data Source=.; Database = CarShop;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(constring);
con.Open();

An error is thrown when I try to Open the SqlConnection saying this:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: A network - related or instance - specific error occurred while establishing a connection to SQL Server.The server was not found or was not accessible.Verify that the instance name is correct and that SQL Server is configured to allow remote connections.(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

So far I have tryied to change the connection string to something like:

string constring = "Data Source=192.168.0.101; Database = CarShop;Integrated Security=True;Connect Timeout=30";

or

constring = "Data Source=(LocalDB)/MSSQLLocalDB;AttachDbFilename=D:/Proiecte/Visual Studio/C#/DataBases/DataBaseTut2/DataBaseTut2/CarShop.mdf;Integrated Security=True;Connect Timeout=30";

But neither have worked.

Upvotes: 0

Views: 890

Answers (4)

Raducu Mihai
Raducu Mihai

Reputation: 343

I figured it out. For those who have this problem. you can get the Connection String by right clicking your database in the Server Explorer and then properties. If you do this you'll find the Connection String in the Properties panel. Copy that string but when you use it in your code use it like this: 

@"whatever your Connection String is"

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8828

  1. I would visit http://www.connectionstrings.com/sql-server/ to get the correct connection string instance for an ADO.net Sql Server connection. I would suggest Standard Security or Trusted Connection from the examples.

  2. I would save it in a config file be it app.config or web.config

  3. When creating a connection always use the using() statement to properly dispose of the connection once it is out of scope.

Therefore:

 using(SqlConnection sqlconn = new SqlConnection(     
ConfigurationManager.ConnectionStrings["defaultconnection"].ConnectionString))
 {
    sqlconn.Open();

   using(SqlCommand sqlcommand  = new SqlCommand("Select field1 FROM  
   Table",sqlconn))
   {
     //code here
   }
}
  1. Ensure that the database on the localhost (.) can accept trusted connection and not rather use Standard Security - sql authentication - by specifying the user credentials i.e. "User Id=username;Password=pwd;"

Upvotes: 0

Scrappy
Scrappy

Reputation: 132

Ensure that tcp/IP is enabled in sql server manager. If not then you won't be able to communicate to it. Also check if you need to provide a named instance. Eg. localhost/sqlexpress

Upvotes: -1

Govind Tupkar
Govind Tupkar

Reputation: 274

Try this one:

Data Source=.;
Initial Catalog=database name;
Integrated Security=True;
Max Pool Size=2048;
Pooling=true;

Upvotes: 0

Related Questions