Wednesday, October 26, 2016

How to query a database in .NET Core using System.Data and SimpleNet.core

Welcome friends,

If you are anything like me and would rather write SQL statements instead of using Entity Framework to query the database in .NET Core this post is for you.

I have written two Nuget packages that should be very helpful for you
  • SimpleNet.Core.Data (DAL using System.Data)
  • SimpleNet.Core.SqlServer (Wrapper for System.Data.SqlClient)
The source code can be found at https://github.com/Hem/SimpleNet.Core


Step 1: Create an instance of the "ISimpleDatabaseProvider"


var dbProvider = new SqlServerProvider("Your SQL server Connection String");


Step 2: Create an instance of the Data Access Layer
var dal = new SimpleDataAccessLayer(dbProvider);

Step 3: Run a Query

dal.ExecuteNonQuery (
                      "UPDATE Table SET Col1=@param1 WHERE ...",
                       CommandType.Text,
                        new []{ dbProvider.GetParameter("@param1", SomeValue) });


Ok so how do we query the database to return some records?
Well You can run a select query against the database however you need to provide a mapper to return an array of objects instead of a data row.


Step 1: Create a IRowMapper<T>

private static readonly IRowMapper<State> StateMapper = MapBuilder<State>.BuildAllProperties();
// sample 2
private static readonly IRowMapper<State> StateRowMapper = MapBuilder<State>.MapAllProperties().Build();
// sample 3 for mapping
private static readonly IRowMapper<State> StateRowMapper2 = MapBuilder<State>
                   .MapNoProperties()
                   .MapByName(x => x.Id)
                   .Map(x => x.Code).ToColumn("Code")
                   .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
                   .Build();
Step 2: Query the database

// dal.Read( .... ) // For Non Async

await dal.ReadAsync( "SELECT ....", CommandType.Text, new [] { /* db parameters*/ });


I would recommend you use the "IRepository" pattern and Create a "StateRepository" for the above Example.


Hint: We have provided an "AbstractSimpleRepository" that you can extent Your BaseRepository..


If you would like to query any other database for example Oracle or MySql, please provide an implementation of "ISimpleDatabaseProvider"

For the standard .net framework (4.5) you can use the "SimpleNet.Data" nuget package.

Wednesday, August 3, 2016

DOTNET CORE and OpenSSL 101 on Mac OSX

Oh! the pains to get dotnet running on mac :)


  • You need to update openssl to 1.0.1 from 0.9.x
  • To update openssl you will have to run a command "csrutil disable" so Mac OSX will allow updating of the openssl

Get openssl101 on your machine using brew.
  • brew update
  • brew doctor
    • Fix any issues that may arrive
  • brew install openssl101

Check what version of openssl you have running by running the following in the Terminal
  • openssl version -a
  • which openssl

Try moving openssl to ~/ if you get errors... you need to reboot mac in recovery and run "csrutil disable"


  • sudo mv /usr/bin/openssl ~/


  • Now you will need to link /usr/bin/openssl to openssl101 in /usr/local/Cellar/openssl101/

    You need to Find the version of openssl that was installed

    • brew list openssl101
    This should list something like


    • /usr/local/Cellar/openssl101/1.0.1t_1/bin/c_rehash
    • /usr/local/Cellar/openssl101/1.0.1t_1/bin/openssl
    • /usr/local/Cellar/openssl101/1.0.1t_1/include/openssl/ (75 files)
    • /usr/local/Cellar/openssl101/1.0.1t_1/lib/libcrypto.1.0.0.dylib
    • /usr/local/Cellar/openssl101/1.0.1t_1/lib/libssl.1.0.0.dylib
    • /usr/local/Cellar/openssl101/1.0.1t_1/lib/engines/ (12 files)
    • /usr/local/Cellar/openssl101/1.0.1t_1/lib/pkgconfig/ (3 files)
    • /usr/local/Cellar/openssl101/1.0.1t_1/lib/ (4 other files)
    • /usr/local/Cellar/openssl101/1.0.1t_1/share/man/ (1186 files)


    Just run the following commands...

    • sudo ln -s /usr/local/Cellar/openssl101/openssl101/bin/openssl /usr/bin
    • sudo ln -s /usr/local/Cellar/openssl101/1.0.1t_1/lib/libcrypto.1.0.0.dylib /usr/local/lib/
    • sudo ln -s /usr/local/Cellar/openssl101/1.0.1t_1/lib/libssl.1.0.0.dylib /usr/local/lib/



    Now you can download and install DotNet Core or reinstall it... :)


    Wednesday, April 6, 2016

    Introduction to "SimpleNet.Data.dll" a Simple Data Access Layer

    I found a lot of teams implementing their version of a "Data Access Layer" some allowed the users to interact with Data Tables other versions allowed for using a Mapping Layer to interact with Objects. I personally would rather interact with an array (list) of typed objects. The most difficult part of this is usually finding a simple solution that allows for mapping of DataRow to an Object.

    The usual approach using a DataTable

      • Query the database load all data into a DataTable
      • Pass the DataTable to a "IMapper" that returns an array of objects
      • Using an AutoMapper when the columns match the properties in an object.

    One of the best and most flexible approaches I found was implemented in "Enterprise Library Data Access", however using Enterprise Library's data access to pass parameters to your query is a Pain.

    So a super simple implementation of DAL with an IRepository wrapper.


    If you wish to query the database and work with DataTables

    var dal = new Simple.Data.Repository.SimpleDataAccess("connectionname");
    var dataTable = dal.ReadSql("SELECT * FROM USERS", null);
    var dataTable = dal.ReadSql("SELECT... where u.UserId = @UserId",  new []{ dal.GetDbParameter("@UserId", 123});


    Working with Objects instead of Data Tables



    
    // 1. Create the Base SQL Repository
    public class BaseSqlRepository : AbstractSimpleSqlRepository
    {
        public override sealed ISimpleDataAccess Database { get; set; }
    
        public BaseSqlRepository()
        {
           Database = new SimpleDataAccess("Connection_Name");
        }
    }
    
    
    
    // Create your concrete implementation of IRepository
    public class StateRepository : BaseSqlRepository, IStateRepository
    {
             // Please review documentation from Enterprise Libary Db Accessors 
             // to Find out more about IRowMapper
    
     private static readonly IRowMapper StateMapper = 
       MapBuilder.BuildAllProperties();
    
             // sample 2
     private static readonly IRowMapper StateRowMapper = 
       MapBuilder.MapAllProperties().Build();
    
              // sample 3 for mapping
     private static readonly IRowMapper StateRowMapper2 = 
       MapBuilder
                       .MapNoProperties()
                       .MapByName(x => x.Id)
                       .Map(x => x.Code).ToColumn("Code")
                       .Map(x => x.Name).WithFunc(x => x["Name"].ToString())
                       .Build();
    
    
    
            public State GetById(int id)
            {
                    const string SQL = @"SELECT Id, Code, Name 
                         FROM STATE s where s.Id = @Id";
    
                    return Read(StateMapper, SQL, CommandType.Text, 
                     new[]
                     {
                         GetDbParameter("@Id", id)
                     }).FirstOrDefault();
            } 
    }
    
    
    
    
    // Working with transactions
    using (var connection = Database.GetConnection())
    {
        using (var transaction = connection.BeginTransaction())
        {
            try{
    
                Database.ExecuteNonQuery(connection, ..... , transaction);
                ...     
                Database.ExecuteScalar(connection, ..... , transaction);
                ...
                transaction.Commit();
    
            }catch(){
                transaction.Rollback();
            }
        }
    }
    
    
    

    Friday, March 18, 2016

    How to Setup SSL Local ROOT Authority, Intermediate Authority, and wildcard certificates

    This article is for Enterprise users who would like to create SSL certificates for internal domains (.local)

    • For a SSL certificate to be trusted on the browser the Signing Authority has to be "Trusted" explicitly, this will lovingly be referred to as the "Root CA".
    • We will also create an "Intermediate CA" this will be signed by the "Root CA" and will be the signing authority for the Server (websites).
    • We will then create the "Website Certificate", essentially you can issue as many SSL certificates as you want (one for each domain / sub-domain URL)

    It is my understanding that the "Root CA" is the only one that needs to be installed and explicitly trusted on the user's computer. The "Intermediate CA" and the "Website Certificate" can be sent from the web server. This might happen auto-magically (should be installed on the web server/proxy server) or both certificates can be packaged as one. I'm not sure :)

    Note: The common name (also known as the "CN" in the config files) is important!


    Certificate Examples
    1. MyCorp Root CA :- This is the main "trusted" certificate.
    2. MyCorpIntermediate CA:- This is what will sign all our domain certificates.
    3. www.mycorp.local :- This certificate is domain specific
    4. *.mycorp.local :- Wildcard certificate that can be used for any mycorp.local subdomain (mail.mycorp.local, web.mycorp.local, etc...)


    My sub directory structure (organization baby)

    SSL (contains batch files so I do not type this in a command prompt)
    • certs: The Final SSL certificates
    • config: contains config files as I really do not like answering questions in a command prompt
    • keys: do not loose this baby, guard it with your life... or not.
    • requests: This is what you would traditionally send to a signing authority

    Open SSL


    • Just get it!

    Step 1: MyCorp Root CA

    1. Create the key oh and password protect it with "- des3".
      • openssl genrsa -des3 -out keys/ipg-root-ca.key 2048
    Do not forget the password
    Do not loose the key
    Do guard it with your life or not!
    1. Create a config file in [config/mycorp-root-ca.conf]
      • [ req ]
      • prompt = no
      • distinguished_name = req_distinguished_name
      • x509_extensions = v3_ca

      • [ req_distinguished_name ]
      • C = US
      • ST = TN
      • L = Nashville
      • O = MYCORP, INC
      • OU = MYCORP RootCA
      • CN = MYCORP-RootCA
      • emailAddress = hem@mycorp.local

      • [ v3_ca ]
      • subjectKeyIdentifier=hash
      • authorityKeyIdentifier=keyid:always,issuer:always
      • #basicConstraints = critical,CA:true
      • basicConstraints = CA:true
    2. Create the self signed root certificate
      • openssl req -new -sha256 -x509 -days 3650 -key keys/mycorp-root-ca.key -out certs/mycorp-root-ca.crt -config config/mycorp-root-ca.conf
    3. You can validate the certificate
      • openssl x509 -noout -in certs/mycorp-root-ca.crt -text -purpose

    You will need to install this certificate (" MYCORP-RootCA" ) as "trusted" on your users computers...




    Now that we have our root certificate and it is installed on all users computers as a trusted certificate we can now issue certificates that our computers will trust...



    Step 2: MyCorp Intermediate CA

    We will create an "MyCorp Intermediate-CA" certificate that will exclusively be used to sign other certificates (websites, domains, users, etc...)
    • You may opt to create multiple intermediate certificates (one for each division, example:- Security, Business Intelligence, Web Application, Accounting & Finance)
    Don't go crazy, simplicity usually is the best :)

    1. Create a new RSA Key

      • openssl genrsa -des3 -out keys/mycorp-intermediate-ca.key 2048

    2. Generate a request to be signed by the "Root-CA" (enter password as requested)

      • openssl req -new -sha256 -key keys/mycorp-intermediate-ca.key -out requests/mycorp-intermediate-ca.req -config config/mycorp-intermediate-ca.conf

    3. Oh! the config file [ mycorp-intermediate-ca.conf] :)
      • [ req ]
      • prompt = no
      • distinguished_name = req_distinguished_name

      • [ req_distinguished_name ]
      • C = US
      • ST = TN
      • L = Nashville
      • O = MYCORP, INC
      • OU = MYCORP IntermediateCA
      • CN = MYCORP-IntermediateCA
      • emailAddress = hem@mycorp.local

      • [ ssl_server_ca ]
      • subjectKeyIdentifier=hash
      • authorityKeyIdentifier=keyid:always,issuer:always
      • #basicConstraints = critical,CA:true
      • basicConstraints = CA:true
      • keyUsage = keyCertSign

    4. Sign the certificate using the root certificate... (Enter password when requested)
    • openssl x509 -req -sha256 -in requests/mycorp-intermediate-ca.req -out certs/mycorp-intermediate-ca.crt -extfile config/mycorp-root-ca-sign.conf -extensions ssl_server_ca -CA certs/mycorp-root-ca.crt -CAkey keys/mycorp-root-ca.key -CAcreateserial -days 3650
    1. Oh! another config file [ mycorp-root-ca-sign.conf]
    • [ ssl_server_ca ]
    • subjectKeyIdentifier=hash
    • authorityKeyIdentifier=keyid:always,issuer:always
    • #basicConstraints = critical,CA:true
    • basicConstraints = CA:true
    • keyUsage = keyCertSign



    Cool Yeah!!!! now for the meat and potatoes baby!!!

    Step 3: Let's create the website SSL certificate

    The common name (CN) is important, this should match the domain address.

    Example:
    https://mail.mycorp.local
    https://blog.mycorp.local
    https://vpn.mycorp.local


    You can issue three different SSL certificates for each of these sub-domains or you could issue a wildcard certificate and use that across all three subdomains (*.mycorp.local)


    1. Create the RSA Key
      • openssl genrsa -out keys/_.mycorp.local.key 2048
    2. Create the request (CSR)
      • openssl req -new -sha256 -key keys/_.mycorp.local.key -out requests/_.mycorp.local.req -config config/_.mycorp.local.conf
    3. Sign the request using the intermediate ssl
      • openssl x509 -req -sha256 -in requests/_.mycorp.local.req -out certs/_.mycorp.local.crt -CA certs/mycorp-intermediate-ca.crt -CAkey keys/mycorp-intermediate-ca.key -CAcreateserial -days 3650
    4. Yet! another config file [config/mycorp.local.conf]

      • [ req ]
      • prompt = no
      • distinguished_name = req_distinguished_name

      • [ req_distinguished_name ]
      • C = US
      • ST = TN
      • L = Nashville
      • O = MYCORP, INC
      • OU = MYCORP Web Team
      • CN = *.mycorp.local
      • emailAddress = hem@mycorp.local
    Enjoy!!!
    -Hem