All posts tagged database

1. Download and install:
MySQL Connector/ODBC 3.51

2. In the ODBC Administrator, under “System DSN” click Add then select the MySQL driver

3. On the next screen enter the information to connect to your MySQL database

4. In Visual Studio 2005, Select the Server Tab

5. Right-click on “Data Connections” and select “Add Connection”

6. Click the ‘change” button

7. Select “Microsoft ODBC Data Source”

8. Select your ODBC entry you created in the earlier step

9. You can now use the query designer in Visual Studio

In this tutorial we use the previous tutorial form. And modify the c# code to insert data in mysql using store procedure. Normally we use phpmyadmin. But using phpmyadmin we do not see the storeprocedure. To create storeprocedure we have to use MySQL Administrator. You can download mysql-gui-tools-5.0-r17-win32.msi from mySql site.

Here we just give the save button code

 try
            {
                // Connection string for a typical local MySQL installation
                string cnnString = "Server=localhost;Port=3306;Database=ci_series;Uid=root;Pwd=";

                // Create a connection object 
                MySqlConnection connection = new MySqlConnection(cnnString);
              
                // call store procedure
                MySqlCommand cmd = new MySqlCommand("insert_membership", connection);

             
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("?first_name", MySqlDbType.VarChar).Value = txtFirstName.Text;
                cmd.Parameters.Add("?last_name", MySqlDbType.VarChar).Value = txtLastName.Text;
                cmd.Parameters.Add("?username", MySqlDbType.VarChar).Value = txtUserName.Text;
                cmd.Parameters.Add("?password", MySqlDbType.VarChar).Value = txtPassword.Text;
                cmd.Parameters.Add("?email_address", MySqlDbType.VarChar).Value = txtEmail.Text;

                connection.Open();

                int result = cmd.ExecuteNonQuery();

                lblError.Text = "Data Saved";

            }
            catch (Exception ex)
            {
                lblError.Text = ex.Message;
            }

Create a store procedure using MySQL Administrator. The code is bellow.

DELIMITER $$

DROP PROCEDURE IF EXISTS `insert_membership` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_membership`(

IN first_name varchar(25),
IN last_name varchar(25),
IN username varchar(25),
IN password varchar(10),
IN email_address varchar(50)


)
BEGIN

INSERT INTO membership (
first_name,
last_name,
username,
password,
email_address
)

VALUES(
first_name,
last_name,
username,
password,
email_address

) ;

END $$

DELIMITER ;

Here is a screen shot of the store procedure inside mysql administrator.

After insert data using store procedure you can see the successful message like bellow.

To learn more about store procedure you can read Stored Procedure Parameters and
MySQL Stored Procedure – INSERT – Example

Bellow is the link to download the sample code used in this tutorial.

Introduction
MySQL is one of the most popular open source databases available. With the release of version 5.0, MySQL adds new features, such as stored procedures, triggers, and user-defined functions, making it more comparable to commercial database server systems. Many Web hosting companies offer MySQL, and it is a compelling solution as well as a great way to learn SQL.

In this series of articles, I want to introduce you to using MySQL 5.0 with ASP.NET. I hope to cover the most common uses for MySQL, as well as take advantage of some of the new features in version 5.0. To do this, we will look at creating a database of Bible verses and ASP.NET pages to view and manage the verses.
Tools Needed
To begin development, you will need to have the following tools.
Continue reading →