SQLhelper总结
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data;
namespace MySqlHelper { public class Class1 { static string strconn = System.Configuration.ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
//-> 提供SqlParameter对象处理参数比较 //-> SqlParameter提供两个参数 //-> 参数别名 //-> 参数值 //-> SqlCommand提供一个Parameter属性 //-> 该属性表示SqlParameter的集合 //-> 使用Add方法添加(记得new) //-> AddWithValue方法 //-> 不使用要清空
/// <summary> /// 增删改(非查询)返回受影响行数 /// </summary> /// <param name="commandText">要执行的SQL语句</param> /// <param name="para">可变参数数组</param> /// <returns></returns> public static int ExecuteNonQuery(string commandText, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(strconn)) { using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } return cmd.ExecuteNonQuery(); } } }
/// <summary> /// 执行查询(返回首行首列) /// </summary> /// <param name="commandText">要执行的SQL语句</param> /// <param name="para">可变参数数组</param> /// <returns></returns> public static object ExecuteScalar(string commandText, params SqlParameter[] para) { using (SqlConnection conn = new SqlConnection(strconn)) { using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } return cmd.ExecuteScalar(); } } }
/// <summary> ///查询(返回DataReader对象) /// </summary> /// <param name="commandText">要执行的SQL语句</param> /// <param name="para">可变参数数组</param> /// <returns></returns> public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] para) { SqlConnection conn = new SqlConnection(strconn); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { if (para != null && para.Length != 0) { cmd.Parameters.AddRange(para); } if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } }
/// <summary> /// 抓取数据以表的形式存在于内存中 /// </summary> /// <param name="commandText">要执行的SQL语句</param> /// <param name="para">可变参数数组</param> /// <returns></returns> public static DataTable ExecuteDataTable(string commandText, params SqlParameter[] para) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(strconn)) { SqlDataAdapter sda = new SqlDataAdapter(commandText, conn); if (para != null && para.Length != 0) { sda.SelectCommand.Parameters.AddRange(para); } sda.Fill(dt); return dt; } }
/// <summary> /// 将数据库中的数据一次性提出放至DataSet /// </summary> /// <param name="commandText">要执行的SQL语句</param> /// <param name="para">可变参数数组</param> /// <returns></returns> public static DataSet DataAdapter(string commandText, params SqlParameter[] para) { DataSet ds = new DataSet(); using (SqlDataAdapter sda = new SqlDataAdapter(commandText, strconn)) { if (para != null && para.Length != 0) { sda.SelectCommand.Parameters.AddRange(para); } sda.Fill(ds); return ds; } }
} }