• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

connecting with ADO.Net.....

Hi,
I'm new in C# , ADO.NET and OOP.
In my project there are some forms which have the same ListBox
(i meen ListBox that populate the same data from a table).
In one of the forms i wrote in the Load Event this code:

private void frmWorkersRcrmnt_Load(object sender, System.EventArgs e)
{
 SqlConnection con = new SqlConnection();
 con.ConnectionString="data source=(local);initial catalog=Mashavit;integrated  security=sspi";
 con.Open();
 SqlCommand com = new SqlCommand("select SourceName from
                                         EmployeeSource",con);
 SqlDataReader dr = com.ExecuteReader();
      while (dr.Read())
         {
              Source.Items.Add(dr["SourceName"].ToString());
          }
          dr.Close();
                   con.Close();
}


it works great and data from the table populates the ListBox.

i know that in OOP programing i'm not soppose to put this code in each form in the Load Event in order to populate the ListBoxes. i think that i soppose to create a class called lets say: "conn"
and put there the connection lines from the code i put in the Load Event and in the form only the lines that deals with the sql lines ant the While loop.
My question is how do i do this? (because i didn't succedd doing it), what is the best way and the simpliest way to do it?
Thanks
Udir
0
udir
Asked:
udir
  • 2
  • 2
1 Solution
 
vbalCommented:
There are several approaches you can take.  However, first off I would like to point out a potentially serious overlook in you code.

SqlConnection con = new SqlConnection();
 con.ConnectionString="data source=(local);initial catalog=Mashavit;integrated  security=sspi";
 con.Open();
 SqlCommand com = new SqlCommand("select SourceName from
                                         EmployeeSource",con);
 SqlDataReader dr = com.ExecuteReader();
     while (dr.Read())
        {
             Source.Items.Add(dr["SourceName"].ToString());
         }
         dr.Close();
                   con.Close();

When you open a connection you have to make sure that the it is closed when you are finished using it.  With your code if an exception occurs while the connection is opened it is not guaranteed to close.

There are two ways to do this:

try
{
    conn.open();
    //read data.........

     conn.close();//when finished
}
catch
{
     //handle the error
}
finnally
{
      conn.close();
}
The finally block will ensure that the connection is closed even in the event of an exception.

My preffered way is to use the using statement when creating the connection.

using(SqlConnection conn = new SqlConnection(connStr))
{
      //you can create you comand object by SqlComand cmd = conn.CreateCommand();
      cmd.CommandText = "select * from xxxx";

      conn.Open();
      //read
      conn.Close();
}

The connection object implements the IDisposable interface.  When the object is out of scope the objects resources will be released and clean up is performed.

You can also use the using statement around the reader object as well.

To answer your question:

Class MyData
{
     public ArrayList GetData()
     {
       
       ArrayList ret = new ArrayList();
      //your sql code here.
        while (dr.Read())
        {
             Ret.Add(dr["SourceName"].ToString());
         }
         dr.Close();
         con.Close();

      return ret;
     }
}

in your form load you could:

{
   MyData m = new MyData();
   ArrayList data = m.GetData();

  for(int i =0;i<data.COunt;i++)
    {
        Source.Items.Add(data[i].ToString());
    }

}

//you could improve on this by passing the connection string in the constructor, etc... to make the class more flexible and not tightly coupled to the data siurce.  You could also pass the listbox as an argument to the getdata public method.

regards,
al



0
 
udirAuthor Commented:
Hi al,
Thanks for the reply.
It works - thanks.
but i have 2 questions.
First question:
why do you put 'for' loop int the Load Event and also  'while' event in the class, isn't it enogh to put only the while loop like i did when i put all the code in the Load Event?  
Second question: (i hope i will be able to explain myself)
isn't the best way to do it (in order to use the OOP correctly) is to put only the connection lines, and i meen -  
       
          SqlConnection con = new SqlConnection();
           con.ConnectionString="data source=(local);initial catalog=Mashavit;integrated          
                   security=sspi";
          con.Open();

in the class, and the rest of the code in the Event, becuase if someday i will replace the Database then i will only need to change the connection lines (in one place) - in the class!

secondly, if there are several ListBoxes in a form and each one populates data from differnt table,(in that case i need to write function for each ListBox in the Conn Class), but if i put only the sql lines in the class then i will only have to change the Database connectin in the class and not all the code for every Event in the Conn class.
Thanks
Udir


0
 
udirAuthor Commented:
HI Al,
are you there????
your solution works as i said, but i think (as i mantion above) that it can be improve (as you also mantion) in a way it will be more OOP (and i still don't know how to do it).
more details in previous comment.
I will be most thanksfull if you could answare it.
Udir
 
0
 
vbalCommented:
There are a million ways to solve this problem.

If you want to promote looser coupling, you can put the connection string in the class contuctor.

class MyData
{
   string _connStr;
   public MyData(string connStr)
   {
      this._connStr = connStr;
   }

   public ArrayList GetData()
   {
        using(SqlConnection conn = new SqlConnection(this._connStr))
   }
}

To avoid the loops you can just bind the arraylist to the list box, that way you dont ahave to loop through in the load event.

You can also use a dataadapter to populate a dataset which you can bind to a list box.


If you want to go extremely oop.

interface IMyData
{
public ArrayList GetDataA();
public ArrarList GetDataB();
}

create a class that implements the interface.   then the client will just code against the interface rather than a particular concrete class.  You can then swap different implementations of the interface.






0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now