Saturday, March 28, 2009

ClientID vs. UniqueID

  • UniqueID is the fully qualified control name which is used by Asp.net for postbacks. You hardly need to use it.
  • ClientID is the value that will appear in the id attribute of the control, it is a translation of the UniqueID to be a html compliant id tag (leading underscores are not really html compliant). So you need to use document.getElementById with ClientID.

References:

UniqueID vs ClientID

The difference between ID, ClientID and UniqueID

Tuesday, March 24, 2009

Backup & Restore Tips

  • Use UNC path when doing backup and restore
  • Create a new web application as well as a site collection before restoring, so that it does not restore on the default web application (C:\Inetpub\wwwroot\wss\VirtualDirectories\80). It will now be something like C:\Inetpub\wwwroot\wss\VirtualDirectories\test.elcom.com.au
  • But before restoring, you need to delete the content database of this web application, otherwise it will prompt you with a database exists error.
  • When restoring, if it says access denied to the backup folder, you need to give folder write permission to the timer service account.
  • Remember local folder permission is not the same as network shared folder permission
  • Whenever you have an access denied error, check the followings:
    • Database security
    • Service account security
    • Folder security
    • Sharepoint group security

Sunday, March 22, 2009

FBA: Create and enable FBA Database

Steps:

1. Under the C:\Windows\Microsoft.Net\Framework\v2.0.50727 folder, run aspnet_regsql -S servername -A all -E > press Enter

2. Go to C:\Inetpub\wwwroot\wss\VirtualDirectories\windowsauthenticationappfolder, make a copy of the web.config file and edit the web.config file

3. Go to system.web tag, replace the <system.web> tag with the following:


<connectionStrings>

<remove name="AspNetSqlProvider" />

<add name="AspNetSqlProvider" connectionString="Data Source=servername;Initial Catalog=aspnetdb;Integrated Security=SSPI;Persist Security Info=False;" />

</connectionStrings>

<system.web>

<membership defaultProvider="AspNetSqlMembershipProvider">

<providers>

<remove name="AspNetSqlMembershipProvider" />

<add connectionStringName="AspNetSqlProvider" passwordAttemptWindow="10" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="partners" requiresUniqueEmail="false" passwordFormat="Clear" description="Stores and retrieves membership data from the Microsoft SQL Server database" name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</membership>

<roleManager enabled="true" defaultProvider="AspNetSqlRoleProvider">

<providers>

<remove name="AspNetSqlRoleProvider" />

<remove name="AspNetWindowsTokenRoleProvider" />

<add applicationName="partners" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<add connectionStringName="AspNetSqlProvider" applicationName="partners" description="Stores and retrieves roles data from the local Microsoft SQL Server database" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</roleManager>



4. Replace the </configuration> with the following:

<system.net>

<mailSettings>

<!-- use your mail settingst here -->

<smtp deliveryMethod="Network" from="test@test.com">

<network host="smtp-server" port="25" />

</smtp>

</mailSettings>

</system.net>

</configuration>


5. Go to C:\Inetpub\wwwroot\wss\VirtualDirectories\fbaappfolder, make a copy of the web.config file and edit the web.config file, make the same change as above.

6. Copy the FBA tools to C:\Inetpub\wwwroot\wss\VirtualDirectories\windowsauthenticationappfolder as well as C:\Inetpub\wwwroot\wss\VirtualDirectories\fbaappfolder

7. Run iisreset /noforce

8. Go to your windows authentication site, run the FBA tools and add a user with username and password.

9. Go to People and Groups > Select the e.g. the Visitor Group > New > get the username just added > give permission to the account > click OK, so this user has been added to the windows authentication site successfully.

10. Go to the FBA site, it shoule allow you to see the home page since we enabled the anonymous access previously.

11. Click Sign In > type in the user you have just created in the windows authentication site (default zone) > Click Sign In > Now you are logged in as that user > you can then access the FBA tool to recover your password, it will send the password to you via email.

12. If you encounter an error in the recover page, you can change the customErrors's mode to RemoteOnly in the web.config and run iisreset /noforce to investigate.

13. Go to Central Admin > Application Management > Application Security > Authentication Providers > click on Extranet > uncheck Enable anonymous access > click Save > do the same for the default windows zone.

FBA: Create new zone

Steps:

1. Central Admin > Application Management > Create or Extend Web application > Extend an existing Web application

2. Select the web application that you want to extend > Create a new IIS web site > give it a description > use port 80 > enter Host Header (e.g. test1.elcom.com) > leave the security configuration at default values

3. Choose the Zone (e.g. Extranet) > click OK

4. Central Admin > Application Management > Application Security > Authentication providers

5. Click Extranet > Change the Authentication Type to Forms > Check Enable anonymous access

6. Fill in AspNetSqlMembershipProvider as the Membership provider name.

7. Fill in AspNetSqlRoleProvider as the Role Manager name.

8. Leave the Enable Client Integration as No. FBA does not work well with Office integration. It is because FBA relies on cookies and most users disable persistent cookies in the browser, that is the default security settings. So if you want to enable client integration in FBA, you have to enable persistent cookies on each client, if you do not do that, if you try to open a Word document in a FBA web application, you will get the FBA logon screen in Word. So we will almost always select No here. It is just too hard to get integration working with FBA.

9. Click Save > Go to Operation tab > Alternate Access Mappings, you will see that it creates a mapping for extranet

Monday, March 16, 2009

Import csv file into SQL server

The following code will:

  • Import csv file into database
  • Validate csv file before import
    • Create Schema.ini ==> Schema.ini is a information file, used to define the data structure and format of each column that contains data in the CSV file.
    • Check Duplicate Rows
    • Check Column Header Exist


Private Function GetCSV() As System.Data.DataTable

If ViewState("SaveLocation") Is Nothing Then

Return Nothing

End If

Dim SaveLocation As String = ViewState("SaveLocation").ToString()

Using conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " & Path.GetDirectoryName(SaveLocation) & "; Extended Properties = ""Text;HDR=YES;FMT=Delimited""")

conn.Open()

Dim strQuery As String = "SELECT * FROM [" & Path.GetFileName(SaveLocation) & "]"

Dim adapter As New System.Data.OleDb.OleDbDataAdapter(strQuery, conn)

Dim ds As New System.Data.DataSet("CSV File")

adapter.Fill(ds)

Return ds.Tables(0)

End Using

End Function


Private Function UploadCSV() As Boolean

If Not Path.GetExtension(fileUploadCSV.PostedFile.FileName).ToLower.Equals(".csv") Then

lblMsg.Text = MessagesDAL.Instance.Get_MessageByID(Messages.MessageCodes.CSVExtensionRequired)

lblMsg.ForeColor = Drawing.Color.Red

Return False

End If

If Not fileUploadCSV.PostedFile Is Nothing And fileUploadCSV.PostedFile.ContentLength > 0 Then

Dim fn As String = Path.GetFileName(fileUploadCSV.PostedFile.FileName)

Dim SaveLocation As String = Path.Combine(Server.MapPath("CSVUploaded"), fn)

Try

fileUploadCSV.PostedFile.SaveAs(SaveLocation)

CreateSchemaFile(fn)

ViewState("SaveLocation") = SaveLocation

Catch Exc As Exception

lblMsg.Text = "Error: " & Exc.Message

lblMsg.ForeColor = Drawing.Color.Red

Return False

End Try

Else

lblMsg.Text = MessagesDAL.Instance.Get_MessageByID(Messages.MessageCodes.UploadFileRequired)

lblMsg.ForeColor = Drawing.Color.Red

Return False

End If

Return True

End Function


Private Function IsColumnHeaderExist(ByVal columnName As String) As Boolean

If ViewState("SaveLocation") Is Nothing Then

Return Nothing

End If

Dim SaveLocation As String = ViewState("SaveLocation").ToString()

Dim sr As StreamReader = New StreamReader(SaveLocation)

Dim headerline As String = sr.ReadLine()

sr.Close()

Dim values As String()

values = headerline.Split(",")

For i As Integer = 0 To values.Length - 1

If values(i).ToString.Equals(columnName) Then

Return True

End If

Next

Return False

End Function


Private Function CheckDuplicateRows() As Integer

If ViewState("SaveLocation") Is Nothing Then

Return Nothing

End If

Dim SaveLocation As String = ViewState("SaveLocation").ToString()

Dim FileContent As String() = File.ReadAllLines(SaveLocation)

Dim al As New ArrayList()

Dim lineNo As Integer = 1

'ignore the header row

For i As Integer = 1 To FileContent.Length - 1

lineNo += 1

If al.Contains(FileContent(i)) Then

Return lineNo

Else

al.Add(FileContent(i))

End If

Next

Return 1

End Function


Private Sub CreateSchemaFile(ByVal fileName As String)

'Create a schema.ini file to define data types for every csv file uploaded

'Otherwise the OleDbDataAdapter will convert e.g. 01/01/09 to datetime automatically rather than string

Dim strFilePath As String = Server.MapPath("CSVUploaded")

Using filestr As New FileStream(strFilePath & "\schema.ini", FileMode.Create, FileAccess.Write)

Using writer As New StreamWriter(filestr)

writer.WriteLine("[" & fileName & "]")

writer.WriteLine("ColNameHeader=True")

writer.WriteLine("Format=CSVDelimited")

writer.WriteLine("Col1=""Agent Registration Number"" Text")

writer.WriteLine("Col2=""Agent Family Name"" Text")

writer.WriteLine("Col3=""CPD Activity Item Number"" Long")

writer.WriteLine("Col4=""CPD Activity Start Date"" Text")

writer.WriteLine("Col5=""CPD Activity Start Time"" Text")

writer.WriteLine("Col6=""CPD Activity End Date"" Text")

writer.WriteLine("Col7=""CPD Activity End Time"" Text")

writer.WriteLine("Col8=Location Text")

writer.Close()

writer.Dispose()

End Using

filestr.Close()

filestr.Dispose()

End Using

End Sub

References:

Import CSV file to DataTable

Importing CSV file into Database with Schema.ini

Removing Duplicate Records from Dataset/DataTable

Insert CSV File to Array using C#

Saturday, March 14, 2009

Get the post back control

You can handle all the events in Page_PreRender like this:

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender

If GetPostBackControl(Me.Page) IsNot Nothing AndAlso GetPostBackControl(Me.Page).UniqueID = ddlFolder.UniqueID Then

'<<Handle Folder DropDownList Post Back>>

'...

ElseIf GetPostBackControl(Me.Page) IsNot Nothing AndAlso GetPostBackControl(Me.Page).UniqueID = btnEdit.UniqueID Then

'<<Handle Edit Button Post Back>>

'...

End If

End Sub

Public Shared Function GetPostBackControl(ByVal page As System.Web.UI.Page) As System.Web.UI.Control

Dim control As Control = Nothing

Dim ctrlname As String = page.Request.Params("__EVENTTARGET")

If ctrlname IsNot Nothing AndAlso ctrlname <> [String].Empty Then

control = page.FindControl(ctrlname)

Else

' if __EVENTTARGET is null, the control is a button type and we need to

' iterate over the form collection to find it

Dim ctrlStr As String = [String].Empty

Dim c As Control = Nothing

For Each ctl As String In page.Request.Form

' handle ImageButton controls ...

If ctl.EndsWith(".x") OrElse ctl.EndsWith(".y") Then

ctrlStr = ctl.Substring(0, ctl.Length - 2)

c = page.FindControl(ctrlStr)

Else

c = page.FindControl(ctl)

End If

If TypeOf c Is System.Web.UI.WebControls.Button OrElse TypeOf c Is System.Web.UI.WebControls.ImageButton Then

control = c

Exit For

End If

Next

End If

Return control

End Function


Notes extracted from Peter's article:

  • Most ASP.NET Controls are "Server Controls". This means that when an action occurs, the Page, which hosts all its controls inside the FORM, makes a regular old HTTP POST, with itself being the target URL. This is referred to as a "POSTBACK".
  • For non-button server controls, ASP.Net provides the javascript __doPostBack() function to be called by the onclick or onchange event of the control. This function records the name of the object that fired the event, as well as any additional event information, places it into the hidden form fields __EVENTTARGET and __EVENTARGUMENT, and then submits the form, initiating the PostBack.
  • Button types
    • Button type controls DO NOT run the "__doPostBack" script method
    • Button types don't populate __EVENTTARGET
    • All they actually do is SUBMIT the form.
  • On the server side, any controls that implement either the IPostBackDataHandler or IPostBackEventHandler interfaces will examine the PostBack data(__EVENTTARGET, __EVENTARGUMENT) and see if they have raised the client-side event. If so, the corresponding server-side event is raised and the event handler method is called.

The __doPostBack function:

<input type="hidden" name="__EVENTTARGET" id="__EVENTTARGET" value="" />

<input type="hidden" name="__EVENTARGUMENT" id="__EVENTARGUMENT" value="" />

function __doPostBack(eventTarget, eventArgument) {

if (!theForm.onsubmit (theForm.onsubmit() != false)) {

theForm.__EVENTTARGET.value = eventTarget;

theForm.__EVENTARGUMENT.value = eventArgument;

theForm.submit();

}

}


References:

ASP.NET: Which Control Posted Back?

Understanding the JavaScript __doPostBack Function

Wednesday, March 11, 2009

Form Based Authentication

By default, Moss uses windows authentication. But for external users who are not part of your domain, you need to use form based authentication.

Steps:

1. Enable anonymous access on the web application, that allows you to get in and add users using FBA.

2. Create a new zone for the web application by extending the current web application to another IIS web site. Each zone has a different URL that you can use to address the same web application. So you can use one URL for internal users, and a different URL for external users which can be authenticated through FBA.

3. Add a DNS Host A record for the sub-domain

4. Create a database to store membership

5. Edit the web.config

6. Add FBA tools to the web application, Microsoft does not provide the FBA tools.

7. Add FBA users to the web application using the FBA tool.

8. Optionally disable anonymous access. FBA usernames and passwords are entered in clear text, you will need to secure the FBA site with SSL.

Create Zones

Adding to DNS

  • Create Host A record in DNS to provide internal access
  • Create Host A record at ISP to provide external access

Creating the FBA Database

  • Use aspnet_regsql utility provided by .NET Framework
  • It creates the aspnetdb database by default, you can name it whatever you want
  • It registered that database as a membership provider on the server

Enabling Forms Authentication

  • In order to use that database, you need to edit web.config for the web application
  • Add connection settings are set in:
    • connectionStrings tag
    • membership tag
    • roleManager tag
  • You can also add e-mail information for ASP.NET (allows the FBA tools to send out the users password if they forget it)
    • system.net tag
    • mailSettings tag
    • smtp tag

FBA Tools

  • Not provided by Microsoft
  • You can create them yourself or obtain from third party vendors

Adding FBA Users

  • Add the users using the FBA tools
  • Set the user's permissions in SharePoint

Turning Off Anonymous Access

  • Finally, when you all done setting things up, you need to disable anonymous access in general.
  • Anonymous is required first to add first users, it is a chicken and egg issue.
  • You then use Central Admin > Application Management to disable anonymous access

Monday, March 9, 2009

SQL server open connections exist in a database

If you get a “timeout connection in the database because there are no connections available in the connection pool” error, the following queries will help you diagnose the problem:

  • To allow users to view current activity on the database:
    sp_who2
  • To give you the total number of connections per database on a database server:
    SELECT DB_NAME(dbid) as 'Database Name',
    COUNT(dbid) as 'Total Connections'
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid > 0
    GROUP BY dbid
  • To get the dbid from database name
    SELECT DB_ID('MyDBName') as [Database ID]
  • To give you the process Ids of existing connections in the database (not necessarily open but existing):
    SELECT spid
    FROM master.dbo.sysprocesses WITH (nolock)
    WHERE dbid = (SELECT DB_ID('MyDBName') as [Database ID])
  • To give you information about the actual process id (replace 1018 with the spid):
    dbcc inputbuffer (1018)
  • To kill a process
    kill 1018

Thanks Tracey.