Wassup 2008

Update: Palin Rap

Late Show w/ David Letterman - Sen John McCain, Full Interview

Part III: MySQL and .NET: Website

Startup Visual Web Developer and create a new website. I'm going to use C# but you can use VB if you want.

First, set the connection string

When you create the project you should get a web.config file, if not add a new one to your project. We're going to use this file to store the database connection string, we can hard code it but it is a good practice to store the string in the web.config file with the other project configurations.

We want to add the <connectionStrings> block to the <configuration> block, if it doesn't exist. So open web.config and under <configuration> add the following:

<configuration>
  ....
  <connectionstrings>
    <add name="DBMonkey" connectionstring="database=dbmonkey;server=localhost;user id=root; pwd=123;Persist Security Info=False;Connect Timeout=30" />
  </connectionstrings>
  .....
</configuraiton>

Replace the database name, server or root password to match what you have.

Second, add reference to MySQL.Data

• Click on Website from the main menu and Add Reference
• Under the .NET tab look for MySQL.Data
• Select it and click OK

Third, let's test the connection

We're going to test the connection so this is a one time thing and then we'll delete the code. Open the load function of the Default.aspx page and add the following:

protected void Page_Load(object sender, EventArgs e)
{
  MySql.Data.MySqlClient.MySqlConnection connection =
      new MySql.Data.MySqlClient.MySqlConnection(
          ConfigurationManager.ConnectionStrings["DBMonkey"].ToString());

  connection.Open();

  if (connection.State == ConnectionState.Open)
      Response.Write("Connected!");
  else
      Response.Write("Failed!");

  connection.Close();
}

Run the website and hopefully you'll get the "Connected!" message :)

Part II: MySQL and .NET: Database - updated

I created a database to do the testing, feel free to use it or create your own. The database name is dbmonkey so if you have a database with the same name then rename my database in the script below. You can download the SQL script from http://www.csmonkey.com/projects/dotnet/mysqlmonkey/dbmonkey.sql

The database has two tables and four six stored procedures.

Tables:

• bookmark: a bookmark has one category
  • bookmarkId integer, auto-increment, primary key
  • title varchar(50)
  • url varchar(100)
  • categoryId integer
    
• category: a category has one or more bookmarks
  • categoryId integer, auto-increment, primary key
  • title varchar(50)

Stored Procedures:

• bookmarkGet
  One parameter: categoryId.
  If the categoryId is zero then return all bookmarks, else return bookmarks for that category.
  
• bookmarkSave
  Four parameters: bookmarkId, title, url, categoryId.
  If the bookmarkId is zero then insert a new record, else update the existing record.
  
• bookmarkDelete
  One parameters: bookmarkId.
  Delete bookmark.
  
• categoryGet
  No parameters. Return all categories.
  
• categorySave
  Two parameters: categoryId, title.
  If the categoryId is zero then insert a new record, else update the existing record.
  
• categoryDelete
  One parameters: categoryId.
  Delete category.

You can restore the database from MySQL Administrator or the command line. MySQL Administrator is great to view and modify the database structure, very easy to use.

To restore the database from the command line follow these steps:

• Save the script to your desktop
• Go to Start > Run > type: cmd
• Type: cd Desktop
• Type: mysql -u root -p < dbmonkey.sql
• Enter your root password

Done! Close the window.

SQL script: http://www.csmonkey.com/projects/dotnet/mysqlmonkey/dbmonkey.sql

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version    5.0.67-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Create schema dbmonkey
--

CREATE DATABASE IF NOT EXISTS dbmonkey;
USE dbmonkey;

--
-- Definition of table `bookmark`
--

DROP TABLE IF EXISTS `bookmark`;
CREATE TABLE `bookmark` (
  `bookmarkId` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `url` varchar(100) NOT NULL,
  `categoryId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`bookmarkId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `bookmark`
--

/*!40000 ALTER TABLE `bookmark` DISABLE KEYS */;
INSERT INTO `bookmark` (`bookmarkId`,`title`,`url`,`categoryId`) VALUES
(1,'CSMonkey Blog','http://blog.csmonkey.com',1),
(2,'Google Calendar','http://calendar.google.com',2),
(3,'ASP.net','http://asp.net',3),
(4,'Google','http://google.com',2);
/*!40000 ALTER TABLE `bookmark` ENABLE KEYS */;

--
-- Definition of table `category`
--

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `categoryId` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY  (`categoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category`
--

/*!40000 ALTER TABLE `category` DISABLE KEYS */;
INSERT INTO `category` (`categoryId`,`title`) VALUES
(1,'CSMonkey'),
(2,'Google'),
(3,'Microsoft');
/*!40000 ALTER TABLE `category` ENABLE KEYS */;

--
-- Definition of procedure `bookmarkDelete`
--

DROP PROCEDURE IF EXISTS `bookmarkDelete`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bookmarkDelete`(IN in_bookmarkId INT)
BEGIN
  DELETE FROM bookmark
  WHERE bookmarkId = in_bookmarkId;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `bookmarkGet`
--

DROP PROCEDURE IF EXISTS `bookmarkGet`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bookmarkGet`(IN in_categoryId INT)
BEGIN

  IF in_categoryId = 0 THEN
    SELECT * FROM bookmark;

  ELSE
    SELECT *
    FROM bookmark
    WHERE categoryId = in_categoryId;
  END IF;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `bookmarkSave`
--

DROP PROCEDURE IF EXISTS `bookmarkSave`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bookmarkSave`(
  IN in_bookmarkId INT,
  IN in_title VARCHAR(50),
  IN in_url VARCHAR(100),
  IN in_categoryId INT
)
BEGIN

  IF in_bookmarkId = 0 THEN
    INSERT INTO bookmark (title, url, categoryId)
    VALUES (in_title, in_url, in_categoryId);

  ELSE
    UPDATE bookmark
    SET title = in_title,
        url = in_url,
        categoryId = in_categoryId
    WHERE bookmarkId = in_bookmarkId;
  END IF;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `categoryDelete`
--

DROP PROCEDURE IF EXISTS `categoryDelete`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `categoryDelete`(IN in_categoryId INT)
BEGIN

  DELETE FROM category
  WHERE categoryId = in_categoryId;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `categoryGet`
--

DROP PROCEDURE IF EXISTS `categoryGet`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `categoryGet`()
BEGIN
  SELECT *
  FROM category
  ORDER BY title ASC;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `categorySave`
--

DROP PROCEDURE IF EXISTS `categorySave`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `categorySave`(
  IN in_categoryId INT,
  IN in_title VARCHAR(50)
)
BEGIN

  IF in_categoryId = 0 THEN
    INSERT INTO category (title)
    VALUES (in_title);

  ELSE
    UPDATE bookmark
    SET title = in_title
    WHERE categoryId = in_categoryId;
  END IF;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Part I: MySQL and .NET

I always use SQL Server for .net application, web or desktop, they work very well together and provide a lot of tools that make my programming life easy. MySQL for PHP applications, they too work together nicely. But recently I tried to use MySQL with .net using MySQL .net connecter, it was very easy to connect to the database and manipulate the data.

I'm going to break this tutorial into several parts, each will talk about one component of MySQL connecter (library) and at the end I will combine everything in one working application, it'll be a web application but you can apply the same code to a desktop (windows forms) application.

So what do you need?

• Microsoft Visual Web Developer, I'm going to use 2008 but you can use 2005 if you want. http://www.asp.net/downloads/essential/
• MySQL http://dev.mysql.com/downloads/mysql/
• MySQL .net connecter http://dev.mysql.com/downloads/connector/net/
• MySQL GUI Tools, not necessary but makes my life easy :) http://dev.mysql.com/downloads/gui-tools/

That's pretty much it, download and install the apps and head to Part II.

SNL: VP Debate - Palin / Biden