关于数据库操作的重复使用归类

web.config 文件

————————————————————————————

<configuration>
  <!--连接字符串-->
 <connectionStrings>
  <add name="sql2005" connectionString="Data Source=.;Initial catalog=MySchool;uid=sa;pwd=221221"/>
 </connectionStrings>
  
  
 <system.web>
  <compilation debug="true" targetFramework="4.0"/>
 </system.web>
</configuration>

————————————

————————————————————————————

 

类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
///DBHelper 的摘要说明
/// </summary>
public static class DBHelper
{

//  private readonly static String connStr = "Data Source=.\\sqlexpress;Initial catalog=ShoppingCart;User ID=sa;Password=221221";
    private static readonly String conStr=System.Configuration.ConfigurationManager.ConnectionStrings["sql2005"].ConnectionString;

    public static SqlConnection GetConnection
    {
        get{return new SqlConnection(conStr);}
    }

    //增、删、改
    public static int ExcuteCommand(String safeSql)
    {
        int result = 0;
        using (SqlConnection conn=GetConnection)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(safeSql, conn);
            result=cmd.ExecuteNonQuery();
        }
        return result;
    }
    //重载（增、删、改）带参数
    //public static int ExcuteCommand(String safeSql, params SqlParameter[] parm)//带params可变数组 
    public static int ExcuteCommand(String safeSql,  SqlParameter[] parm)//带params可变数组 
    {
        int result = 0;
        using (SqlConnection conn=GetConnection)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(safeSql, conn);
            cmd.Parameters.AddRange(parm);
            result = cmd.ExecuteNonQuery();
        }
        return result;
    }

    //返回第一行第一列
    public static Object GetScalar( string safeSql)
    {
        Object obj = null;
        using (SqlConnection conn=GetConnection)
        {

            conn.Open();
            SqlCommand cmd = new SqlCommand(safeSql, conn);
            obj = cmd.ExecuteScalar();
        }
        return obj;
    }

    //返回第一行第一列，带参数
    public static Object GetScalar(string safeSql,SqlParameter [] parm)
    {
        Object obj = null;
        using (SqlConnection conn = GetConnection)
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(safeSql, conn);
            cmd.Parameters.AddRange(parm);
            obj = cmd.ExecuteScalar();
        }
        return obj;
    }
    //返回结果集
    public static SqlDataReader GetDataReader(string safeSql)
    {
        SqlConnection conn = GetConnection;
        conn.Open();
        SqlCommand cmd = new SqlCommand(safeSql,conn);
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);//关闭Reader
    }

    //带参数,返回结果集
    public static SqlDataReader GetDataReader(string safeSql,SqlParameter[] parm)
    {
        SqlConnection conn = GetConnection;
        conn.Open();
        SqlCommand cmd = new SqlCommand(safeSql, conn);
        cmd.Parameters.AddRange(parm);
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }

 

    //查询表
    public static DataTable GetDataTable(string safesql)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(safesql, GetConnection);
        sda.Fill(dt);
        return dt;

    }
    //查询表带参数
    public static DataTable GetDataTable(string safesql,SqlParameter [] parm)
    {
        DataTable dt = new DataTable();
        SqlDataAdapter sda = new SqlDataAdapter(safesql, GetConnection);
        sda.SelectCommand.Parameters.AddRange(parm);
        sda.Fill(dt);
        return dt;
    }

}

 

————————————————————————————————————

后台操作！

   //不带参数
        DBHelper.ExcuteCommand("insert into Admin Values('A001','A001')");
        //带参数
        string sql = "insert into Admin values（@LoginID，@LoginPwd）";
        SqlParameter[] par =
        {
            new SqlParameter("@LoginID","A002"),
             new SqlParameter("@LoginPwd","A002"),
        };

        DBHelper.ExcuteCommand(sql, par);

        //可变数组 数组过多不适用
        //DBHelper.ExcuteCommand(sql,new SqlParameter("@LoginID", "A002"),new SqlParameter("@LoginPwd", "A002"));

例子

string sql = "select * from Student where StudentName like @StudentName";
        SqlParameter[] par =
        {
            new SqlParameter("@StudentName","%" + txtStuName.Text + "%"),
          
        };

        gvStudent.DataSource = DBHelper.GetDataTable(sql,par);
        gvStudent.DataBind();