.net的基于mysql的DBhelper实现

来源：互联网发布：知乎神经性耳鸣5年编辑：程序博客网时间：2024/04/30 02:52
网上很少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];        }    }}
0 0