Beispiel #1
0
        static public DataTable ExecuteQuery(string connectionString, string sql)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Setting up ..."));
            DataTable dataTable = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                    using (SqlCommand cmd = new SqlCommand(sql, connection))
                    {
                        log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString)));
                        connection.Open();
                        log.Info(string.Format("Query: {0}", sql));
                        log.Info(string.Format("Query execution starting ..."));

                        using (var reader = cmd.ExecuteReader())
                        {
                            dataTable = new DataTable();
                            dataTable.Load(reader);
                        }
                        log.Info(string.Format("Query executed! Result count: {0}", LibString.ItemsNumber(dataTable)));
                        connection.Close();
                        log.Info("Connection Closed!");
                    }
            }
            catch (Exception ex)
            {
                log.Info("Excepion detected!");
                log.Error(ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(dataTable);
        }
Beispiel #2
0
        static public int DeleteOperation(string connectionString, string tabName, Dictionary <string, QueryCondition> conditions)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            int result = -1;

            try
            {
                string query = "DELETE FROM " + tabName + " WHERE " +
                               string.Join(" ", conditions.Select(x => x.Value.Key + x.Value.Op + "@" + x.Key + " " + x.Value.Conj).ToArray());
                Dictionary <string, object> pars = new Dictionary <string, object>();
                foreach (KeyValuePair <string, QueryCondition> entry in conditions)
                {
                    pars[entry.Key] = entry.Value.Value;
                }

                log.Info(string.Format("Query: {0}", query));
                log.Info(string.Format("Params: {0}", string.Join("; ", pars.Select(x => x.Key + "=" + x.Value).ToArray())));

                result = ExecuteNonQueryWithParams(connectionString, query, pars);

                log.Info(string.Format("Query Executed! Inserted {0} records!", result));

                return(result);
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                log.Error(msg + " " + ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }
        }
Beispiel #3
0
        // GetAll
        public List <T> GetAll()
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Starting Retrieving procedure ..."));

            List <T> entities = null;

            try
            {
                string connectionString = this.DBConnectionString;

                string table = this.DBTabName;

                DataTable data = DBSQL.SelectOperation(connectionString, table, null);
                log.Info(string.Format("DBSQL Query Executed! Retrieved {0} record!", LibString.ItemsNumber(data)));
                if (data != null)
                {
                    entities = MapperDataTableToEntities.Invoke(data);
                    log.Info(string.Format("{0} Records mapped to {1}", LibString.ItemsNumber(entities), LibString.TypeName(entities)));
                }
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                throw ex;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(entities);
        }
Beispiel #4
0
        static public int ExecuteNonQuery(string connectionString, string sql)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Setting up ..."));
            int result = -1;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                    using (SqlCommand cmd = new SqlCommand(sql, connection))
                    {
                        log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString)));
                        connection.Open();
                        log.Info(string.Format("Query: {0}", sql));
                        log.Info(string.Format("Query execution starting ..."));
                        result = cmd.ExecuteNonQuery();
                        log.Info(string.Format("Query executed! Result count: {0}", result));
                        connection.Close();
                        log.Info("Connection Closed!");
                    }
            }
            catch (Exception ex)
            {
                log.Info("Excepion detected!");
                log.Error(ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(result);
        }
Beispiel #5
0
        // Delete by Entities
        public int Remove(List <T> items)
        {
            int result = 0;

            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Starting Deleting procedure ..."));

            if (items == null || items.Count == 0)
            {
                throw new ArgumentNullException("Unable to delete NULL entities!");
            }

            string table = this.DBTabName;

            try
            {
                string connectionString = this.DBConnectionString;
                if (primaryKey == null)
                {
                    throw new Exception(string.Format("No Primary Key has been defined for the entity '{0}'. Use a removing procedure based on conditions!", typeof(T).Name));
                }

                List <Dictionary <string, object> > keys = new List <Dictionary <string, object> >();
                foreach (T item in items)
                {
                    Dictionary <string, object> key = new Dictionary <string, object>();
                    foreach (string k in this.primaryKey)
                    {
                        object v = item.GetType().GetProperty(k).GetValue(item, null);
                        key.Add(k, v);
                    }
                    keys.Add(key);
                }

                // DELETE
                if (keys != null)
                {
                    int i = 0;
                    Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>();
                    foreach (Dictionary <string, object> key in keys)
                    {
                        foreach (KeyValuePair <string, object> kvPair in key)
                        {
                            int  j       = 0;
                            bool hasNext = true;
                            if (i == keys.Count - 1)
                            {
                                if (j == key.Count - 1)
                                {
                                    hasNext = false;
                                }
                            }

                            string condName = "cond" + i;
                            DBSQL.QueryCondition condValue = new DBSQL.QueryCondition()
                            {
                                Key   = kvPair.Key,
                                Value = kvPair.Value,
                                Op    = DBSQL.Op.Equal,
                                Conj  = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None,
                            };
                            conditions.Add(condName, condValue);
                            i++;
                            j++;
                        }
                    }
                    result = DBSQL.DeleteOperation(connectionString, table, conditions);
                }

                log.Info(string.Format("Deleted {0} records!", result));
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                throw ex;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(result);
        }
Beispiel #6
0
        // Delete by Attributes
        public int RemoveByKeys(List <Dictionary <string, object> > keys)
        {
            int result = 0;

            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Starting Deleting procedure ..."));

            string table = this.DBTabName;

            try
            {
                string connectionString = this.DBConnectionString;

                // DELETE
                if (keys != null)
                {
                    int i = 0;
                    Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>();
                    foreach (Dictionary <string, object> key in keys)
                    {
                        foreach (KeyValuePair <string, object> kvPair in key)
                        {
                            int  j       = 0;
                            bool hasNext = true;
                            if (i == keys.Count - 1)
                            {
                                if (j == key.Count - 1)
                                {
                                    hasNext = false;
                                }
                            }

                            string condName = "cond" + i;
                            DBSQL.QueryCondition condValue = new DBSQL.QueryCondition()
                            {
                                Key   = kvPair.Key,
                                Value = kvPair.Value,
                                Op    = DBSQL.Op.Equal,
                                Conj  = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None,
                            };
                            conditions.Add(condName, condValue);
                            i++;
                            j++;
                        }
                    }
                    result = DBSQL.DeleteOperation(connectionString, table, conditions);
                }

                log.Info(string.Format("Deleted {0} records!", result));
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                throw ex;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(result);
        }
Beispiel #7
0
        // Update
        public T Update(T item)
        {
            T written = default(T);

            Stopwatch tw = new Stopwatch();

            tw.Start();

            log.Info(string.Format("Starting Updating procedure ..."));

            if (item == null)
            {
                throw new ArgumentNullException("Unable to update NULL entity!");
            }

            string table = this.DBTabName;

            try
            {
                string connectionString = this.DBConnectionString;
                if (primaryKey == null)
                {
                    throw new Exception(string.Format("No Primary Key has been defined for the entity '{0}'. Use an updating procedure with declared conditions!", typeof(T).Name));
                }
                int  i       = 0;
                bool hasNext = true;
                Dictionary <string, DBSQL.QueryCondition> conditions = new Dictionary <string, DBSQL.QueryCondition>();
                foreach (string k in primaryKey)
                {
                    object v = item.GetType().GetProperty(k).GetValue(item, null);

                    if (i == primaryKey.Count - 1)
                    {
                        hasNext = false;
                    }
                    string condName = "cond" + i;
                    DBSQL.QueryCondition condValue = new DBSQL.QueryCondition()
                    {
                        Key   = k,
                        Value = v,
                        Op    = DBSQL.Op.Equal,
                        Conj  = hasNext ? DBSQL.Conj.And : DBSQL.Conj.None,
                    };
                    conditions.Add(condName, condValue);
                    i++;
                }
                int result = DBSQL.UpdateOperation(connectionString, table, item, conditions, this.autoincrement);
                log.Info(string.Format("Updated {0} records!", result));

                //Retrieve Item By Condition to give it back as result!
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                throw ex;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }

            return(written);
        }
Beispiel #8
0
        static public DataTable MultiInsertBackOperation <T>(string connectionString, string tabName, List <T> dataVOs, List <string> pk, List <string> autoincrement = null)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            if (autoincrement == null)
            {
                autoincrement = new List <string>()
                {
                }
            }
            ;

            DataTable data = null;

            try
            {
                List <Dictionary <string, object> > pars_  = new List <Dictionary <string, object> >();
                List <Dictionary <string, object> > pars__ = new List <Dictionary <string, object> >();
                Dictionary <string, object>         into   = new Dictionary <string, object>();

                int i = 0;
                foreach (object dataVO in dataVOs)
                {
                    Dictionary <string, object> pars = new Dictionary <string, object>();

                    foreach (var prop in dataVO.GetType().GetProperties())
                    {
                        if (!autoincrement.Select(e => e.ToLower()).ToList().Contains(prop.Name.ToLower()))
                        {
                            string keyOfPar = string.Format("{0}{1}", prop.Name, i);
                            pars[keyOfPar] = prop.GetValue(dataVO, null);
                            if (i == 0)
                            {
                                into[prop.Name] = prop.GetValue(dataVO, null);
                            }
                        }
                    }
                    i++;
                    pars__.Add(pars);
                }

                string query = "INSERT INTO " + tabName + " (" +
                               string.Join(", ", into.Select(x => x.Key).ToArray()) +
                               ") " + "OUTPUT " + string.Join(", ", pk.Select(p => "INSERTED." + p).ToArray()) + " VALUES ";

                into = null;

                Dictionary <string, object> parss = new Dictionary <string, object>();

                int j = 1;
                foreach (Dictionary <string, object> pars in pars__)
                {
                    query += "(" +
                             string.Join(", ", pars.Select(x => "@" + x.Key).ToArray()) +
                             ")";
                    parss = parss.Union(pars).ToDictionary(k => k.Key, v => v.Value);
                    if (j < pars__.Count)
                    {
                        query += ", ";
                    }
                    j++;
                }

                using (SqlConnection connection = new SqlConnection(connectionString))
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        foreach (KeyValuePair <string, object> entry in parss)
                        {
                            object dat = entry.Value != null ? entry.Value : DBNull.Value;
                            cmd.Parameters.AddWithValue(entry.Key, dat);
                        }
                        log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString)));
                        connection.Open();
                        log.Info(string.Format("Query: {0}", LibString.SQLCommand2String(cmd)));
                        log.Info(string.Format("Query execution starting ..."));
                        DataTable dataTable = new DataTable();
                        using (var reader = cmd.ExecuteReader())
                        {
                            dataTable.Load(reader);
                        }
                        log.Info(string.Format("Query Insert executed! Result count: {0}", LibString.ItemsNumber(dataTable)));

                        int t = 0;
                        foreach (DataRow row in dataTable.Rows)
                        {
                            Dictionary <string, object> tmp = new Dictionary <string, object>();
                            foreach (string k in pk)
                            {
                                if (row[k] != null)
                                {
                                    tmp[k + t.ToString()] = row[k];
                                }
                            }
                            pars_.Add(tmp);
                            t++;
                        }

                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                        }
                        log.Info("Connection Closed!");
                    }

                Dictionary <string, object> pars2 = new Dictionary <string, object>();
                string query2 = "SELECT * FROM " +
                                tabName + " WHERE " +
                                "(" + string.Join(", ", pk.Select(x => x)) + ") " +
                                "IN (";
                int    r     = 0;
                string cond_ = "";
                foreach (Dictionary <string, object> tmp in pars_)
                {
                    cond_ += "(";
                    cond_ += string.Join(", ", tmp.Select(z => "@" + z.Key));
                    cond_ += ")";
                    if (r < pars_.Count - 1)
                    {
                        cond_ += ", ";
                    }
                    r++;
                    pars2 = pars2.Concat(tmp).ToDictionary(x => x.Key, x => x.Value);
                }
                query2 += cond_;
                query2 += ")";

                log.Info(string.Format("Query: {0}", query2));
                log.Info(string.Format("Params: {0}", string.Join("; ", pars2.Select(x => x.Key + " = " + x.Value).ToArray())));

                data = ExecuteQueryWithParams(connectionString, query2, pars2);

                log.Info(string.Format("Query Executed! Retrieved {0} records!", LibString.ItemsNumber(data)));

                return(data);
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                log.Error(msg + " " + ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }
        }
Beispiel #9
0
        static public int MultiInsertOperation <T>(string connectionString, string tabName, List <T> dataVOs, List <string> autoincrement = null)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            int result = -1;

            try
            {
                List <Dictionary <string, object> > pars_ = new List <Dictionary <string, object> >();

                Dictionary <string, object> into = new Dictionary <string, object>();

                int i = 0;
                foreach (object dataVO in dataVOs)
                {
                    Dictionary <string, object> pars = new Dictionary <string, object>();

                    foreach (var prop in dataVO.GetType().GetProperties())
                    {
                        if (!autoincrement.Select(e => e.ToLower()).ToList().Contains(prop.Name.ToLower()))
                        {
                            string keyOfPar = string.Format("{0}{1}", prop.Name, i);
                            pars[keyOfPar] = prop.GetValue(dataVO, null);
                            //Console.WriteLine("{0}={1}", prop.Name, prop.GetValue(chiamata, null));
                            if (i == 0)
                            {
                                into[prop.Name] = prop.GetValue(dataVO, null);
                            }
                        }
                    }
                    i++;
                    pars_.Add(pars);
                }

                string query = "INSERT INTO " + tabName + " (" +
                               string.Join(", ", into.Select(x => x.Key).ToArray()) +
                               ") VALUES ";

                into = null;

                Dictionary <string, object> parss = new Dictionary <string, object>();

                int j = 1;
                foreach (Dictionary <string, object> pars in pars_)
                {
                    query += "(" +
                             string.Join(", ", pars.Select(x => "@" + x.Key).ToArray()) +
                             ")";
                    parss = parss.Union(pars).ToDictionary(k => k.Key, v => v.Value);
                    if (j < pars_.Count)
                    {
                        query += ", ";
                    }
                    j++;
                }

                log.Info(string.Format("Query: {0}", query));
                log.Info(string.Format("Params: {0}", string.Join("; ", parss.Select(x => x.Key + "=" + x.Value).ToArray())));

                result = ExecuteNonQueryWithParams(connectionString, query, parss);

                log.Info(string.Format("Query Executed! Inserted {0} records!", result));

                return(result);
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                log.Error(msg + " " + ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }
        }
Beispiel #10
0
        static public DataTable InsertBackOperation(string connectionString, string tabName, object dataVO, List <string> pk, List <string> autoincrement = null)
        {
            Stopwatch tw = new Stopwatch();

            tw.Start();

            if (autoincrement == null)
            {
                autoincrement = new List <string>()
                {
                }
            }
            ;

            DataTable data = null;

            try
            {
                Dictionary <string, object> pars  = new Dictionary <string, object>();
                Dictionary <string, object> pars_ = new Dictionary <string, object>();

                foreach (var prop in dataVO.GetType().GetProperties())
                {
                    pars[prop.Name] = prop.GetValue(dataVO, null);
                }

                string query = "INSERT INTO " + tabName + " (" +
                               string.Join(", ", pars.Where(l => !autoincrement.Select(d => d.ToLower()).ToList().Contains(l.Key.ToLower())).Select(x => x.Key).ToArray()) +
                               ") " +
                               "OUTPUT " + string.Join(", ", pk.Select(p => "INSERTED." + p).ToArray()) + " VALUES "
                               + "(" +
                               string.Join(", ", pars.Where(l => !autoincrement.Select(d => d.ToLower()).ToList().Contains(l.Key.ToLower())).Select(x => "@" + x.Key.ToLower()).ToArray()) +
                               ")";

                using (SqlConnection connection = new SqlConnection(connectionString))
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        foreach (KeyValuePair <string, object> entry in pars)
                        {
                            if (!autoincrement.Select(d => d.ToLower()).ToList().Contains(entry.Key.ToLower()))
                            {
                                object dat = entry.Value != null ? entry.Value : DBNull.Value;
                                cmd.Parameters.AddWithValue(entry.Key, dat);
                            }
                        }
                        log.Info(string.Format("Opening Connection on '{0}' ...", HidePwd(connectionString)));
                        connection.Open();
                        log.Info(string.Format("Query: {0}", LibString.SQLCommand2String(cmd)));
                        log.Info(string.Format("Query execution starting ..."));
                        DataTable dataTable = new DataTable();
                        using (var reader = cmd.ExecuteReader())
                        {
                            dataTable.Load(reader);
                        }
                        log.Info(string.Format("Query Insert executed! Result count: {0}", LibString.ItemsNumber(dataTable)));

                        DataRow row = dataTable.Rows[0];
                        foreach (string k in pk)
                        {
                            if (row[k] != null)
                            {
                                pars_[k] = row[k];
                            }
                        }
                        row = null;

                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                        }
                        log.Info("Connection Closed!");
                    }

                string query2 = "SELECT * FROM " + tabName + " WHERE " + string.Join(" AND ", pars_.Select(x => x.Key + " = " + "@" + x.Key + " ").ToArray());

                log.Info(string.Format("Query: {0}", query));
                log.Info(string.Format("Params: {0}", string.Join("; ", pars.Select(x => x.Key + " = " + x.Value).ToArray())));

                data = ExecuteQueryWithParams(connectionString, query2, pars_);

                log.Info(string.Format("Query Executed! Retrieved {0} records!", LibString.ItemsNumber(data)));

                return(data);
            }
            catch (Exception ex)
            {
                string msg = "An Error occured! Exception detected!";
                log.Info(msg);
                log.Error(msg + " " + ex.Message);
                throw;
            }
            finally
            {
                tw.Stop();
                log.Info(string.Format("Completed! Elapsed time {0}", LibString.TimeSpanToTimeHmsms(tw.Elapsed)));
            }
        }