This post goes through a few steps before the core problem becomes clear so please bear with me. Also I would be very interested in hearing what people think of my solution so please comment if you can. I was in the course of building a rest api using asp.net mvc which returns json from its end points.

As one might expect I started by using JsonResult as the return type from my controllers. This is built into MVC and inherits from Actionresult adding some validation and setting the content-type of the response.

My inital controller action looked like this

   public JsonResult Single()
        {
            var user = new User() { name = "David Hume" }; 
            return Json(user,JsonRequestBehavior.AllowGet);
        }

This works fine and the controller correctly returns {"name":"David Hume"} with the content-type set to applicaiton/json.

So far so good. However if we have a null user name we get ugliness.

   public JsonResult SingleEmpty()
        {
            var user = new User();
            return Json(user, JsonRequestBehavior.AllowGet);
        }

Perhaps user is not a good example but there will be many types returned by my api with nullable properties. But then i dont want these null properties serialised. SingleEmpty however returns {"name":null}

So i then started looking into how i can tell the serialiser to ignore null properties and the can of worms began to open.

The Json() serialiser is needed to return JsonResult objects, unfortunatly the serialiser JsonResult uses is based on the once deprecated then reinstated .Net javascript serialiser whose very limited options do not include ignoring null properties. The suggested method on SO seemed to be to write my own JavaScriptConverter and register it using the RegisterConverters method. This seemed like a potenitally flaky solution with considerable work just to get a relativly simple feature.

This led me back to tried and tested json.net library from James Newton-King. This has all the options i need so i rewrote the controller like this

   public JsonResult Double()
        {
            var user = new User() { name = "David Hume" };
            return Json(JsonConvert.SerializeObject(user, Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }), JsonRequestBehavior.AllowGet);
        }

This sucessfully removes the null properties but as you might expect it serialises twice. Once with the json.net and once in the JsonResult. This returns "{\"name\":\"David Hume\"}"

However the second cast cannot be reomved whilst still retuning the JsonResult type.

After searching around the net i decied to abandon the JsonResult type altogether as the clients apps only see the object as a string anyway so all JsonResult is really doing is setting the content-type in the response header for me.

So i returned a string and set the content-type manually. I have also moved the formating and properites for the json into a helper method

public static class FormatHelpers
{
    public static string FormattedResult(User user)
    {
        return JsonConvert.SerializeObject(user, Formatting.None, new     JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
    }
}

 public string Index()
            {
                Response.ContentType = "application/json";
                var user = new User() { name = "David Hume" };
                return FormatHelpers.FormattedResult(user);
            }

This now ignores null properties and returns correctly serialised application/json.

One final step to make this a little tidier and more robust is to avoid having to set the content-type in every controller action in the api. I can set this everywhere by creating a new controller which overirdes the OnActionExecuting method of the MVC base controller. I can then inherit from that controller in all my api controllers and thereby ensure the content-type is correctly set for all the actions. I can also use this design pattern to create other global features of my endpoint like having all returns wrapped in a result tag which shows a status.

So here is my custom base controller

public class JsonController : Controller
    {
        public Result result;
        public JsonController()
        {
            result = new Result();
        }
        protected override void OnActionExecuting(ActionExecutingContext filterContext)
        {
            Response.ContentType = "application/json";
        }

    }

Notice how the result object is created here in the contructor when the controller is instanitated and the content-type is set. Then i simply inherit from the controller in my api end points and the result object is available and the content-type is set for me. i also edit my formatting helper to accept and return the global result object.

public static string FormattedResult(Result result)
        {
            return JsonConvert.SerializeObject(result, Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
        }

     public class MyController : JsonController
        {

            public string Index()
            {
                result.status = "ok";
                result.User = new User() { name = "David Hume" };
                return FormatHelpers.FormattedResult(result);
            }
         }

So after a fair amount of hassle i have my actions returning good json. There is a lot of interesting stuff you can do by overriding the methods in the base controller which i will write another post on soon. In case anything here isnt clear the working sln is on git hub

It is impossible to drop a specific partition, from last month for example. Also, as one cant place a where clause on a truncate statement, a delete from table where x=y would be logged and hence very slow. However, one can switch data from a specific partition into a another table and then truncate that table.

The following is a dynamic method of doing this for any table. This sp must be created first as it is called by another sp that does the work. It creates a table with the same schema as the table from which the partition is to be deleted. The data to be deleted is switched into the created table. Note: the switch out table must be permanent (unfortunately you cannot switch to a #tmp table) so one must check for schema locks.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Harry McCarney
-- Object: mnt_GetSQLForSwitchOutTable
-- =============================================
ALTER PROCEDURE [dbo].[mnt_GetSQLForSwitchOutTable] 
(
    @TableName SYSNAME,
    @NewTableName SYSNAME = NULL,
    @Output varchar(max) output
)
AS
BEGIN

    DECLARE @MainDefinition TABLE
    (
        FieldValue VARCHAR(200)
    )

    DECLARE @DBName SYSNAME
    DECLARE @ClusteredPK BIT
    DECLARE @TableSchema NVARCHAR(255)
    SET @DBName = DB_NAME(DB_ID())

    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

    DECLARE @ShowFields TABLE
    (
        FieldID INT IDENTITY(1,1),
        DatabaseName VARCHAR(100),
        TableOwner VARCHAR(100),
        TableName VARCHAR(100),
        FieldName VARCHAR(100),
        ColumnPosition INT,
        ColumnDefaultValue VARCHAR(100),
        ColumnDefaultName VARCHAR(100),
        IsNullable BIT,
        DataType VARCHAR(100),
        MaxLength INT,
        NumericPrecision INT,
        NumericScale INT,
        DomainName VARCHAR(100),
        FieldListingName VARCHAR(110),
        FieldDefinition CHAR(1),
        IdentityColumn BIT,
        IdentitySeed INT,
        IdentityIncrement INT,
        IsCharColumn BIT
    )

    DECLARE @HoldingArea TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000),
        FldValue CHAR(1) DEFAULT(0)
    )

    DECLARE @PKObjectID TABLE
    (
        ObjectID INT
    )

    DECLARE @Uniques TABLE
    (
        ObjectID INT
    )

    DECLARE @HoldingAreaValues TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000),
        FldValue CHAR(1) DEFAULT(0)
    )

    DECLARE @Definition TABLE
    (
        DefinitionID SMALLINT IDENTITY(1,1),
        FieldValue VARCHAR(200)
    )


    INSERT INTO @ShowFields
    (       
        DatabaseName,
        TableOwner,
        TableName,
        FieldName,
        ColumnPosition,
        ColumnDefaultValue,
        ColumnDefaultName,
        IsNullable,
        DataType,
        MaxLength,
        NumericPrecision,
        NumericScale,
        DomainName,
        FieldListingName,
        FieldDefinition,
        IdentityColumn,
        IdentitySeed,
        IdentityIncrement,
        IsCharColumn
    )

    SELECT
        DB_NAME(),
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        CAST(ORDINAL_POSITION AS INT),
        COLUMN_DEFAULT,
        dobj.name AS ColumnDefaultName,
        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
        DATA_TYPE,
        CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
        CAST(NUMERIC_PRECISION AS INT),
        CAST(NUMERIC_SCALE AS INT),
        DOMAIN_NAME,
        COLUMN_NAME + ',',
        '' AS FieldDefinition,
        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
        CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
        CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
        CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
    FROM
        INFORMATION_SCHEMA.COLUMNS c
        JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
        LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
        JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name

        LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'


    WHERE c.TABLE_NAME = @TableName
    ORDER BY
        c.TABLE_NAME, c.ORDINAL_POSITION

    SELECT TOP 1 @TableSchema = TableOwner
    FROM @ShowFields

    INSERT INTO @HoldingArea (Flds) VALUES('(')

    INSERT INTO @Definition(FieldValue)
    VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)

    INSERT INTO @Definition(FieldValue)
    VALUES('(')

    INSERT INTO @Definition(FieldValue)
        SELECT
                CHAR(10) + FieldName + ' ' +
                                    CASE
                                        WHEN DomainName IS NOT NULL  THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                        ELSE UPPER(DataType) +
                                            CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
                                            CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
                                            CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
    										 CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END end
        FROM    @ShowFields 

INSERT INTO @MainDefinition(FieldValue)
            SELECT FieldValue FROM @Definition
            ORDER BY DefinitionID ASC


        SELECT @output  = isnull(@output,'') + FieldValue FROM @MainDefinition


END

The next sp calls the sp above and does the deleting. It removes data from partitions older than the number of days spcified in the input paramter. This is much faster than querying the tables and issuing deletes. Using switch and truncate we take no locks and perform no scans/seeks. Specific partitions cant be explicitly dropped/truncated.

We switch data into a difffernet table: Table_SwitchOut and truncate it from there. Switch statements require DDL and we may want to switch multiple partitions This means dynamically running DDL alter table statements which is perhaps not ideal. I have also created dynamic structure in case job fails one night or we only want to run it once a a week.

Also if we stop collecting data it will keep the last @DaysPartitionsToKeep days collected data. The sp checks that the partitions are actually weekly or monthly by querying system tables. This double check ensures we don’t delete data erroneously if the partition function is altered.

-- =============================================
-- Author:      Harry McCarney
-- Object: mnt_Truncate_Data_From_Partitions

-- =============================================

ALTER PROC [dbo].[mnt_Truncate_Data_From_Partitions] 
@Table_Name varchar (100),
@DaysPartitionsToKeep INT ---Number of days data to keep aassuming daily partition. 
AS

SET NOCOUNT ON
SET transaction ISOLATION level read uncommitted

DECLARE @no       INT,
        @i        INT,
        @sql      VARCHAR(MAX),
        @truncate VARCHAR(500),
    	@Table_Name_SwitchOut varchar(100),
    	@now BIGINT,
    	@PartionCheck INT,
    	@PartionScheme varchar(100),
    	@PartitionsToKeep INT,
    	@PartitionFunction varchar(100),
    	@Error varchar(1000),
    	@CreateSwitchOutTableCMD varchar(max),
    	@DropExistingTableCMD varchar(max)

DECLARE @tab TABLE (ID INT identity, PartitionID INT)

IF (select object_ID(@Table_Name)) is null
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Table '+@Table_Name+' Not Found.'
RAISERROR(@Error ,10,1,'')with log
RETURN 
END

SELECT 
@Table_Name_SwitchOut = @Table_Name+'_SwitchOut', --All switch out Table have this format.
@truncate = 'truncate table dbo.'+@Table_Name_SwitchOut,-- Create dynamic truncate as must be run after each switch. Switch out table must be empty before switch.
@now = dbo.datetime2uts_id(getdate()), @DaysPartitionsToKeep = 14, ---Get Id for Current Date
@DropExistingTableCMD = 'if (select object_id('''+@Table_Name_SwitchOut+''')) is not null drop table '+ @Table_Name_SwitchOut

exec mnt_GetSQLForSwitchOutTable @table_name, @Table_Name_SwitchOut,@CreateSwitchOutTableCMD output
exec (@DropExistingTableCMD)

print @CreateSwitchOutTableCMD
exec (@CreateSwitchOutTableCMD)


IF (select object_ID(@Table_Name_SwitchOut)) is null
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Table '+@Table_Name_SwitchOut+' Not Created.'
RAISERROR(@Error,10,1,'')with log
RETURN 
END

SELECT 
@PartionScheme = ps.[name],
@PartitionFunction = pf.[name]
FROM sys.partitions p
JOIN sys.indexes i
   ON p.[object_id] = i.[object_id]
   AND p.index_id = i.index_id
JOIN sys.data_spaces ds
   ON i.data_space_id = ds.data_space_id
JOIN sys.partition_schemes ps
 ON ds.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf
   ON ps.function_id = pf.function_id
WHERE p.[object_id] = OBJECT_ID(@Table_Name)

SELECT @PartitionsToKeep = CASE @PartionScheme
    WHEN   'PS_daily' THEN @DaysPartitionsToKeep 
    WHEN 'PS_weekly' THEN @DaysPartitionsToKeep/7
    ELSE -1 END

IF @PartitionsToKeep = -1
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Unknown Partition Scheme '+@PartionScheme +' Not Found.'
RAISERROR(@Error,10,1,'')with log
RETURN 
END

---A little check to ensure the partioning function ranges hasnt changed, for example ,from daily to 
--weekly. 

SELECT @PartionCheck = DATEDIFF(d,MIN(dbo.uts_id2datetime([value],1)), 
MAX(dbo.uts_id2datetime([value],1)))+1
FROM(
SELECT TOP (@PartitionsToKeep) CAST([value] as BIGINT)[value]
FROM  sys.partition_range_values  rv
JOIN sys.partition_functions f
    ON rv.function_id = f.function_id
WHERE name = @PartitionFunction
AND [value] < @now
)x


IF ISNULL(@PartionCheck,9999)  > @DaysPartitionsToKeep 
BEGIN
SET @Error = 'Error: Proc = '+ object_name(@@procid)+', Message = Partition function is inconsistent and a truncate would only keep '+
    													cast(ISNULL(@PartionCheck,0)as varchar(10))+' days data.'
RAISERROR(@Error ,10,1,'')
RETURN 
END

---Now we do the work
INSERT @tab
SELECT distinct Partition_Number -- partition numbers for delete
FROM   sys.partitions
WHERE  [OBJECT_ID]= OBJECT_ID(@Table_Name)
   AND Partition_Number + @PartitionsToKeep <=
       (SELECT MAX(Partition_Number) --Latest partition with rows.
       FROM    sys.partitions
       WHERE   [OBJECT_ID] = OBJECT_ID(@Table_Name)
       AND [Rows]    > 0
       )
and   [Rows]    > 0



SET @no   = SCOPE_IDENTITY()
SET @i    =1
WHILE @i <= @no -- Loop through partition number generating and excuting alter and truncate.
BEGIN

        SELECT @sql = 'ALTER TABLE '+@Table_Name+'  SWITCH PARTITION '+ CAST(PartitionID AS VARCHAR(10))+ ' TO '+@Table_Name_SwitchOut 
        FROM   @tab
        WHERE  ID = @i
    	EXEC (@sql) --Alter table switch
       EXEC (@truncate) -- Truncate switch table
    	print @sql
    	print @truncate
SET @i = @i + 1
END

exec('drop table '+@Table_Name_SwitchOut)

I have been using python a little recently to build some internal tools and am really really impressed. It is the nut crcker to the c# sledge hammer.

A community accepted web frame work seems to be the main thing lacking; many shops are rolling their own. I think this fragments the community and prevents python from competing effectivly with ruby/rails.

Django, which is the main player, seems to insist you use their orm which doesnt fit with our architecture or ideological commitments;)

Either way python has some geat features. For example one line variable swaping

a = 1
b = 2

a, b = b, a

You can swap the values of two variables without the use of a third variable and in one line.

In c# you can do this without the 3rd variable by using xor.

int a = 1;
int b = 2;

a ^= b;
b ^= a;
a ^= b;

Still three lines though; not nearly as elegant as the python solution.

This is a lovely little trick which gives a hint of how flexible and powerful python can be in more 'real' world uses.

Recently I had the chance to visit Vienna, which has to be one of the most significant cities in 20th century intellectual history.

This building, which looks like aliens landed their craft on the acropolis, was designed by Gustav Klimt and housed the Vienna Secession exhibitions.

Interestingly enough the building was financed by Karl Wittgenstein whose son Ludwig also dabbled in architecture, although he had a rather different style from Klimt.

Ludwig Wittgenstein designed this building in 1926.

I wasn’t able to go or see inside the house, despite my fellow sightseer demonstrating how we could jump over the wall. Apparently LW spent a year designing the radiators and installed solid sheet metal curtains that could be lowered to the floor using a pulley system.

Better known for his philosophical work, Ludwig Wittgenstein was the author of Tractatus Logico-Philosophicus whose famous lines such as "The world is the totality of facts, not of things." seem to express the thought that guided the design of this very, umm, factual looking building.

It looks like he began with the dimensions of the windows and then the space between the windows, then the distance from the edge of the windows to the corners of the building and so on. Indeed it is claimed that all the proportions of the building are either 3:1, 3:2 or 2:1.

In my horribly crude understanding of the Tractatus the world is constructed through the meshing together of possible facts which are themselves composed of other facts. The space of logically possible configurations of facts acts as a kind of grid into which worlds must fit.

Someone looking at the world in this way might see architectural design as a process of defining basic facts and then assembling them in a logically coherent way. In accordance with this principle the building completely lacks any kind of decoration and I think this sparseness gives it a reassuring kind of splendour. I expect Wittgenstein thought Klimt’s dome was rather garish and superficial, perhaps even nonsensical.

I have been looking around for a while for a way to zip and ftp DB backups out of my production server from within the SQL engine without using xpcmdshell.
xpcmdshell creates huge security holes and should not be used. Extended procs are also likely to end in tears due the high chance of memory leaks if they ever encounter a glitch.
After looking around on Google I couldn''t find anything so ended up writing a C# CLR procedure which gzips a backup and then ftps it to a specified destination. (gzip libraries are now built into .NET 4 and are actually much more efficient and less bother than using windows native zip format.)
This means you can zip and ftp any file using a stored procedure within a standard SQL batch.
The proc requires ''external access'' to the file system and the remote server so to ensure the code doesnt create security weakness''s i have created it with a certificate (asymmetric key) which is mapped to a specific sql login. This ensures that the dll can only be accessed by this SQL login and no other process can take advantage of its increased security context.
First one needs to create dll.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.IO.Compression;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void zipandftp(SqlString FTPAddress, SqlString user, SqlString pwd, SqlString filePath)
    {
        string zippedbaktobeftp = compress(filePath);
        Upload(zippedbaktobeftp, FTPAddress.ToString(), user.ToString(), pwd.ToString());
    }
    private static string compress(SqlString filename)
    {
        string zippedbak = filename.ToString() + ".gz";          // Get bytes from input stream          FileStream inFileStream = new FileStream(filename.ToString(), FileMode.Open);     
        FileStream outFileStream = File.Create(zippedbak); GZipStream compressedStream = new GZipStream(outFileStream, CompressionMode.Compress); CopyStream(inFileStream, compressedStream);
        compressedStream.Close(); outFileStream.Close(); inFileStream.Close(); return zippedbak;
    }
    private static void CopyStream(Stream input, Stream output)   
                    {          byte[] buffer = new byte[32768];          while (true)          {              int read = input.Read(buffer, 0,buffer.Length);              if (read <= 0) return;        
                          output.Write(buffer,0, read);          }      }
    private static void Upload(string filename, string ftpServerIP, string ftpUserID, string ftpPassword)
    {
        FileInfo fileInf = new FileInfo(filename); string uri = "ftp://" + ftpServerIP + "/" + fileInf.Name; FtpWebRequest reqFTP;
        reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + fileInf.Name));
        reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword); reqFTP.KeepAlive = false; reqFTP.Method = WebRequestMethods.Ftp.UploadFile;
        reqFTP.UseBinary = true; reqFTP.ContentLength = fileInf.Length;            // The buffer size is set to 2kb     
        int buffLength = 2048; byte[] buff = new byte[buffLength]; int contentLen; FileStream fs = fileInf.OpenRead(); Stream strm = reqFTP.GetRequestStream(); contentLen = fs.Read(buff, 0, buffLength); while (contentLen != 0) { strm.Write(buff, 0, contentLen); contentLen = fs.Read(buff, 0, buffLength); } strm.Close(); fs.Close();
    }
};

Once you have buit this assembly place it in the root of C on your SQL server. You now need to create the asymmetric key and its mapped login and db user.

USE master  
GO  
-- First Create the Asymmetric Key from the Assembly  
CREATE ASYMMETRIC KEY SQLCLR_ExampleKey  FROM EXECUTABLE FILE = ''C:ftpzip.dll'' 
GO    
-- Create the Login from the Asymmetric Key  
CREATE LOGIN SQLCLR_ExampleLogin FROM ASYMMETRIC KEY SQLCLR_ExampleKey  
GO    
-- Grant the External Access Priviledge to the Login  
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLR_ExampleLogin  
GO    
USE [your_db]  
GO  
-- Add a database user in the SQLCLR_Net Database for the Login  
CREATE USER [SQLCLR_ExampleLogin] FOR LOGIN [SQLCLR_ExampleLogin]

Now create the proc

CREATE PROCEDURE [dbo].[zipandftp]
    @FTPAddress [nvarchar](4000),
    @user [nvarchar](4000),
    @pwd [nvarchar](4000),
    @filePath [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ftpzip].[StoredProcedures].[zipandftp]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'zipandftp.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'zipandftp'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'17' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'zipandftp'
GO

Now you have you assembly and the CLR proc you just need the script which creates the backup and calls proc with the ftp credentials

DECLARE @bak  NVARCHAR(255)
-- this bit of date mangling gives us the same backup name format generated by management studio scheduled backups. Which is nice. 
SET @bak  = N'C:\SQL\'+'your_db'+ CONVERT(varchar(255),getdate(),112)+ REPLACE(LEFT(CONVERT(TIME,GETDATE()),5),':','')+'.BAK'
print 'beginning backup'
BACKUP DATABASE [your_db] TO  DISK = @bak
WITH NOFORMAT, NOINIT,  NAME = N'your_db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
print 'finsihed back now ftp'

exec dbo.zipandftp '192.168.1.1', 'someuser', 'somepassword', @bak

This will backup your db and move it your ftp location. No SSL is on the FTP connection in this example although the C# Upload function can be easily extended to do this. Generally ftp location should always only be exposed to the ip of your source server so even with the password external access is blocked by the firewall.