SqlHelper

数据库操作的时候，模式很固定。于是把这些操作都封装起来。 达到提供sql语句或参数，得到结果的目的。 我们把这个类库叫SqlHelper SqlHelper using System; using System.Configuration; using System.Data; using System.Data.SqlClient;namespace YZLibra

数据库操作的时候，模式很固定。于是把这些操作都封装起来。
达到提供sql语句或参数，得到结果的目的。
我们把这个类库叫SqlHelper

SqlHelper

<code class=" hljs cs"><span class="hljs-keyword">using</span> System;
<span class="hljs-keyword">using</span> System.Configuration;
<span class="hljs-keyword">using</span> System.Data;
<span class="hljs-keyword">using</span> System.Data.SqlClient;

namespace YZLibrary
{
    class SqlHelper
    {
        <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">readonly</span> <span class="hljs-keyword">string</span> connStr = ConfigurationManager.ConnectionStrings[<span class="hljs-string">"sql"</span>].ConnectionString;

        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> 执行非查询语句并返回受影响行数</span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"></summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="sql"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="pms"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><returns></span><span class="hljs-xmlDocTag"></returns></span></span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">int</span> <span class="hljs-title">ExecuteNonQuery</span>(<span class="hljs-keyword">string</span> sql, <span class="hljs-keyword">params</span> SqlParameter[] pms)
        {
            <span class="hljs-keyword">using</span> (SqlConnection conn = <span class="hljs-keyword">new</span> SqlConnection(connStr))
            {
                <span class="hljs-keyword">using</span> (SqlCommand cmd = <span class="hljs-keyword">new</span> SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(pms);
                    conn.Open();
                    <span class="hljs-keyword">return</span> cmd.ExecuteNonQuery();
                }
            }
        } 

        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> 执行查询，并返回查询的结果集中的第一行第一列</span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"></summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="sql"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="pms"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><returns></span><span class="hljs-xmlDocTag"></returns></span></span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">object</span> <span class="hljs-title">ExecuteScalar</span>(<span class="hljs-keyword">string</span> sql,  <span class="hljs-keyword">params</span> SqlParameter[] pms)
        {
            <span class="hljs-keyword">using</span> (SqlConnection conn = <span class="hljs-keyword">new</span> SqlConnection(connStr))
            {
                <span class="hljs-keyword">using</span> (SqlCommand cmd = <span class="hljs-keyword">new</span> SqlCommand(sql, conn))
                {

                    cmd.Parameters.AddRange(pms);
                    conn.Open();
                    <span class="hljs-keyword">return</span> cmd.ExecuteScalar();
                }
            }
        }

        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> 执行查询，并返回SqlDataReader。</span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"></summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="sql"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="pms"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><returns></span><span class="hljs-xmlDocTag"></returns></span></span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> SqlDataReader <span class="hljs-title">ExecuteReader</span>(<span class="hljs-keyword">string</span> sql, <span class="hljs-keyword">params</span> SqlParameter[] pms)
        {
            SqlConnection conn = <span class="hljs-keyword">new</span> SqlConnection(connStr);
            <span class="hljs-keyword">try</span>
            {
                <span class="hljs-keyword">using</span> (SqlCommand cmd = <span class="hljs-keyword">new</span> SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(pms);
                    conn.Open();
                    <span class="hljs-keyword">return</span> cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            <span class="hljs-keyword">catch</span> (Exception ex)
            {
                conn.Dispose();
                <span class="hljs-keyword">throw</span> ex;
            }
        }

        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> 执行查询，返回DataTable</span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"></summary></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="sql"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><param name="pms"></span><span class="hljs-xmlDocTag"></param></span></span>
        <span class="hljs-comment"><span class="hljs-xmlDocTag">///</span> <span class="hljs-xmlDocTag"><returns></span><span class="hljs-xmlDocTag"></returns></span></span>
        <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> DataTable <span class="hljs-title">ExecuteDataTable</span>(<span class="hljs-keyword">string</span> sql,<span class="hljs-keyword">params</span> SqlParameter[] pms)
        {
            DataTable dt = <span class="hljs-keyword">new</span> DataTable();
            <span class="hljs-keyword">using</span> (SqlDataAdapter adapter = <span class="hljs-keyword">new</span> SqlDataAdapter(sql, connStr))
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
                adapter.Fill(dt);
            }

            <span class="hljs-keyword">return</span> dt;
        } 
    }
}</code>

ExecuteReader

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

使用SQLHelper

<code class=" hljs cpp">            <span class="hljs-comment">//ExecuteNonQuery 无参的</span>
            <span class="hljs-built_in">string</span> sql = <span class="hljs-string">"update LoginTest set pwd=1234 where uid='小明';"</span>;
            <span class="hljs-keyword">int</span> res = SqlHelper.ExecuteNonQuery(sql);
            Console.WriteLine(res);
            Console.ReadKey();

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


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

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

            <span class="hljs-comment">//DataAdapter,这个要建winform程序</span>
            <span class="hljs-built_in">string</span> sql = <span class="hljs-string">"select * from LoginTest"</span>;
            dataGridView1.DataSource = SqlHelper.ExecuteDataTable(sql);</code>

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