Can I make use re-useable code for slightly common methods?

Hi all (hope you all had a good weekend),

I have a scenario where I have a Form that has some ComboBoxes and a ListView. The comboBoxes act as the filters in which the base the results that will populate the ListView control. Now, essentailly, the SQL required to populate the ListView for the different Filter types is the same apart from the WHERE clauses that will differ depending on what has been selected in the ComboBoxes (if any). Below is an example of two methods in my classes that have very similar SQL Code:

public static int PopulateSnagGridStatus(ListView grdGrid, int Status)
{
return PopulateListView(grdGrid, "SELECT SnagUID, " +
" CONVERT(NVARCHAR(10), SnagDate, 101) AS SnagDate, " +
" tbl_SNG_Status.Description as Status, " +
" '' as Trade, " +
" tbl_SNG_Snag.Description " +
"FROM tbl_SNG_Snag " +
" INNER JOIN tbl_SNG_Status ON (tbl_SNG_Snag.StatusUID = tbl_SNG_Status.StatusUID) " +
"WHERE (tbl_SNG_Snag.StatusUID = " + Status + ")", 5);
}

and...

public static int PopulateSnagGridType(ListView grdGrid, int Type)
{
return PopulateListView(grdGrid, "SELECT SnagUID, " +
" CONVERT(NVARCHAR(10), SnagDate, 101) AS SnagDate, " +
" tbl_SNG_Status.Description as Status, " +
" '' as Trade, " +
" tbl_SNG_Snag.Description " +
"FROM tbl_SNG_Snag " +
" INNER JOIN tbl_SNG_Status ON (tbl_SNG_Snag.StatusUID = tbl_SNG_Status.StatusUID) " +
"WHERE (tbl_SNG_Snag.TypeUID = " + Type + ")", 5);
}

I have many methods that are like this, and as you can see, the SELECT clause in the SQL is always the same apart from the WHERE clause
which is different as it uses different Parameters that are passed into the method.

My question is this, is there anyway I can improve my coding in anyway, so that I can reuse code, and not duplicate it as I have done

Hope I have clarified everything, if not then please post back and I will explain further.

Thanks

Tryst

Answer this question

Can I make use re-useable code for slightly common methods?

Amit Kejriwal

You can better use paramitrimized queries. Then you never have to worry about format's or SQL Injection.
It's olso better for the preformance, because you don't need to have to concatenate a string for example:

string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";

No escape characters needed, you doesn't have to think about using a " or not etc.

Parameters are like placeholders, you use them in Stored Procedures as well.

A little example:

// TODO: Set date variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1 WHERE MyDate = @MyDate";
SqlParameter pMyDate = new SqlParameter("@MyDate", SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new SqlConnection("ConnectingString");
dbConn.Open();

try
{
   using(SqlCommand dbCommand = new SqlCommand(query, dbConn))
   {
       // Add paramter to Command.
       dbCommand.Parameters.Add( pMyDate );

       // Execute the query and get results.
       SqlDataReader reader = dbCommand.ExecuteReader();

       try
       {
           // Walkthrough results.
           while(reader.Read())
           {
           // TODO: Do something with the data.
           }
       }
       finally
       {
           // Close reader.
           reader.Close();
       }
   }
}
finally
{
   // Close connection.
   dbConn.Close();
}

Now you can just put the Type value in a parameter and you code is save and can be reused.

If you want to store the full Where statement you can create an interface for your domain object with a internal method or something, just the scope you want, with a method that will return a selection statement piece. You can also specify this with Attributes for example, but i don't know how large your project is.

Mikkel Nordvang Roved

Tryst wrote:
why have you used try/finallys when making use of the 'using' statement I thought the 'using' statement takes care of all that error trapping, or is it just the releasing of resources it takes care of

In the sample code the using statement wrappes only the SqlCommand, the SqlConnection object isn't used with a using statement. This is wrapped by a tr-catch-finally statement to clean it up.

Irakli Lomidze
Hi, PJ. van de Sande,

that seems like a very efficient solution, and as its for a handheld (low battery) device, so its extra good :).

Can I just ask one question, why have you used try/finallys when making use of the 'using' statement I thought the 'using' statement takes care of all that error trapping, or is it just the releasing of resources it takes care of

Thanks

Tryst
Can I make use re-useable code for slightly common methods?

Answer this question