System; using System.Configuration; using System.Data; using System.Data.SqlClient; namespace YZLibrary { class SqlHelper { private static readonly string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString; /// <summary> /// 执行非查询语句并返回受影响行数 /// </summary> /// <param name="sql"></param> /// <param name="pms"></param> /// <returns></returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] pms) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(pms); conn.Open(); return cmd.ExecuteNonQuery(); } } } /// <summary> /// 执行查询，并返回查询的结果集中的第一行第一列 /// </summary> /// <param name="sql"></param> /// <param name="pms"></param> /// <returns></returns> public static object ExecuteScalar(string sql, params SqlParameter[] pms) { using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(pms); conn.Open(); return cmd.ExecuteScalar(); } } } /// <summary> /// 执行查询，并返回SqlDataReader。 /// </summary> /// <param name="sql"></param> /// <param name="pms"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms) { SqlConnection conn = new SqlConnection(connStr); try { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(pms); conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } } catch (Exception ex) { conn.Dispose(); throw ex; } } /// <summary> /// 执行查询，返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="pms"></param> /// <returns></returns> public static DataTable ExecuteDataTable(string sql,params SqlParameter[] pms) { DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr)) { adapter.SelectCommand.Parameters.AddRange(pms); adapter.Fill(dt); } return dt; } } }

ExecuteReader

ExecuteScalar只执行一次，执行完后就可以把连接断开，但ExecuteReader要从数据库取好几次，所以连接要一直开着，直到数据取完了。
那怎么关掉连接，开始是想从外边关，但从外边关根本无法访问到函数里边的conn对象，没法儿关。
于是就用到了CommandBehavior.CloseConnection，在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭。

使用SQLHelper

            //ExecuteNonQuery 无参的
            string sql = "update LoginTest set pwd=1234 where uid=‘小明‘;";
            int res = SqlHelper.ExecuteNonQuery(sql);
            Console.WriteLine(res);
            Console.ReadKey();

            //ExecuteNonQuery 有参的
            string sql = "update LoginTest set pwd=@pwd where uid=@uid;";
            SqlParameter[] ps =
            {
                new SqlParameter("@pwd",1),//如果第二个参数为0，要用（object）强转，否则会默认为第二个构造函数。第二个构造函数，是枚举型，和第一个构造函数冲突
                new SqlParameter("@uid","小明"),
            };
            int res = SqlHelper.ExecuteNonQuery(sql, ps);
            Console.WriteLine(res);
            Console.ReadKey();  


            //ExecuteScalar 参数化模糊查询
            string sql = "select count(*) from LoginTest where uid like @uid";
            SqlParameter pUid = new SqlParameter("@uid", "杨%");
            int count = (int)SqlHelper.ExecuteScalar(sql, pUid);
            Console.WriteLine(count);
            Console.ReadKey();

            //ExecuteReader
            using (SqlDataReader reader = SqlHelper.ExecuteReader("select * from LoginTest"))
            {
                while (reader.Read())
                {
                    List<string> list = new List<string>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        list.Add(reader[i].ToString());
                    }
                    Console.WriteLine(string.Join(",", list));
                }
            }
            Console.ReadKey();

            //DataAdapter,这个要建winform程序
            string sql = "select * from LoginTest";
            dataGridView1.DataSource = SqlHelper.ExecuteDataTable(sql);

这是比较简单的SqlHelper。
还有比较完善的微软SQLHelper.cs类 中文版

SqlHelper

标签：