postgreQueryGenerator.cs

public static class Utils
{
        public static void QueryGenerator(ref NpgsqlCommand cmd, string tableName, string conflictStr, string returningStr, Params prms, int index)
        {
            PropertyInfo[] props = null;
            List<Column> columns = new List<Column>();
            Dictionary<string, bool> confMap = new Dictionary<string, bool>();
            Dictionary<string, object> paramMap = new Dictionary<string, object>();

            List<string> conflicts = conflictStr.Split(',').ToList();

            for (int i = 0; i < conflicts.Count; i++)
                confMap[conflicts[i]] = true;

            for (int i = 0; i < prms.Count; i++)
                paramMap[prms[i].Name] = prms[i].Value;

            switch (tableName) // burada tablo adına bakıp modellerden direkt olarak model bulunabilir aslında
            {
                case "action_tab":
                    props = typeof(Model.Entities.Action).GetProperties();
                    break;
                default:
                    //return err
                    break;
            }

            foreach (PropertyInfo prop in props)
            {
                if (prop.Name == "Id")
                    continue;

                Column col = new Column();

                object[] attrs = prop.GetCustomAttributes(typeof(ColumnAttribute), true);
                if (attrs.Length > 0)
                {
                    if (attrs[0] is ColumnAttribute clm)
                    {
                        if (!paramMap.ContainsKey(clm.Name))
                            continue;
                        col.Name = clm.Name;
                    }
                }
                else
                    continue;

                Type propType = prop.PropertyType;
                if (propType.IsGenericType && propType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    col.Nullable = true;
                    col.Typ = propType.GetGenericArguments()[0].FullName;
                }
                else
                {
                    col.Typ = propType.FullName;
                    if (col.Typ == "System.String")
                    {
                        object[] reqAttrs = prop.GetCustomAttributes(typeof(RequiredAttribute), true);
                        if (reqAttrs.Length > 0)
                            col.Nullable = false;
                        else
                            col.Nullable = true;
                    }
                }

                columns.Add(col);
            }

            string cmdText = "INSERT INTO " + tableName, colStr = " (", valStr = "VALUES(",
                confStr = "ON CONFLICT(" + conflictStr + ") ", updateStr = "DO UPDATE SET ",
                returnStr = " RETURNING " + returningStr;

            for (int i = 0; i < columns.Count; i++)
            {
                colStr += columns[i].Name;
                valStr += "@" + columns[i].Name + index;

                if (i == columns.Count - 1)
                {
                    colStr += ") ";
                    valStr += ") ";
                }
                else
                {
                    colStr += ", ";
                    valStr += ", ";
                }
            }

            for (int i = 0; i < columns.Count; i++)
            {
                confMap.TryGetValue(columns[i].Name, out bool isConflict);
                if (!isConflict)
                    updateStr += columns[i].Name + " = @" + columns[i].Name + index + ", ";
            }
            updateStr = updateStr[0..^2];

            cmd.CommandText += cmdText + colStr + valStr;
            if (conflictStr != "")
                cmd.CommandText += confStr + updateStr;
            if (returningStr != "")
                cmd.CommandText += returnStr;
            cmd.CommandText += ";";

            for (int i = 0; i < columns.Count; i++)
            {
                var col = columns[i];
                object val = paramMap[col.Name];
                string typ = col.Typ;
    
                NpgsqlTypes.NpgsqlDbType dbType = NpgsqlTypes.NpgsqlDbType.Varchar;

                switch (typ)
                {
                    case "System.String":
                        dbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                        break;
                    case "System.Int64":
                    case "System.UInt64":
                        if (val != DBNull.Value)
                            val = Convert.ToInt64(val);
                        dbType = NpgsqlTypes.NpgsqlDbType.Bigint;
                        break;
                    case "System.Int32":
                    case "System.Int16":
                    case "System.UInt32":
                    case "System.UInt16":
                        if (val != DBNull.Value)
                            val = Convert.ToInt32(val);
                        dbType = NpgsqlTypes.NpgsqlDbType.Integer;
                        break;
                    case "System.Decimal":
                        dbType = NpgsqlTypes.NpgsqlDbType.Numeric;
                        break;
                    case "System.Byte":
                        dbType = NpgsqlTypes.NpgsqlDbType.Smallint;
                        break;
                    case "System.Double":
                        dbType = NpgsqlTypes.NpgsqlDbType.Double;
                        break;
                    case "System.Char":
                        dbType = NpgsqlTypes.NpgsqlDbType.Varchar;
                        break;
                    case "System.Boolean":
                        dbType = NpgsqlTypes.NpgsqlDbType.Boolean;
                        break;
                    case "NetTopologySuite.Geometries.Geometry":
                        if (val == DBNull.Value) //null olabiliyor ama modele bakarak anlaşılmıyor. o yüzden böyle kontrol etmeli.
                            col.Nullable = true;
                        dbType = NpgsqlTypes.NpgsqlDbType.Geometry;
                        break;
                    default:
                        //return err
                        break;
                }

                if (col.Nullable)
                    cmd.Parameters.AddWithValue(col.Name + index, dbType, val ?? DBNull.Value);
                else
                    cmd.Parameters.AddWithValue(col.Name + index, dbType, val);
            }

            return cmd;
        }

        public static void UpdateQueryGenerator(ref NpgsqlCommand cmd, string tableName, Params setParams, Params whereParams)
        {
            string cmdTxt = "UPDATE " + tableName + " SET ";

            for (int i = 0; i < setParams.Count; i++)
                cmdTxt += setParams[i].Name + " = " + setParams[i].Value + " ,";

            cmdTxt = cmdTxt[0..^1]; // son virgülü sil
            cmdTxt += "WHERE ";

            for (int i = 0; i < whereParams.Count; i++)
            {
                object val = whereParams[i].Value;
                if (whereParams[i].Value.GetType() == typeof(string))
                    val = "'" + val + "'";
                cmdTxt += whereParams[i].Name + " = " + val + " and ";
            }

            cmdTxt = cmdTxt[0..^5]; // son ` and ` i sil 

            cmd.CommandText = cmdTxt + ";";
        }
}