locked
Confused about open and close connection RRS feed

  • Question

  • User448619955 posted

    HI there ,I'm developing a website and bout its connections I've confused.

    below are my code to open and execute queries please give me your advice ,thanks.

     protected void Page_Load(object sender, EventArgs e)
            {
                if(!IsPostBack)
                {
                    Commands cmd = new Commands();
                    Allusers = cmd.AllUsers();
                    using (DataSet ds = cmd.SelectReadyCampaign(User.Identity.Name))
                    {
                        if (ds.Tables.Count - 1 >= 0)
                        {
                            LSTCampaign.DataSource = ds.Tables[0];
                            LSTCampaign.DataBind();
                        }
                    }
    
                    using (DataSet ds = cmd.SelectCampaignReportByDay())
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            hidXCategories1 = hidXCategories1 + dr["ParticipatedDate"].ToString() + ",";
    
                        }
                        foreach (DataRow dr1 in ds.Tables[0].Rows)
                        {
                            hidValues1 = hidValues1 + dr1["participation"].ToString() + ",";
    
                        }
                    }
    
                    hidValues1 = hidValues1.TrimEnd(',');
                    hidXCategories1 = hidXCategories1.TrimEnd(',');
                    Ranks = new decimal[4];
                    Ranks=cmd.SelectUserRank(User.Identity.Name);
                    ReadyCampaign = cmd.SelectCountReadyCampaign();
                }
            }
    
    public class Commands : BasePage
    {
        DataSet DS;
    
        public Commands()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public int AllUsers()
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                Con.Open();
                cmd.Connection = Con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[UserAccount].[AllUsers]";
                cmd.Parameters.Add("@Allusers", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                int Exists = int.Parse(cmd.Parameters["@Allusers"].Value.ToString());
                Con.Close();
                return Exists;
            }
        }
        public DataSet SelectReadyCampaign(string UserName)
        {
            DataSet DS = new DataSet();
    
            using (SqlDataAdapter da = new SqlDataAdapter("[app].[SelectReadyCampaign]", Con))
            {
                Con.Open();
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@Username", UserName);
                da.Fill(DS, "table");
                Con.Close();
                return DS;
            }
        }
        public DataSet SelectCampaignReportByDay()
        {
            DataSet DS = new DataSet();
    
            using (SqlDataAdapter da = new SqlDataAdapter("[UserAccount].[SelectCampaignReportByDay]", Con))
            {
                Con.Open();
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
    
                da.Fill(DS, "table");
                Con.Close();
                return DS;
            }
        }
        public decimal[] SelectUserRank(string UserName)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                Con.Open();
                cmd.Connection = Con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[UserAccount].[SelectUserRank]";
                cmd.Parameters.AddWithValue("@UserName", UserName);
    
                cmd.Parameters.Add("@UserLevel", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@UserPoints", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@UserPotintP", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@UserRankPoint", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                decimal[] report = new decimal[4];
                 report[0] = decimal.Parse(cmd.Parameters["@UserLevel"].Value.ToString());
                 report[1] = decimal.Parse(cmd.Parameters["@UserPoints"].Value.ToString());
                 report[2] = decimal.Parse(cmd.Parameters["@UserPotintP"].Value.ToString());
                 report[3] = decimal.Parse(cmd.Parameters["@UserRankPoint"].Value.ToString());
                 Con.Close();
                 return report;
            }
        }
        public int SelectCountReadyCampaign()
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                Con.Open();
                cmd.Connection = Con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[App].[SelectCountReadyCampaign]";
                cmd.Parameters.Add("@Count", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                int Count = int.Parse(cmd.Parameters["@Count"].Value.ToString());
                Con.Close();
                return Count;
            }
        }
    }
    
    public class BasePage:System.Web.UI.Page
    {
        //public static Commands db = new Commands();
        public static SqlConnection Con;
        private string ConString;
        public static string MessageComment;
      
    	public BasePage()
    	{
            ConString = System.Configuration.ConfigurationManager.ConnectionStrings["FlyConnection"].ToString();
            Con = new SqlConnection(ConString);
            //Con.Close();
            //Con.Open();I opened my connection like this in the past.
         
    	}
    }

    in some cases I receive closed connection issue and other things,are my code above correct or they need to be fixed somehow.

    please give me your advise.

    thanks

    Thursday, July 16, 2015 5:19 AM

Answers

All replies