Working with data

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.

  • Code every aspect from creating a connection and command object, which also requires writing SQL statements. Data read is stored in list or in a DataTable where a DataTable can be contained in a DataSet for relational data.
  • Minimal code using an Adapter

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
                                      }
                                    }

Which to choice

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.

  • ASP.NET Core projects are in the sense of a word Entity Framework Core ready
  • By placing data centric code into a class project both web and desktop solutions can share the code base

Entity Framework code samples

  • Query tags provide an easy way for a developer or DBA to find specific queries, code sample
  • Projections and INotifyPropertyChanged code sample for property change notification

Entity Framework pro/cons

Pros

Cons

  • Works best with primary keys
  • No scafolding tool for Oracle at this time, Karen Payne created the following tool which provides what's needed.'
  • When table schemas change, models need to be regen's (Is this really a disadvantage?)
    • Actually a regen or a migration will handle this for us
  • Keyless Entity Types fall into pros and 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 to C#

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);
                                
                                }

Oracle scafolding inhouse tool

See also