SQLHelper类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;


namespace SQLPA.MyClass
{
    public static class SQLHelper
    {
        //从config中获取链接字符串
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["sqlconn"].ToString();


        //获得connection对象
        public static SqlConnection GetConn()
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            return conn;
        }


       


        //获得DataTable
        public static DataTable GetDataTable(string sqlStr)
        {
            using(SqlConnection conn=SQLHelper.GetConn())
             {
                 using(SqlCommand cmd=new SqlCommand(sqlStr,conn))
                   {
                     using(SqlDataAdapter adapter=new SqlDataAdatper(cmd))
                      {
                           DataTable tb = new DataTable();
                           adapter.Fill(tb);
                           return tb;
                      }
                   }
             }
         }


       //执行非查询操作
        public static void ExecuteNonQuery(string sqlStr)
        {
            using(SqlConnection conn=MyClass.SQLHelper.GetConn())
              {
                 using(SqlCommand cmd=new SqlCommand(sqlStr,conn))
                   {
                      cmd.ExecuteNonQuery();
                    }
               }
            
        }


      


        //执行查询操作，返回受影响的行数
        public static int GetRowsNum(string sqlStr)
        {
            using(SqlConnection conn=SQLHelper.GetConn())
              {
                 using(SqlCommand cmd=new SqlCommand(sqlStr,conn))
                   {
                      using(SqlDataAdapter adapter=new SqlDataAdapter(cmd))
                        {
                            DataTable tb=new DataTable();
                            adapter.Fill(tb);
                            return tb.Rows.Count;
                         }
                    }
               }
         }


        //执行非查询语句操作，返回受影响的行数
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = ExecuteConn())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }


        
        //获得DataTable
        public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
        {
            using (SqlConnection conn =ExecuteConn())
            {
                
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                       
                        DataTable tb = new DataTable();
                        adapter.Fill(tb);
                       
                        conn.Close();
                        return tb;
                    }
                }
            }
        }


        //获得DataTable
        public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText, params SqlParameter[] parameters)
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.Parameters.AddRange(parameters);
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    DataTable tb = new DataTable();
                    adapter.Fill(tb);
                    conn.Close();
                    return tb;
                }
            }
        }
        
        //获得SqlDataReader
        public static SqlDataReader ExecuteDataReader(string cmdText, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = ExecuteConn())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteReader();
                
                }
            }
        }


        //获得返回结果的第一行第一列的数据
        public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = ExecuteConn())
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }


        //将从数据库表中的NULL，转换为本地程序的null
        public static object FromDB(object value)
        {
            if (value == DBNull.Value)
                return null;
            else
                return value;
        }


        //将本地程序的null转换成数据库中的NULL
        public static object ToDB(object value)
        {
            if (value == null)
                return DBNull.Value;
            else
                return value;
        }


    }
}