C#使用DBHelper调用数据库查询

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using BeiQiMoSu;
using System.Collections.Generic;

namespace BeiQiMoSu
{
    public class SQLHelper
    {
        #region SqlConnection

        //从配置文件中获取连接字符串
        public static string conStr = "Data Source=;Initial Catalog=;Persist Security Info=True;User ID=sa;Password=";

        //创建连接对象
        private static SqlConnection con = null;

        //获取连接对象
        public static SqlConnection GetConnection()
        {
            if (con == null || con.ConnectionString == "")
            {
                con = new SqlConnection(conStr);
            }
            return con;
        }

        //打开 连接
        public static void OpenConnection()
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
        }
        //关闭连接
        public static void CloseConnection()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        //连接状态
        public static bool GetConnectionState()
        {
            if (con.State == ConnectionState.Closed)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion


        

        #region 查询
        
        // SQL查询转DataTable
        public static DataTable ExecuteDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = cmd;
                    sqlDA.Fill(dataSet);
                    DataTable dt = dataSet.Tables[0];
                    return dt;
                }
                catch (Exception ex)
                {
                    //throw new Exception();
                    throw ex;

                    // System.Exception:“Exception_WasThrown”
                }
            }
        }

        public static DataTable ExecuteDataTable(string sql, string conString)
        {
            using (SqlConnection conn = new SqlConnection(conString))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = sql;
                    DataSet dataSet = new DataSet();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = cmd;
                    sqlDA.Fill(dataSet);
                    DataTable dt = dataSet.Tables[0];
                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception();
                }
            }
        }

        //执行查询：返回多行多列
        public static SqlDataReader ExecuteReader(string sql,CommandType type,params SqlParameter[] para)
        {
            SqlConnection con = GetConnection();
            OpenConnection();
            SqlCommand com = new SqlCommand(sql, con);
            com.CommandType = type;
            com.Parameters.AddRange(para);
            SqlDataReader dr = com.ExecuteReader();
            return dr;
        }
        #endregion

        //动作查询：添加、修改、删除
        public static int ExecuteNonQuery(string sql)
        {
            SqlConnection con = GetConnection();
            OpenConnection();
            SqlCommand com = new SqlCommand(sql, con);
            int n = com.ExecuteNonQuery();
            CloseConnection();
            return n;
        }
    }
}

查询在使用时候直接调用：

string sql = "";
DataTable dt = SQLHelper.ExecuteDataTable(sql);

SQL里面写入SQL语句
删改在使用时候直接调用：

string sql = "  ";
int dt = SQLHelper.ExecuteNonQuery(sql);