Beispiel #1
0
        public bool Update(Modellable item)
        {
            String     sqlString = "SELECT * FROM " + tableName + " WHERE ";
            SqlCommand cmd       = new SqlCommand(sqlString, con);

            foreach (String key in args.Keys)
            {
                sqlString += key + " = @" + key + " AND ";
                SqlParameter param = new SqlParameter("@" + key, args[key].Item2, args[key].Item3);
                param.Value = args[key].Item1;
                cmd.Parameters.Add(param);
            }

            if (sqlString.Substring(sqlString.Length - 4).Equals("AND "))
            {
                sqlString = sqlString.Substring(0, sqlString.Length - 4);
            }

            sqlString      += ";";
            cmd.CommandText = sqlString;
            cmd.Prepare();

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                reader.Close();
                String[]   keyNames        = item.FieldsNotSpecifiedInPOST();
                String     updateSqlString = "UPDATE " + tableName + " SET ";
                SqlCommand upCmd           = new SqlCommand();
                upCmd.Connection = con;

                int cur        = 1;
                var properties = item.GetType().GetProperties();

                foreach (var prop in properties)
                {
                    if (!keyNames.Contains(prop.Name))
                    {
                        updateSqlString += prop.Name + "=@" + prop.Name;
                        if (cur != properties.Count())
                        {
                            updateSqlString += ",";
                        }

                        Tuple <SqlDbType, int> DBTypeAndSize = item.GetAssociatedDBTypeAndSize(prop.Name);
                        SqlParameter           param         = new SqlParameter("@" + prop.Name, DBTypeAndSize.Item1, DBTypeAndSize.Item2);
                        param.Value = (object)prop.GetValue(item, null) ?? DBNull.Value;
                        upCmd.Parameters.Add(param);
                    }
                    cur++;
                }
                updateSqlString += " WHERE ";
                foreach (String key in args.Keys)
                {
                    updateSqlString += key + " = @" + key + " AND ";
                    SqlParameter param = new SqlParameter("@" + key, args[key].Item2, args[key].Item3);
                    param.Value = args[key].Item1;
                    upCmd.Parameters.Add(param);
                }

                if (updateSqlString.Substring(updateSqlString.Length - 4).Equals("AND "))
                {
                    updateSqlString = updateSqlString.Substring(0, updateSqlString.Length - 4);
                }

                updateSqlString  += ";";
                upCmd.CommandText = updateSqlString;

                try
                {
                    upCmd.Prepare();

                    upCmd.ExecuteNonQuery();
                    return(true);
                }
                catch (Exception ex)
                {
                    return(false);
                }
            }
            else
            {
                return(false);
            }
        }
Beispiel #2
0
        public int Save(Modellable item, String keyToReturn)
        {
            string[] keyNames = item.FieldsNotSpecifiedInPOST();

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = con;
            String sqlString = "INSERT INTO " + this.tableName + " (";

            int cur        = 1;
            var properties = item.GetType().GetProperties();

            foreach (var prop in properties)
            {
                if (!keyNames.Contains(prop.Name))
                {
                    sqlString += prop.Name;
                    if (cur != properties.Count())
                    {
                        sqlString += ",";
                    }
                }
                cur++;
            }
            sqlString += ") OUTPUT INSERTED." + keyToReturn + " VALUES (";

            cur = 1;
            foreach (var prop in properties)
            {
                if (!keyNames.Contains(prop.Name))
                {
                    sqlString += "@" + prop.Name;
                    if (cur != properties.Count())
                    {
                        sqlString += ",";
                    }


                    Tuple <SqlDbType, int> DBTypeAndSize = item.GetAssociatedDBTypeAndSize(prop.Name);
                    SqlParameter           param         = new SqlParameter("@" + prop.Name, DBTypeAndSize.Item1, DBTypeAndSize.Item2);
                    param.Value = (object)prop.GetValue(item, null) ?? DBNull.Value;
                    cmd.Parameters.Add(param);
                }
                cur++;
            }
            sqlString += ");";

            cmd.CommandText = sqlString;

            try
            {
                cmd.Prepare();
                int id = (int)cmd.ExecuteScalar();
                con.Close();
                return(id);
            }
            catch (Exception ex)
            {
                return(-1);
            }
        }