Beispiel #1
0
        public static string GetJsonData(int appDatabaseId, string sql, SqlParameter[] sqlParameters = null)
        {
            using (DodoBirdEntities Db = new DodoBirdEntities())
            {
                if (appDatabaseId > 0)
                {
                    Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId);
                }
                else
                {
                    Db.Database.Connection.ConnectionString = SessionService.DodoBirdConnectionString;
                }

                StringBuilder sb = new StringBuilder();


                if (sqlParameters != null)
                {
                    var items = Db.Database.SqlQuery <string>(sql + " FOR JSON AUTO, INCLUDE_NULL_VALUES", sqlParameters).ToList();
                    foreach (var item in items)
                    {
                        sb.Append(item);
                    }
                }
                else
                {
                    var items = Db.Database.SqlQuery <string>(sql + " FOR JSON AUTO, INCLUDE_NULL_VALUES").ToList();
                    foreach (var item in items)
                    {
                        sb.Append(item);
                    }
                }

                return(sb.ToString());
            }
        }
Beispiel #2
0
        public static string SaveFormData(int appDatabaseId, string tableName, string json)
        {
            try
            {
                dynamic jsonObj = Newtonsoft.Json.JsonConvert.DeserializeObject(json);

                var tableSchema = GetTableSchema(appDatabaseId, tableName);


                var recordId = "";
                using (DodoBirdEntities Db = new DodoBirdEntities())
                {
                    Db.Database.Connection.ConnectionString = SessionService.GetConnectionString(appDatabaseId);


                    string wherePrimaryKey           = "";
                    string recordValue               = "";
                    List <SqlParameter> insertParams = new List <SqlParameter>();
                    List <SqlParameter> updateParams = new List <SqlParameter>();

                    StringBuilder sbInsert = new StringBuilder();
                    StringBuilder sbValue  = new StringBuilder();
                    StringBuilder sbUpdate = new StringBuilder();

                    sbUpdate.Append("UPDATE " + tableName + " SET ");
                    sbInsert.Append("INSERT INTO " + tableName + "(");
                    sbValue.Append(" VALUES(");

                    Guid newGuid  = Guid.NewGuid();
                    var  newGuid_ = "";

                    // set primary keys
                    foreach (var column in tableSchema.PrimaryKeys)
                    {
                        if (jsonObj[column.ColumnName] != null)
                        {
                            recordId = jsonObj[column.ColumnName].ToString();
                            if (recordId.Length > 0 && recordId != "0")
                            {
                                wherePrimaryKey += " " + column.ColumnName + " = '" + recordId + "' AND ";
                            }
                        }

                        if (column.DataType == "uniqueidentifier")
                        {
                            sbInsert.Append(column.ColumnName + ",");
                            sbValue.Append("'" + newGuid + "',");
                            newGuid_ = newGuid.ToString();
                        }
                    }

                    foreach (var column in tableSchema.Columns.Where(w => !(bool)w.IsComputed && !w.IsPrimaryKey))
                    {
                        if (jsonObj[column.ColumnName] != null)
                        {
                            recordValue = jsonObj[column.ColumnName].ToString();

                            sbInsert.Append(column.ColumnName + ",");
                            sbValue.Append("@" + column.ColumnName + ",");
                            insertParams.Add(new SqlParameter("@" + column.ColumnName, recordValue));


                            sbUpdate.Append(column.ColumnName + " = @" + column.ColumnName + ",");
                            updateParams.Add(new SqlParameter("@" + column.ColumnName, recordValue));
                        }
                    }


                    if (wherePrimaryKey.Length > 0)  // update
                    {
                        var            sql           = sbUpdate.ToString().Substring(0, sbUpdate.ToString().Length - 1) + " WHERE " + wherePrimaryKey.Substring(0, wherePrimaryKey.Length - 4);
                        SqlParameter[] sqlParameters = updateParams.ToArray();
                        Db.Database.ExecuteSqlCommand(sql, sqlParameters);
                    }
                    else
                    {
                        SqlParameter[] sqlParameters = insertParams.ToArray();

                        var sql = sbInsert.ToString().Substring(0, sbInsert.ToString().Length - 1) + ") " + sbValue.ToString().Substring(0, sbValue.ToString().Length - 1) + "); SELECT CAST(@@IDENTITY AS varchar(250));";
                        if (newGuid_.Length > 0) // is guid
                        {
                            sql      = sbInsert.ToString().Substring(0, sbInsert.ToString().Length - 1) + ") " + sbValue.ToString().Substring(0, sbValue.ToString().Length - 1) + ");";
                            recordId = newGuid.ToString();
                            Db.Database.ExecuteSqlCommand(sql, sqlParameters);
                        }
                        else
                        {
                            recordId = Db.Database.SqlQuery <string>(sql, sqlParameters).FirstOrDefault();
                        }
                        Db.SaveChanges();
                    }

                    return(recordId.ToString());
                }
            }
            catch (Exception ex)
            {
                return("Unable to process record - " + ex.Message);
            }
        }