using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;


namespace SilverlightWeb.portlets.Silverlight_sample
{
    public class DBTool
    {
        private static string dbConn = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();


        public static void sendToDb(string filePath, string fileName, string desc)
        {
            FileStream fs = File.OpenRead(filePath);

            if (fs.Length == 0)
            {
                throw new System.ApplicationException("Empty File");
            }
            BinaryReader reader = new BinaryReader(fs);

            byte[] fileContent = reader.ReadBytes((int)fs.Length);
            sendToDb(fileContent, fileName, desc);
            reader.Close();
            fs.Close();
        }

        public static bool deleteInDB(long id)
        {

            try
            {
                using (SqlConnection cn = new SqlConnection(dbConn))
                {
                    SqlCommand command = new SqlCommand(
                        "DELETE FROM media WHERE id=@Id;", cn);

                    command.Parameters.Add("@Id",
                       SqlDbType.BigInt).Value = id;

                    cn.Open();
                    command.ExecuteNonQuery();
                    return true;
                }
            }
            catch (System.Exception e)
            {
                return false;
            }
        }


        public static void sendToDb(byte[] fileContent, string fileName, string desc)
        {

            using (SqlConnection cn = new SqlConnection(dbConn))
            {
                SqlCommand command = new SqlCommand(
                    "INSERT INTO media (description, fileName, fileContent) " +
                    "Values(@Description, @FileName, @FileContent);", cn);

                command.Parameters.Add("@Description",
                    SqlDbType.VarChar, 256).Value = desc;
                command.Parameters.Add("@FileName",
                    SqlDbType.VarChar, 128).Value = fileName;



                command.Parameters.Add("@FileContent",
                     SqlDbType.VarBinary, fileContent.Length).Value = fileContent;

                cn.Open();
                command.ExecuteNonQuery();


            }
        }

        public static string getFromDB(string tempPath, string fileid)
        {
            FileStream fs;                          // Writes the BLOB to a file (*.bmp).
            BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
            int bufferSize = 100;                   // Size of the BLOB buffer.
            byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
            long retval;                            // The bytes returned from GetBytes.
            long startIndex = 0;                    // The starting position in the BLOB output.

            string filePath = null;

            using (SqlConnection cn = new SqlConnection(dbConn))
            {
                SqlCommand command = new SqlCommand(
                    "SELECT fileName, fileContent from  media " +
                    "WHERE id = @FileId;", cn);


                command.Parameters.Add("@FileId",
                    SqlDbType.BigInt).Value = long.Parse(fileid);

                // Open the connection and read data into the DataReader.
                cn.Open();
                SqlDataReader myReader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                if (myReader.Read())
                {
                    string fileName = (string)myReader[0];
                    filePath = Path.Combine(tempPath, fileName);

                    fs = new FileStream(filePath,
                                        FileMode.OpenOrCreate, FileAccess.Write);
                    bw = new BinaryWriter(fs);
                    // Reset the starting byte for the new BLOB.
                    startIndex = 0;

                    // Read the bytes into outbyte[] and retain the number of bytes returned.
                    retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

                    // Continue reading and writing while there are bytes beyond the size of the buffer.
                    while (retval == bufferSize)
                    {
                        bw.Write(outbyte);

                        // Reposition the start index to the end of the last buffer and fill the buffer.
                        startIndex += bufferSize;
                        retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                    }

                    // Write the remaining buffer.
                    bw.Write(outbyte, 0, (int)retval);
                    bw.Flush();

                    // Close the output file.
                    bw.Close();
                    fs.Close();
                }

                // Close the reader and the connection.
                myReader.Close();
                cn.Close();
                return filePath;

            }
        }

        public static byte[] getFileContent(string fileid)
        {
            BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
            int bufferSize = 100;                   // Size of the BLOB buffer.
            byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
            long retval;                            // The bytes returned from GetBytes.
            long startIndex = 0;                    // The starting position in the BLOB output.


            using (SqlConnection cn = new SqlConnection(dbConn))
            {
                SqlCommand command = new SqlCommand(
                    "SELECT fileName, fileContent from  media " +
                    "WHERE id = @FileId;", cn);


                command.Parameters.Add("@FileId",
                    SqlDbType.BigInt).Value = long.Parse(fileid);

                // Open the connection and read data into the DataReader.
                cn.Open();
                SqlDataReader myReader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                using (MemoryStream ms = new MemoryStream())
                {

                    if (myReader.Read())
                    {
                        bw = new BinaryWriter(ms);
                        // Reset the starting byte for the new BLOB.
                        startIndex = 0;

                        // Read the bytes into outbyte[] and retain the number of bytes returned.
                        retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

                        // Continue reading and writing while there are bytes beyond the size of the buffer.
                        while (retval == bufferSize)
                        {
                            bw.Write(outbyte);

                            // Reposition the start index to the end of the last buffer and fill the buffer.
                            startIndex += bufferSize;
                            retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                        }

                        // Write the remaining buffer.
                        bw.Write(outbyte, 0, (int)retval);
                        bw.Flush();

                        // Close the output file.
                        bw.Close();
                        //fs.Close();
                    }


                    // Close the reader and the connection.
                    myReader.Close();
                    cn.Close();
                    return ms.ToArray();
                }

            }
        }

        public static List<FileInfo> getfileInfos()
        {
            List<FileInfo> files = new List<FileInfo>();
            string dbConn = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString()))
            {
                SqlCommand cmd = new SqlCommand("SELECT id, fileName, description FROM media", cn);
                cn.Open();
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (rdr.Read())
                {
                    FileInfo f = new FileInfo();
                    f.id = (long)rdr[0];
                    f.name = (string)rdr[1];
                    f.description = (string)rdr[2];
                    files.Add(f);

                }

                // Call Close when done reading.
                rdr.Close();

            }
            return files;
        }


        internal static byte[] getFileContent(long id)
        {
            throw new NotImplementedException();
        }
    }


    public class FileInfo
    {
        public long id { set; get; }
        public string name { set; get; }
        public string description { set; get; }
        public string url { set; get; }
    }
}