In my project I am using 10-15 SQL queries, for that I have a common class
for database connectivity and using that I have executed the queries by passing the connection
, command
and dataReader
.
With some reference in SO, I learned that my code is not properly structured. I would like to learn and write high performance code.
DBConnectivity.cs:
class DBConnectivity
{
public SqlConnection connection = null;
public SqlCommand command = null;
public SqlDataReader dataReader = null;
public string connectionString = null;
public List<MasterTableAttributes> masterTableList;
public DBConnectivity()
{
connectionString = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
connection = new SqlConnection(connectionString.ToString());
//-----Master table results
connection.Open();
string masterSelectQuery = "SELECT * FROM MASTER_TABLE";
command = new SqlCommand(masterSelectQuery, connection);
dataReader = command.ExecuteReader();
masterTableList = new List<MasterTableAttributes>();
while (dataReader.Read())
{
MasterTableAttributes masterTableAttribute = new MasterTableAttributes()
{
fileId = Convert.ToInt32(dataReader["Id"]),
fileName = Convert.ToString(dataReader["FileName"]),
frequency = Convert.ToString(dataReader["Frequency"]),
scheduledTime = Convert.ToString(dataReader["Scheduled_Time"])
};
masterTableList.Add(masterTableAttribute);
}
dataReader.Close();
connection.Close();
}
}
Program.cs:
class Program
{
static void Main(string[] args)
{
DBConnectivity dbConnectivity = new DBConnectivity();
Transaction txnObj = new Transaction(dbConnectivity); //Query1
Sales saleObj = new Sales (dbConnectivity);//Query2
}
}
Transaction.cs:
class Transaction
{
public Transaction(DBConnectivity dbConnectivity)
{
dbConnectivity.connection.Open();
string txnQuery = "SELECT ...";
dbConnectivity.command = new SqlCommand(txnQuery, dbConnectivity.connection);
dbConnectivity.dataReader = dbConnectivity.command.ExecuteReader();
while (dbConnectivity.dataReader.Read())
{
...
}
dbConnectivity.dataReder.close();
dbConnectivity.connection.Close();
}
}
Sales.cs:
class Sales
{
public Sales(DBConnectivity dbConnectivity)
{
dbConnectivity.connection.Open();
string salesQuery = "SELECT ...";
dbConnectivity.command = new SqlCommand(salesQuery, dbConnectivity.connection);
dbConnectivity.dataReader = dbConnectivity.command.ExecuteReader();
while (dbConnectivity.dataReader.Read())
{
...
}
dbConnectivity.dataReder.close();
dbConnectivity.connection.Close();
}
}