1、使用navicat创建了三个存储过程:
pro_update_ip(不带形参)
pro_insert_user(带三个形参 textUser、textDevice、textTime 都是text类型)
pro_truncate_table(带一个形参 truncateType 是int类型)
2、C#调用代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//引入新类
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace serverManage.mysql.mysqlProce
{
class mysqlProce
{
/*
* Class:
* Method:
* parameter:
* Function:执行存储过程pro_update_ip,不带参数
*/
public static void writeProcePublicIp()
{
MySqlConnection conMysql = getMySqlCon();
MySqlCommand mySqlCommandPro = getSqlCommand("pro_update_ip", conMysql);//定义存储过程接口
mySqlCommandPro.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程
conMysql.Open();
mySqlCommandPro.ExecuteNonQuery();//执行存储过程
conMysql.Close();
}
/*
* Class:
* Method:
* parameter:
* Function:执行存储过程pro_inert_user,带参数textUser,textDevice,textTime
*/
public static void writeProceInsertUser(string strUser,string strDevice,string strTime)
{
try
{
MySqlConnection conMysql = getMySqlCon();
MySqlCommand mySqlCommandPro = getSqlCommand("pro_insert_user", conMysql);//定义存储过程接口
mySqlCommandPro.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程
MySqlParameter sqlParme;
//参数1
sqlParme = mySqlCommandPro.Parameters.AddWithValue("@textUser", "");
sqlParme.Direction = ParameterDirection.Input;
sqlParme.Value = strUser;
//参数2
sqlParme = mySqlCommandPro.Parameters.AddWithValue("@textDevice", "");
sqlParme.Direction = ParameterDirection.Input;
sqlParme.Value =strDevice;
//参数3
//sqlParme = mySqlCommandPro.Parameters.AddWithValue("@textTime", SqlDbType.NText);
sqlParme = mySqlCommandPro.Parameters.AddWithValue("@textTime", "");
sqlParme.Direction = ParameterDirection.Input;
sqlParme.Value = strTime;
conMysql.Open();
mySqlCommandPro.ExecuteNonQuery();//执行存储过程
conMysql.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/*
* Class:
* Method:
* parameter:
* Function:执行存储过程pro_truncate_table,带参数truncateType
*/
public static void writeProceTruncate(int strTruncateType)
{
try
{
MySqlConnection conMysql = getMySqlCon();
MySqlCommand mySqlCommandPro = getSqlCommand("pro_truncate_table", conMysql);//定义存储过程接口
mySqlCommandPro.CommandType = CommandType.StoredProcedure;//设置调用的类型为存储过程
MySqlParameter sqlParme;
//参数1
sqlParme = mySqlCommandPro.Parameters.AddWithValue("@truncateType", SqlDbType.Int);
sqlParme.Direction = ParameterDirection.Input;
sqlParme.Value = strTruncateType;
conMysql.Open();
mySqlCommandPro.ExecuteNonQuery();//执行存储过程
conMysql.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/*
* Class:
* Method: MySqlConnection
* parameter:
* Function: 建立mysql数据库链接
*/
public static MySqlConnection getMySqlCon()
{
MySqlConnection mysql = new MySqlConnection(DefineMysqlAddress.mysqlStr);
return mysql;
}
/*
* Class:
* Method: getSqlCommand
* parameter:
* Function: 建立执行命令语句对象
*/
public static MySqlCommand getSqlCommand(String sql, MySqlConnection mysql)
{
MySqlCommand mySqlCommand = new MySqlCommand(sql, mysql);
//MySqlCommand mySqlCommand = new MySqlCommand(sql);
//mySqlCommand.Connection = mysql;
return mySqlCommand;
}
}
}