C# 7 new methods for returning data with out parameters and ValueTuple
Introduction
Many developers who work with databases in solutions will learn one or two methods to interact with data and never explore alternative methods that may very well be better for one or several operations performed in their applications. This article will focus on reading data from a SQL-Server database, keeping simple by using two tables joined together. Although a SQL-Server database is used the methods presented are not constrained to SQL-Server database. Here the focus is on data containers which are populated in this case from SQL-Server database, but the data may come from an XML file, a JSON file, any database which Microsoft Visual Studio supports or by use of an third party library.
The two main containers are DataTable and a class which represents a row in a database table. Seeing a DataTable alternate methods to return data also works with a DataSet. The methods presented for a class representing a row from a database table works with a List<T> and with that works with Entity Framework.
Requirements
C# 7 is required for all methods presented in this article. The two main methods which will be explored utilize Out variables and named Tuples. The focus of the two methods is with Out parameters/variables. Since Out parameters don’t work with asynchronous methods (see the following) to present an alternate method a Tuple example is provided.
ValueTuple must be installed via NuGet on the following page. Or by right clicking on solution explorer, select "Manage NuGet packages for solution", select browse, type in ValueTuple, select and install.
Basics
This article does not explain out parameters and Tuples, please refer to the following links for the basics.
Tuple types
Out parameter modifier
C# 7.0 Out Parameters
Projects types
The methods presented are demonstrated in unit test which means there are no ties to any type of project from console to WPF to Windows conventional desktop to ASP.NET.
Unit test
For most of methods presented there are both positive and negative test methods. Simply using positive unit test does not validate a method functions properly although several positive test may be written in this case negative test were written to validate positive test.
Example, a test method expects n records returned by a test only validate more than zero records were returned. In this case if one record was returned but expected 10 records it would appear the test passed yet there may be a bug or connection issues.
Returning data variations
A commonly used method to return a database table is by DataTable. A connection is made to a database, a command object is configured. The connection opened and ExecuteReader is invoked off the command object, in this case in a new DataTable Load method. Unexperienced developers many time will have no exception handling so in the example below exception handling is provided.
Exception handling in the accompanying code samples is implemented in a base class which is inherited in a base connection class. Properties in the exception class may be set in the connection class or the database but not the code calling the data class. mHasException in the base exception class is set in a data method to true on any failure. IsSuccessFul in the base exception class can be interrogated in the code calling the data class. Many developers neglect to use exception handling or will use exception handling incorrectly. Using the pattern provided in the code samples here greatly deduces the chances of an embarrassing crashes of an application. Therefore each code sample provides a return value which indicates success or failure of an operation.
Putting this together, the following method will return either an empty DataTable if there is an exception thrown or a populated DataTable if no exception is thrown. The caller would check IsSuccessFul prior to accessing the DataTable to see if the loading of data was successful. If not successful then LastException property in the base exception class will contain the exception. The property LastExceptionMessage is a string property containing the text from the last exception thrown.
public DataTable Customers1()
{
mHasException = false;
var dt = new DataTable();
const string selectStatement =
"SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cn.Open();
dt.Load(cmd.ExecuteReader());
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return dt;
}
In the next example the DataTable is presented via an out parameter and success is checked by the return type of bool. As with the first example by implementing the base exception class any exception thrown is available to the developer.
Calling the above method is similar to using int.TryParse(somesString, out var value);
[TestMethod]
[TestTraits(Trait.Positive)]
public void GetCustomerDataTablesWithAssertionFromMethodReturn()
{
// arrange
var ops = new DataOperations();
// act
if (ops.Customers2(out var dt))
{
// assert
Assert.IsTrue(dt.Rows.Count >0);
}
}
Reflecting at the two-method presented, is one better than the other, no they are simply alternate methods to return data. As mentioned above, writing a positive and negative test, here is where an exception will be thrown because of a field name misspelled (hard to find). This should never happen as SQL statements should be written outside of code e.g. in SSMS (SQL-Server Management Studio) etc. yet fumble fingers can mess things up moving a query from editor to code.
public bool CustomersWithError2(out DataTable dtCustomers)
{
mHasException = false;
dtCustomers = new DataTable();
// using a invalid fieldname
const string selectStatement =
"SELECT cust.CustomerIdentifer,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cn.Open();
dtCustomers.Load(cmd.ExecuteReader());
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Moving away from DataTable containers to a light weight container, a simple class which represents a row in a database (or in a real-life example may have related properties e.g. navigation properties for Entity Framework), Using the same table used for a DataTable above.
namespace BackEndLibrary
{
public class Customer
{
public int CustomerIdentifier { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public int? ContactTypeIdentifier { get; set; }
public DateTime? ModifiedDate { get; set; }
public override string ToString()
{
return $"{CustomerIdentifier},{CompanyName}";
}
}
}
In the method which follows the return type is bool which represents success, if try the list is populated while if the return value is false there is an exception and needs to be investigated via the property mLastException. Note the out parameter which is setup as any normal parameter but with the inclusion of out preceding the type, List<Customer>. The list is populated via a data reader as in conventional coding without the use of out parameter.
public bool Customers3(out List<Customer> Customers)
{
mHasException = false;
Customers = new List<Customer>();
const string selectStatement =
"SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Customers.Add(new Customer()
{
CustomerIdentifier = reader.GetInt32(0),
CompanyName = reader.GetString(1),
ContactName = reader.GetString(2),
ContactTitle = reader.GetString(3),
Street = reader.GetString(4),
City = reader.GetString(5),
PostalCode = reader.GetString(6),
Country = reader.GetString(7),
Phone = reader.GetString(8)
});
}
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Important note on the above method, in the image below the initialization of the out parameter has been commented out, note Visual Studio indicates that the out parameter must be initialized. The is easy to see in this case yet as code becomes more complex if the out parameter is initialized within a try-catch the same error message will be seen disallowing compiling the project.
All methods presented are returning data with no conditions. It’s simple to provide values to use in a WHERE of a SELECT statement as shown below (and this is a basic example, follow the pattern presented or pass in a structure or class with parameters for the command object or invoke a private method to setup parameters and “prepare” them for use). In this case try and find a customer record by a primary key. Other than the method signature data is read the same as the methods above with the inclusion of a where condition.
public bool CustomersSingleByOutParameter(int pId, out Customer Customer)
{
mHasException = false;
Customer = new Customer();
const string selectStatement =
"SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier " +
"WHERE cust.CustomerIdentifier = @Id";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cmd.Parameters.AddWithValue("@Id", pId);
cn.Open();
var reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
Customer.CustomerIdentifier = reader.GetInt32(0);
Customer.CompanyName = reader.GetString(1);
Customer.ContactName = reader.GetString(2);
Customer.ContactTitle = reader.GetString(3);
Customer.Street = reader.GetString(4);
Customer.City = reader.GetString(5);
Customer.PostalCode = reader.GetString(6);
Customer.Country = reader.GetString(7);
Customer.Phone = reader.GetString(8);
}
else
{
return false;
}
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return IsSuccessFul;
}
Calling the method in a unit test by passing in a valid primary key. If the method returns true then assert that the customer found has the company name taken from the database table. This is a brittle test as the customer name may change. A better option would be to create mocked data, insert that data prior to the test, remove the data afterwards. There are more advance methods to deal with setting up unit test so they are not brittle where with one or three tables one can implement easily yet when dealing with ten or more tables with complex relationships time and care needs to be taken to setup a test environment. See the following for a model to following using Entity Framework.
[TestMethod]
[TestTraits(Trait.Positive)]
public void GetSingleCustomerByIdentifierUsingOutParameter()
{
// arrange
var ops = new DataOperations();
var id = 14;
// act
if (ops.CustomersSingleByOutParameter(id,out var customer))
{
Assert.IsTrue(customer.CompanyName == "Consolidated Holdings");
}
else
{
Assert.IsTrue(1== 0,
$"Expected to find customer with id of {id}");
}
}
Another option is to return data using named tuples aka ValueTuple, the idea candidate when working with data is to supplement any of the above method with a method returning tuples with several fields. For example, all information is not required, only one or a handful of fields. Keeping with the other methods you need to include a return value, suggest as the first return value which indicates success from executing the method followed by values needed in the program. In this case the first value is success followed by contact name and title, perhaps returning a contact number too yet in a properly done database schema for contacts a handful of tables may be used, for that the contact name is not included in this example to keep on track of using tuples which you the developer may expand on.
Note: With traditional Tuple, we must allocate a class instance but with ValueTuple, the Framework uses dynamic types to reduce overhead. Measuring performance, ValueTuple is a third faster on average than a conventional Tuple.
public (bool Success, string ContactName, string ContactTitle) CustomerContactNameTitleUsingTuples(int pId)
{
mHasException = false;
const string selectStatement =
"SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier " +
"WHERE cust.CustomerIdentifier = @Id";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cmd.Parameters.AddWithValue("@Id", pId);
cn.Open();
var reader = cmd.ExecuteReader();
reader.Read();
return reader.HasRows ? (true, reader.GetString(2), reader.GetString(3)) : (false, "", "");
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
return (IsSuccessFul, "","");
}
For testing in this case only success is checked. Note the underscores, this is known as discards, using an underscore ignores the name and title.
[TestMethod]
[TestTraits(Trait.Positive)]
public void GetSingleCustomerUsingValueTuple()
{
// arrange
var ops = new DataOperations();
var id = 1;
// act
// ReSharper disable once InconsistentNaming
var (Success, _, _) = ops.CustomerContactNameTitleUsingTuples(id);
// assert
Assert.IsTrue(Success);
}
To get the values replace the underscores with variable names e.g.
[TestMethod]
[TestTraits(Trait.Positive)]
public void GetSingleCustomerUsingValueTuple()
{
// arrange
var ops = new DataOperations();
var id = 1;
// act
// ReSharper disable once InconsistentNaming
var (Success, contactName, contactTitle) = ops.CustomerContactNameTitleUsingTuples(id);
Console.WriteLine($"{contactName}, {contactTitle}");
// assert
Assert.IsTrue(Success);
}
Once the test method is finished, click the green check, click output to see the values.
Oracle example
All examples so far have been SQL-Server, to see there is really no difference with other databases, here is a ValueTuple example using Oracle managed data provider where in this case success is the first value returned followed by a List<Decimal> of keys working from a WHERE condition in a SELECT.
public (bool Success, List<decimal> MessageKeyList) OcsMessageKeys(string pTemplateName)
{
mHasException = false;
var results = new List<decimal>();
var selectStatement =
"SELECT ID FROM Templates " +
$"WHERE TemplateName = :TemplateName";
using (OracleConnection cn = new OracleConnection() { ConnectionString = ConnectionString })
{
using (OracleCommand cmd = new OracleCommand() { Connection = cn })
{
cmd.CommandText = selectStatement;
cmd.BindByName = true;
cmd.Parameters.Add(":TemplateName", pTemplateName);
try
{
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
results.Add(reader.GetDecimal(0));
}
}
catch (Exception ex)
{
mHasException = true;
mLastException = ex;
}
}
}
return (IsSuccessFul,results);
}
SQL-Server asynchronous
All the methods presented have been synchronous, all of those methods may be implemented as asynchronous. The following example demonstrates tuples asynchronously, no example for out parameters as this is not currently supported. In the code sample presented below success is determined by the first return value and may also be checked via HasException property of the base exception class.
public async Task<(bool Success, List<Customer> Customers)> GetSCustomersUsingTuplesAsync()
{
mHasException = false;
var results = await Task.Run(() =>
{
mHasException = false;
var customersList = new List<Customer>();
const string selectStatement =
"SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " +
"cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " +
"cust.ContactTypeIdentifier FROM dbo.Customers AS cust " +
"INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;";
using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
{
using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
{
try
{
cn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
customersList.Add(new Customer()
{
CustomerIdentifier = reader.GetInt32(0),
CompanyName = reader.GetString(1),
ContactName = reader.GetString(2),
ContactTitle = reader.GetString(3),
Street = reader.GetString(4),
City = reader.GetString(5),
PostalCode = reader.GetString(6),
Country = reader.GetString(7),
Phone = reader.GetString(8)
});
}
}
catch (Exception e)
{
mHasException = true;
mLastException = e;
}
}
}
// IsSuccessFul equates to returning all data, no data or partial data
// customerList will contain, no customers, some customers, all customers
return (IsSuccessFul, customersList);
}).ConfigureAwait(false);
return results;
}
Summary
Several options have been presented to return data using new features of C#7 coupled with returning a value for the success of a read operation. This does not mean there is a need to adapt any of these methods unless there is a compelling reason in your project. Do these have a place in MVC or repository pattern? It would depend if your setup is fixed for specific entities or if for example using a generic repository where using tuples and out parameters don’t fit well.
See also
Source code
https://github.com/karenpayneoregon/CSharpWorkingWithOutParamsWithData