Sql paging

DECLARE @PageNumber nvarchar(10) = '5'
DECLARE @PageSize nvarchar(50)  = '10'

IF(ISNULL(@PageNumber,'') = '')
      BEGIN
            SET @PageNumber = '1'
      END

IF(ISNULL(@PageSize,'') = '')
      BEGIN
            SET @PageSize = '10'
      END

DECLARE @query nvarchar(max) = 'SELECT * FROM Vendors'

EXEC ('SELECT * FROM (SELECT * , ROW_NUMBER() OVER(Order By VendorId) AS RowNum         
,COUNT(*) OVER (PARTITION BY NULL) AS PageCount        
FROM ( '+@query + '  )T) T1          
WHERE ROWNUM BETWEEN (' + @PageNumber +'*'+ @PageSize + '+ 1 -' + @PageSize +') AND ('+@PageSize +'*'+ @PageNumber+')' )

Page life cycle of Asp.net

Following are steps involves in the Asp.net page life cycle.

• S – Start
• I – Initialize
• L – Load
• V – Validate
• E – Event Handling
• R – Render

1. Page Request :When user requests for the page, ASP.NET checks if it is a first request then page needs to be parsed and compiled and if it is not a first request then the page can be sent from the cached version store on the server.

2. Start : In this Request, Request properties are set and if it is a first request then Request, Response, IsPostBack and UICulture are set also set. If we need to access or override behavior for this step, use the PreInit method to create or re-create dynamic controls, set a master page or theme or read or set profile property values

3. Page Initialization : Each control uniqueId is created .

Three Page initialization events are : 

• Init : After initialization of all controls this event is called and it is also used to initialize control properties.
• InitComplete :This event is raised by the page object and is used for processing tasks which are required for initialization.
• PreLoad :  Use this event if you need to perform processing on your page or control before the Load event. After the Page raises this event, it loads view state for itself and all controls, and then processes any post-back data included with the Request instance. 

4. Load : In this stage controls are loaded with information retrieved from view and
control states. This is where we will want to set properties for all of the server controls
on our page, request query string and establish database connections.

5. Validation : If we have controls that require validation, they are validated here and
we can check the IsValid property of the control.

6. Event Handling : The event handling for server controls occurs during this stage. This means that events such as click, selectedIndexChanged etc are applied to your sever controls, and in the case of postback, these event handlers are fired by the control. The accessible events of note in this
stage age as follows :

• Load Complete : All of the controls for the page have been loaded.
• PreRender : A few things of import happen here. First, the page object will call "EnsureChildControls" for each control,and finally for the page. Additionally any data bound control that has a "DataSourceId" set will call its "DataBind" method. It is important to note that the "PreRender" event occurs for each control on the page. At the conclusion of the event, viewstate will be saved for the page and all of the controls.
• SaveStateComplete : ViewState has been saved. If you have actions that do not require changes to controls but require Viewstate to have been saved, you can handle the "SaveStateComplete" event.

7.  Render : Render is not really an event. Rather, the page object calls this method on each control, Which in turn writes out the HTML markup for the control to the browser.

8. Unload : This final event occurs first for each control, then, finally, for the page. At this point, all controls have been rendered to the output stream and cannot be changed. During this event any attempt to access the response stream will result in an exception being thrown.

To check mail functionality

Steps of Sending mail

1.  Create object of MailMessage Class "oMsg".
2. Assign "From email" address to oMsg.From.
3. Assign "To email address" to oMsg.To.
4. Assign Subject to oMsg.Subject.
5. Assign body text to oMsg.Body.
6. Assign Subject Encoding to  oMsg.SubjectEncoding.
7. Assign Body Encoding to oMsg.BodyEncoding.  
8. Assign Isbodyhtml bool variable value to oMsg.IsBodyHtml.
9.  Assign priority to oMsg.Priority.
10. Create SMTP Client object.
11. Give SMTP credentials in the constructor as parameters.
12. Assign Delivery method to oSmtp.DeliveryMethod.
13. Assign SSL bool variable to oSmtp.EnableSsl.
14. Create object of NetworkCredential class.
15. Pass SMTP user name and password as constructor parameters.
16. Assign "UseDefaultCredentials" bool value to oSmtp.UseDefaultCredentials.
17. Assign  Network credential object to oSmtp.Credential.
18. Pass "oMsg" object to Send function.
     
    
    

Example :

using System;
using System.Collections.Generic;
using System.Text;
using System.Net.Mail;
using System.Net;

namespace mysendemail
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("mail Processs started");
                MailMessage oMsg = new MailMessage();
                oMsg.From = new MailAddress("FromEmailAddress", "xxx");
                oMsg.To.Add(new MailAddress("ToEmailAddress", "Tejas"));
                oMsg.Subject = "Packet Parsing Problem";
                oMsg.Body = " Problem Occuread test mail";
                oMsg.SubjectEncoding = System.Text.Encoding.UTF8;
                oMsg.BodyEncoding = System.Text.Encoding.UTF8;
                oMsg.IsBodyHtml = false;
                oMsg.Priority = MailPriority.High;
                SmtpClient oSmtp = new SmtpClient("smtp.mail.yahoo.com", 587);//SMTP and Port
                oSmtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                oSmtp.EnableSsl = true;
                NetworkCredential oCredential = new NetworkCredential("SMTP UserName", "Password");
                oSmtp.UseDefaultCredentials = false;
                oSmtp.Credentials = oCredential;
                oSmtp.Send(oMsg);
                Console.WriteLine("Mail sent");
                Console.ReadKey();
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.InnerException);
                Console.ReadKey();
            }
        }
    }
}

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;
        }


    }
}