1、配置mysql连接语句
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="MySqlCon" connectionString="Database=ticket;Data Source=localhost;Port=3306;UserId=root;Password=root;Charset=utf8;pooling=true;"/>
</connectionStrings>
</configuration>
2.编写sqlhelper工具类
其中要添加引用
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
namespace SqlHelper
{
public static class SqlHelper
{
//定义一个连接字符串
//readonly修饰的变量,只能在初始化的时候赋值,以及在构造函数中赋值
//其他地方只能读取不能设置值。
private static readonly string constr = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString;
//1.执行增(insert)、删(delete)、改(update) 的方法
//ExecuteNonQuery()
public static int ExecuteNonQuery(string sql,params MySqlParameter[] parms)
{
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
//2.执行查询,返回单个值的方法
//ExecuteScalar()
public static object ExecuteScalar(string sql, params MySqlParameter[] parms)
{
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
//3.执行查询 ,返回多行,多列的方法
//ExecuteReader()
public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] parms)
{
MySqlConnection con = new MySqlConnection(constr);
using (MySqlCommand cmd = new MySqlCommand(sql,con))
{
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
try
{
con.Open();
//system.Data.CommandBehavior.closeconnection这个枚举参数,表示将来使用完毕
//sqlDataReader后,在关闭reader的同时,在sqlDataReader内部会将关联的connection对象也关闭掉
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}catch
{
con.Close();
con.Dispose();
throw;
}
}
}
}
}
3.应用
string sql = "select count(*) from users where loginName=@loginName and loginPwd=@loginPwd";
MySqlParameter[] parms = new MySqlParameter[]
{
new MySqlParameter("@loginName",MySqlDbType.VarChar,50){Value=txtloginName.Text.Trim()},
new MySqlParameter("@loginPwd",MySqlDbType.VarChar,50){Value=txtPassword.Text.Trim()}
};
int r=Convert.ToInt32(SqlHelper.ExecuteScalar(sql,parms));
if (r > 0)
{
this.Text = "操作成功";
}
else
{
this.Text = "操作失败";
}