Need IDispose method to update database_问答_开发者

I guess I need an alternate method to opening and closing the database connection because I get an error which locks up IIS6. Thanks for the help.

From what I have read, I need the IDispose method to do开发者_如何学C the db update from XML

I'm getting this error message:

StackTrace: at System.Data.ProviderBase.DbConnectionInternal.PrePush(Object expectedOwner)
at System.Data.ProviderBase.DbConnectionPool.PutObject(DbConnectionInternal obj, Object owningObject)
at System.Data.ProviderBase.DbConnectionInternal.CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlInternalConnection.CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Close()
at ASP.chat7_handlers_broadcasting_handler_aspx.XML.Finalize() in g:\blah.handler.aspx:line 73

Code:

<%@ Page language="c#" %>
<%@ Import namespace="System.Net"%>
<%@ Import namespace="System.Data"%>
<%@ Import namespace="System.Data.SqlClient"%>
<%@ Import namespace="System.IO"%>
<%@ Import namespace="System.Xml"%>

<script runat="server" language="C#">

    public class XML
    {
        SqlConnection dbConn;

        public XML()
        {
            dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
            dbConn.Open();
        }

        public void add2Db(string table, string sqlRows, string sqlValues)
        {
            string sql = "INSERT INTO " + table + " (" + sqlRows + ") VALUES (" + sqlValues + ")";
            SqlCommand cmd = new SqlCommand(sql, dbConn);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }

        public void parseXML(XmlDocument doc)
        {
            string tsLogpro = "";
            string sqlRows = "";
            string sqlValues = "";

            //SELECT NODE: logPro
            foreach (XmlNode logPro in doc.SelectNodes("logPro"))
            {
                tsLogpro = logPro.SelectSingleNode("@ts").InnerText;

                //SELECT CHILD NODE: logPro
                foreach (XmlNode child in logPro.ChildNodes)
                {
                    //SELECT ALL CHILD NODES
                    foreach (XmlNode node in logPro.SelectNodes(child.Name))
                    {
                        //GET ROWS
                        foreach (XmlNode rows in child.Attributes)
                        {
                            sqlRows += rows.Name + ", ";
                        }

                            foreach (XmlNode nodeChild in node)
                            {
                                sqlRows += nodeChild.Name + ", ";
                            }


                        //GET VALUES
                        foreach (XmlNode values in child.Attributes)
                        {
                            sqlValues += "'" + values.InnerText + "', ";
                        }

                            foreach (XmlNode nodeChild in node)
                            {
                                sqlValues += "'" + nodeChild.InnerText + "', ";
                            }

                        sqlRows = "action, " + sqlRows;
                        sqlRows = sqlRows.Substring(0, sqlRows.Length - 2);

                        sqlValues = "'" + child.Name + "', " + sqlValues;
                        sqlValues = sqlValues.Substring(0, sqlValues.Length - 2);

                        //Response.Write("\n\n");
                        //Response.Write(sqlRows);
                        //Response.Write("\n" + sqlValues);

                        add2Db("fchat7_logpro", sqlRows, sqlValues);

                    }

                    sqlValues = "";
                    sqlRows = "";
                }
            }
        }

        ~XML()
        {
            dbConn.Close();
        }
    }


    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Clear();
        Response.ExpiresAbsolute = DateTime.Now;
        Response.AddHeader("Content-type", "text/plain");

        HttpRequest request = HttpContext.Current.Request;
        System.IO.Stream body = request.InputStream;
        System.Text.Encoding encoding = request.ContentEncoding;
        System.IO.StreamReader reader = new System.IO.StreamReader(body, encoding);
        XmlDocument doc = new XmlDocument();

        string s = reader.ReadToEnd();

        if (Request.Params["action"] != string.Empty && Request.Params["action"] == "test")
        {
            doc.InnerXml = "" +
                "<logPro ts=\"1283351509\">" +
                    "<autoLogin uid=\"d5b0ef4c8c51f303ecbaed81a6e078c5\" hasCam=\"false\" ip=\"127.0.0.1\" ts=\"1283351406\" />" +
                    "<chatLogin hasCam=\"false\" ip=\"127.0.0.1\" ts=\"1283352053\">" +
                        "<userName>Guest</userName>" +
                    "</chatLogin>" +
                    "<adminLogin hasCam=\"false\" ip=\"127.0.0.1\" ts=\"1283352053\">" +
                        "<userName>admin</userName>" +
                    "</adminLogin>" +
                    "<guestLogin hasCam=\"false\" ip=\"127.0.0.1\" ts=\"1283352020\">" +
                        "<userName>guest</userName>" +
                    "</guestLogin>" +
                    "<loginResult ip=\"127.0.0.1\" id=\"1\" error=\"\" ts=\"1283351406\" />" +
                    "<joinRoom id=\"1\" roomID=\"z11283351417236\" ts=\"1283351417\" />" +
                    "<leaveRoom id=\"1\" roomID=\"room1\" ts=\"1283351426\" />" +
                    "<createRoom id=\"1\" ts=\"1283351417\">" +
                        "<roomName>test</roomName>" +
                    "</createRoom>" +
                    "<sendFile id=\"2\" receiverID=\"1\" fileID=\"1283351441141\" fileSize=\"6Kb\" ts=\"1283351441\">" +
                        "<fileName>script.txt</fileName>" +
                    "</sendFile>" +
                    "<acceptFile id=\"1\" senderID=\"2\" isAccept=\"true\" fileID=\"1283351441141\" ts=\"1283351444\" />" +
                    "<changeColor id=\"1\" color=\"6684876\" ts=\"1283351406\" />" +
                    "<changeAvatar id=\"1\" avatarID=\"02\" ts=\"1283351406\" />" +
                    "<changeStatus id=\"2\" status=\"1\" ts=\"1283351383\" />" +
                    "<publish id=\"1\" video=\"false\" audio=\"true\" pause=\"false\" ts=\"1283351421\" />" +
                    "<subscribe id=\"1\" publisherID=\"2\" roomID=\"proom2000\" ts=\"1283351998\" />" +
                    "<unsubscribe id=\"1\" publisherID=\"2\" roomID=\"proom2000\" ts=\"1283351998\" />" +
                    "<sendInvitation id=\"1\" receiverID=\"2\" roomID=\"null\" ts=\"1283351494\" />" +
                    "<answerInvitation id=\"2\" senderID=\"1\" roomID=\"null\" accepted=\"true\" ts=\"1283351499\" />" +
                    "<startRecordVideo userID=\"1\" ts=\"1283351499\" />" +
                    "<videoSaved userID=\"1\" fileName=\"someFile.flv\" fileSize=\"1234121234\" ts=\"1283351499\" />" +
                "</logPro>";
        }
        else if (!string.IsNullOrEmpty(s))
        {

            doc.InnerXml = s;
        }

        XML oXML = new XML();
        oXML.parseXML(doc);

        Response.Write("Done");
        Response.End();

    }
         /*
          * 
          * 
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        CREATE TABLE [dbo].[chat7_logpro](
            [log_id] [int] IDENTITY(1,1) NOT NULL,
            [action] [nchar](40) NULL,
            [ip] [nchar](40) NULL,
            [ts] [nchar](40) NULL,
            [uid] [nchar](70) NULL,
            [userName] [nchar](70) NULL,
            [hasCam] [nchar](40) NULL,
            [id] [nchar](40) NULL,
            [error] [nchar](250) NULL,
            [roomID] [nchar](70) NULL,
            [roomName] [nchar](70) NULL,
            [receiverID] [nchar](70) NULL,
            [fileSize] [nchar](70) NULL,
            [fileName] [nchar](70) NULL,
            [senderID] [nchar](70) NULL,
            [isAccept] [nchar](70) NULL,
            [fileID] [nchar](70) NULL,
            [color] [nchar](70) NULL,
            [avatarID] [nchar](70) NULL,
            [status] [nchar](70) NULL,
            [video] [nchar](40) NULL,
            [audio] [nchar](40) NULL,
            [pause] [nchar](40) NULL,
            [publisherID] [nchar](40) NULL,
            [accepted] [nchar](40) NULL,
            [userID] [nchar](40) NULL
        ) ON [PRIMARY]
        GO
          * 
          */

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
  <head id="Head1" runat="server">
    <title>blah blah</title>
  </head>
  <body>
    <form id="Form1" method="post" runat="server"><% Page_Load(null, null); %></form>
  </body>
</html>

The most glaring problem is that you open your database connection with an instance of your class XML is instantiated.

So, for the life of that object, the connection is open. What is your reason for this?

You should only be opening the connection when you need it, then closing it after you're done. Placing the code that opens the connection in a using statement will dispose of the connection, so you don't have to manually dispose the connection, like in your code above.

Here is an example, I didn't test, but an example:

    public void add2Db(string table, string sqlRows, string sqlValues)
    {
        string sql = "INSERT INTO " + table + " (" + sqlRows + ") VALUES (" + sqlValues + ")";
        using(SqlConnection dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString())
        {
             SqlCommand cmd = new SqlCommand(sql, dbConn);
             cmd.CommandType = CommandType.Text;
             cmd.ExecuteNonQuery();
        }
    }

You should also look into what Sql Injection attacks are.

I'm not exactly sure what your specific problem is, but you may be better off utilizing a using statement to manage you db connection:

string sql = "INSERT INTO " + ...;

using(SqlConnection dbConn = new SqlConnection(ConfigurationManager.ConnectionStrings["..."].ConnectionString)){
  //create your SqlCommand
  SqlCommand cmd = ...;

  //THEN open your database -- keep the connection open as short as possible
  dbConn.Open();

  //execute the query
  cmd.ExecuteNonQuery();
}

Upon leaving the using statement, Dispose() will be called on your SqlConnection, which will close your connection. You can also create your SqlCommand in a using also as it also implements IDisposable.

As kprobst mentioned, using a destructor to dispose your db connection is not recommended.

You're closing the connection in the class destructor, which is not guaranteed to execute at any particular point in time.

Instead, use a using(x) block to manage the lifetime of the connection within the class method, and don't worry about when a particular instance of your class will be garbage collected. Or simply place a try/catch block in the function and close the connection manually.