When your project connected with database in visual studio, the properties of Data Connection holds working connection string in Server Explorer in Visual Studio. So to find connection string you only need to connect database through visual studio. Here I will explain database connectivity with three main databases as SQL Server, MS Access database and Oracle Database.
Connect SQL Server with Visual Studio/SQL server Database Connectivity
1. Open Visual Studio.
2. Go to View menu=> Server Explorer.
3. Right click on Data Connections and select Add Connection.
4. You will get add connection window.
5. Provide Server name. Select Use SQL Server Authentication/Use Window Authentication radio button. Then submit the username and password.
6. After validating provided credentials, the database lists is shown in dropdown list .Select relevant database. OR You can attach a .mdf database file also.
7. You can test connection and if succeeded Ok.
9. Right Click on data connection and go to properties. You can see Connection string here.
10. Copy connection string and paste it in your web config.
11. You can use this connection string in your project to open and use connection
public class DbData
{
static SqlConnection cn; //defining in class
static string cnn; //defining in class
public DbData()
{
}
public static SqlConnection GetConnection()
{
cnn = System.Web.Configuration.WebConfigurationManager.AppSettings["con"].ToString();
cn = new SqlConnection(cnn);
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
else
{
cn.Close();
}
return cn;
}
}
Use this method as
SqlCommand cmd = new SqlCommand(query, DbData.GetConnection());
12. That is whole thing about getting and using connection string from Visual Studio.
Connection String for MS Access database/MS Access database connectivity
1. Open Visual Studio.
2. Go to View menu=> Server Explorer.
3. Right click on Data Connections and select Add Connection.
4. You will get add connection window.
5. By default Microsoft SQL Server (sqlclient) selected , click change and select Microsoft Access database.
6. Browse Access database file and test connection if connection succeeded then OK.
7. A new data connection created in your visual studio Server Explorer.
8. Right Click on data connection and go to properties. You can see Connection string here. Copy and use as above steps for SQL Server.
Connection String for Oracle database/Oracle Database Connectivity
1. First install oracle client on machine where you want to configure linked server.
2. Go to [Oracle Client Installation Directory]\product\11.2.0\]dbhome_1\NETWORK\ADMIN
3. Open tnsnames.ora file.(It is Network configuration file for oracle)
4. Add TNS setting in this file as Example here
TEST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.0.35)
(PORT=1556)
)
(CONNECT_DATA=
(SERVICE_NAME=TEST)
)
)
5. See more details of tnsnames.ora file.
To Test oracle connection:
6. Open cmd
7. Enter SQLPLUS
8. Enter USERNAME: username@Test
9. Enter PASSWORD: password
10. Type any select query to confirm connection.
Now you can create connection string
11. Open Visual Studio.
12. Go to View menu=> Server Explorer.
13. Right click on Data Connections and select Add Connection.
14. You will get add connection window.
15. By default Microsoft SQL Server (sqlclient) selected , click change and select Oracle Database (OracleClient).In TNS Server name is TNS entry name.and Provied user name and password
16. A new data connection created in your visual studio Server Explorer.
17. Right Click on data connection and go to properties. You can see Connection string here. Copy and use as above steps for SQL Server.
Thanks
ReplyDelete