• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

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

原作者: [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;
}
}

鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
实战CENTOS6.5安装docker并创建asp.netmvc5镜像,运行MVC网站发布时间:2022-07-10
下一篇:
ASP.NET的ClientIDMode属性发布时间:2022-07-10
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap