[Asp.Net]自己的一个SqlHelper类

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

using System;
using System.Configuration;//
using System.Data;//
using System.Data.SqlClient;//

public class SqlHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader sdr = null;

        public SqlHelper()
        {
            /*
             * 先引用组件：System.configuration
             * 再using System.Configuration
             * 	修改节点：<connectionStrings><add name="connStr" connectionString="数据库连接字符串"/></connectionStrings>
             */
            string connstr =  ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; 
            conn = new SqlConnection(connstr);
        }
        public bool ss()
        {
            if (conn.State==ConnectionState.Open)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //打开conn
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        /// <summary>
        /// 该方法执行传入增删改SQL语句，返回受影响行数
        /// </summary>
        /// <param name="sql">增删改SQL语句</param>
        /// <returns>返回受影响行数</returns>
        public int ExecuteNonQuery(String sql)
        {
            int res;
            try
            {
                SqlCommand cmd = new SqlCommand(sql, GetConn());
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }

        /// <summary>
        ///  该方法执行传入增删改SQL语句，返回受影响行数(+1)
        /// </summary>
        /// <param name="cmdText">增删改SQL语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras)
        {
            int res;
            using (cmd = new SqlCommand(cmdText, GetConn()))
            {
                cmd.Parameters.AddRange(paras);
                res = cmd.ExecuteNonQuery();
            }
            return res;
        }
        /// <summary>
        /// 该方法执行传入的Sql查询语句
        /// </summary>
        /// <param name="sql">要执行的Sql查询语句</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteQuery(string sql)
        {
            DataTable dt = new DataTable();
            cmd = new SqlCommand(sql, GetConn());
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }

    }

下面是使用！！

使用


using System;
using System.Collections.Generic;

using System.Text;
using System.Data;//
using System.Data.SqlClient;//

public class TestQuestions
    {
private SqlHelper s = null;
public TestQuestions()
        {
            s = new SqlHelper();
        }


/// <summary>
/// 插入题目
/// </summary>
/// <param name="questionsclass">问题分类</param>
/// <param name="quesstions">问题题目</param>
/// <param name="answer1">答案1（正确答案）</param>
/// <param name="answer2">答案2</param>
/// <param name="answer3">答案3</param>
/// <param name="answer4">答案4</param>
/// <returns></returns>
        public bool Insert(string questionsclass, string questions, string answer1, string answer2, string answer3, string answer4)
        {
bool flag = false;
string sql = @"insert into t_testquestions values(@questionsclass,@questions,@answer1,@answer2,@answer3,@answer4)";
            SqlParameter[] paras = new SqlParameter[] { 
new SqlParameter("@questionsclass", questionsclass),
new SqlParameter("@questions", questions),
new SqlParameter("@answer1", answer1),
new SqlParameter("@answer2", answer2),
new SqlParameter("@answer3", answer3),
new SqlParameter("@answer4", answer4)
            };
int res = s.ExecuteNonQuery(sql, paras);
if (res > 0)
            {
                flag = true;
            }
return flag;
        }

/// <summary>
/// 该方法取出所有题目（按题目分类排序）
/// </summary>
/// <param name="classid">题库id</param>
/// <returns>返回DataTable</returns>
        public DataTable SelectAll_test(string classid)
        {
string sql = @"select * from t_testquestions where questionsclassid="+classid;
            DataTable dt = s.ExecuteQuery(sql);
return dt;
        }




/// <summary>
/// 该方法删除一条题目
/// </summary>
/// <param name="id">题目的id</param>
/// <returns></returns>
        public bool Delete(string id)
        {
bool flag = false;
string sql = @"delete t_testquestions where id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
new SqlParameter("@id", id)
            };
int res = s.ExecuteNonQuery(sql, paras);
if (res > 0)
            {
                flag = true;
            }
return flag;
        }
/// <summary>
/// 该方法更新指定id的题目
/// </summary>
/// <param name="id">题目的id</param>
/// <param name="questionsclass">问题分类</param>
/// <param name="quesstions">问题题目</param>
/// <param name="answer1">答案1（正确答案）</param>
/// <param name="answer2">答案2</param>
/// <param name="answer3">答案3</param>
/// <param name="answer4">答案4</param>
/// <returns>成功返回真</returns>
        public bool update(string id,string questionsclass, string questions, string answer1, string answer2, string answer3, string answer4)
        {
bool flag = false;
string sql = @"update t_testquestions set [questionsclass]=@questionsclass,[questions]=@questions,[answer1]=@answer1,[answer2]=@answer2,[answer3]=@answer3,[answer4]=@answer4 where id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
new SqlParameter("@id", id),
new SqlParameter("@questionsclass", questionsclass),
new SqlParameter("@questions", questions),
new SqlParameter("@answer1", answer1),
new SqlParameter("@answer2", answer2),
new SqlParameter("@answer3", answer3),
new SqlParameter("@answer4", answer4)
            };
int res = s.ExecuteNonQuery(sql, paras);
if (res > 0)
            {
                flag = true;
            }
return flag;
        }


public string idToQuestions(string id)
        {
string sql = @"select * from t_testquestions where id=" + id;
            DataTable dt = s.ExecuteQuery(sql);
return dt.Rows[0][2].ToString();
        }

//---类别管理


/// <summary>
/// 该方法取出所有题库分类
/// </summary>
/// <returns>返回DataTable</returns>
        public DataTable SelectAll_class()
        {
string sql = @"select * from t_questionsclass ";
            DataTable dt = s.ExecuteQuery(sql);
return dt;
        }

/// <summary>
/// 刚方法添加题库
/// </summary>
/// <param name="name">题库名</param>
/// <returns>成功返回真</returns>
        public bool Insert_Class(string name)
        {
bool flag = false;
if (!IsExists_class(name))
            {
string sql = @"insert into t_questionsclass values('" + name + "')";
int res = s.ExecuteNonQuery(sql);
if (res > 0)
                {
                    flag = true;
                }
            }

return flag;
        }


/// <summary>
/// 该方法删除一个题库
/// </summary>
/// <param name="id">题库id</param>
/// <returns></returns>
        public bool Delete_class(string id)
        {
bool flag = false;
string sql = @"delete t_questionsclass where id=@id";
            SqlParameter[] paras = new SqlParameter[] { 
new SqlParameter("@id", id)
            };
int res = s.ExecuteNonQuery(sql, paras);
if (res > 0)
            {
                flag = true;
            }
return flag;
        }

/// <summary>
/// 该方法判断传入题库名是否存在
/// </summary>
/// <param name="name">题库名</param>
/// <returns>存在返回true，不存在返回false</returns>
        public bool IsExists_class(string name)
        {
bool flag = false;
string sql = "select * from t_questionsclass where [classname]='" + name + "'";
            DataTable dt = s.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
            {
                flag = true;
            }
return flag;
        }
    }