Fustion table insertion updation

using System;
using System.Threading;
using System.Threading.Tasks;

using Google;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Download;
using Google.Apis.Drive.v2;
using Google.Apis.Drive.v2.Data;
using Google.Apis.Logging;
using Google.Apis.Services;
using Google.Apis.Upload;
using Google.Apis.Fusiontables;
using Google.Apis.Fusiontables.v2;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using Google.Apis.Requests;
using System.Collections.Generic;
using System.Net;




namespace Drive.Sample
{
    /// <summary>
    /// A sample for the Drive API. This samples demonstrates resumable media upload and media download.
    /// See https://developers.google.com/drive/ for more details regarding the Drive API.
    /// </summary>

    class Program
    {

        static Program()
        {
            // initialize the log instance
            ApplicationContext.RegisterLogger(new Log4NetLogger());
            Logger = ApplicationContext.Logger.ForType<ResumableUpload<Program>>();
        }

        #region Consts

        private const int KB = 0x400;
        private const int DownloadChunkSize = 256 * KB;

        // CHANGE THIS with full path to the file you want to upload
        private const string UploadFileName = @"D:\Desert.jpg";

        // CHANGE THIS with a download directory
        private const string DownloadDirectoryName = @"D:\";

        // CHANGE THIS if you upload a file type other than a jpg
        private const string ContentType = @"image/jpeg";

        #endregion

        /// <summary>The logger instance.</summary>
        private static readonly ILogger Logger;

        /// <summary>The Drive API scopes.</summary>
        private static readonly string[] Scopes = new[] { DriveService.Scope.DriveFile, DriveService.Scope.Drive, FusiontablesService.Scope.Fusiontables };
        // private static readonly string[] Scopes = new[] { FusiontablesService.Scope.Fusiontables };

        /// <summary>
        /// The file which was uploaded. We will use its download Url to download it using our media downloader object.
        /// </summary>
        // private static File uploadedFile;

        static void Main(string[] args)
        {
            Console.WriteLine("Google Drive API Sample");

            try
            {
                new Program().Run().Wait();
            }
            catch (AggregateException ex)
            {
                foreach (var e in ex.InnerExceptions)
                {
                    Console.WriteLine("ERROR: " + e.Message);
                }
            }

            Console.WriteLine("Press any key to continue...");
            Console.ReadKey();
        }


        private async Task Run()
        {
            GoogleWebAuthorizationBroker.Folder = "Fusion.Sample";
            UserCredential credential;
            using (var stream = new System.IO.FileStream("client_secrets.json",
                System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None);
            }

            // Create the service.
            var Driveservice = new DriveService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "Drive API Sample",
            });


            var funsionTableService = new FusiontablesService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "FusionTable API Sample",
            });

            CreateFusionTable(funsionTableService, Driveservice);

        }
        private Task<IUploadProgress> UploadCsvFileAsync(FusiontablesService service, string tableId, string filePath)
        {
            string fileName = @filePath;

            var uploadStream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);

            TableResource oresource = new TableResource(service);
            // byte[] bytCsvFile = System.IO.File.ReadAllBytes(@"D:\myCsvData.csv");
            //  System.IO.Stream oStream = new System.IO.MemoryStream(bytCsvFile);

            TableResource.ImportRowsMediaUpload mediaUp = oresource.ImportRows(tableId, uploadStream, "application/octet-stream");
            mediaUp.ChunkSize = FilesResource.InsertMediaUpload.MinimumChunkSize * 2;

            mediaUp.ProgressChanged += mediaUp_ProgressChanged;
            mediaUp.ResponseReceived += mediaUp_ResponseReceived;

            Console.WriteLine("Time before start" + DateTime.Now);
            var task = mediaUp.UploadAsync();

            task.ContinueWith(t =>
            {
                // NotOnRanToCompletion - this code will be called if the upload fails
                Console.WriteLine("Upload Failed. " + t.Exception);
            }, TaskContinuationOptions.NotOnRanToCompletion);
            task.ContinueWith(t =>
            {
                Logger.Debug("Closing the stream");
                uploadStream.Dispose();
                Logger.Debug("The stream was closed");
                Console.WriteLine("Time after End " + DateTime.Now);
            });

            return task;
        }

        void mediaUp_ResponseReceived(Google.Apis.Fusiontables.v2.Data.Import obj)
        {
            Console.WriteLine("Number of Rows Imported: " + obj.NumRowsReceived.ToString());
        }

        void mediaUp_ProgressChanged(IUploadProgress obj)
        {
            Console.WriteLine("Number of Bytes Sent: " + obj.BytesSent.ToString());
        }

        private void ToCSV(DataTable dtDataTable, string strFilePath)
        {
            StreamWriter sw = new StreamWriter(strFilePath, false);
            //headers
            for (int i = 0; i < dtDataTable.Columns.Count; i++)
            {
                sw.Write(dtDataTable.Columns[i]);
                if (i < dtDataTable.Columns.Count - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
            foreach (DataRow dr in dtDataTable.Rows)
            {
                for (int i = 0; i < dtDataTable.Columns.Count; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        string value = dr[i].ToString();
                        if (value.Contains(","))
                        {
                            value = String.Format("\"{0}\"", value);
                            sw.Write(value);
                        }
                        else
                        {
                            sw.Write(dr[i].ToString());
                        }
                    }
                    if (i < dtDataTable.Columns.Count - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }
        private void CreateFusionTable(FusiontablesService service, DriveService Driveservice)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection("Data Source=104.239.138.166;Initial Catalog=MatInc;User ID=matinc;Password=Seasia#123;Connect Timeout=30000;MultipleActiveResultSets=True");
                //SqlConnection connection = new SqlConnection("Data Source=10.8.18.91\\seasia2k82;Initial Catalog=MatInc;User ID=matinc;Password=Se@sia#2k14;MultipleActiveResultSets=True");
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                DataSet dsDeviceType = new DataSet();
                SqlDataAdapter adpDeviceType = new SqlDataAdapter("select Id, DeviceTypeName from tblDeviceType Where IsMovable = 1 AND IsActive = 1 AND DeviceTypeName= 'LTE700';", connection);
                adpDeviceType.Fill(dsDeviceType);// dataset of device type id and device type name

                if (dsDeviceType.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow DeviceTypeRow in dsDeviceType.Tables[0].Rows)//Getting device type ids
                    {
                        int DeviceTypeId = Convert.ToInt16(DeviceTypeRow[0]);//Device type id
                        string tableId = "";


                        DataTable dtDevices = new DataTable();
                        SqlDataAdapter adpDevices = new SqlDataAdapter("DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'SELECT Distinct CI FROM '+@TableName+' ORDER BY CI' EXEC(@Sql)", connection);
                        adpDevices.Fill(dtDevices);

                        foreach (DataRow RowDevices in dtDevices.Rows)
                        {
                            DataSet dsfusionTable = new DataSet();
                            // SqlDataAdapter adpfusionTable = new SqlDataAdapter("IF EXISTS(SELECT Id FROM tblFusionTable Where DeviceTypeId = " + DeviceTypeId + ") BEGIN SELECT FustionTableId AS Status FROM tblFusionTable Where DeviceTypeId = " + DeviceTypeId + "  ; END ELSE BEGIN SELECT '2' AS Status; END ; DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60) SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'SELECT * FROM '+@TableName+'' EXEC(@Sql) ", connection);
                            SqlDataAdapter adpfusionTable = new SqlDataAdapter("IF EXISTS(SELECT Id FROM tblFusionTable Where DeviceTypeId = " + DeviceTypeId + " AND DeviceId ='" + RowDevices["CI"] + "') BEGIN SELECT TOP 1 FustionTableId AS Status,DriveFolderId as DriveStatus FROM tblFusionTable Where DeviceTypeId = " + DeviceTypeId + " AND DeviceId = '" + RowDevices["CI"] + "' ; END ELSE BEGIN IF EXISTS(SELECT ID FROM tblFusionTable WHERE DeviceTypeId = " + DeviceTypeId + " AND IsNULL(DriveFolderId,'') <> '') BEGIN DECLARE @ExistedDriveFolderId nvarchar(100) = ''; SET @ExistedDriveFolderId = (SELECT TOP 1 DriveFolderId FROM tblFusionTable WHERE DeviceTypeId = " + DeviceTypeId + "); SELECT '2' AS Status, @ExistedDriveFolderId as DriveStatus; END ELSE BEGIN SELECT '2' AS Status,'0' as DriveStatus; END  END; DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60) SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'SELECT TOP 1 * FROM '+@TableName+' WHERE CI=''" + RowDevices["CI"] + "''' EXEC(@Sql) ", connection);
                            adpfusionTable.Fill(dsfusionTable);//Dataset of schema table and status

                            if (dsfusionTable.Tables[0].Rows.Count > 0)
                            {
                                foreach (DataRow Status in dsfusionTable.Tables[0].Rows)
                                {

                                    if (Convert.ToString(Status[0]) == "2")// value 2 if not exists
                                    {
                                        if (dsfusionTable.Tables[1].Rows.Count > 0)//Getting coloumns of the schema table
                                        {
                                            StringBuilder createTableScript = new StringBuilder();
                                            createTableScript.Append("CREATE TABLE " + Convert.ToString(RowDevices["CI"]) + " (");


                                            foreach (DataColumn Col in dsfusionTable.Tables[1].Columns)
                                            {
                                                string datatype = Col.DataType.FullName;

                                            }

                                            foreach (DataColumn Col in dsfusionTable.Tables[1].Columns)
                                            {
                                                string DataType = "";

                                                switch (Col.DataType.FullName)
                                                {
                                                    case "System.String":
                                                        {
                                                            DataType = "STRING";
                                                        }
                                                        break;
                                                    case "System.Double":
                                                        {
                                                            DataType = "NUMBER";
                                                        }
                                                        break;
                                                    case "System.DateTime":
                                                        {
                                                            DataType = "DATETIME";
                                                        }
                                                        break;
                                                    default:
                                                        {
                                                            DataType = "STRING";
                                                        }
                                                        break;
                                                }


                                                if (Col.ColumnName.Contains("Lat"))
                                                {

                                                    createTableScript.Append("'" + Col.ColumnName + "'" + ": LOCATION,");//appending coloumn names
                                                }
                                                //else if (Col.ColumnName == "Lon")
                                                //{
                                                //    createTableScript.Append("'Longitude'" + ": Location,");//appending coloumn names
                                                //}
                                                else
                                                {
                                                    createTableScript.Append("'" + Col.ColumnName + "'" + ": " + DataType + ",");//appending coloumn names
                                                }
                                            }
                                            createTableScript.Length = createTableScript.Length--;
                                            createTableScript.Append(")");
                                            QueryResource obj = new QueryResource(service);
                                            // Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql("CREATE TABLE tejas(animal: STRING,birds: STRING)");
                                            Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql(createTableScript.ToString());
                                            Google.Apis.Fusiontables.v2.Data.Sqlresponse oResponse = sqlRequest.Execute();
                                            tableId = Convert.ToString(oResponse.Rows[0][0]);

                                            ChangeFilePermissions(Driveservice, tableId);

                                            string DrivefolderId = "";
                                            //if (Convert.ToString(Status[1]) == "0")
                                            //{
                                            //    DrivefolderId = CreateFolder(Driveservice, Convert.ToString(DeviceTypeRow[1]));
                                            //}
                                            //else
                                            //{
                                            //    DrivefolderId = (Convert.ToString(Status[1]));
                                            //}
                                            SqlCommand cmd = new SqlCommand();
                                            cmd.Connection = connection;
                                            cmd.CommandText = "UDSP_tblFusionTable_Insert";
                                            cmd.CommandType = CommandType.StoredProcedure;
                                            cmd.Parameters.AddWithValue("@DeviceTypeId", DeviceTypeId);
                                            cmd.Parameters.AddWithValue("@FustionTableId", tableId);
                                            cmd.Parameters.AddWithValue("@DeviceId", Convert.ToString(RowDevices["CI"]));
                                            cmd.Parameters.AddWithValue("@DriveFolderId", DrivefolderId);
                                            cmd.ExecuteNonQuery();

                                            //insertFileIntoFolder(Driveservice, DrivefolderId, tableId);

                                        }
                                    }
                                    else
                                    {
                                        tableId = Convert.ToString(Status[0]);
                                    }
                                }

                                if (tableId != "")
                                {

                                    SqlCommand cmd = new SqlCommand();
                                    cmd.Connection = connection;
                                    cmd.CommandText = "DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'SELECT Count(*) FROM '+@TableName+'  WHERE Ecio = 0 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)";
                                    int recordCount = Convert.ToInt32(cmd.ExecuteScalar());
                                    int maxLength = (recordCount / 40000);
                                    if (recordCount > 0)
                                    {

                                        if (recordCount > 40000)
                                        {
                                            for (int i = 0; i <= maxLength; i++)
                                            {
                                                DataTable dtCSV = new DataTable();
                                                SqlDataAdapter adpfusionTableforCSV = new SqlDataAdapter("DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + "; SET @Sql = 'SELECT TOP 40000 * FROM '+@TableName+'  WHERE Ecio = 0 AND CI = ''" + RowDevices["CI"] + "'';update TOP (40000)  '+@TableName+' set Ecio = 1 WHERE Ecio = 0 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)", connection);
                                                adpfusionTableforCSV.Fill(dtCSV);

                                                DataTable dtCloned = dtCSV.Clone();

                                                foreach (DataColumn Col in dtCloned.Columns)
                                                {
                                                    Col.DataType = typeof(string);
                                                }

                                                foreach (DataRow row in dtCSV.Rows)
                                                {
                                                    dtCloned.ImportRow(row);
                                                }

                                                foreach (DataRow Row in dtCloned.Rows)
                                                {
                                                    Row["Lat"] = Row["Lon"] + " " + Row["Lat"];
                                                }

                                                string filePath = "D:/" + RowDevices["CI"] + "_" + i + ".csv";
                                                if (System.IO.File.Exists(filePath))
                                                {
                                                    System.IO.File.Delete(filePath);
                                                }
                                                ToCSV(dtCloned, filePath);

                                                var uploadStream = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                                                TableResource oresource = new TableResource(service);
                                                TableResource.ImportRowsMediaUpload mediaUp = oresource.ImportRows(tableId, uploadStream, "application/octet-stream");
                                                mediaUp.ChunkSize = FilesResource.InsertMediaUpload.MinimumChunkSize * 2;
                                                try
                                                {
                                                    var returnValue = mediaUp.Upload();

                                                    if (Convert.ToString(returnValue.Status) == "Failed")
                                                    {
                                                        SqlCommand cmd2 = new SqlCommand();
                                                        cmd2.Connection = connection;
                                                        cmd2.CommandText = "DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'update TOP (40000)  '+@TableName+' set Ecio = 0 WHERE Ecio = 1 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)";
                                                        cmd2.ExecuteNonQuery();
                                                        Console.WriteLine(returnValue.Exception.Message);
                                                        Console.ReadKey();
                                                        Environment.Exit(0);
                                                    }
                                                    if (i != maxLength)
                                                    {
                                                        Thread.Sleep(120000);
                                                    }
                                                    //QueryResource obj = new QueryResource(service);
                                                    //string Query = "SELECT ROWID FROM " + tableId + " Where CI='CI'";
                                                    //// Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql("CREATE TABLE tejas(animal: STRING,birds: STRING)");
                                                    //Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql(Query);
                                                    //Google.Apis.Fusiontables.v2.Data.Sqlresponse oResponse = sqlRequest.Execute();
                                                    //var rowid = oResponse.Rows[0][0];

                                                    //QueryResource objDelete = new QueryResource(service);
                                                    //string DeleteQuery = "DELETE FROM " + tableId + " WHERE ROWID = '" + rowid + "'";
                                                    //Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequestobjDelete = objDelete.Sql(DeleteQuery);
                                                    //Google.Apis.Fusiontables.v2.Data.Sqlresponse oResponseobjDelete = sqlRequestobjDelete.Execute();
                                                }
                                                catch (Exception ex)
                                                {
                                                    SqlCommand cmd2 = new SqlCommand();
                                                    cmd2.Connection = connection;
                                                    cmd2.CommandText = "DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'update TOP (40000)  '+@TableName+' set Ecio = 0 WHERE Ecio = 1 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)";
                                                    cmd2.ExecuteNonQuery();
                                                    Console.WriteLine(ex.Message);
                                                    Console.ReadKey();
                                                    throw ex;
                                                }
                                            }
                                        }
                                        else
                                        {
                                            DataTable dtCSV = new DataTable();
                                            SqlDataAdapter adpfusionTableforCSV = new SqlDataAdapter("DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'SELECT  * FROM '+@TableName+'  WHERE Ecio = 0 AND CI = ''" + RowDevices["CI"] + "'';update '+@TableName+' set Ecio = 1 WHERE Ecio = 0 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)", connection);
                                            adpfusionTableforCSV.Fill(dtCSV);

                                            DataTable dtCloned = dtCSV.Clone();

                                            foreach (DataColumn Col in dtCloned.Columns)
                                            {
                                                Col.DataType = typeof(string);
                                            }

                                            foreach (DataRow row in dtCSV.Rows)
                                            {
                                                dtCloned.ImportRow(row);
                                            }

                                            foreach (DataRow Row in dtCloned.Rows)
                                            {
                                                Row["Lat"] = Row["Lon"] + " " + Row["Lat"];
                                            }

                                            string filePath = "D:/" + RowDevices["CI"] + ".csv";
                                            if (System.IO.File.Exists(filePath))
                                            {
                                                System.IO.File.Delete(filePath);
                                            }
                                            ToCSV(dtCloned, filePath);
                                            var uploadStream = new System.IO.FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

                                            TableResource oresource = new TableResource(service);
                                            TableResource.ImportRowsMediaUpload mediaUp = oresource.ImportRows(tableId, uploadStream, "application/octet-stream");
                                            mediaUp.ChunkSize = FilesResource.InsertMediaUpload.MinimumChunkSize * 2;
                                            try
                                            {
                                                var returnValue = mediaUp.Upload();
                                                if (Convert.ToString(returnValue.Status) == "Failed")
                                                {
                                                    SqlCommand cmd2 = new SqlCommand();
                                                    cmd2.Connection = connection;
                                                    cmd2.CommandText = "DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'update TOP (40000)  '+@TableName+' set Ecio = 0 WHERE Ecio = 1 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)";
                                                    cmd2.ExecuteNonQuery();
                                                    Environment.Exit(0);
                                                }
                                                //QueryResource obj = new QueryResource(service);
                                                //string Query = "SELECT ROWID FROM " + tableId + " Where CI='CI'";
                                                //// Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql("CREATE TABLE tejas(animal: STRING,birds: STRING)");
                                                //Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequest = obj.Sql(Query);
                                                //Google.Apis.Fusiontables.v2.Data.Sqlresponse oResponse = sqlRequest.Execute();
                                                //var rowid = oResponse.Rows[0][0];

                                                //QueryResource objDelete = new QueryResource(service);
                                                //string DeleteQuery = "DELETE FROM " + tableId + " WHERE ROWID = '" + rowid + "'";
                                                //Google.Apis.Fusiontables.v2.QueryResource.SqlRequest sqlRequestobjDelete = objDelete.Sql(DeleteQuery);
                                                //Google.Apis.Fusiontables.v2.Data.Sqlresponse oResponseobjDelete = sqlRequestobjDelete.Execute();

                                            }
                                            catch (Exception ex)
                                            {
                                                SqlCommand cmd1 = new SqlCommand();
                                                cmd1.Connection = connection;
                                                cmd1.CommandText = "DECLARE @Sql NVARCHAR(MAX);DECLARE @TableName nvarchar(60); SELECT TOP 1 @TableName = TableName FROM tblDeviceTypeDetail WHERE DeviceTypeId = " + DeviceTypeId + " ; SET @Sql = 'update TOP (40000)  '+@TableName+' set Ecio = 0 WHERE Ecio = 1 AND CI = ''" + RowDevices["CI"] + "'';' EXEC(@Sql)";
                                                cmd1.ExecuteNonQuery();
                                                Console.WriteLine(ex.Message);
                                                Console.ReadKey();
                                                throw ex;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception exp)
            {
                Console.WriteLine(exp.Message);
                Console.ReadKey();
                throw (exp);
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }

        }

        private void ChangeFilePermissions(DriveService service, string fileId)
        {
            try
            {

                FilesResource.ListRequest orequest = service.Files.List();
                FileList lstFIles = orequest.Execute();

                // var fileId = lstFIles.Items.ToList().Find(x => x.Title == "Animal Food").Id;

                InsertPermission(service, fileId, "me", "anyone", "reader");
            }
            catch (Exception)
            {

                throw;
            }

        }

        public static Permission InsertPermission(DriveService service, String fileId, String value, String type, String role)
        {
            Permission newPermission = new Permission();
            newPermission.Value = value;
            newPermission.Type = type;
            newPermission.Role = role;
            try
            {
                return service.Permissions.Insert(newPermission, fileId).Execute();
            }
            catch (Exception e)
            {
                Console.WriteLine("An error occurred: " + e.Message);
            }
            return null;
        }

        public static string CreateFolder(DriveService service, string FolderName)
        {
            Google.Apis.Drive.v2.Data.File body = new Google.Apis.Drive.v2.Data.File();
            body.Title = FolderName;
            body.Description = FolderName;
            body.MimeType = "application/vnd.google-apps.folder";

            // service is an authorized Drive API service instance
            Google.Apis.Drive.v2.Data.File file = service.Files.Insert(body).Execute();
            return file.Id;

        }

        public static Google.Apis.Drive.v2.Data.File insertFileIntoFolder(DriveService service, String folderId, String fileId)
        {
            ParentReference newParent = new ParentReference();
            newParent.Id = folderId;
            ChildReference newchild = new ChildReference();
            newchild.Id = fileId;

            try
            {
                Google.Apis.Drive.v2.Data.File file = new Google.Apis.Drive.v2.Data.File();
                file.Title = "sdfds";
                // Rename the file.
                FilesResource.PatchRequest request = service.Files.Patch(file, fileId);
                Google.Apis.Drive.v2.Data.File updatedFile = request.Execute();
                updatedFile.Parents.Add(newParent);

                FilesResource.PatchRequest request1 = service.Files.Patch(updatedFile, fileId);
                Google.Apis.Drive.v2.Data.File updatedFile1 = request1.Execute();

                return updatedFile;


                // return service.Parents.Insert(newParent, fileId).Execute();
            }
            catch (Exception e)
            {
                Console.WriteLine("An error occurred: " + e.Message);
            }
            return null;
        }


    }
}