Making Database Connections on ASP.NET 2.0

0. Prolog : My Suggestion

For making connections to various types of database, I suggest using ODBC driver.



1. SQL Server (Mssql)

1) Using ODBC Driver

1: Check & Modify SQL Native Client Configuration

SQL Server Configuration Manager – SQL Native Client Configuration - Client Protocols – Enabled TCP/IP


2: Installing DSN

Start - 관리 도구 - 데이터 원본(ODBC) - 시스템 DSN 추가 – SQL Server 선택 후 설치

(굳이 추가할 필요가 없으며, 연결을 테스트해보는 수단으로 이용할 수 있다.)


3: Configurating Web.config File
: Connection to Local SQL Server Express Using SQL Server ODBC Driver

<connectionStrings>

    <add name="MssqlConnectionString" connectionString="Driver={SQL Server};Server=HOSTNAME\SQLEXPRESS;Database=DB_NAME;Uid=USERID;Pwd=PASSWORD;option=3" providerName="System.Data.Odbc" />
</connectionStrings>

4: C# Code Sample

using System.Data.Odbc;

…

string ConnString = ConfigurationManager.ConnectionStrings["MssqlConnectionString"].ConnectionString;

OdbcConnection objcon = new OdbcConnection(ConnString);

objcon.Open();

 

OdbcCommand objcmd = new OdbcCommand();

objcmd.Connection = objcon;

objcmd.CommandText = "SELECT Name FROM users WHERE ID = '" + id + "' AND Password = '" + pwd + "'";

objcmd.CommandType = CommandType.Text;

 

OdbcDataReader objdr = objcmd.ExecuteReader();

if (objdr.HasRows) // Get User's Name

{

    if (objdr.Read())

    {

        string name = objdr.GetString(0);

        StatusLabel.Text = name;

    }

 

    objdr.Close();

}

 

objcon.Close();

2) Using SQL Client

1: Check & Modify SQL Server Network Configuration

SQL Server Configuration Manager – SQL Server 2005 Network Configuration - Protocols for SQLEXPRESS – Enabled TCP/IP or Named Pipes(local) - Restart SQL Server(*)

(*): SQL Server Configuration Manager – SQL Server 2005 Services - Click Right Button on SQL Server - Restart


2: Configurating Web.config File
: Connection to Local SQL Server Express Using SQL Client

<connectionStrings>

    <add name="SqlClientConnectionString" connectionString="Data Source=.\SQLEXPRESS; Integrated Security=False; Initial Catalog=DATABASE_NAME; User ID=USER_ID; Password=PASSWORD" providerName="System.Data.SqlClient" />

</connectionStrings>

 3: C# Code Sample

using System.Data.SqlClient;

…

string ConnString = ConfigurationManager.ConnectionStrings["SqlClientConnectionString"].ConnectionString;

SqlConnection objcon = new SqlConnection(ConnString);

 

objcon.Open();

 

SqlCommand objcmd = new SqlCommand();

objcmd.Connection = objcon;

objcmd.CommandText = "SELECT Name FROM users WHERE ID = '" + id + "' AND Password = '" + pwd + "'";

objcmd.CommandType = CommandType.Text;

 

SqlDataReader objdr = objcmd.ExecuteReader();

if (objdr.HasRows) // Get User's Name

{

    if (objdr.Read())

    {

        string name = objdr.GetString(0);

        StatusLabel.Text = name;

    }

 

    objdr.Close();

}

 

objcon.Close();

 

2. MySQL 

1) Using ODBC Driver

1: Installing MyODBC Driver

Download Link: http://dev.mysql.com/downloads/connector/odbc/3.51.html

2: Installing DSN

Start - 관리 도구 - 데이터 원본(ODBC) - 시스템 DSN 추가 - MySQL ODBC 3,51 Driver 선택 후 설치

(굳이 추가할 필요가 없으며, 연결을 테스트해보는 수단으로 이용할 수 있다.)


3. Configurating Web.config File
(Connection to Remote MySQL Database Server)

<connectionStrings>

    <add name="MysqlConnectionString" connectionString="Driver={MySql ODBC 3.51 Driver};Server=SERVER_ADDRESS;Database=DB_NAME;Uid=USERID;Pwd=PASSWORD;option=3" providerName="System.Data.Odbc" />
</connectionStrings>

4: C# Code Sample
Reference: 5. 1-1) 4: C# Code Sample of SQL Server Connection Using ODBC Driver

2) Using MySQL .Net Connector

1: Make a Bin folder in the root directory of your web site.

Path(ex): C:\inetpub\wwwroot\Bin

2: Download & Install MySQL Connector into Bin folder.

Download Link: http://dev.mysql.com/downloads/connector/net/5.0.html

3: C# Code Sample

using MySql.Data.MySqlClient;

…

MySqlDataAdapter myDataAdapter;

DataSet myDataSet;

 

MySqlConnection myConnection = new MySqlConnection("server=SERVER_ADDRESS; user id=USERID; password=PASSWORD; database=DATABASE_NAME; pooling=false;");

 

string strSQL = "SELECT ID, Name FROM users;";

 

myDataAdapter = new MySqlDataAdapter(strSQL, myConnection);

myDataSet = new DataSet();

myDataAdapter.Fill(myDataSet, "users");

 

MySQLDataGrid.DataSource = myDataSet;

MySQLDataGrid.DataBind();