欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

.net的基于mysql的DBhelper实现_MySQL

程序员文章站 2022-06-09 18:22:40
...
网上很少dbhelper的基于mysql数据库的,所以实现了下mysql的dbhelper,封装好了各种方法调用数据库函数,可以应用于小型的c#程序中。使用时记得修改password和端口号,mysql默认是3306,我自己定义了新端口号,所以是3307。

using System;using System.Collections.Generic;using System.Text;using System.Data;using MySql.Data.MySqlClient;namespace studentdb{    public class DBHelper    {        //引导数据库连接数据库调用Web.Config文件            private static MySqlConnection connection;        //创建连接        public static MySqlConnection Connection        {            get            {                MySqlConnection myConn = new MySqlConnection("Database=student;Data Source=localhost;User Id=root;Password=pw;pooling=false;CharSet=gb2312;port=3307");                string connectionString = myConn.ConnectionString;                if (connection == null)                {                    connection = new MySqlConnection(connectionString);                    //打开连接                    connection.Open();                }                else if (connection.State == System.Data.ConnectionState.Closed)                {                    connection.Open();                }                else if (connection.State == System.Data.ConnectionState.Broken)                {                    connection.Close();                    connection.Open();                }                return connection;            }        }        //(无参)返回执行的行数(删除修改更新)        public static int ExecuteCommand(string safeSql)        {            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);            int result = cmd.ExecuteNonQuery();            return result;        }        //(有参)        public static int ExecuteCommand(string sql, params MySqlParameter[] values)        {            MySqlCommand cmd = new MySqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            return cmd.ExecuteNonQuery();        }        //(无参)返回第一行第一列(删除修改更新)        public static int GetScalar(string safeSql)        {            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);            int result = Convert.ToInt32(cmd.ExecuteScalar());            return result;        }        //(有参)        public static int GetScalar(string sql, params MySqlParameter[] values)        {            MySqlCommand cmd = new MySqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            int result = Convert.ToInt32(cmd.ExecuteScalar());            return result;        }        //返回一个DataReader(查询)        public static MySqlDataReader GetReader(string safeSql)        {            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);            MySqlDataReader reader = cmd.ExecuteReader();            return reader;        }        public static MySqlDataReader GetReader(string sql, params MySqlParameter[] values)        {            MySqlCommand cmd = new MySqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            MySqlDataReader reader = cmd.ExecuteReader();            return reader;        }        //返回一个DataTable        public static DataTable GetDataSet(string safeSql)        {            DataSet ds = new DataSet();            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);            MySqlDataAdapter da = new MySqlDataAdapter(cmd);            da.Fill(ds);            return ds.Tables[0];        }        public static MySqlDataAdapter GetAdapter(string safeSql)        {            DataSet ds = new DataSet();            MySqlCommand cmd = new MySqlCommand(safeSql, Connection);            //cmd.Parameters.AddRange(values);            MySqlDataAdapter da = new MySqlDataAdapter(cmd);            return da;        }        public static DataTable GetDataSet(string sql, params MySqlParameter[] values)        {            DataSet ds = new DataSet();            MySqlCommand cmd = new MySqlCommand(sql, Connection);            cmd.Parameters.AddRange(values);            MySqlDataAdapter da = new MySqlDataAdapter(cmd);            da.Fill(ds);            return ds.Tables[0];        }    }}