I'm currently developing my coding skills by practicing Object Oriented Programming.
I am working with a lot of SQL connections in order to retrieve information to initialize my class objects. I believe I am not doing well though, due to the amount of SQL connections I initialize at the same time. I often need to run several connections at the same time in order to complete initializing my main class. Below is a short example of what I mean.
I have got several classes. Each class has several properties of a different class type. Here's a short example of how it looks mostly:
public class UserAccount
{
public long UserId { get; set; }
public string UserName { get; set; }
public Address UserAddress { get; set;}
}
public class UserAddress
{
public long AddressId { get; set; }
public string Address { get; set; }
}
Now, in order to retrieve information about each one of these classes, I am using specific methods which extract the value of each class property from my database. Here is how my method looks for the class UserAccount:
public static UserAccount GetUserAccountById(long id)
{
UserAccount result = null;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(string.Empty, con))
{
cmd.CommandText = "SELECT * FROM UserAccounts WHERE UserId = @Id";
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
result = RetrieveFromDatabaseRow(reader);
}
con.Close();
}
}
return result;
}
public static UserAccount RetrieveFromDatabaseRow(SqlDataReader reader)
{
try
{
return new UserAccount
{
UserId = (long)reader["UserId"],
UserName = (string)reader["UserName"],
UserAddress = UserAddress.GetUserAddressById((long)reader["PrimaryAddressId"])
};
}
catch (Exception unhandledException)
{
GlobalMethods.LogException(unhandledException);
return null;
}
}
Now the UserAddress.GetUserAddressById method is pretty much the same as the extraction method of my first class. It initializes a connection and runs an SQL command.
Why do I think I'm doing it wrong: The current way it's being executing at this point causes several SQL connections to be open at the same time (in my example it's only 2, but there's many more in my actual project).
I was thinking about making one huge SQL statement which joins all of the needed SQL tables in one single command, and then extracts all of that information in order to initialize the main class without requesting additional information from its properties (the UserAddress class for instance). But then it's not funny good to me because often I need to retrieve much information at the same time, and sometimes one statement won't be enough. Besides, it's a pain in the ass to write one heck of a huge SQL statement which JOINs ten tables.
I was thinking about making one global SqlConnection variables which will be initialized only once, and all of my SQL queries will go through him all the time, at the same time. I don't know how it would react to such actions, though.
I could use some tips and hints on how to execute this properly. Thank you in advance.
