dbhelp

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

namespace JINGRUI {     public static class DBHelper     {

        private static SqlConnection connection;         public static SqlConnection Connection         {             get             {                 string connectionString = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
                //string connectionString = "Data Source=localhost;Initial Catalog=TestLand; uid=sa;Password=123456;MultipleActiveResultSets = true;";                 connection = new SqlConnection(connectionString);                 if (connection == null)                 {                     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;             }         }

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

        public static int ExecuteCommand(string sql, params SqlParameter[] values)         {             using (SqlCommand cmd = new SqlCommand(sql, Connection))             {                 cmd.Parameters.AddRange(values);                 return cmd.ExecuteNonQuery();             }         }

        public static int GetScalar(string safeSql)         {             using (SqlCommand cmd = new SqlCommand(safeSql, Connection))             {                 int result = Convert.ToInt32(cmd.ExecuteScalar());                 return result;             }         }

        public static int GetScalar(params SqlParameter[] values)         {             using (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;             }         }

        public static int GetScalar(string sql, params SqlParameter[] values)         {             using (SqlCommand cmd = new SqlCommand(sql, Connection))             {                 cmd.Parameters.AddRange(values);                 int result = Convert.ToInt32(cmd.ExecuteScalar());                 return result;             }         }

        public static SqlDataReader GetReader(string safeSql)         {             using (SqlCommand cmd = new SqlCommand(safeSql, Connection))             {                 SqlDataReader reader = cmd.ExecuteReader();                 return reader;             }         }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)         {             using (SqlCommand cmd = new SqlCommand(sql, Connection))             {                 cmd.Parameters.AddRange(values);                 SqlDataReader reader = cmd.ExecuteReader();                 return reader;             }         }

        public static DataTable GetDataSet(string safeSql)         {             DataSet ds = new DataSet();             using (SqlCommand cmd = new SqlCommand(safeSql, Connection))             {                 SqlDataAdapter da = new SqlDataAdapter(cmd);                 da.Fill(ds);                 return ds.Tables[0];             }         }         //public static DataTable GetDataSet(string safeSql)         //{         //    DataSet ds = new DataSet();         //    using (SqlCommand cmd = new SqlCommand(safeSql, Connection))         //    {         //        SqlDataAdapter da = new SqlDataAdapter(cmd);         //        da.Fill(ds);         //        //cmd.Cancel         //        return ds.Tables[0];         //    }         //}

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)         {             DataSet ds = new DataSet();             using (SqlCommand cmd = new SqlCommand(sql, Connection))             {                 cmd.Parameters.AddRange(values);                 SqlDataAdapter da = new SqlDataAdapter(cmd);                 da.Fill(ds);                 return ds.Tables[0];             }         }     } }