Checking Excel file is open or not in (In Shared mode also)

 Below is the code ,where i am checking a specified file is opened or not and if opened i am closing or else deleting.

There is no built in functionality in .Net to check ISFILEOPEN.
So we are forcebily trying to read the ,so if its not opened it will through a exception & there we can write the code for if file is not opened.
Using f As New IO.FileStream("C:\1.xlsx", FileMode.Open, FileAccess.ReadWrite, FileShare.None)

If the  file is in shared mode we have to check as below
Using f As New IO.FileStream("C:\1.xlsx", FileMode.Truncate, FileAccess.ReadWrite, FileShare.None)

Filemode.truncate will check it in shared mode.


  Dim thisFileInUse As Boolean = False
            If System.IO.File.Exists("C:\1.xlsx") Then
                Try
                    Using f As New IO.FileStream("C:\1.xlsx", FileMode.Open, FileAccess.ReadWrite, FileShare.None)
                        ' thisFileInUse = False
                    End Using
                    IO.File.Delete("C:\1.xlsx")
                Catch

                    thisFileInUse = True
                    nFiles = excel.Workbooks.Count
                    For iVar = 1 To nFiles
                        If UCase(excel.Workbooks(iVar).Path & "\" & excel.Workbooks(iVar).Name) = UCase("C:\1.xlsx") Then
                            excel.Workbooks(iVar).Close()

                        End If
                    Next
                End Try
            End If

SQL Bulk Update from Excel

Below is the sample code in C# for bulk upload of data from excel to Sql Server table.

Steps:

1-Write connection string for Sql Database

2-Coonection string for Excel sheet

3-Query to get required Data from excel sheet

4-If we need specific data from excel sheet then we can mention as FROM [Sheet1$A3:o12],So that it will read from A3 upto o12

5-Fille that data into one dataset

6-If need to do any filteration on the excel data we can do on that dataset using dataview rowfilter

7-Using column mapping ,fill the excel data into sql database table

8-Column mapping is required to specify the excel column name to Sql table column name

9-Destination table name should be specified .

 

Imports System.Data.SqlClient

Imports System.Data.OleDb

Imports System.Text.RegularExpressions

Imports System.IO

Imports System.Data

Partial Class _Default

    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try

            Dim con As String = "Write your connection string to connect sql database"

            Dim sqlcon As SqlConnection = New SqlConnection(con)

            If Not FileUpload1.HasFile Then

                Return

            End If

            Dim PathName As String = ""

            If FileUpload1.HasFile Then

                Dim filepath As String = FileUpload1.PostedFile.FileName

                Dim pat As String = "\\(?:.+)\\(.+)\.(.+)"

                Dim r As New Regex(pat)

                'run

                Dim m As Match = r.Match(filepath)

                Dim file_ext As String = m.Groups(2).Captures(0).ToString()

                Dim filename As String = m.Groups(1).Captures(0).ToString()

                Dim file As String = filename & "." & file_ext

NOTE 'Below code is to save the Excel file in local folder'

NOTE 'Its not mandatory'

                FileUpload1.PostedFile.SaveAs(Server.MapPath("Uploads\") & file)

 

                PathName = Server.MapPath("Uploads\") & file

            End If

 'Below is the connection string for excel sheet'

            Dim connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + PathName + ";Extended Properties=Excel 8.0;")

          

'Below is Query for excel data'

            Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter("Select '0' as BatchID,'0' as BatchDate,* FROM [Sheet1$] ", connection)

            Dim dsk As New DataSet

            oleAdapter.Fill(dsk)

            Dim excelData As New DataTable("ExcelData")

            exceldata = dsk.Tables(0)

            If sqlcon.State = ConnectionState.Closed Then

                sqlcon.Open()

            End If

            Using copy As New SqlBulkCopy(sqlcon)

                

NOTE 'Below is the code for mapping Source or Excel table to Destination or Sql database table'

                'copy.ColumnMappings.Add("BatchID", "BatchID")

                'copy.ColumnMappings.Add("BatchDate", "BatchDate")

                'copy.ColumnMappings.Add("compPartNumber", "compPartNumber")

                'copy.ColumnMappings.Add("PoUnitPrice", "PoUnitPrice")

               

                copy.DestinationTableName = "Give the destination table name"

                copy.WriteToServer(excelData)

            End Using

        Catch ex As Exception

        End Try

    End Sub

End Class

New Book By Pinal

Pinals new book has been released with all sql server interview questions.  Details are here in the link.

http://blog.sqlauthority.com/sql-server-books/sql-server-interview-questions-and-answers-for-all-database-developers-and-developers-administrators/

Windows Azura Version 1.6 Released

Windows Azure SDK for .NET – November 2011 (Version 1.6)–Released

Microsoft has released latest version of Windows Azure SDK for .NET – November 2o11 – Version 1.6.
Windows Azure SDK for .NET, which include SDKs, basic tools, and extended tools for Microsoft Visual Studio 2010.
Latest Windows Azure SDK 1.6 includes the following new features:

• Windows Azure Libraries for .NET
• Improved Service Bus and Caching capabilities and performance.
• New service management APIs for storage.
• URL Rewrite 2.0
• Windows Azure Emulator – November 2011
• Performance Improvements to compute & storage emulators.
• Windows Azure Authoring Tools – November 2011
• Windows Azure SDK 1.6 Build 1.6.41103.1601
• Windows Azure Tools for Visual Studio 2010 SP1 – November 2011
• Streamlined publishing: Easily connect your environment to Windows Azure by downloading a publish settings file for your account. You can then configure all aspects of deployments, such as Remote Desktop (RDP), without ever leaving Visual Studio. By default, publish will make use of in-place deployment upgrades for significantly faster application updates.
• Multiple profiles: Your publish settings, build config, and cloud config choices will be stored in one or more publish profile MSBuild files. This makes it easy for you and your team to quickly change all your environment settings.
• Team Build: The Windows Azure Tools for Visual Studio 2010 now offer MSBuild command-line support to package your application and pass in properties. Additionally they can be installed on a lighter-weight build machine without the requirement of Visual Studio being installed.
• Visual Studio now allows you to make improved in-place updates to deployed services in Windows Azure.
• http://blogs.msdn.com/b/windowsazure/archive/2011/10/19/announcing-improved-in-place-updates.aspx
• Improved robustness of Remote Desktop Connectivity to Windows Azure VMs.
• Windows Azure SDK for .NET – November 2011
• ASP.NET MVC3 Tools Update Installer

Official Announcement: http://blogs.msdn.com/b/windowsazure/archive/2011/11/14/updated-windows-azure-sdk-amp-windows-azure-hpc-scheduler-sdk.aspx
DOWNLOADS :
The release includes the following components for download (for diff processor architecture platforms)
WindowsAzureEmulator-x64.exe Download ( Windows Azure Developer Emulator for Windows x64 )
WindowsAzureEmulator-x86.exe Download ( Windows Azure Developer Emulator for Windows x86 )
WindowsAzureLibsForNet-x64.msi Download ( Necessary libraries for .NET Development in Windows X64 )
WindowsAzureLibsForNet-x86.msi Download ( Necessary libraries for .NET Development in Windows x86 )
WindowsAzureSDK-x64.msi Download (Windows Azure SDK – for Win64)
WindowsAzureSDK-x86.msi Download (Windows Azure SDK – for Winx86)
WindowsAzureTools.VS100.exe Download (Windows Azure tools v1.6 for Visual Studio 2010)
also you can install necessary software’s for Windows Azure development using the Web Platform Installer here: http://go.microsoft.com/fwlink/?LinkID=156784
Ref Courtesy & More info available at:
http://blogs.msdn.com/b/windowsazure/archive/2011/11/14/updated-windows-azure-sdk-amp-windows-azure-hpc-scheduler-sdk.aspx
11/16/2011 Blog Update 1 : Windows Azure Platform Training Kit also available.
Windows Azure Platform Training Kit – November Update
Windows Azure Platform Training Kit includes a comprehensive set of technical content to help you learn how to use Windows Azure platform.

If you are looking forward to try out Windows Azure Trial account. Try this 90 DAY trial offer http://www.microsoft.com/windowsazure/free-trial/

 

Programcall Link

http://www.programcall.com/27/csnet/abstract-classes-and-abstract-methods-in-csnet.aspx