Connecting ASP.Net with C# pages to SQLserver

**Gunaar** · 21-05-2011

How do I connect my asp.net pages with sql server . I am not been able to connect it . sir please send me a very detail the process of connectivity between Asp.net and Sql server.i will highly thankful to you . I have been trying to solve this problem since two weeks but all my efforts have been in vain. I need to connect my pages with the database as without it my pages would no longer be dynamic and the main motive of my project would be void .

**windows_user** · 21-05-2011

Please specify which version of sql server and visual studio are you using because there may be some changes in the coding for different version‘s of visual studio and sql server.For connecting to SQL Server using Windows authentication, perform the following steps: Configure a connection string. Encrypt the connection string, Configure SQL Server security. Test security access.

Code:

<connectionStrings>
  <add name="MyDbConn1" 
       connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
  <add name="MyDbConn2" 
      connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
</connectionStrings>

The above code ‘s are same. Its optional to encrypt your data . the code works fine without it also To test your asp.net pages , with the connections you made To test database access, create a test ASP.NET Web application and add the following .aspx page.

Code:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">
  protected void Page_Load(object sender, EventArgs e)
  {
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn1"].ToString()))
      {
          SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM authors", cn);
          cn.Open();
          SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
          rdr.Read();
          Response.Write(rdr[0].ToString()); //read a value
      }
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>SQL Authentication</title>
</head>
<body/>
</html>

**Shreevats** · 21-05-2011

There might be an installation problem in sql server . Because most of the time it’s not installed properly and we blame our code for not working. Check if you can see and open the link called query analyzer in sql server. If you don‘t find it there then there might be a problem in the installation. As sql installation rarely points out any problem even if it ‘s installed with errors. And if it has installed properly you can view it then open it can note down the server name. And also check for with security does it you opts for when it connects with the sql server, note it . And then check if you have mentioned the right server name, also check if you have done right setting‘s in integrated security. If the integrated security is set to true then you should be using windows authentication mode for connecting to server. And if it is set to false then you need to write your login id and password.

**Villiers** · 21-05-2011

If you are connecting it using ms access then this is the code remember that the code written for ms acess is different for that we write for sql . in sql connection we used sql.client as a namespace, while in access we use Microsoft.oledb as the namespace. The code is:

Code:

public void ConnectToAccess() 
{ 
System.Data.OleDb.OleDbConnection conn = new 
System.Data.OleDb.OleDbConnection(); 
database. conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data source= @"path \mydatabase.mdb"; 
try 
{ 
conn.Open(); 

} 
catch (Exception Error) 
{ 
MessageBox.Show(Error.Message); 
} 
finally 
{ 
conn.Close(); 
} 
}

**Ronchi** · 21-05-2011

First of all you have to import required namespace , in case is for sqlserver is sqlclient by using directive.
Code:
```
using System.Data.SqlClient;
```

Create object of sqlconnection class

Code:

private SqlConnection  dr = New SqlConnection();

then Assign the path to sqlserver server by setiing ConnectionString property to the sqlserver path.
Code:
```
dr.ConnectionString = " Data Source=.\\Servername; 
Initial Catalog=DatabaseName; Integrated Security=True";
```
For finding out what you should write in integrated security see above.
Then Open connection by calling Open() method
Code:
```
connection.Open();
```