namespace Sql
{
    /// <summary>
    /// Sql 的摘要说明
    /// </summary>
    public class DataClass : IDisposable
    {
        private SqlConnection con = null;

        public void Open()
        {
            if (con == null)
            {
                con = this.getCon();
            }
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }
        }

        public SqlConnection getCon()
        {
            return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }

        public void Close()
        {
            if (con != null)
            {
                con.Close();
            }
        }

        public void Dispose()
        {
            con.Dispose();
            con = null;
            GC.SuppressFinalize(true);
        }

        public void Sql_NonQuery(string strSql)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                this.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public void Sql_NonQuery(string strSql, SqlConnection con1)
        {
            try
            {
                con1.Open();
                SqlCommand cmd = new SqlCommand(strSql, con1);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con1.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public void Sql_NonQuery(string strSql, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                this.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public void Sql_NonQuery_Pro(string strSql, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSql;
                cmd.Connection = con;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                this.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public void Sql_NonQuery_Pro(string strSql, string strRvl, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSql;
                cmd.Connection = con;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                cmd.ExecuteNonQuery();
                foreach (SqlParameter Item in paras)
                {
                    if (Item.Direction == System.Data.ParameterDirection.Output)
                    {
                        strRvl += Item.Value.ToString() + ",";
                    }
                }
                cmd.Dispose();
                this.Close();
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public SqlDataReader Sql_Reader(string strSql)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                return null;
            }
        }

        public SqlDataReader Sql_Reader(string strSql, SqlConnection con1)
        {
            try
            {
                con1.Open();
                SqlCommand cmd = new SqlCommand(strSql, con1);
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                return null;
            }
        }

        public SqlDataReader Sql_Reader(string strSql, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand(strSql, con);
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                return null;
            }
        }

        public SqlDataReader Sql_Reader_Pro(string strSql, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSql;
                cmd.Connection = con;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                return null;
            }
        }

        public SqlDataReader Sql_Reader_Pro(string strSql, SqlParameter[] paras, SqlConnection con1)
        {
            try
            {
                con1.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strSql;
                cmd.Connection = con1;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                SqlDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                return null;
            }
        }

        public object Sql_Scalar(string strSql)
        {
            SqlCommand cmd = null;
            try
            {
                this.Open();
                cmd = new SqlCommand(strSql, con);
                return (object)cmd.ExecuteScalar().ToString();
            }
            catch
            {
                return null;
            }
            finally
            {
                cmd.Dispose();
                this.Close();
            }
        }

        public object Sql_Scalar(string strSql, SqlConnection con1)
        {
            SqlCommand cmd = null;
            try
            {
                con1.Open();
                cmd = new SqlCommand(strSql, con1);
                return (object)cmd.ExecuteScalar().ToString();
            }
            catch
            {
                return null;
            }
            finally
            {
                cmd.Dispose();
                con1.Close();
            }
        }

        public DataSet Sql_DataSet(string strSql, string strTable)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand(strSql, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, strTable);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {

                this.Close();
            }
        }

        public DataSet Sql_DataSet(string strSql, string strTable, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = strSql;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, strTable);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }

        public DataSet Sql_DataSet_Pro(string strSql, string strTable, SqlParameter[] paras)
        {
            try
            {
                this.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                cmd.CommandText = strSql;
                cmd.Parameters.Clear();
                foreach (SqlParameter Item in paras)
                {
                    cmd.Parameters.Add(Item);
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, strTable);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }
    }
}