MySQL Stored Procedure + C# Using ODBC

第一个存储过程

DELIMITER //
 CREATE PROCEDURE sproc_SelectUser()
   BEGIN
   SELECT * from users;
   END //
DELIMITER ;

 

第一个带参数的存储过程以及其C#调用

DELIMITER //
 CREATE PROCEDURE sproc_InsertUser(
   IN UserName varchar(250),
   IN Password varchar(250),
   IN Email varchar(200),
   IN UserType varchar(3))
     BEGIN
       INSERT INTO users (UserName,Password,Email,UserType)
    VALUE (UserName,Password,Email,UserType);
     END //
  DELIMITER ;

 

C#部分：

        // now execute the SQL to update the database
        OdbcConnection con = new OdbcConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        con.Open();

        OdbcCommand comm = new OdbcCommand();
        comm.Connection = con;
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.CommandText = "{call sproc_InsertUser (?,?,?,?)}";
        comm.Parameters.Add("UserName", OdbcType.NChar, 250).Value = tbxUserName.Text.Trim();
        comm.Parameters.Add("Password", OdbcType.NChar, 250).Value = tbxPassword.Text.Trim();
        comm.Parameters.Add("UserName", OdbcType.NChar, 200).Value = tbxEmailAddress.Text.Trim();
        comm.Parameters.Add("UserName", OdbcType.NChar, 3).Value = rbUserType.SelectedValue;

        comm.ExecuteNonQuery();

        con.Close();

 

第一个带有返回值的存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `sproc_InsertUser` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_InsertUser`(
   IN UserName varchar(250),
   IN Password varchar(250),
   IN Email varchar(200),
   IN UserType varchar(3),
   OUT NewID int)
BEGIN
  INSERT INTO users (UserName,Password,Email,UserType) VALUE (UserName,Password,Email,UserType);
    set NewID = LAST_INSERT_ID();
  select NewID;
END $$

DELIMITER ;

 

C#调用部分

        OdbcConnection con = new OdbcConnection(ConfigurationManager.AppSettings["ConnectionString"]);
        con.Open();
        OdbcCommand comm = new OdbcCommand("{call sproc_InsertUser(?,?,?,?,@a)}",con);

        comm.Parameters.Add("UserName", OdbcType.NChar, 250).Value = tbxUserName.Text.Trim();
        comm.Parameters.Add("Password", OdbcType.NChar, 250).Value = tbxPassword.Text.Trim();
        comm.Parameters.Add("Email", OdbcType.NChar, 200).Value = tbxEmailAddress.Text.Trim();
        comm.Parameters.Add("UserType", OdbcType.NChar, 3).Value = rbUserType.SelectedValue;

        OdbcDataReader myReader = comm.ExecuteReader();
        while (myReader.Read())
        {
            Session["sesUID"] = myReader.GetInt32(0);
        }
        myReader.Close();
        con.Close();