Come posso migliorare questo "modello"?

Recentemente ho rifattorizzato un po 'del nostro codice di database per utilizzare l' Dapper ORM . Ciò ha comportato un codice simile a questo.

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var p = new DynamicParameters();

    p.Add("@ServerIP", "192.168.100.200");
    p.Add("@PartName", "Dapper");
    p.Add("@Config_Data_Modified", false);
    p.Add("@LatestRecord", false);
    p.Add("@IsRunning", true);
    p.Add("@Stopped", false);
    p.Add("@SessionID", dbType: DbType.Int32, direction: ParameterDirection.Output);

    connection.Execute(
        "DECLARE @TmpTable TABLE (ID INT) " +
        "INSERT Session(ServerIP, PartName, Config_Data_Modified, LatestRecord, IsRunning, Stopped) " +
        "OUTPUT Inserted.SessionID INTO @TmpTable " +
        "VALUES (@ServerIP, @PartName, @Config_Data_Modified, @LatestRecord, @IsRunning, @Stopped)" +
        "SELECT @SessionID = ID FROM @TmpTable", p);

    var sessionID = p.Get<Int32>("@SessionID");
}

Ciò ha migliorato enormemente la leggibilità del codice a mio parere. Non so cosa stia succedendo nella tua classe di utilità, ma se si tratta di un semplice DAL, non dovrebbe essere troppo difficile sostituirlo con un ORM leggero come Dapper.

Lasciando da parte i problemi SQL (usa proc parametrizzati ecc anziché SQL inline) e indirizzando il codice nella domanda.

Invece di usare il generatore di stringhe, usa System.IO.StringWriter usa un StringBuilder sotto le copertine ma ti dà l'intera interfaccia di TextWriter che conosciamo da System.Console e System.IO.StreamWriter

Rende le cose molto più leggibili IMHO

public void CallDataBase()
{
   Utility sqlHelper = new Utility();   

   StringWriter query = new StringWriter();
   query.WriteLine("update {0}", tableName);//or insert, select
   query.WriteLine(" set " );
   //...
   utility.ConnectAndExecuteNonQuery(query.ToString());//or query, scalar   
} 

Inoltre, puoi costruirlo con un'istanza StringBuilder se hai davvero bisogno di inizializzare StringBuilder .

   StringWriter query = new StringWriter(new StringBuilder(200));

Trovo che SQL nel codice C # sia piuttosto orribile. Sembra inelegante con tutte quelle concatenazioni di stringhe, è difficile da modificare, difficile riutilizzare le query e difficile da testare perché non si può semplicemente copiare la query dal codice sorgente e incollarla in uno strumento di database come SSMS.

Preferisco inserire tutto SQL in uno o più file di risorse (Aggiungi - > File di risorse). Di solito lo chiamo "sql.resx".

Piuttosto che:

connection.Execute(
    "DECLARE @TmpTable TABLE (ID INT) " +
    "INSERT Session(ServerIP, PartName, Config_Data_Modified, LatestRecord, IsRunning, Stopped) " +
    "OUTPUT Inserted.SessionID INTO @TmpTable " +
    "VALUES (@ServerIP, @PartName, @Config_Data_Modified, @LatestRecord, @IsRunning, @Stopped)" +
    "SELECT @SessionID = ID FROM @TmpTable", p);

Mi piace il codice per avere un aspetto più simile a

connection.Execute(sql.InsertStuffIntoSomeTable, p);

Con il file di risorse che memorizza il testo SQL copiabili, modificabile, riutilizzabile e testabile senza tutto questo + "junk" +.

    DECLARE @TmpTable TABLE (ID INT)
    INSERT Session(ServerIP, PartName, Config_Data_Modified, LatestRecord, IsRunning, Stopped)
    OUTPUT Inserted.SessionID INTO @TmpTable
    VALUES (@ServerIP, @PartName, @Config_Data_Modified, @LatestRecord, @IsRunning, @Stopped)
    SELECT @SessionID = ID FROM @TmpTable

Disclaimer: non ho esperienza nel mondo C #, le mie osservazioni sono interamente basate sullo spazio Java.

Ho visto API fluenti utilizzate per questo . Con alcuni di quelli sofisticati, sarai in grado di scrivere (tipizzato o non tipizzato, a seconda dell'API) almeno alquanto query tipo LINQ.

Qualunque cosa si sentirà come un compromesso, dato che qualsiasi linguaggio sufficientemente flessibile per gestire query complesse tenderà a diventare un tipo di utilizzo intensivo.

Come è troppo lavoro creare una stored procedure?

Se sei disposto ad entrare e modificare il codice comunque, come sarebbe più un lavoro per copiare lo sql inline in un proc?

Per lo meno, usa le query parametrizzate. È possibile archiviarli per SQL in file sql e caricarli secondo necessità (e memorizzarli nella cache man mano che vengono caricati)?

In questo modo puoi semplicemente scrivere sql senza tutto il markup extra #.

Ho creato una classe che mi aiuta nell'eseguire e caricare i risultati di SQL Query, quindi questa è la classe

public static VOSqlResult Load(string cName, string command, params SqlParameter[] parameters)
        {
            DataTable tbl = new DataTable();
            VOSqlResult result = InitResult();
            SqlConnection cn = CreateConnection(cName);
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(command);
                cmd.CommandTimeout = 3600;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                    result.Table.Load(reader);
            }
            catch (Exception ex)
            {
                Logger.Error(String.Format("DataHelper -> Loading data [{1}] {0}", ex.Message, command), ex);
                result.Error = ex.Message;
                result.Valid = false;
            }
            finally
            {
                CloseConnection(cn);
            }
            return result;
        }

        public static VOSqlResult ExecuteScalar(string cName, string command, params SqlParameter[] parameters)
        {
            DataTable tbl = new DataTable();
            VOSqlResult result = InitResult();
            SqlConnection cn = CreateConnection(cName);
            try
            {
                cn.Open();

                SqlCommand cmd = new SqlCommand(command);
                cmd.CommandTimeout = 3600;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Logger.Error(String.Format("DataHelper -> Executing scalar [{1}] {0}", ex.Message, command), ex);
                result.Error = ex.Message;
                result.Valid = false;
            }
            finally
            {
                CloseConnection(cn);
            }
            return result;
        }

        private static void CloseConnection(SqlConnection cn)
        {
            try
            {
                cn.Close();
            }
            catch (Exception ex)
            {
                Logger.Error(String.Format("DataHelper Closing COnnection -> {0}", ex.Message), ex);
                //TODO: create more dynamic solution
            }
        }

        private static VOSqlResult InitResult()
        {
            VOSqlResult v = new VOSqlResult();
            v.Table = new DataTable();
            v.Valid = true;
            v.Error = "";
            return v;
        }

        private static SqlConnection CreateConnection(string cName)
        {
            SqlConnection cn = new SqlConnection(ConfigReader.CStringGet(cName));

            return cn;
        }

        public static VOSqlResult ExecuteStoreProcedure(string cName, string spName, params SqlParameter[] parameters)
        {
            DataTable tbl = new DataTable();
            VOSqlResult result = InitResult();
            SqlConnection cn = CreateConnection(cName);
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(spName);
                cmd.CommandTimeout = 3600;
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                    result.Table.Load(reader);
            }
            catch (Exception ex)
            {
                StringBuilder sb = new StringBuilder();
                foreach (var sqlParameter in parameters)
                {
                    sb.AppendFormat("[{0} -> {1}]", sqlParameter.ParameterName, sqlParameter.Value.ToString());
                }

                Logger.Error(String.Format("DataHelper -> Executing store procedure {1} with the params {2} -- {0}", ex.Message, spName, sb.ToString()), ex);
                result.Error = ex.Message;
                result.Valid = false;
            }
            finally
            {
                CloseConnection(cn);
            }

            return result;
        }
    }

    public class VOSqlResult
    {
        public String Error;
        public DataTable Table;
        public Boolean Valid;        
    }

Ho un altro ConfigReader di classe in grado di leggere stringhe di connessione ma puoi sostituirlo con ConfigurationManager

e questo è come usarlo

VOSqlResult rez = DataHelper.Load("connection string name", @"Select * from 
                      my_table where
                         Email = @Email", new SqlParameter("@Email", "[email protected]"));

puoi avere tutti i parametri che desideri. Abbastanza semplice per