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; } }
