Simple DataAccess class for c# and oracle

There are lots of information out there on ORM/ Microsoft Enterprise Library Framework (Data Access Application Block)/Generic Repository etc as well as lot of patterns (Active Record/DAO/TDG) on how to structure your code for large scale applications.

However for many small scale applications (especially Desktop Based Applications) you just want to use plain old ADO.net/ODP.net.With Managed ODP.net and Publishing on Visual Studio, its a breeze to deploy these apps on any client machines. (ofcourse they need to have .net framework installed).

Here is  a very simple DataAccess Class that uses the Oracle.ManagedDataAccess that you can reuse across all your applications. It has two methods (and we can add more similar methods) one that establishes a connection and one that executes a select query that takes in a dictionary of string parameters. The second method returns a dataset which you can then read based on the needs of your application.If you want params of other types, you could make the dictionary<string,object>. Though there would be performance penalty on type conversion, you could live with that for smaller applications. Again, this is a very simplified version and you could extend it by using Generics thereby taking in any params (albeit all of them should be of same type !!)

 public class DataAccess
    {
        public static OracleConnection Connect()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["myDb"].ConnectionString;
            OracleConnection connection = new OracleConnection(connectionString);
            connection.Open();
            return connection;
        }

       public string GetScalar(string selectSql,Dictionary<string,string> paramList)
       {
           
            using (var conn = DataAccess.Connect())
            {
                OracleCommand cmd=new OracleCommand(selectSql,conn);
                cmd.BindByName = true;
                cmd.Parameters.Clear();
                try
                {
                    foreach (var param in paramList)
                    {
                        cmd.Parameters.Add(param.Key, String.IsNullOrEmpty(param.Value) ? String.Empty : param.Value);
                    }
                    var colValue = cmd.ExecuteScalar();
                    if (colValue != null)
                        return colValue.ToString();
                    else
                    {
                        return "Empty";
                    }
                }
                catch (OracleException oex)
                {
                    throw oex;
                    //return "Empty";
                }
                finally{
                    conn.Close();
                    conn.Dispose();
                }
            }
       }
        public DataSet DoSelect(string selectSql,Dictionary<string,string> parameters,string tableName)
        {
            DataSet dSet1=new DataSet();
            DataTable dTable = new DataTable();

            dTable.TableName = tableName;
            
            using (var conn = DataAccess.Connect())
            {
                OracleCommand cmd = conn.CreateCommand();
                cmd.BindByName = true;
                cmd.CommandText = selectSql;
               
                cmd.Parameters.Clear();
                try
                {
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            cmd.Parameters.Add(param.Key, String.IsNullOrEmpty(param.Value) ? String.Empty : param.Value);
                        }
                    }
                    OracleDataReader rdr = cmd.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        
                        for (var i = 0; i <= rdr.FieldCount - 1; i++)
                        {
                            dTable.Columns.Add(new DataColumn(rdr.GetName(i), Type.GetType("System.String")));

                        }
                        while (rdr.Read())
                        {
                            var dr = dTable.NewRow();
                            for (var i = 0; i <= rdr.FieldCount - 1; i++)
                            {
                                dr[i] = rdr[i].ToString();

                            }
                            dTable.Rows.Add(dr);

                        }
                        rdr.Close();
                        dSet1.Tables.Add(dTable);                     
                        conn.Close();
                        conn.Dispose();
                        return dSet1;
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (OracleException oex)
                {
                  
                    throw oex;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
             
            }
            
        }
    }

SHA-512 encryption c#

Recently we had to write an external utility that talks to a third party. The third party required us to authenticate through their SHA-512 encryption and we were not given what kind of encoding we may have to support. They were using java and its version of SHA.

If they were using unicode UTF-16 encoding, there would have been a difference in which c# vs java handles little endian vs big endian. Java uses big endian byte order whereas c# defaults to little endian. However if they were using UTF-8, then it works the same for both languages.In our case, it was indeed UTF-8 and so our implementation was fairly straightforward.

public static string EncryptUsingSHA(string text)
 {
  string hex = "";
  SHA512 alg = SHA512Managed.Create();
  byte[] result = alg.ComputeHash(Encoding.Default.GetBytes(text));
  string hash = Encoding.UTF8.GetString(result);
  foreach (byte x in result)
  {
   hex += String.Format("{0:x2}", x);
  }
  return hex;
}

If you were to encounter UTF-16 between java and c# you would have to specify the little endian encoding in Java (UTF-16LE) and use the default UTF-16 encoding in c#.