コード例 #1
0
ファイル: DBSQL.cs プロジェクト: GitMAGI/hikari.net.aspxtests
        static public int ExecuteNonQueryWithParams(string connectionString, string sql, Dictionary <string, object> atts)
        {
            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))
                    {
                        foreach (KeyValuePair <string, object> entry in atts)
                        {
                            if (entry.Value == null)
                            {
                                cmd.Parameters.AddWithValue(entry.Key, DBNull.Value);
                            }
                            else
                            {
                                cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                            }
                        }
                        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 ..."));
                        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);
        }
コード例 #2
0
ファイル: DBSQL.cs プロジェクト: GitMAGI/hikari.net.aspxtests
        static public DataTable ExecuteQueryWithParams(string connectionString, string sql, Dictionary <string, object> atts)
        {
            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))
                    {
                        foreach (KeyValuePair <string, object> entry in atts)
                        {
                            cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                        }
                        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 ..."));
                        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);
        }
コード例 #3
0
ファイル: DBSQL.cs プロジェクト: GitMAGI/hikari.net.aspxtests
        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)));
            }
        }
コード例 #4
0
ファイル: DBSQL.cs プロジェクト: GitMAGI/hikari.net.aspxtests
        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)));
            }
        }