[Asp.Net]自己的一个SqlHelper类
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;
}
}
