Connection objects and Threads ...... please help..

Hello All,
I'm creating a multithreaded C# service application that reads files from
a directory and insert data into multiple SQL Server tables.  I understand that
it's probably a good idea that each thread maintains its own connection object.
The way the code is structured, I have created classes that represent each database
table.  I also have a DB class that handles the database transactions.

For example.....

//Function called by each thread...
public void StartAdding()
{

  Student x = new Student();
  x.StudentID = //value read from the file;
  x.Name = //value read from the file
  x.Insert();

  Teacher Y = new Teacher();
  y.TeacherID = //value read from file;
  y.Name = //Value read from file
  y.Insert
}

public class student
{
   private int iStudentID
   private string sName
   
   public StudentID
   {
     { get.... }
     { set.... }
   }

   public Name
   {
     { get.... }
     { set.... }
   }

   public void Insert()
   {
      ......
      ......
      DB.LoadStudent(_params)
   }
}

public class DB
{
   public static void LoadStudent(SqlParameter[] p)
   {
      //insert into the DB
   }
   public static void LoadTeacher(SqlParameter[] p)
   {
             //insert into the DB
   }
}

Now, since I a thread pool setup, multiple threads are going to be running this code.  Where can
I specify a separate connection string for each thread.  Should I do something something like this inside
the function "StartAdding()", and pass the connection object around...

SqlConnection conn = new SqlConnection("some connection string")
conn.open()

Student x = new Student();
x.StudentID = //value read from the file;
x.Name = //value read from the file
x.Insert(conn);

Somehow, it seems like there could be an easier way to do this...
fifo123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_kiwi_Commented:
Since your DB Code depends on a connection, and only your DB Code, I would consider passing the DB class as a non static class, and in the constructor of your class pass the connection string, so only a DB Object will knopw how to access the db.

Then, to make the link between the Student and the DB, you can pass the DB object you created in the constructor of the student.

This would give something like this:

//Function called by each thread...
public void StartAdding()
{
  DB myDB = new DB("my connection string");

  Student x = new Student(myDB);
  x.StudentID = //value read from the file;
  x.Name = //value read from the file
  x.Insert();

  Teacher Y = new Teacher(myDB);
  y.TeacherID = //value read from file;
  y.Name = //Value read from file
  y.Insert();
}

public class student
{
   private int iStudentID
   private string sName
   private DB myDB;
   
   public Student(DB conn)
   {
      myDB = conn;
   }

   public StudentID
   {
     { get.... }
     { set.... }
   }

   public Name
   {
     { get.... }
     { set.... }
   }

   public void Insert()
   {
      ......
      ......
      myDB.LoadStudent(_params)
   }
}

public class DB
{
  private SqlConnection myConn;

   public DB(string conn)
   {
      myConn = new SqlConnection(conn);
   }

   public void LoadStudent(SqlParameter[] p)
   {
     //insert into the DB using myConn
   }
   public void LoadTeacher(SqlParameter[] p)
   {
             //insert into the DB using myConn
   }
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.