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 * 修改節(jié)點(diǎn)：<connectionStrings><add name="connStr" connectionString="數(shù)據(jù)庫連接字符串"/></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> /// 該方法執(zhí)行傳入增刪改SQL語句，返回受影響行數(shù) /// </summary> /// <param name="sql">增刪改SQL語句</param> /// <returns>返回受影響行數(shù)</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> /// 該方法執(zhí)行傳入增刪改SQL語句，返回受影響行數(shù)(+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> /// 該方法執(zhí)行傳入的Sql查詢語句 /// </summary> /// <param name="sql">要執(zhí)行的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> /// 該方法刪除一個(gè)題庫 /// </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; } }

轉(zhuǎn)載于:https://www.cnblogs.com/webdesign/archive/2011/05/26/tt116.html

總結(jié)

以上是生活随笔為你收集整理的[Asp.Net]自己的一个SqlHelper类的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。