There are two paths for working with databases. Both paths can interact with all major databases.
Microsoft does not provide classes for working with all databases, instead the company behind the database e.g. Oracle for example writes the classes to work with their database. There are rules for those writing core classes for working with data. For instance to connect to data the connection class must have certain methods, properties and events. To read, edit, insert, delete a command class must have specific methods, properties and events. Since there are no review process by Microsoft a company may have issues with their provider classes which in turn means we are at the mercy of the company who wrote the classes.
conventional path, what the majority of developers start off with and in many cases stay with. Although many will advocate the second path is better it really depends on the task need to be performed along with the experience of the developer coding.
Example for accessing data
/// <summary>
/// Get all Contact types for DataGridView ComboBox column
/// </summary>
/// <returns></returns>
public static DataTable ContactTypeTable()
{
var dt = new DataTable();
using var cn = new SqlConnection(ConnectionString);
using var cmd = new SqlCommand() { Connection = cn };
cmd.CommandText = "SELECT ContactTypeIdentifier, ContactTitle FROM dbo.ContactType;";
cn.Open();
dt.Load(cmd.ExecuteReader());
return dt;
}
The actual connection string for the above is stored within the same class although okay for novice and short term usage connection strings are commonly stored in configuration files.
private static string ConnectionString =
"Data Source=.\\SQLEXPRESS;" +
"Initial Catalog=NorthWindAzureForInserts;" +
"Integrated Security=True";
Entity Framework is the second option which is supported by all major databases and as with companies writing classes to access data can have issues. Here are the providers.
The following mirrors the above code sample to perform the exact same operation.
public static List<ContactType> ContactTypes()
{
using (var context = new NorthWindContext())
{
return context.ContactType.AsNoTracking().ToList();
}
}
Although upon first inspection the Entity Framework code has a smaller footprint there is a good deal of code behind which is done usually using a Visual Studio free or paid for extension. Code generated by one of these extensions produces clear, easy to understand code. Also, code generated by an extension can rerun and not distrub cusom code when done right and is easy.
Connecting to a database
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
/*
* Read connection string from client calling this class
*/
optionsBuilder.UseSqlServer(Helper.GetConnectionString());
}
}
Connection string stored in a json file which has two environments and can had been done with one, two or three.
{
"ConnectionStrings": {
"DevelopmentConnection": "Server=.\\SQLEXPRESS;Database=NorthWind2020;Integrated Security=true",
"ProductionConnection": "Server=.\\SQLEXPRESS;Database=NorthWind2020;Integrated Security=true"
},
"Environment": {
"Production": false
}
}
For developers in the course building web solutions, Entity Framework Core while for utility work either conventional or Entity Framework dependent on the task and clients.
Pros
Cons
Some will praise EF Core while others will bash EF Core. For instance, some will say its syntax is complicated, which is dependent on the developer’s skill level or willingness to adapt.
Cold Fusion
C# see also: configuration for model
public static async Task<IccTran> GetTransaction(decimal id, LanguageCodes language)
{
var code = language.ToText();
await using var context = new Context();
return await context.IccTrans.FirstOrDefaultAsync(trans => trans.Id == id && trans.TrLanguageCode == code);
}
Language code for above
public enum LanguageCodes
{
/// <summary>
/// English
/// </summary>
English = 1,
/// <summary>
/// Spanish
/// </summary>
Spanish = 2
}
Usage in a unit test method
[TestMethod]
[TestTraits(Trait.OracleEntityFrameworkCore)]
public async Task ValidateGetTransactionByIdentifierAndLanguageTestMethodNotExistsTask()
{
// arrange
var transIdentifier = 64381986;
var completeTime = new DateTime(2020, 8, 4, 11, 15, 0);
var languageCode = LanguageCodes.Spanish;
// act
var transaction = await TransactionOperations.GetTransaction(transIdentifier, languageCode);
// assert
Assert.IsTrue(transaction is null);
}
