<%@ Page language="c#"%>
<%@ Import Namespace="ElementIT.PowUpload" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="CS" runat="server">

public class SQLDbWriterUploadHandler: IUploadHandle
{        
    private SqlConnection cn;
    private bool first = false;
    private int offset = 0;    
    
    private SqlCommand cmdBinaryWrite;
    private SqlParameter PointerToCurrentFile;
    private SqlParameter PointerParam;
    private SqlParameter OffsetParam;
    private SqlParameter DeleteParam;
    private SqlParameter BytesParam;

    public string dataBaseName;
    public string tableName;
    public string fileDataColumn;
    public string fileNameColumn;

    public SQLDbWriterUploadHandler(string connectionString)
    {
        //Create new SqlConnection object with specified connection string
        cn = new SqlConnection(connectionString);            
        dataBaseName = "PowUpload";
        tableName = "dbo.Files";
        fileNameColumn = "FileName";
        fileDataColumn = "data";
    }

    public bool StartParseRequest(PowUpload info)
    {
        //Open connection to sql server on each request
        cn.Open();
        //Create SqlCommand for append data to a database field
        cmdBinaryWrite = new SqlCommand(string.Format("UPDATETEXT {0}.{1}.{2} @Pointer @Offset @Delete WITH LOG @Bytes", dataBaseName, tableName, fileDataColumn), cn);
        //Add pointer param
        PointerParam  = cmdBinaryWrite.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
        //Add offset param
        OffsetParam= cmdBinaryWrite.Parameters.Add("@Offset", SqlDbType.Int);
        //Add delete param
        DeleteParam = cmdBinaryWrite.Parameters.Add("@Delete", SqlDbType.Int);                    
        //Add bytes param
        BytesParam  = cmdBinaryWrite.Parameters.Add("@Bytes", SqlDbType.Binary); //, chunk.Length

        //Proceed all requests
        return true;            
    }

    public void CancelRequest(PowUpload info)
    {            
        
    }

    public bool ParseError(Exception ex, PowUpload info)
    {            
        CloseAll();
        //Throw error immediatly
        return true;    
    }

    public void WriteChunk(byte[] chunk, PowUpload info)
    {
        if((cn != null & (cn.State != ConnectionState.Broken && cn.State != ConnectionState.Closed)) && PointerToCurrentFile.Value != null && cmdBinaryWrite != null)
        {                    
            //Setting all parameters in sqlcommand
            if(first) //Delete first byte written before
            {
                DeleteParam.Value = 1;
                first=false;
            }
            else
                DeleteParam.Value = 0;
            OffsetParam.Value = offset;                            
            PointerParam.Value = PointerToCurrentFile.Value;
            BytesParam.Value = chunk;    
            BytesParam.Size = chunk.Length;
            cmdBinaryWrite.ExecuteNonQuery();
            //Increase offset value
            offset += chunk.Length;    
        }    
    }

    public bool NewFileFound(UploadedFile file, PowUpload info)
    {    
        //Set offset to 0
        offset = 0;

        //Insert into table new record with 1 byte in image field (impossible to set null value!)
        if(cn != null & (cn.State != ConnectionState.Broken && cn.State != ConnectionState.Closed))
        {
            SqlCommand cmdInsertFileName = new SqlCommand(string.Format("INSERT INTO {0}.{1} ({2},{3}) VALUES ('{4}', 0x0)", dataBaseName, tableName, fileNameColumn, fileDataColumn, file.FileName), cn);
            cmdInsertFileName.ExecuteNonQuery();

            //Select pointer to current file and save it in PointerToCurrentFile
            SqlCommand cmdGetPointer = new SqlCommand(string.Format("SELECT @Pointer=TEXTPTR({3}) FROM {0}.{1} WHERE {2}='{4}'", dataBaseName, tableName, fileNameColumn, fileDataColumn, file.FileName), cn);
            PointerToCurrentFile  = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
            PointerToCurrentFile.Direction = ParameterDirection.Output;                
            cmdGetPointer.ExecuteNonQuery();

            first = true;
        }
        return false; //do not save file into hard disk
    }

    public void EndFileFound(UploadedFile file, PowUpload info)
    {            

    }

    public void EndParseRequest(PowUpload info)
    {
        CloseAll();
    }

    public void FormFieldFound(string fieldName, string fieldValue, PowUpload info)
    {

    }

    private void CloseAll()

    {
        //Close connection to sql server
        if(cn!=null)
            cn.Close();
        if(cmdBinaryWrite!=null)
            cmdBinaryWrite.Dispose();
        if(cn!=null)
            cn.Dispose();
    }

    ~SQLDbWriterUploadHandler()
    {
        CloseAll();
    }
    
}    

        protected string uniqueID;
        
        private void Page_Load(object sender, System.EventArgs e)
        {
            uniqueID = (new Random()).Next(int.MaxValue).ToString();
        
            //First set correct connection string to database.

            string ConnectionString = "Persist Security Info=False;Integrated Security=true;Initial Catalog=PowUpload;Data Source=VAPHOUSE;Packet Size=4096;Workstation ID=VAPHOUSE;";
    
            //Or set individual settings for singe request.
            Settings.SingleSettings[uniqueID][Settings.SettingsParams.UploadHandleObject] = new SQLDbWriterUploadHandler(ConnectionString);
        }
</script>
<HTML>
    <HEAD>
        <title>Example demonstrates IUploadHandle interface to do your custom actions while uploading.</title>
        <LINK href="styles.css" rel="stylesheet">
        <script src="PowUploadScripts.js" type="text/javascript"></script>
    </HEAD>
    <body>
        <script language="javascript">            
            var progressWindow;
            function StartUpload(formObj)
            {
                var uniqueID = '<%=uniqueID%>';
                progressWindow = ShowProgressInNewWindow(uniqueID, 'Progress.aspx', 600, 200);
                formObj.submit();
                return true;
            }
        </script>
        Example demonstrates <b>IUploadHandle</b> interface that allows to do custom 
        actions while uploading.<br>
        This sample demonstrates saving files directly to SQL batabase while uploading 
        without saving to hard disk.<br>
        <br>
        To run sample you need:<br>
        1. Make at SQL Server new database called <b>PowUpload</b><br>
        2. Make new table called <b>Files</b> (dbo.Files) with columns: <b>FileName</b> 
        of type <b>varchar</b> and <b>data</b> of type <b>image</b><br>
        3. Modify Connection String to connect to the database.<br>
        4. Comment at Multiplefilesupload.aspx file saving code (file will be saved to 
        database while upload)
        <br>
        <form onSubmit="return StartUpload(this);" name="myform" id="myform"  method="post" action="Multiplefilesupload.aspx?UploadID=<%=uniqueID%>" encType="multipart/form-data">
            <input type="file" name="file1" id="file1" style="WIDTH:440px"> <input type="submit" name="SubmitButton" id="SubmitButton" value="Upload">
        </form>
        <hr>
        <a href='default.aspx'>Other examples</a><BR>
        <p class="usedfiles">Used files: Bin\Element-IT.PowUpload.dll, Web.Config, 
            IUploadHandle.aspx, SQLDbWriterUploadHandler.aspx, Multiplefilesupload.aspx, Progress.aspx, 
            PowUploadScripts.js, Styles.css [Optional]
        </p>
    </body>
</HTML>