Exemplo n.º 1
0
        public int Delete <T>(T dbobj, string TableName, params KeyValuePair <string, object>[] parameters)
            where T : class
        {
            if (dbobj == null)
            {
                throw new ArgumentNullException(nameof(dbobj));
            }

            var SQL       = "DELETE FROM " + TableName;
            var SqlParams = new List <KeyValuePair <string, object> >();

            if (parameters != null && parameters.Length > 0)
            {
                var lkeys = (string[])parameters.Clone();
                for (int i = 0; i < lkeys.Length; i++)
                {
                    lkeys[i] = lkeys[i].ToUpperInvariant();
                }
                SQL += " WHERE ";
                var type       = dbobj.GetType();
                var properties = type.GetProperties();
                foreach (var prp in properties)
                {
                    if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
                    {
                        continue;
                    }
                    if (ORMLite.IsSupportedProperyType(prp))
                    {
                        var fname = prp.Name;
                        var idx   = Array.IndexOf(parameters, fname.ToUpperInvariant());
                        if (idx < 0)
                        {
                            var fname2 = ORMLite.ConvertNameToDB(fname);
                            idx = Array.IndexOf(lkeys, fname2.ToUpperInvariant());
                            if (idx >= 0)
                            {
                                fname = fname2;
                            }
                        }
                        if (idx >= 0)
                        {
                            SQL += fname + "=:" + fname + " AND ";
                            SqlParams.Add(new KeyValuePair <string, object>(fname, GetPropValue(prp, dbobj)));
                        }
                    }
                }
                SQL = SQL.Remove(SQL.Length - 5);
            }

            var res = mth.ExecuteNonQuery(SQL, SqlParams.ToArray());

            return(res);
        }
        public void GenerateSchema(string SchemaName)
        {
            Logger?.Info("Database Schema check/generation started");

            var schema = db.GetSchema("Tables");

            foreach (DataRow?row in schema.Rows)
            {
                if (row == null)
                {
                    continue;
                }
                var dbName  = row[0]?.ToString();
                var schName = row[1].ToString();
                var tblName = row[2].ToString();
                if (schName == SchemaName && tblName != null)
                {
                    ExistedTables.Add(tblName);
                }
            }

            foreach (var c in tableClasses)
            {
                if (!c.IsClass)
                {
                    continue;
                }
                var orm = new ORMLite(db);
                CreateTable(c);
            }


            var sqls = PostGenerationScript.Split(";");

            foreach (var sql in sqls)
            {
                var lsql = inlineCommentsRgx.Replace(sql, "");
                if (!string.IsNullOrEmpty(lsql))
                {
                    try
                    {
                        db.Transaction((th) => th.ExecuteNonQuery(lsql));
                    }
                    #pragma warning disable CA1031 // Do not catch general exception types
                    catch (Exception ex)
                    {
                        Logger?.Error(ex, $"DBSchemaGenerator. Operation: {lsql.Trim()} ");
                    }
                    #pragma warning restore CA1031 // Do not catch general exception types
                }
            }
            Logger?.Info("Database Schema check/generation done");
        }
Exemplo n.º 3
0
        public void Insert <T>(T dbobj, string TableName, bool ConvertFieldNames = true, params KeyValuePair <string, object>[] parameters)
            where T : class
        {
            if (dbobj == null)
            {
                throw new ArgumentNullException(nameof(dbobj));
            }

            var SQL        = "INSERT INTO " + TableName + "(";
            var SQLV       = "VALUES(";
            var type       = dbobj.GetType();
            var properties = type.GetProperties();
            var SqlParams  = new List <KeyValuePair <string, object> >();

            foreach (var prp in properties)
            {
                if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
                {
                    continue;
                }
                if (ORMLite.IsSupportedProperyType(prp))
                {
                    var fname = prp.Name;
                    if (ConvertFieldNames)
                    {
                        fname = ORMLite.ConvertNameToDB(fname);
                    }
                    var value = GetPropValue(prp, dbobj);
                    SQL  += fname + ",";
                    SQLV += ":" + fname + ",";
                    SqlParams.Add(new KeyValuePair <string, object>(fname, value));
                }
            }
            if (parameters != null)
            {
                foreach (var k in parameters)
                {
                    SQL  += k.Key + ",";
                    SQLV += ":" + k.Key + ",";
                    SqlParams.Add(new KeyValuePair <string, object>(k.Key, k.Value));
                }
            }
            SQL  = SQL.TrimEnd(',');
            SQLV = SQLV.TrimEnd(',');
            SQL += ")" + SQLV + ")";

            mth.ExecuteNonQuery(SQL, SqlParams.ToArray());
        }
        private void CreateTable(Type typ)
        {
            var nattr = typ.GetCustomAttribute <TableNameAttribute>();
            var tname = "";

            if (nattr != null)
            {
                tname = nattr.TableName;
            }
            else
            {
                tname = ORMLite.ConvertNameToDB(typ.Name);
            }

            if (ExistedTables.Exists((n) => n == tname))
            {
                return;
            }

            Logger?.Info($"Table {tname} not found in database. Creating...");

            var colCnt = 0;
            var SQL    = "CREATE TABLE " + tname + "(";

            #region Reflection returns properties of parent class after child. Here we reverse it.
            List <KeyValuePair <Type, List <PropertyInfo> > > pTypes = new List <KeyValuePair <Type, List <PropertyInfo> > >();
            foreach (var p in typ.GetProperties())
            {
                KeyValuePair <Type, List <PropertyInfo> > pType;
                if (!pTypes.Exists((pp) => pp.Key == p.DeclaringType) && p.DeclaringType != null)
                {
                    pType = new KeyValuePair <Type, List <PropertyInfo> >(p.DeclaringType, new List <PropertyInfo>());
                    pTypes.Add(pType);
                }
                else
                {
                    pType = pTypes.Find((pp) => pp.Key == p.DeclaringType);
                }
                pType.Value.Add(p);
            }
            pTypes.Reverse();
            #endregion

            foreach (var pType in pTypes)
            {
                foreach (var p in pType.Value)
                {
                    var cName   = ORMLite.ConvertNameToDB(p.Name);
                    var aColDef = p.GetCustomAttribute <ColumnDefAttribute>();
                    var colDef  = "";
                    if (aColDef != null)
                    {
                        colDef = aColDef.ColumnDef;
                    }
                    else
                    {
                        continue;
                    }
                    SQL += cName + " " + colDef + ",";
                    colCnt++;
                }
            }


            SQL = SQL.Trim(',') + ")";
            if (colCnt == 0)
            {
                throw new Exception(
                          $"No any DB column definitions found for class {typ.Name}");
            }
            db.Transaction((th) => {
                th.ExecuteNonQuery(SQL);
                var types = new List <Type>()
                {
                    typ
                };
                while (typ != typeof(object) && typ.GetTypeInfo().BaseType != typeof(object))
                {
                    types.Insert(0, typ.GetTypeInfo().BaseType ?? typeof(object));
                    typ = typ.GetTypeInfo().BaseType ?? typeof(object);
                }
                foreach (var t in types)
                {
                    var atPostCreate = t.GetCustomAttribute <PostTableCreateScriptAttribute>();
                    if (atPostCreate != null)
                    {
                        var sqls = atPostCreate.Script.Split(";");
                        foreach (var sql in sqls)
                        {
                            if (!string.IsNullOrEmpty(sql?.Trim()))
                            {
                                var lsql = sql.Replace("{TNAME}", tname, StringComparison.InvariantCultureIgnoreCase);
                                th.ExecuteNonQuery(lsql);
                            }
                        }
                    }
                }
                Logger?.Info($"Created table {tname}");
            },
                           IsolationLevel.ReadCommitted,
                           true // Perform DDL out of transaction
                           );
        }
Exemplo n.º 5
0
        public int Update <T>(T dbobj, string TableName, bool ConvertFieldNames = true, params string[] keys)
            where T : class
        {
            if (dbobj == null)
            {
                throw new ArgumentNullException(nameof(dbobj));
            }
            var lkeys = (string[])Array.CreateInstance(typeof(string), 0);

            if (keys != null)
            {
                lkeys = (string[])keys.Clone();
            }

            for (int i = 0; i < lkeys.Length; i++)
            {
                lkeys[i] = lkeys[i].ToUpperInvariant();
            }
            var SQL        = "UPDATE " + TableName + " SET ";
            var SQLW       = " WHERE ";
            var type       = dbobj.GetType();
            var properties = type.GetProperties();
            var keyscnt    = 0;
            var SqlParams  = new List <KeyValuePair <string, object> >();
            var SqlParamsW = new List <KeyValuePair <string, object> >();

            foreach (var prp in properties)
            {
                if (prp.GetCustomAttribute <PDBIgnoreAttribute>() != null)
                {
                    continue;
                }
                if (ORMLite.IsSupportedProperyType(prp))
                {
                    var fname = prp.Name;
                    if (ConvertFieldNames)
                    {
                        fname = ORMLite.ConvertNameToDB(fname);
                    }
                    var value = GetPropValue(prp, dbobj);
                    var idx   = keys == null ? -1
                                           : Array.IndexOf(keys, fname.ToUpperInvariant());
                    if (idx < 0)
                    {
                        idx = Array.IndexOf(lkeys, fname.ToUpperInvariant());
                    }
                    if (idx < 0)
                    {
                        SQL += (fname + " = :" + fname + ",");
                        SqlParams.Add(new KeyValuePair <string, object>(fname, GetPropValue(prp, dbobj)));
                    }

                    if (idx >= 0)
                    {
                        keyscnt++;
                        SQLW += (fname + " = :" + fname + " AND ");
                        SqlParamsW.Add(new KeyValuePair <string, object>(fname, value));
                    }
                }
            }
            SQL = SQL.TrimEnd(',');
            SqlParams.AddRange(SqlParamsW);
            if (keyscnt > 0)
            {
                SQL += SQLW.Remove(SQLW.Length - 5);// Remove trailing ' AND '
            }
            var res = mth.ExecuteNonQuery(SQL, SqlParams.ToArray());

            return(res);
        }