C#连接MySql数据库

最新推荐文章于 2023-09-03 13:16:22 发布

原创最新推荐文章于 2023-09-03 13:16:22 发布 · 1.9k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #c#

本文介绍了如何使用C#连接并操作MySQL数据库，包括封装工具类、实现模糊查询、级联删除、批量删除以及ExecuteReader方法的应用实例。

//建立连接
string connectStr = "server=127.0.0.1; User Id=root; password=; Database=studentdb;SslMode=none;Charset=utf8";
MySqlConnection conn = new MySqlConnection(connectStr);
//写查询语句，拼接式易sql注入不安全(or 1=1)，单引号易出错，推荐参数化sql
//string sql = "select count(1) from UserInfo where UserName = '" + uName + "' and UserPwd='" + uPwd + "'";
string sql = "select count(1) from UserInfo where UserName = @UserName and UserPwd = @UserPwd";
//添加参数
//MySqlParameter paraName = new MySqlParameter("@UserName",uName);
//MySqlParameter paraPwd = new MySqlParameter("@UserPwd",uPwd);
MySqlParameter[] para = { new MySqlParameter("@UserName", uName) , new MySqlParameter("@UserPwd", uPwd) };
//创建Command对象
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Clear();
//cmd.Parameters.Add(paraName);
//cmd.Parameters.Add(paraPwd);
cmd.Parameters.AddRange(para);
//cmd.CommandType = CommandType.StoredProcedure;//存储过程
//打开连接,最晚打开，最早关闭
conn.Open();
//执行查询命令
object o = cmd.ExecuteScalar();//返回第一行第一列的值
 //cmd.ExecuteNonQuery();//返回受影响的行数
//cmd.ExecuteReader();//返回数据流，相当于一个游标，读取到下一行数据时，上一行数据被抛弃               
 //关闭连接
conn.Close();

封装工具类：

<connectionStrings>
    <add name="connStr" connectionString="server=127.0.0.1; User Id=root; password=123456; Database=studentdb;SslMode=none;Charset=utf8" 
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>

public static class MySqlHelper
    {
        public static readonly string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        /// <summary>
        /// 查询，返回结果集中第一行的第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params MySqlParameter[] paras)
        {
            object o = null;
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                //创建Command对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Clear();
                //cmd.Parameters.Add(paraName);
                //cmd.Parameters.Add(paraPwd);
                cmd.Parameters.AddRange(paras);
                //cmd.CommandType = CommandType.StoredProcedure;//存储过程
                //打开连接,最晚打开，最早关闭
                conn.Open();
                //执行查询命令
                o = cmd.ExecuteScalar();  
            }

            return o;
        }

        /// <summary>
        /// 查询，返回数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params MySqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                //创建Command对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                if (paras != null)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(paras);
                }
                //MySqlDataAdapter断开式连接，自动隐式打开关闭连接
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                //自动生成MySqlCommand，适用于没有参数的sql语句
                //MySqlDataAdapter da = new MySqlDataAdapter(sql,conn);
                //MySqlDataAdapter打开和关闭连接
                da.Fill(dt);
            }

            return dt;
        }

        /// <summary>
        /// 执行查询，返回数据读取流
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] paras)
        {
            MySqlConnection conn = new MySqlConnection(connStr);
            
            try
            {
                conn.Open();
                //创建Command对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paras);
                //在MySqlDataReader关闭之后，关闭conn。在读取数据的过程中conn必须是打开的
                //离开using()区域，conn就关闭了，数据流读不到数据
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (MySqlException ex)
            {
                conn.Close();
                throw new Exception("执行查询出现异常",ex); ;
            }
        }

        /// <summary>
        ///  增、删、改  返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params MySqlParameter[] paras)
        {
            int count = 0;
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                //创建Command对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paras);
                //cmd.CommandType = CommandType.StoredProcedure;//存储过程
                //打开连接,最晚打开，最早关闭
                conn.Open();
                //执行查询命令
                count = cmd.ExecuteNonQuery();
            }

            return count;
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="comList"></param>
        /// <returns></returns>
        public static bool ExecuteTrans(List<CommandInfo> comList)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlTransaction trans = conn.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = trans;
                try
                {
                    int count = 0;
                    for (int i = 0; i < comList.Count; i++)
                    {
                        cmd.CommandText = comList[i].CommandText;
                        if (comList[i].IsProc)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            cmd.CommandType = CommandType.Text;
                        }
                        if (comList[i].Parameters.Length > 0)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(comList[i].Parameters);
                        }
                        count += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("执行事务出现异常");
                }
            }
        }
    }

模糊查询，返回数据表实例：

private void btnFind_Click(object sender, EventArgs e)
        {
            int classId = (int)cboClass.SelectedValue;
            string stuName = txtStuName.Text.Trim();
            string sql = "select StuId,StuName,ClassName,GradeName,Sex,Phone,CreateTime from studentinfo s, ClassInfo c ,gradeinfo g " +
                "where s.ClassId=c.ClassId  and c.GradeId = g.GradeId and s.IsDeleted = 0";
            //sql += " and 1=1";
            if (classId > 0)
            {
                sql += " and s.ClassId = @ClassId";
            }
            if (!string.IsNullOrEmpty(stuName))
            {
                sql += " and s.StuName like @StuName";
            }
            sql += " order by StuId";
            MySqlParameter[] paras = { new MySqlParameter("@ClassId", classId), new MySqlParameter("@StuName", "%" + stuName + "%") };
            DataTable dt = MySqlHelper.GetDataTable(sql, paras);
         }

级联删除，执行事务实例：

 //删除学生
string delStudent = "delete from StudentInfo where ClassId=@ClassId";

//删除班级
string delClass = "delete from ClassInfo where ClassId=@ClassId";
MySqlParameter[] para = { new MySqlParameter("@ClassId", classId) };

List<CommandInfo> listComs = new List<CommandInfo>();
CommandInfo comStudent = new CommandInfo()
{
	CommandText = delStudent,
	IsProc = false,
	Parameters = para
};
listComs.Add(comStudent);

CommandInfo comClass = new CommandInfo();
comClass.CommandText = delClass;
comClass.IsProc = false;
comClass.Parameters = para;
listComs.Add(comClass);

bool b1 = MySqlHelper.ExecuteTrans(listComs);

批量删除：

public class CommandInfo
    {
        public string CommandText;//sql或存储过程名
        public DbParameter[] Parameters;//参数列表
        public bool IsProc;//是否是存储过程
        public CommandInfo()
        {
        }
    }

//删除学生
string delStudent = "delete from StudentInfo where ClassId=@ClassId";

//删除班级
string delClass = "delete from ClassInfo where ClassId=@ClassId";

//循环删除每一条
List<CommandInfo> listComs = new List<CommandInfo>();
foreach (int id in listIds)
{
      MySqlParameter[] para = { new MySqlParameter("@ClassId", id) };
       CommandInfo comStudent = new CommandInfo()
       {
           CommandText = delStudent,
           IsProc = false,
           Parameters = para
        ;
        listComs.Add(comStudent);

        CommandInfo comClass = new CommandInfo();
        comClass.CommandText = delClass;
        comClass.IsProc = false;
        comClass.Parameters = para;
        listComs.Add(comClass);

}
bool b1 = MySqlHelper.ExecuteTrans(listComs);

ExecuteReader使用实例：

string sql = "select ClassName,GradeId,Remark from ClassInfo where ClassId = @ClassId";
MySqlParameter paraId = new MySqlParameter("@ClassId", classId);
MySqlDataReader dr = MySqlHelper.ExecuteReader(sql, paraId);
//Read()数据流前进到下一条记录，读一条，丢一条，相当于判断游标之后有没有数据
if (dr.Read())
{
    txtClassName.Text = dr["ClassName"].ToString();
    oldClassNmae = txtClassName.Text;
    txtRemark.Text = dr["Remark"].ToString();
    int gradeId = (int)dr["GradeId"];
    oldGradeId = gradeId;
    cboGrade.SelectedValue = gradeId;
}
//关闭数据流之后，conn自动关闭
 dr.Close();