How to disconnect database in asp.net

varoluscuprens · 08-15-2012, 03:01 AM

Hi There;
I have a asp.net project. My aim is to connect postgres database, perform some actions and disconnect from database. The connection is being set up while the page loads, and it is expected to disconnect it from database by clicking a button. But it doesn't. Here are my codes. To clarify, codes have been purified.

My aspx side:

Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="select_data_2.aspx.cs" Inherits="_08_Veritabanlari_AD0_NET_select_data_2" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <p>

    <!-- This is the disconnect button. The function "disconnectFromDatabase" is defined in the c sharp side. -->
    <asp:Button id="closeConnect" runat="server" Text="Veritabanı bağlantısını kes." OnClick="disconnectFromDatabase"></asp:Button>
    <asp:Button id="isConnected" runat="server" Text="Veritabanına bağlı mı?" OnClick="isConnectedToDatabase"></asp:Button>

    </p>

    <p>
        <asp:Label ID="outputLabel" runat="server" ></asp:Label>
    </p>

    </div>
    </form>
</body>
</html>

and this is the c sharp side:

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using Npgsql;

public partial class _08_Veritabanlari_AD0_NET_select_data_2 : System.Web.UI.Page
{
    public _08_Veritabanlari_AD0_NET_select_data_2()
    {
        //The connection is done here. 
        this.connectToDatabase();
        this.getGreatestIDFromData();
        ds = new DataSet();
        dt = new DataTable();
    }

    private DataSet ds;
    private DataTable dt;
    private int greatestId;
    private NpgsqlConnection conn;
    private NpgsqlCommand command;
    private NpgsqlDataReader dr;
    private string connstring;

 //this is the problemmatic procedure
    protected void disconnectFromDatabase(object nesne, EventArgs e)
    {
        try
        {
            this.conn.Close();
            this.conn = null;
        }
        catch (Exception msg)
        {
            // something went wrong, and you wanna know why
            Response.Write(msg.ToString());
            throw;
        }


    }
}

thanks in advance.

add1:
I have defined the private NpgsqlConnection conn and private NpgsqlCommand command variables as static, but no disconnection again.

sundialsvcs · 08-15-2012, 08:18 AM

You don't show all your relevant code, or show why you think it's not disconnecting, nor what happens when you try to do so. The connectToDatabase routine is not shown. You should set this.conn = null in a finally block so that it happens even if the disconnect attempt fails. You need to be certain you know what this is, and you also need to be certain that this.conn does in fact contain a valid connection-handle. If it is null then you should see an exception to that effect being thrown.

However ... this really isn't the right place to be asking for programmers to "debug my code for me." You should be asking your manager or co-workers, and you should have done so long before now. Someone fifteen meters away from you in Turkey could probably have solved your problem in minutes and the project would not be delayed.

varoluscuprens · 08-16-2012, 01:59 AM

Almost every user of this forum struggle for solving their programming problems.Apart from the fact of impreciseness of being very sure of not performing a debug over the code, when I take a look at the forums topic, many problems could be solved by debugging. If I have a chance to ask someone fifteen meters away from me in Turkey, I will not attemp to steal your precios time.

Here are my whole code:

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using Npgsql;

public partial class _08_Veritabanlari_AD0_NET_select_data_2 : System.Web.UI.Page
{
    public _08_Veritabanlari_AD0_NET_select_data_2()
    {
        //The connection is done here. 
        this.connectToDatabase();
        this.getGreatestIDFromData();
        ds = new DataSet();
        dt = new DataTable();
    }

    private DataSet ds;
    private DataTable dt;
    private int greatestId;
    private NpgsqlConnection conn;
    private NpgsqlCommand command;
    private NpgsqlDataReader dr;
    private string connstring;

    private void getGreatestIDFromData()
    {
        try
        {
            int i = 0;
            int j = 0;

            try
            {
                this.executeSQLQuery("SELECT MAX(id) FROM myschema1.mytable;");
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }


            while (this.dr.Read())
            {
                for (i = 0; i < 1; i++)
                {
                    this.greatestId = (int)this.dr[i];

                }

            }
            
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

        //Şuraya Response.Write(this.id); yazdığımda Response is not available in this context. adında bir HTTPException yiyorum.
    }

    private int getNextID()
    {
        return (++this.greatestId);
    }

    private void connectToDatabase()
    {
        try
        {
            connstring = String.Format("Server=localhost;Port=5432;User Id=osman;Password=123456;Database=deneme;");
            conn = new NpgsqlConnection(connstring);
            conn.Open();

        }
        catch (Exception msg)
        {
            // something went wrong, and you wanna know why
            //outputLabel.Text = (msg.ToString());
            throw;
        }

    }

    //this is the problemmatic procedure
    protected void disconnectFromDatabase(object nesne, EventArgs e)
    {
        try
        {
            this.conn.Close();
            this.conn = null;
        }
        catch (Exception msg)
        {
            // something went wrong, and you wanna know why
            Response.Write(msg.ToString());
            throw;
        }


    }

    protected void isConnectedToDatabase(object nesne, EventArgs e)
    {
        this.initialiseOutputLabel();
        outputLabel.Text += conn.FullState.ToString();
        outputLabel.Text += conn.ToString();
    }

    private void executeSQLQuery(string sqlKomutu)
    {

        try
        {
            command = new NpgsqlCommand(sqlKomutu, conn);
            dr = command.ExecuteReader();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

    }

    protected void insertData(object nesne, EventArgs e)
    {
        string name;
        string surname;
        string cash;

        name = nameText.Text.ToString();
        surname = surnameText.Text.ToString();
        cash = cashText.Text.ToString();
        this.initialiseOutputLabel();

        nameText.Text = "";
        surnameText.Text = "";
        cashText.Text = "";

        try
        {
            this.executeSQLQuery("INSERT INTO myschema1.mytable  VALUES('" + this.getNextID() + "' " + ",'" + name + "','" + surname + "','" + cash + "');");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

        finally
        {
            outputLabel.Text += ("Bu değer eklendi:");
            outputLabel.Text += (name);
            outputLabel.Text += (surname);
            outputLabel.Text += (cash);
        }

    }

    protected void viewDatabase(object nesne, EventArgs e)
    {
        this.initialiseOutputLabel();
        
        try
        {

            int i = 0;
            int j = 0;

            try
            {
                this.executeSQLQuery("SELECT * FROM myschema1.mytable");
   
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

            
            while (this.dr.Read())
            {
                for (i = 0; i < this.dr.FieldCount; i++)
                {
                    outputLabel.Text += (this.dr[i]);
                    outputLabel.Text += (" / ");
                }
                outputLabel.Text += ("<br /> ");

            }

        }
        catch (Exception msg)
        {
            // something went wrong, and you wanna know why
            outputLabel.Text = (msg.ToString());
           // throw;
        }

    }

    protected void DATAGRIDSORGULA(object nesne, EventArgs e)
    {
        this.initialiseOutputLabel();
        try
        {

            int i = 0;
            int j = 0;

            try
            {
                this.executeSQLQuery("SELECT * FROM myschema1.mytable ");
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

            datagridd1.DataSource = this.ds;
            datagridd1.DataBind();

        }
        catch (Exception msg)
        {
            // something went wrong, and you wanna know why
            Response.Write(msg.ToString());
            throw;
        }

    }

    private void initialiseOutputLabel() 
    {
        outputLabel.Text = "";
    }
}

Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="select_data_2.aspx.cs" Inherits="_08_Veritabanlari_AD0_NET_select_data_2" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <p>
    <asp:Button id="viewDatabaseId" runat="server" Text="Veritabanını Sorgula" OnClick="viewDatabase"></asp:Button>
    <!-- This is the disconnect button. The function "disconnectFromDatabase" is defined in the c sharp side. -->
    <asp:Button id="closeConnect" runat="server" Text="Veritabanı bağlantısını kes." OnClick="disconnectFromDatabase"></asp:Button>
    <asp:Button id="isConnected" runat="server" Text="Veritabanına bağlı mı?" OnClick="isConnectedToDatabase"></asp:Button>
    <asp:Button id="sendButon2" runat="server" Text="Veritabanını Sorgula 2" OnClick="DATAGRIDSORGULA"></asp:Button>
    </p>

    <p>
        <asp:Label ID="Label1" runat="server" Text="isim giriniz:"></asp:Label>
        <asp:TextBox ID="nameText" runat="server" ></asp:TextBox>
        <asp:Label ID="Label2" runat="server" Text="soyisim giriniz:"></asp:Label>
        <asp:TextBox ID="surnameText" runat="server" ></asp:TextBox>
        <asp:Label ID="Label3" runat="server" Text="Maaşı giriniz:"></asp:Label>
        <asp:TextBox ID="cashText" runat="server" ></asp:TextBox>
    </p>
    
    <p>
        <asp:Button id="insertButton" runat="server" Text="Veritabanına Ekle" OnClick="insertData"></asp:Button>
    </p>

    <p>
        <asp:Label ID="outputLabel" runat="server" ></asp:Label>
    </p>

     <p>
        <asp:DataGrid runat="server" ID="datagridd1" ViewStateMode="Disabled"></asp:DataGrid>
    </p>

    </div>
    </form>
</body>
</html>