连接没有关闭。连接的当前状态是打开的。 [英] The connection was not closed. The connection's current state is open.

问题描述

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;


namespace BI
{
    [Serializable]
    public class UserRegisterClass
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ToString());

        public void saveUser(UserProperties OBJ)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into alluser(role_id,first_name,last_name,username,password,email,contact)values('" + OBJ.USER_ROLE + "','" + OBJ.FIRST_NAME + "','" + OBJ.LAST_NAME + "','" + OBJ.USERNAME + "','" + OBJ.PASSWORD + "','" + OBJ.EMAIL + "','" + OBJ.CONTACT + "')", con);
            int res = cmd.ExecuteNonQuery();
            if (res > 0)
            {
                HttpContext.Current.Response.Write("<script>alert('User Added Successfully');</script>");
            }
            else
            {
                HttpContext.Current.Response.Write("<script>alert('User Not Added');</script>");
            }
            con.Close();
        }

        public UserProperties[] getUser()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];
            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }

            return UP;
        }

        public UserProperties getUserlogin(string uname, string pass)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser where username='" + uname + "' and password='" + pass + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.ID = dt.Rows[i].ItemArray[0].ToString();
                UP.USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP.FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP.LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP.USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP.PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP.EMAIL = dt.Rows[i].ItemArray[2].ToString();
                UP.CONTACT = dt.Rows[i].ItemArray[2].ToString();
            }
            return UP;
        }

        public UserProperties[] getAllUser(string Id)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser where role_id= '" + Id + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];
            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }

            return UP;

        }

        public UserProperties getUsername(string name)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select username from alluser where username= '" + name + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.USERNAME = dt.Rows[i].ItemArray[0].ToString();
            }

            return UP;

        }

        public UserProperties[] getUsername1(string uname)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from  alluser where username='" + uname + "' ", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];
            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }
            return UP;

        }

        public UserProperties getLetestUser()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("select max(Id) from dbo.alluser", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.ID = dt.Rows[i].ItemArray[0].ToString();
                /*UP.USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP.FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP.LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP.USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP.PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP.EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP.CONTACT = dt.Rows[i].ItemArray[7].ToString();*/
            }

            return UP;
        }
    }

    [Serializable]
    public class UserProperties
    {
        public string ID { get; set; }
        public string USER_ROLE { get; set; }
        public string FIRST_NAME { get; set; }
        public string LAST_NAME { get; set; }
        public string USERNAME { get; set; }
        public string PASSWORD { get; set; }
        public string EMAIL { get; set; }
        public string CONTACT { get; set; }
    }
}

推荐答案

Quote：

连接未关闭。连接的当前状态是打开的。

The connection was not closed. The connection's current state is open.

非常清楚。您需要在完成后立即关闭连接，以便下一个Command可以使用Connection。



您无法打开连接。还建议使用使用块初始化连接，以便在块执行后，连接将被关闭并自动处理。

Quite clear. You need to close the connection as soon as you are done so that next Command can use the Connection.

You can't have an opened connection. It is also suggested to use using blocks to initialize the connections, so that after the block is executed, connection will be closed and disposed automatically.

总是写try catch finally块。

最后你写con.Close（）;

所以即使你的数据库插入失败，连接也会被关闭。 br />
编写代码的最佳方法。

Always write try catch finally block.
In finally u write con.Close();
so than even your database insertion gets failed still connection gets closed.
Best way to write the code.

请尝试下面的代码来解决连接状态问题。

我希望它能帮助满对你而言。

Please try below code to resolve your connection state problem.
I hope it will help full for you.

using System;
using System.Collections.Generic;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace FillzAndEbayUkSKUDelister
{
    [Serializable]
    public class UserRegisterClass
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ToString());

        public void saveUser(UserProperties OBJ)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("insert into alluser(role_id,first_name,last_name,username,password,email,contact)values('" + OBJ.USER_ROLE + "','" + OBJ.FIRST_NAME + "','" + OBJ.LAST_NAME + "','" + OBJ.USERNAME + "','" + OBJ.PASSWORD + "','" + OBJ.EMAIL + "','" + OBJ.CONTACT + "')", con);
            int res = cmd.ExecuteNonQuery();
            if (res > 0)
            {
                HttpContext.Current.Response.Write("<script>alert('User Added Successfully');</script>");
            }
            else
            {
                HttpContext.Current.Response.Write("<script>alert('User Not Added');</script>");
            }
            con.Close();
        }

        public UserProperties[] getUser()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];
            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }

            return UP;
        }

        public UserProperties getUserlogin(string uname, string pass)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser where username='" + uname + "' and password='" + pass + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.ID = dt.Rows[i].ItemArray[0].ToString();
                UP.USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP.FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP.LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP.USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP.PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP.EMAIL = dt.Rows[i].ItemArray[2].ToString();
                UP.CONTACT = dt.Rows[i].ItemArray[2].ToString();
            }
            return UP;
        }

        public UserProperties[] getAllUser(string Id)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("select * from alluser where role_id= '" + Id + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];

            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }
            return UP;

        }

        public UserProperties getUsername(string name)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("select username from alluser where username= '" + name + "'", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.USERNAME = dt.Rows[i].ItemArray[0].ToString();
            }

            return UP;

        }

        public UserProperties[] getUsername1(string uname)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("SELECT * from  alluser where username='" + uname + "' ", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties[] UP = new UserProperties[count];
            for (int i = 0; i < count; i++)
            {
                UP[i] = new UserProperties();
                UP[i].ID = dt.Rows[i].ItemArray[0].ToString();
                UP[i].USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP[i].FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP[i].LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP[i].USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP[i].PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP[i].EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP[i].CONTACT = dt.Rows[i].ItemArray[7].ToString();
            }
            return UP;

        }

        public UserProperties getLetestUser()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Open();
            SqlCommand cmd = new SqlCommand("select max(Id) from dbo.alluser", con);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            int count = dt.Rows.Count;
            UserProperties UP = new UserProperties();
            for (int i = 0; i < count; i++)
            {
                UP = new UserProperties();
                UP.ID = dt.Rows[i].ItemArray[0].ToString();
                /*UP.USER_ROLE = dt.Rows[i].ItemArray[1].ToString();
                UP.FIRST_NAME = dt.Rows[i].ItemArray[2].ToString();
                UP.LAST_NAME = dt.Rows[i].ItemArray[3].ToString();
                UP.USERNAME = dt.Rows[i].ItemArray[4].ToString();
                UP.PASSWORD = dt.Rows[i].ItemArray[5].ToString();
                UP.EMAIL = dt.Rows[i].ItemArray[6].ToString();
                UP.CONTACT = dt.Rows[i].ItemArray[7].ToString();*/
            }

            return UP;
        }
    }

    [Serializable]
    public class UserProperties
    {
        public string ID { get; set; }
        public string USER_ROLE { get; set; }
        public string FIRST_NAME { get; set; }
        public string LAST_NAME { get; set; }
        public string USERNAME { get; set; }
        public string PASSWORD { get; set; }
        public string EMAIL { get; set; }
        public string CONTACT { get; set; }
    }
}

这篇关于连接没有关闭。连接的当前状态是打开的。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！