封装类 .NET适用 存储过程


using
System; using System.Collections.Generic; using System.Linq; using System.Web; using MySql.Data.MySqlClient; using System.Configuration; using System.Data; /** *创建人:LK *说明:数据库封装类 */ namespace MySql { public class MySQlHelper { private MySqlConnection conn = null; private MySqlCommand cmd = null; private MySqlDataReader sdr; private MySqlDataAdapter sda = null; public MySQlHelper() { string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; //获取MySql数据库连接字符串 conn = new MySqlConnection(connStr); //数据库连接 } #region /// <summary> /// 打开数据库链接 /// </summary> /// <returns></returns> #endregion private MySqlConnection GetConn() { if(conn.State== ConnectionState.Closed) { conn.Open(); } return conn; } #region /// <summary> /// 关闭数据库链接 /// </summary> #endregion private void GetConnClose() { if (conn.State == ConnectionState.Open) { conn.Close(); } } #region /// <summary> /// 执行不带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="cmdText">增删改SQL语句或存储过程的字符串</param> /// <param name="ct">命令类型</param> /// <returns>受影响的函数</returns> #endregion public int ExecuteNonQuery(string cmdText,CommandType ct) { int res; using(cmd = new MySqlCommand(cmdText,GetConn())) { cmd.CommandType = ct; res = cmd.ExecuteNonQuery(); } return res; } #region /// <summary> /// 执行带参数的增删改SQL语句或存储过程 /// </summary> /// <param name="cmdText">增删改SQL语句或存储过程的字符串</param> /// <param name="paras">往存储过程或SQL中赋的参数集合</param> /// <param name="ct">命令类型</param> /// <returns>受影响的函数</returns> #endregion public int ExecuteNonQuery(string cmdText,MySqlParameter[] paras,CommandType ct) { int res; using(cmd = new MySqlCommand(cmdText,GetConn())) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); res = cmd.ExecuteNonQuery(); } return res; } #region /// <summary> /// 执行不带参数的查询SQL语句或存储过程 /// </summary> /// <param name="cmdText">查询SQL语句或存储过程的字符串</param> /// <param name="ct">命令类型</param> /// <returns>查询到的DataTable对象</returns> #endregion public DataTable ExecuteQuery(string cmdText,CommandType ct) { DataTable dt = new DataTable(); cmd = new MySqlCommand(cmdText,GetConn()); cmd.CommandType = ct; using(sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } return dt; } #region /// <summary> /// 执行带参数的查询SQL语句或存储过程 /// </summary> /// <param name="cmdText">查询SQL语句或存储过程的字符串</param> /// <param name="paras">参数集合</param> /// <param name="ct">命令类型</param> /// <returns></returns> #endregion public DataTable ExecuteQuery(string cmdText,MySqlParameter[] paras,CommandType ct) { DataTable dt = new DataTable(); cmd = new MySqlCommand(cmdText,GetConn()); cmd.CommandType = ct; cmd.Parameters.AddRange(paras); using(sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } return dt; } #region /// <summary> /// 执行指定数据库连接字符串的命令,返回DataSet. /// </summary> /// <param name="strSql">一个有效的数据库连接字符串</param> /// <returns>返回一个包含结果集的DataSet</returns> #endregion public DataSet ExecuteDataset(string strSql) { DataSet ds = new DataSet(); sda = new MySqlDataAdapter(strSql,GetConn()); try { sda.Fill(ds); } catch (Exception ex) { throw ex; } finally { GetConnClose(); } return ds; } } }
在举例子之前必须懂得两种概念

                ExecuteNonQuery,执行非查询类SQL语句或存储过程,也就是我们通常说的增、删、改语句,并返回受影响的行数。

                ExecuteReader,执行查询类SQL语句或存储过程,用于返回SqlDataReader对象,该对象包含由某一命令返回的结果集。




1、带参数的查询例子

public DataTable SelectByNewsId(string Id)  
            {  
                MySQlHelper h = new MySQlHelper();
                DataTable dt = new DataTable();  
                string sql = "select * from comment where newsId=@Id order by createTime desc";  
                SqlParameter[] paras = new SqlParameter[]  
                {  
                     new SqlParameter ("@newsId",Id )  
                };  
                dt = h.ExecuteQuery(sql, paras, CommandType.Text);  
                return dt;  
            }  

2、不带参数的查询例子

public DataTable SelectByNewsId(string Id)  
            {  
                MySqlHelper h = new MyHelper();
                DataTable dt = new DataTable();  
                string sql = "select * from comment where newsId=‘Id‘ order by createTime desc";  
                dt = sqlhelper.ExecuteQuery(sql,CommandType.Text);  
                return dt;  
            }  

3、带参数的增、删、改操作例子

 public Boolean Delete(stringId)  //插入、修改也如此
            {  
                Boolean flag = false;  
                MyHelper h = new MyHelper();
                string sql = "delete from comment where newsId=@Id";  
                SqlParameter[] paras = new SqlParameter[]  
                {  
                    new SqlParameter ("newsId",Id )  
      
                };  
                int res = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);  
                if (res > 0)  
                {  
                    flag = true;  
                }  
                return flag;  
            }  

4、不带参数的增、删、改操作的例子

public Boolean Delete(string Id)  //插入、修改也如此
            {  
                Boolean flag = false;  
                MyHelper h = new MyHelper();
                string sql = "delete from comment where newsId=‘Id‘";  
                int res = sqlhelper.ExecuteNonQuery(sql,  CommandType.Text);  
                if (res > 0)  
                {  
                    flag = true;  
                }  
                return flag;  
            }  

 

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。