DBHelper(asp.net)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class DBHelper
{
    private static SqlConnection connection;

    private static string connectionString;

    private static SqlConnection Connection
    {
        get
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;

            if (connection == null)
            {
                connection = new SqlConnection(connectionString);
            }
            if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
            {
                connection.Open();
            }
            return connection;
        }
    }

    public static int ExecuteCommand(string safeSql)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        int result = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return result;
    }

    public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
    {
        //cmd.Parameters.Add(new SqlParameter("@age", 17));
        //SqlParameter[] valess = new SqlParameter[] {
        //    new SqlParameter("@age",17),
        //    new SqlParameter("@name","peiyi")
        //};

        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        cmd.Parameters.AddRange(values);
        int result = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return result;
    }
   
    public static object GetScalar(string safeSql)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        cmd.Connection.Close();
        return cmd.ExecuteScalar();
    }

    public static object GetScalar(string safeSql, params SqlParameter[] values)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        cmd.Parameters.AddRange(values);
        cmd.Connection.Close();
        return  cmd.ExecuteScalar();
    }

    public static SqlDataReader GetReader(string safeSql)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        cmd.Connection.Close();
        return reader;
    }

    public static SqlDataReader GetReader(string safeSql, params SqlParameter[] values)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        cmd.Parameters.AddRange(values);
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        cmd.Connection.Close();
        return reader;
    }

    public static DataSet GetDataSet(string safeSql)
    {
        DataSet ds = new DataSet();
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Connection.Close();
        da.Fill(ds);
        return ds;
    }

    public static DataTable GetDataTable(string safeSql, params SqlParameter[] values)
    {
        SqlCommand cmd = new SqlCommand(safeSql, Connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = safeSql;
        cmd.Parameters.AddRange(values);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        cmd.Connection.Close();
        return ds.Tables[0];
    }

    /*
    public static DataTable GetDataTable(string safeSql)
    {
        SqlDataReader reader = GetReader(safeSql);

        DataTable table = new DataTable();
        table.Load(reader);
        reader.Close();

        return table;
    }
     */
    //返回一个数据表
    public static DataSet Getcommodityiscomid(string sql)
    {
        SqlCommand cmd = new SqlCommand("select * from commodity where commodityid in ("+sql+")", Connection);
        string s = cmd.CommandText;
        SqlDataAdapter dr = new SqlDataAdapter(cmd);
        DataSet das = new DataSet();
        dr.Fill(das);
        cmd.Connection.Close();
        return das;
    }

}

 

 

 

配置文件中更改

<connectionStrings>
  <add name="myconn" connectionString="server=.;database=sevencolorcake;uid=sa;pwd=sa2005"/>
 </connectionStrings>