[limlim]

Hi i am having some trouble with getting results from my database. I am trying to make a class for the database connection so i dont have to retype everything every time i want to work with my db. I can get this to work fine by having all my code in the frmMain load but as when i try create a class to handle the connection my listbox turns up empty. no build errors

Code for my frmMain

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace FilmBlogg
{
    public partial class frmMain : Form
    {

        private dbConnect connect = new dbConnect();
        
        public frmMain()
        {
            InitializeComponent();
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
                connect.connection().Open();
             
                SqlCommand command = new SqlCommand("SELECT FirstName from Users", connect.connection());

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    string name = (string)reader["FirstName"];
                    listBox1.Items.Add(name);
                }                                     
        }
    }
}

Code for my dbConnect class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.Sql;

namespace FilmBlogg
{
    class dbConnect
    {               
        public dbConnect()
        {            
        }

        public SqlConnection connection()
        {

            string Server = @".\SQLEXPRESS";
            string Username = "myusername";
            string Password = "mypassword";
            string Database = "filmProjektDB";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;


            SqlConnection connection = new SqlConnection(ConnectionString);
            return connection;
        }
    }
}

if i am going about this a stupid way i would appreciate some tips. Im new to programming with databases so go easy on me.

[janne_panne]

Every time you call your cbConnect.connection() method you get different SqlConnection class because of these last lines:

            SqlConnection connection = new SqlConnection(ConnectionString);
            return connection;

So here you are using actually two different SqlConnections:

                connect.connection().Open();
             
                SqlCommand command = new SqlCommand("SELECT FirstName from Users", connect.connection());

So, to fix this, you could make your dbConnect class to look like this:

    class dbConnect
    {               
          
        private SqlConnection connection;

        public dbConnect()
        {          
            string Server = @".\SQLEXPRESS";
            string Username = "myusername";
            string Password = "mypassword";
            string Database = "filmProjektDB";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;


            connection = new SqlConnection(ConnectionString);
            return connection;  
        }

        public SqlConnection Connection
        {
            get { return this.connection; }
        }
    }

So what we are doing here is that we initialize the connection in the class' constructor and then we have a readonly property that returns the initialized connection. This way we have only one connection and we aren't returning a new one each time we call Connection property.

[limlim]

janne_panne, on 03 February 2010 - 12:06 PM, said:

Every time you call your cbConnect.connection() method you get different SqlConnection class because of these last lines:

            SqlConnection connection = new SqlConnection(ConnectionString);
            return connection;

So here you are using actually two different SqlConnections:

                connect.connection().Open();
             
                SqlCommand command = new SqlCommand("SELECT FirstName from Users", connect.connection());

So, to fix this, you could make your dbConnect class to look like this:

    class dbConnect
    {               
          
        private SqlConnection connection;

        public dbConnect()
        {          
            string Server = @".\SQLEXPRESS";
            string Username = "myusername";
            string Password = "mypassword";
            string Database = "filmProjektDB";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;


            connection = new SqlConnection(ConnectionString);
            return connection;  
        }

        public SqlConnection Connection
        {
            get { return this.connection; }
        }
    }

So what we are doing here is that we initialize the connection in the class' constructor and then we have a readonly property that returns the initialized connection. This way we have only one connection and we aren't returning a new one each time we call Connection property.

by using your code i get the following error:

Error	1	Since 'FilmBlogg.dbConnect.dbConnect()' returns void, a return keyword must not be followed by an object expression	

can the constructor really return a sqlConnection like that?

EDIT: i removed the return in the constructor and everything works fine. thank you for the help. much appreciated!

This post has been edited by limlim: 03 February 2010 - 01:30 PM

[eclipsed4utoo]

limlim, on 03 February 2010 - 03:21 PM, said:

janne_panne, on 03 February 2010 - 12:06 PM, said:

Every time you call your cbConnect.connection() method you get different SqlConnection class because of these last lines:

            SqlConnection connection = new SqlConnection(ConnectionString);
            return connection;

So here you are using actually two different SqlConnections:

                connect.connection().Open();
             
                SqlCommand command = new SqlCommand("SELECT FirstName from Users", connect.connection());

So, to fix this, you could make your dbConnect class to look like this:

    class dbConnect
    {               
          
        private SqlConnection connection;

        public dbConnect()
        {          
            string Server = @".\SQLEXPRESS";
            string Username = "myusername";
            string Password = "mypassword";
            string Database = "filmProjektDB";

            string ConnectionString = "Data Source=" + Server + ";";
            ConnectionString += "User ID=" + Username + ";";
            ConnectionString += "Password=" + Password + ";";
            ConnectionString += "Initial Catalog=" + Database;


            connection = new SqlConnection(ConnectionString);
            return connection;  
        }

        public SqlConnection Connection
        {
            get { return this.connection; }
        }
    }

So what we are doing here is that we initialize the connection in the class' constructor and then we have a readonly property that returns the initialized connection. This way we have only one connection and we aren't returning a new one each time we call Connection property.

by using your code i get the following error:

Error	1	Since 'FilmBlogg.dbConnect.dbConnect()' returns void, a return keyword must not be followed by an object expression	

can the constructor really return a sqlConnection like that?

you code would look like this..

dbConnect myDbConnect = new dbConnect();
myDbConnect.Connection.Open();
SqlCommand command = new SqlCommand("SELECT FirstName from Users", myDbConnect.Connection);