Steps:
- Create an Import Page
- Capture the Upload File
- Process the Import
1. Create an Import Page
<%@ Page Language="vb" AutoEventWireup="false" Inherits="Admin.ProjectImport" CodeFile="ProjectImport.aspx.vb"
MasterPageFile="~/Shared/template/AppMasterPage.master" Title="Import Projects" %>
<asp:Content ContentPlaceHolderID="ActionBarContentPlaceHolder" runat="server">
<dc:ActionButton ID="ImportButton" Text="Import" runat="server" HasLeadingBullet="false" />
</asp:Content>
<asp:Content ContentPlaceHolderID="WorkSpaceContentPlaceHolder" runat="server">
<asp:Label ID="Label1" runat="server">Enter location</asp:Label>
<asp:FileUpload id="FileUpload1" runat="server" />
<asp:ObjectDataSource ID="ImportProjectLogic" runat="server"
InsertMethod="Add"
TypeName="DC.Business.ImportProject" >
<InsertParameters>
<asp:Parameter Name="attachment" Type="Object" />
</InsertParameters>
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ProgrammeLogic" runat="server"
InsertMethod="Insert"
TypeName="DC.Business.Programme">
</asp:ObjectDataSource>
<asp:ObjectDataSource ID="ProjectTypeLogic" runat="server"
InsertMethod="Insert"
TypeName="DC.Business.ProjectType">
</asp:ObjectDataSource>
</asp:Content>
<asp:Content ContentPlaceHolderID="StatusBarContentPlaceHolder" runat="server">
<asp:Label ID="ErrorMessageLabel" runat="server" ForeColor="Red"></asp:Label><br />
</asp:Content>
2. Capture the Upload File
Imports DC.Model
Namespace Admin
Partial Class ProjectImport
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Select Case Request.QueryString("Err")
Case 1
Dim sheetName As String = ConfigurationManager.AppSettings("SheetName")
ErrorMessageLabel.Text = "Could not find sheet named: " + sheetName
Exit Sub
Case 2
Dim columnProjectIDName As String = ConfigurationManager.AppSettings("ProjectID")
ErrorMessageLabel.Text = "Could not find column named: " + columnProjectIDName
Exit Sub
End Select
Session("Requery") = "No"
End Sub
Protected Sub ImportButton_Click() Handles ImportButton.Click
If FileUpload1.FileName = "" Then
ErrorMessageLabel.Text = "Error: you must enter a file name"
Return
End If
If UCase(Right(FileUpload1.FileName, 3)) <> "XLS" Then
ErrorMessageLabel.Text = "Must be an Excel spreadsheet."
Return
End If
If Not (FileUpload1.PostedFile Is Nothing) Then
ErrorMessageLabel.Text = ""
Try
ImportProjectLogic.Insert()
ProgrammeLogic.Insert()
ProjectTypeLogic.Insert()
Catch ex As Exception
While ex IsNot Nothing
ErrorMessageLabel.Text &= ex.Message & " "
ex = ex.InnerException
End While
Exit Sub
End Try
Response.Redirect("ProjectProcess.aspx")
End If
End Sub
Protected Sub ImportProjectLogic_Inserting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) _
Handles ImportProjectLogic.Inserting
Dim iod As IOrderedDictionary = e.InputParameters
iod("attachment") = FileUpload1.PostedFile.InputStream
End Sub
End Class
End Namespace
3. Process the Import
Imports SubSonic
Imports DAL = DC.DataAccess
Imports Columns = DC.DataAccess.ImportProject.Columns
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Text
Imports Excel
<System.ComponentModel.DataObject()>
Public Class ImportProject
Private _qry As New SqlQuery
Private _ret As New DAL.ImportProjectCollection
<System.ComponentModel.DataObjectMethod(
ComponentModel.DataObjectMethodType.Select, True)>
Public Function GetData(ByVal projectID As Integer) As DAL.ImportProjectCollection
_qry.From(DAL.ImportProject.Schema)
If projectID > 0 Then _qry.Where(Columns.ProjectID).IsEqualTo(projectID)
_ret.LoadAndCloseReader(_qry.ExecuteReader)
Return _ret
End Function
<System.ComponentModel.DataObjectMethod(
ComponentModel.DataObjectMethodType.Insert, True)>
Public Function Add(
ByVal projectID As String,
ByVal projectName As String,
ByVal projectManagerName As String,
ByVal status As String,
ByVal programme As String,
ByVal programmeManager As String,
ByVal currentPhase As String,
ByVal projectType As String,
ByVal businessOwner As String) As Boolean
Dim dr As DAL.ImportProject = DAL.ImportProject.FetchByID(projectID)
If dr Is Nothing Then dr = New DAL.ImportProject
Try
dr.ProjectID = projectID
dr.ProjectManagerName = projectManagerName
dr.ProjectName = projectName
dr.Status = status
dr.Programme = programme
dr.ProgrammeManager = programmeManager
dr.CurrentPhase = currentPhase
dr.ProjectType = projectType
dr.BusinessOwner = businessOwner
dr.Save()
Return True
Catch ex As Exception
Throw New ApplicationException("Unable to add new record.", ex)
Return False
End Try
End Function
<System.ComponentModel.DataObjectMethodAttribute(
System.ComponentModel.DataObjectMethodType.Insert, False)>
Public Function Add(ByVal attachment As Stream) As Boolean
Try
'0. Delete all projects
DeleteProjects(0)
'2. Reading from a binary Excel file ('97-2003 format; *.xls)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(attachment)
'3. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()
Dim dt As DataTable = result.Tables(0)
Dim projectID As String
Dim projectName As String
Dim projectManager As String
Dim projectStatus As String
Dim programme As String
Dim programmeManager As String
Dim currentPhase As String
Dim projectType As String
Dim businessOwner As String
For Each dr As DataRow In dt.Rows
projectID = GetValueOrNull(dr("Project ID").ToString)
projectName = StripIdFromName(dr("Project Name").ToString)
projectManager = GetValueOrNull(dr("Project Manager").ToString)
projectStatus = GetValueOrNull(dr("Telecom Status").ToString)
programme = GetValueOrNull(dr("Programme").ToString)
programmeManager = GetValueOrNull(dr("Programme Manager").ToString)
currentPhase = GetValueOrNull(dr("Current Phase").ToString)
projectType = GetValueOrNull(dr("Project Type").ToString)
businessOwner = GetValueOrNull(dr("Business Owner").ToString)
Add(projectID,
projectName,
projectManager,
projectStatus,
programme,
programmeManager,
currentPhase,
projectType,
businessOwner)
Next
Return True
Catch ex As Exception
Throw New ApplicationException("Unable to import Projects.", ex)
End Try
End Function
Private Function GetValueOrNull(ByVal value As String) As String
If value = "" Then
Return vbNullString
Else
Return value
End If
End Function
Private Function StripIdFromName(ByVal value As String) As String
Dim retVal As String
If IsDBNull(value) Then
retVal = vbNullString
Else
Dim projectNameList = value.Split("-")
If projectNameList.Length = 1 Then
retVal = projectNameList(0)
Else
retVal = ""
For i As Integer = 1 To projectNameList.Length - 1
retVal += projectNameList(i) & "-"
Next
retVal = Left(retVal, Len(retVal) - 1)
End If
End If
Return retVal
End Function
<System.ComponentModel.DataObjectMethod(
ComponentModel.DataObjectMethodType.Delete, True)>
Public Function DeleteProjects(ByVal projectID As Integer) As Boolean
Dim delQuery As New DeleteQuery(DAL.ImportProject.Schema)
If projectID > 0 Then delQuery.AddWhere(Columns.ProjectID, projectID)
Try
delQuery.Execute()
Return True
Catch ex As Exception
Throw New ApplicationException(ex.Message)
Return False
End Try
End Function
End Class
