Using SqlHelper in a .Net application

June 24, 2009

In the Dovetail SDK, there is a very useful SqlHelper class. SqlHelper provides a convenience and data provider-agnostic wrapper around ADO.NET. This allows an application to execute SQL commands in a database.

 

SqlHelper has various ways of being initialized, which all depend on a DbProvider instance to make a connection to the database. In most applications the DbProvider is separately instantiated based on configuration, so the examples here will not detail that process.

 

SqlHelper can execute a variety of SQL commands. SQL SELECT statements are usually performed by the ExecuteDataSet method. The ExecuteNonQuery method can be used to perform other SQL commands such as UPDATE or DELETE statements. ExecuteScalar is used frequently to get a value returned from a SQL statement for use later in the application.

 

public void SqlHelper_example()
{
    var sqlCommand = "select title, id_number from table_subcase";
    var sqlHelper = new SqlHelper(_dbProvider, sqlCommand);

    var dataset = sqlHelper.ExecuteDataSet();
    var table = dataset.Tables[0];

    foreach (DataRow row in table.Rows)
    {
        var subcaseInfo = String.Format("Subcase {0}: {1}", row["id_number"], row["title"]);

        Console.WriteLine(subcaseInfo);
    }
}

 

 

In the first example, a simple database select statement is being executed to retrieve an ADO dataset. The dataset is then processed, writing the value from each of the two columns for each row in the first table in the dataset to the console. If no rows were retrieved, then nothing would be written to the console.

 

In this example, a database DELETE statement is being executed using a static SqlHelper instance to delete rows from the notes_log table.

 

public void SqlHelper_example()
{
    SqlHelper.ExecuteNonQuery(“DELETE from table_notes_log WHERE internal = ‘-666’”);
}

 

 

Finally, here is an example using ExecuteScalar. The SQL SELECT statement will return the requested identifier, which is converted to an integer.

 

public void SqlHelper_example()
{
	int caseObjid = Convert.ToInt32(SqlHelper.ExecuteScalar(“SELECT objid 
        from table_case WHERE id_number = ‘1’”));
}

 

 

In reality, these examples just scratched the surface of possibilities for the uses of SqlHelper. The online documentation for

SqlHelper

 

provides a list if all the available methods, and putting them to use in applications gets easier with experience, and good examples.

 

Please add other examples that you have implemented in your comments, and we can all discuss!