Tag: MySql

A MySql Test site to for your LAMA Server

This is part of the LAMA – Best of all worlds tutorial
LAMA Tutorial Home Page

 

MySql Test Site with Visual Web Developer 2005

1.) Lets open up VWD, and create a new website called mysql-test-site

2.) Add the Mysql Connector/Net to the references.
Right click on the website name in Solution explorer, and choose Add Reference.

Select the MySql.Data from the list, and choose OK.

3.) Add a Data Grid View to the page.
In the Toolbox under Data, Select Grid View and drag it out onto the blank page.

4.) Your Code behind file should have the following code in it:

Imports System.Data

Imports MySql.Data.MySqlClient

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) HandlesMe.Load

‘The MySql Connection String:  replace IP address with the IP address of your LAMA server

Dim Conn As NewMySqlConnection(“server=192.168.1.37,database=testsite,Uid=aspnet,Pwd=aspnetpassword”)

Dim sSql As String = _

“Select * from phonebook”

Dim DA As New MySqlDataAdapter(sSql, Conn)

Dim DT As New DataTable

DA.Fill(DT)

Me.GridView1.DataSource = DT ‘set the datagrid to the datatable

Me.DataBind()

End Sub

End Class

 

5.) Run the project in VWD to make sure it works.  You should see a datagrid with our test data in it.

If you see John Doe, then its time to copy your project into your asp_net share and setup a test site for it.

Follow the instructions in the tutorial for copying and setting up the Asp.net site.

VB.net users: Make sure to precompile your sites before copying them to the LAMA server.

Tutorial – Configure Apache for Asp.net Site


Precompiling your Visual Web Developer Sites

Configuring MySql for your LAMA server and Visual Web Developer

 

This is part of the LAMA – Best of all worlds tutorial
LAMA Tutorial Home Page

MySql Connector .Net

MySql Connector/Net is the component that allows Asp.net to talk to Mysql Databases.

You will need this connector to be installed on the machine that has Visual Web Developer installed.
The latest version of MySql Connector is 5.2. 

Download the Widows Binary (ZIP) file from MySql Website:
http://dev.mysql.com/downloads/connector/net/5.2.html

1.)Unzip and go thru the Installer.  When it is completed, we will have to add the Assembly file to the LAMA Server.

2.)Navigate to the MySql Connector/Net folder inside Program Files.  This is usually:

C:\Program Files\MySQL\MySQL Connector Net 5.2.1

3.)Navigate into the Binaries\.NET 2.0\ folder.

You should see a MySql.Data.dll file.

4.)Copy that file into your asp_net share on your LAMA server.

5.)Now use Putty to connect to your LAMA server.

6.)Change to the asp_net directory

cd /asp_net

7.) Run the gacutil to install the MySql Assembly into Mono’s Global Assembly Cache

sudo gacutil -i MySql.Data.dll

You should see:

Installed MySql.Data.dll into the gac (/usr/local/lib/mono/gac)

 

Setting up a MySql user for Asp.net to use for web apps

Asp.net needs to have access to the MySql database for it’s web apps.  To allow this, we are going to setup a new user on the LAMA server.

1.) Use  Putty to connect to your LAMA server.

2.)Go into MySql.  You will need to know the root password if you set one during install.

sudo mysql -u root -p

It will ask for password, hit enter if you didnt use one

3.) From the Mysql command line, add a user for Asp.net

CREATE USER ‘aspnet’ IDENTIFIED by ‘aspnetpassword’;

This creates an aspnet user with a password of aspnetpassword
*This is for testing / demonstration purposes only.  For securing your mysql database please see MySql’s website for more information.

4.) Now we need to grant privileges to the user

GRANT ALL ON *.* TO ‘aspnet’@’%’ IDENTIFIED BY  ‘aspnetpassword’;

This grants all privileges to the aspnet user
*This is for testing / demonstration purposes only.  For securing your mysql database please see MySql’s website for more information.

 

Create a sample database

We will setup a simple database for testing.  We will make a simple phone book.

1.) Create the database,  from the Mysql command line type:

CREATE DATABASE testsite;

2.) Change to the testsite database

USE testsite;

3.) Create the phonebook table

CREATE TABLE `testsite`.`phonebook` ( `name` VARCHAR(50) NOT NULL, `number` VARCHAR(45) NOT NULL );

This will create a table (phonebook) with 2 fields: name, number

4.) Add a test record

INSERT INTO phonebook VALUES (‘John Doe’, ‘555-123-45678’);

 

One more thing about MySql:

By default MySql does not allow connections from outside computers.  They only allow connections from 127.0.0.1 (localhost).

If you want to test your connections to MySql on your LAMA server from your development machine, you will have to make a change to your MySql configuration file.

To do this:

1.) Use  Putty to connect to your LAMA server.

2.) change directory to the Mysql configuration directory.

cd /etc/mysql

3.) Make a copy of your my.cnf file

sudo cp my.cnf old_my.cnf

4.) Open my.conf in your editor

sudo pico my.cnf

5.) find the following line and comment it out:

            bind-address = 127.0.0.1
            
            change to :
            
            #bind-address = 127.0.0.1

6.) ctrl-x to exit, Y to save the file

7.) restart MySql

sudo /etc/init.d/mysql restart

Security note: This opens up your MySql server to anyone.  Please take all necessary precautions before making your LAMA server live on the internet. You can learn more about MySql security from the MySql web site.

Next in tutorial .. Create a MySql test site