Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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...
0
fifo123
Asked:
fifo123
1 Solution
 
_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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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