Programming Articles

Connecting to MySQL in ASP.NET without a DSN

The following scripts demonstrate creating connection to a MySQL database in .NET (C#) using the MySql.Data.MySqlClient Namespace.

Pre-requisites:
Contact our support team requesting a copy of the MySQL Connector NET DLL required for this connection.
NOTE: This DLL must be provided by Easy CGI to prevent version mismatching
Email support@easycgi.com to request the MySQL Connector

Sample Script 1:
The script will output all the rows of a specified table.

// Set your connection string
// Substitute the variables below with actual values
// For example if your database is called "Northwind" replace <<YourDatabaseName>> with Northwind, mysqlX.easycgi.com with mysql4.easycgi.com etc
string myConnectionString = "Database=<<YourDatabaseName>>; Data Source=mysqlX.easycgi.com;User Id=<<YourUserName>>;Password=<<YourPassword>>";
MySqlConnection myConnection = new MySqlConnection(myConnectionString);

// Form query string
string mySelectQuery = "SELECT * FROM Table1";

// Create Object of class MySQLCommand
MySqlCommand myCommand = new MySqlCommand(mySelectQuery);
myCommand.Connection = myConnection;

// Open connection
myConnection.Open();

// Read data into DataGrid Control
MyDataGrid.DataSource = myCommand.ExecuteReader();
MyDataGrid.DataBind();

// Close the connection when done with it.
myConnection.Close();

<!------------------- The entire script is shown below ----------------------------------->
FileName: test.aspx


//This script will connect to the database and output all the rows of a specified table.
<%@ import Namespace="System.Data" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<%@ Page Language="C#" Debug="true"%>

<script runat="server">

private void Page_Load(object sender, EventArgs e) {

// Connection String to the database
string myConnectionString = "Database=<<YourDatabaseName>>; Data Source=mysqlX.easycgi.com;User Id=<<YourUserName>>;Password=<<YourPassword>>";
MySqlConnection myConnection = new MySqlConnection(myConnectionString);

// Form query string
string mySelectQuery = "SELECT * FROM Table1";

// Creating Command object
MySqlCommand myCommand = new MySqlCommand(mySelectQuery);
myCommand.Connection = myConnection;

// Open connection
myConnection.Open();

// Read data from database
MyDataGrid.DataSource = myCommand.ExecuteReader();
MyDataGrid.DataBind();

// Close the connection when done with it.
myConnection.Close();

}
</script>

<html>
<body>

<h3><font face="Verdana">Simple Select to a DataGrid Control</font></h3>

<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
EnableViewState="false"
/>

</body>
</html>

<!--------------------------------------------------------------------------------->



Sample Script 2:
The script will insert a row of data into MySQL database

Pre-requisites:
Create a directory called "bin" in the root of your website and place "MySql.Data.dll" in this folder
Email support@easycgi.com to get the DLL file as stated above.

<!------------------- The entire script is shown below ----------------------------------->
File: test2.aspx

<%@ import Namespace="System.Data" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<%@ Page Language="C#" %>

<script runat="server">

private void Page_Load(object sender, EventArgs e) {

// Set your connection string
// Substitute the variables below with real values
// For example if your database is called "Northwind" replace <<YourDatabaseName>> with Northwind, mysqlX.easycgi.com with mysql4.easycgi.com etc
string myConnectionString = "Database=<<YourDatabaseName>>; Data Source=mysqlX.easycgi.com;User Id=<<YourUserName>>;Password=<<YourPassword>>";
MySqlConnection myConnection = new MySqlConnection(myConnectionString);
string myInsertQuery = "INSERT INTO Table1 (id, feild1, feild2) Values(2, 'a', 'b')";
MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();

}
</script>


<html>
<body>

</body>
</html>
1/5/2008 2:15:01 AM Category Database Connection Strings Comments 0

Back