I don't know if your code will work 100% after you apply this but at least it will solve the issue reading from the database.
A sample database-access (for SQL-Server), from opening the connection to reading all the records, applying good practices (using-Blocks, Sql-Parameters and abstract Db*****-baseclasses instead of database-specific classes):
using (DbConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT col1, col2 FROM table1 WHERE col3 = @p1;";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = "something";
cmd.Parameters.Add(p1);
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
int col1 = dr.GetInt32(0);
string col2 = dr.GetString(1);
}
}
}
}
Edit after comment:
Sql-Parameters are the best way to "communicate" your query-constraints to the database. In your case, instead of this:
SELECT ResourceName FROM StaffTracking where PersonnelResourceType = 'Supplier' order by PersonnelResourceType, ResourceName
you could write this:
SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName
and create an Sql-Parameter (in this case named "restype") that carries the desired value (in this case "Supplier") and append it to the Parameter-Collection of the Sql-Command:
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);
Think of it like some sort of variable for SQL-commands. SQL-Server takes your query-string and replaces all occurrences of those Parameter-Names (starting with an
@
) by the value that you assigned to the Sql-Parameter-Object with the same name.
It is "the best way" because:
- If you develop an application that stores input from strangers (e.g. website-visitors) into your database, you avoid the risk of
SQL-Injection-Attacks[
^]
- It is better maintainable because your query-string is easily readable
- In case of string-values you avoid potential SQL-syntax errors in case the string contains quotes
Edit 2:
The last two points don't directly apply to your previous code but very often you see people constructing their query-strings like this:
string query = "SELECT col1 FROM table1 WHERE col2 = '" + someVariable + "' AND col3 = '" someOtherVariable "'";