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

    } }