de: using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; namespace MyBookShop.DAL { public static class DBHelper { private static SqlConnection connection; /// <summary> /// 连接数据库 /// </summary> public static SqlConnection Connection { get { string connectionString = ConfigurationManager.ConnectionStrings["TourDB"].ConnectionString; if (connection == null) { connection = new SqlConnection(connectionString); connection.Open(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } else if (connection.State == System.Data.ConnectionState.Broken) { connection.Close(); connection.Open(); } return connection; } } /// <summary> /// 执行Sql命令 /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static int ExecuteCommand(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = cmd.ExecuteNonQuery(); return result; } /// <summary> /// 执行带参数的sql命令 /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static int ExecuteCommand(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); return cmd.ExecuteNonQuery(); } /// <summary> /// 执行sql语句,并返回第一行第一列的值。当查询时仅仅返回一条数据时使用。 /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static int GetScalar(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 执行带参数的存储过程,并返回第一行第一列的值。 /// </summary> /// <param name="values"></param> /// <returns></returns> public static int GetScalar(params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(); cmd.Connection = Connection; cmd.CommandText = "Pro_InsertOrder"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 根据sql语句和参数返回第一行第一列的值。 /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static int GetScalar(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } /// <summary> /// 根据sql语句执行查询返回sqlDataReader。 /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static SqlDataReader GetReader(string safeSql) { SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataReader reader = cmd.ExecuteReader(); return reader; } /// <summary> /// 根据sql语句和参数执行查询返回sqlDataReader /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataReader reader = cmd.ExecuteReader(); return reader; } /// <summary> /// 根据sql语句执行查询返回DataTable /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static DataTable GetDataSet(string safeSql) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(safeSql, Connection); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } /// <summary> /// 根据sql语句和参数执行查询返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static DataTable GetDataSet(string sql, params SqlParameter[] values) { DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(sql, Connection); cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); return ds.Tables[0]; } } }