Thursday, 12 October 2006
Query Active Directory using Atlas (AJAX)
Last updated : 12th October 2006
Background
There is a lot of buzz around about AJAX and Microsoft's 'wrapper' technology referred to as Atlas. This is still in development but it's stable enough to use, certainly in my environment. This tutorial uses the CTP edition of the Ajax Library.
We will build a tool that allows users to find individuals on an Active Directory(AD) and then retrieve additional information from their AD Entry
Strategy
To make this as slick as possible we will Atlas enable the solution. What we want is for the user to start typing the individuals name and the application should then retrieve/filter and suggest valid entries as they type. When they locate the correct entry they will then click a button to retrieve the required information about the user back from Active Directory. This should update a section of the page without a full postback.
The first thing you need is to have the Atlas libraries installed. You can download the installation files from http://atlas.asp.net. After installation you will have a new project template as shown below. This adds the Microsoft.Web.Atlas.dll and relevant web.config entries that are necessary to 'bridge' out to the web service.
I've had a few emails requesting C# instead of VB.Net, so this tutorial is in C# but you can download a VB version as well. In order to achieve this AJAX functionality we will use 2 server controls from the Atlas library - UpdatePanel and the AutoCompleteExtender. See below.
Atlas Toolbar in VS 2005
Implementation
Lets have a look at what the final result will look like. I have kept the styling and error handling to a minimum for clarity.
To achieve this we require a single web page and a web service with a single method call.
Web Service
<%@ WebService Language="C#" Class="ADValidate" %>
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.DirectoryServices;
using System.Configuration;
using System.Collections.Generic;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class ADValidate : System.Web.Services.WebService {
[WebMethod]
public string[] GetUserNames(string prefixText, int count)
{
// Entry to LDAP objects e.g. LDAP://name.server.com/OU=UsersOU=Managed Objects,DC=server,DC=com
DirectoryEntry de = new DirectoryEntry(ConfigurationManager.AppSettings.Get("ADPath"));
de.Username = ConfigurationManager.AppSettings.Get("ADServiceAccount");
de.Password = ConfigurationManager.AppSettings.Get("ADServiceAccountPassword");
de.AuthenticationType = AuthenticationTypes.FastBind;
DirectorySearcher ds = new DirectorySearcher(de);
ds.ClientTimeout = TimeSpan.FromSeconds(30);
ds.PropertiesToLoad.Add("displayName");
ds.SizeLimit = 100;
SortOption srt;
srt = new SortOption("displayName", SortDirection.Ascending);
ds.Sort = srt;
ds.Filter = ("(displayName=" + prefixText.Trim() + "*)");
SearchResultCollection src;
src = ds.FindAll();
List<String> CandidateList = new List<string>();
foreach (SearchResult sr in src)
{
foreach (string Candidate in sr.Properties["displayName"])
{
CandidateList.Add(Candidate);
}
}
return CandidateList.ToArray();
}
}
The web service accepts 2 parameters (prefixText and count). The web service is responsible for returning an Array of strings back to the JavaScript calling function and this is used to populate the filtered list of matching users. Although the calling mechanism is JavaScript there is no client side coding required as the Atlas server controls take care of the necessary JavaScript that is emitted based on the web client (IE, Firefox etc).
Inside the web service we basically create a searcher object that will query the Active Directory using the System.DirectoryServices namespace (Remember to add it into your solution as a reference). Note the asterisk (*) at the end of the filter, this acts like a wild card. We also make sure that the list comes back sorted. We are only interested in returning the 'displayName' entry in the Active Directory structure so this is the only property we load prior to execting the FindAll() call.
After the call I am simply looping through the results and adding them to a generic collection of strings or simply returning an empty string array if no entries match.
UI - Web Page
<%@ Page Language="C#" %>
<%@ Import Namespace="System.DirectoryServices" %>
<%@ Import Namespace="System.Configuration" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<style type="text/css">
body {
font-family: Arial, Verdana;
font-size: 11pt;
font-color: #000000;
margin: 0;
padding: 0 10px 0 10px;
text-align: left
}
</style>
<atlas:ScriptManager ID="AtlasScriptCore" runat="server" EnablePartialRendering="true" />
</head>
<body>
<form id="Form1" runat="server">
<div id="content">
<h3>
Active Directory Searcher</h3>
<div id="searchbox">
Type Users Surname<br />
<asp:TextBox ID="txtFullName" runat="server">
</asp:TextBox>
<asp:Button ID="btnDetails" runat="server" Text="Get AD Details" OnClick="GetADDetails" />
<atlas:AutoCompleteExtender runat="server" ID="autoComplete1">
<atlas:AutoCompleteProperties
TargetControlID="txtFullName"
Enabled="True"
ServicePath="ADValidate.asmx"
ServiceMethod="GetUserNames"
MinimumPrefixLength="1" />
</atlas:AutoCompleteExtender>
</div><br />
<div id="Results">
<atlas:UpdatePanel ID="UpdatePanel1" runat="server" Mode="Conditional">
<ContentTemplate>
<asp:GridView ID="ADUserProperties" runat="server">
</asp:GridView>
<asp:Literal runat="server" ID="SysMessage" EnableViewState="false" ></asp:Literal>
</ContentTemplate>
<Triggers>
<atlas:ControlEventTrigger ControlID="btnDetails" EventName="Click" />
</Triggers>
</atlas:UpdatePanel>
</div>
</div>
</form>
</body>
</html>
<script runat="server">
protected void GetADDetails(Object sender , EventArgs e )
{
// Take the value from the input box and pull back a few AD details
DataTable UserProperties = null;
UserProperties = GetUserByDisplayName(txtFullName.Text);
// Display data if we have any or show warning
if (UserProperties != null)
{
ADUserProperties.DataSource = UserProperties;
ADUserProperties.DataBind();
}
else
{
// Show no records
SysMessage.Text = "<p style=\"color: red;\">Could not find any details for this user. <br />" +
"Please check that the users name is correct.</p>";
}
}
protected DataTable GetUserByDisplayName(String fullUserName)
{
DirectoryEntry de = new DirectoryEntry(ConfigurationManager.AppSettings.Get("ADPath"));
// Authentication details
de.Username = ConfigurationManager.AppSettings.Get("ADServiceAccount"); //DOMAIN\User
de.Password = ConfigurationManager.AppSettings.Get("ADServiceAccountPassword");
de.AuthenticationType = AuthenticationTypes.FastBind;
DirectorySearcher DirectorySearcher = new DirectorySearcher(de);
DirectorySearcher.ClientTimeout = TimeSpan.FromSeconds(30);
// load the properties we are interested in
DirectorySearcher.PropertiesToLoad.Add("cn");
DirectorySearcher.PropertiesToLoad.Add("sAMAccountName");
DirectorySearcher.PropertiesToLoad.Add("mail");
DirectorySearcher.PropertiesToLoad.Add("displayName");
DirectorySearcher.PropertiesToLoad.Add("mDBStorageQuota");
DirectorySearcher.PropertiesToLoad.Add("title");
DirectorySearcher.PropertiesToLoad.Add("physicalDeliveryOfficeName");
DirectorySearcher.PropertiesToLoad.Add("telephoneNumber");
// filter it on exact entry - NOTE no wild card
DirectorySearcher.Filter = "(displayName=" + fullUserName.Trim() + ")";
SearchResult result;
// There should only be one entry
result = DirectorySearcher.FindOne();
if (result != null)
{
// Create a table an populate it with properties to bind to gridview
DataTable myTable = new DataTable("ActiveDir");
myTable.Columns.Add(new DataColumn("Key", System.Type.GetType("System.String")));
myTable.Columns.Add(new DataColumn("Value", System.Type.GetType("System.String")));
DataRow myRow;
foreach (string propname in result.Properties.PropertyNames)
{
foreach (Object objValue in result.Properties[propname])
{
myRow = myTable.NewRow();
myRow[0] = propname;
myRow[1] = objValue.ToString();
myTable.Rows.Add(myRow);
}
}
return myTable;
}
else
{
return null;
}
}
</script>
Note the entries for the Atlas controls. I'm only setting properties, there is no need to write any Javascript as the Atlas framework is responsible for this. The UpdatePanel is updated when we click on the 'Get AD Details' button. You can see that I have set this in the <Triggers> section of the Update Panel. Also note that I set a property on the script manger to enable partial page rendering - EnablePartialRendering="true"
For the AutoCompleteExtender, I tell the control where to go for the data (web service and method name), how many characters need to be typed before making the web service call and which control on the page is looking for the data. This is all done in a declarative fashion.
When the user selects a entry form the dropdown list and clicks on the 'Get AD Details' button we then execute another search on the Active directory but this time we are searching for only one entry. We use the .FindOne() method to bring back the reequired information, hence the additional PropertiesToLoad entries. Once we get these back I am simply creating a DataTable to return back for binding to a GridView in the standard fashion. See below.
Summary
Hopefully this tutorial has shown how you might query an Active Directory structure and how easy it then is to add AJAX functionality with Atlas Server controls.
If you download the samples remember to change the AppSettings section in the web.config file to match entries in your environment.
Wednesday, 13 September 2006
SQL Transactions
Last updated : 13th September 2006
Background
In many applications there is a need to be able to insert data across multiple tables as an atomic unit of work. In this example I will cover a simple page in a shopping cart application that requires users to provide both a billing and a shipping address. This information is stored across two separate tables (User and Address) as a parent-child relationship. There would be little point in creating a new user in our database if no address details were added. The Billing dept would not know where to send the invoice and the Shipping department would not know where to deliver the goods.
Therefore we require that the new customer page will only succeed if:
- User details are inserted into the User table
- Billing address is inserted into the Address table with the new customer ID
- Shipping address is inserted into the Address table with the new customer ID
To achieve this we need to wrap all this work together as a transaction. If all parts succeed we then commit this to the database otherwise everything needs to be rolled back so that the database is in the same state that it was in prior to the insert.
Strategy
To implement this functionality as an ATOMIC transaction we will use the SQLTransaction class of the System.Data.SQLClient namespace. Just so you are aware, there are other ways to implement transactions, notably TSQL in stored procedures and the System.Transactions namespace that is in the new version of the DOT.NET framework.
I like System.Transactions coding but this does not work well with the SQL 2000 databases that I use. This is because it enlists the support of the MSDTC and this adds to the overhead. However it was designed to work well with SQL Server 2005 using promotable transactions.
Implementation
We will assume that a user has filled their shopping basket and the next stage for new users is to register themselves and their billing and shipping information. Again this is an all or nothing insert.
Step 1 - Architecture
We will create a User and Address class to hold the information that we collect from the web form. As we will be holding multiple addresses. We will use a Generic List collection to hold these.
User Class
Namespace Bracora.Articles.TransactionExample
Public Class User
Private _forename As String
Private _surname As String
Private _email As String
''' <summary>
''' Initializes a new instance of the User class.
''' </summary>
''' <param name="forename"></param>
''' <param name="surname"></param>
''' <param name="email"></param>
Public Sub New(ByVal forename As String, ByVal surname As String, ByVal email As String)
_forename = forename
_surname = surname
_email = email
End Sub
''' <summary>
''' Initializes a new instance of the User class.
''' </summary>
Public Sub New()
End Sub
Public Property Forename() As String
Get
Return _forename
End Get
Set(ByVal value As String)
If _forename = value Then
Return
End If
_forename = value
End Set
End Property
Public Property Surname() As String
Get
Return _surname
End Get
Set(ByVal value As String)
If _surname = value Then
Return
End If
_surname = value
End Set
End Property
Public Property Email() As String
Get
Return _email
End Get
Set(ByVal value As String)
If _email = value Then
Return
End If
_email = value
End Set
End Property
End Class
End Namespace
Address Class
Namespace Bracora.Articles.TransactionExample
Public Class Address
Public Const AT_BILL As String = "BILLADDRESS"
Public Const AT_SHIP As String = "SHIPADDRESS"
Private _userID As Integer
Private _address1 As String
Private _address2 As String
Private _address3 As String
Private _city As String
Private _postZipCode As String
Private _addressType As String
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
''' <param name="userID"></param>
''' <param name="address1"></param>
''' <param name="address2"></param>
''' <param name="address3"></param>
''' <param name="city"></param>
''' <param name="postZipCode"></param>
''' <param name="addressType"></param>
Public Sub New(ByVal userID As Integer, ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String, ByVal addressType As String)
_userID = userID
_address1 = address1
_address2 = address2
_address3 = address3
_city = city
_postZipCode = postZipCode
_addressType = AddressType
End Sub
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
''' <param name="address1"></param>
''' <param name="address2"></param>
''' <param name="address3"></param>
''' <param name="city"></param>
''' <param name="postZipCode"></param>
Public Sub New(ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String)
_address1 = address1
_address2 = address2
_address3 = address3
_city = city
_postZipCode = postZipCode
End Sub
''' <summary>
''' Initializes a new instance of the Address class.
''' </summary>
Public Sub New()
End Sub
Public Property userID() As Integer
Get
Return _userID
End Get
Set(ByVal value As Integer)
If _userID = Value Then
Return
End If
_userID = Value
End Set
End Property
Public Property Address1() As String
Get
Return _address1
End Get
Set(ByVal value As String)
If _address1 = value Then
Return
End If
_address1 = value
End Set
End Property
Public Property Address2() As String
Get
Return _address2
End Get
Set(ByVal value As String)
If _address2 = value Then
Return
End If
_address2 = value
End Set
End Property
Public Property Address3() As String
Get
Return _address3
End Get
Set(ByVal value As String)
If _address3 = value Then
Return
End If
_address3 = value
End Set
End Property
Public Property City() As String
Get
Return _city
End Get
Set(ByVal value As String)
If _city = value Then
Return
End If
_city = value
End Set
End Property
Public Property PostZipCode() As String
Get
Return _postZipCode
End Get
Set(ByVal value As String)
If _postZipCode = value Then
Return
End If
_postZipCode = value
End Set
End Property
Public Property AddressType() As String
Get
Return _addressType
End Get
Set(ByVal value As String)
If _addressType = value Then
Return
End If
_addressType = value
End Set
End Property
End Class
End Namespace
Step 2 - Data Access Code
There is a static class UserManager that we will use to perform the necessary database inserts. The methods in this class will work on the User and Address objects mentioned above.
In the UserManager class we have the shared methods to handle the database insert logic and the transactional part. We have a method called InsertUserAndAddresses that controls the SQL Transaction. This method takes a populated User object and a Generic List of Address objects.
For the InsertUser and InsertAddress functions to participate in our transaction we need to pass a reference to the SQLConnection and the SQLTransaction that we created in our controlling function InsertUserAndAddresses. These are then attached to the actual SQLCommand objects that perform the inserts.
We wrap this functionality in a try/catch block so that we can easily rollback the transaction if any errors occur. If all the inserts are successful we then commit the transaction and it is saved in the database.
Static Data Access Class
Imports Bracora.Articles
Imports System.Collections.Generic
Imports System.Data.SqlClient
Namespace Bracora.Articles.TransactionExample
Public NotInheritable Class UserManager
'static class
'not to be instantiated
Private Sub New()
End Sub
Public Shared Function NewConn() As SqlConnection
Return New SqlConnection(ConfigurationManager.ConnectionStrings("Articles").ConnectionString)
End Function
Public Shared Sub InsertUserAndAddresses(ByVal newUser As TransactionExample.User, _
ByVal newAddress As List(Of TransactionExample.Address))
Dim userID As Int32 = 0
Dim Tran As SqlTransaction
Using conn As SqlConnection = NewConn()
conn.Open()
Tran = conn.BeginTransaction
Try
' Try to add the user
userID = InsertUser(newUser, Tran, conn)
Dim AddressCount As Int32 = 0
For i As Int32 = 0 To newAddress.Count - 1
newAddress(i).userID = userID
InsertAddress(newAddress(i), Tran, conn)
Next
Tran.Commit()
Catch ex As Exception
Tran.Rollback()
' re-throw back to the ui
Throw ex
End Try
End Using
End Sub
Public Shared Function InsertUser(ByVal newUser As TransactionExample.User, _
ByVal sqlTran As SqlTransaction, _
ByVal sqlConn As SqlConnection) As Int32
Dim UserID As Object = Nothing
Dim Sql As String
Sql = "INSERT INTO [User] (" & _
"[Forename],[Surname],[Email]) " & _
"VALUES(@Forename,@Surname,@Email);SELECT SCOPE_IDENTITY();"
Using cmd As New SqlCommand
cmd.Connection = sqlConn
cmd.Transaction = sqlTran
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = Sql
cmd.Parameters.Add(New SqlParameter("Forename", newUser.Forename))
cmd.Parameters.Add(New SqlParameter("Surname", newUser.Surname))
cmd.Parameters.Add(New SqlParameter("Email", newUser.Email))
userID = cmd.ExecuteScalar
If userID IsNot Nothing Then
Return Convert.ToInt32(userID)
End If
End Using
End Function
Public Shared Sub InsertAddress(ByVal newAddress As TransactionExample.Address, _
ByVal sqlTran As SqlTransaction, _
ByVal sqlConn As SqlConnection)
Dim Sql As String
Sql = "INSERT INTO [Address] (" & _
"[UserID],[Address1],[Address2],[Address3],[City],[PostZipCode],[AddressType]) " & _
"VALUES(@UserID,@Address1,@Address2,@Address3,@City,@PostZipCode,@AddressType)"
Using cmd As New SqlCommand
cmd.Connection = sqlConn
cmd.Transaction = sqlTran
cmd.CommandType = Data.CommandType.Text
cmd.CommandText = Sql
cmd.Parameters.Add(New SqlParameter("UserID", newAddress.userID))
cmd.Parameters.Add(New SqlParameter("Address1", newAddress.Address1))
cmd.Parameters.Add(New SqlParameter("Address2", newAddress.Address2))
cmd.Parameters.Add(New SqlParameter("Address3", newAddress.Address3))
cmd.Parameters.Add(New SqlParameter("City", newAddress.City))
cmd.Parameters.Add(New SqlParameter("PostZipCode", newAddress.PostZipCode))
cmd.Parameters.Add(New SqlParameter("AddressType", newAddress.AddressType))
cmd.ExecuteNonQuery()
End Using
End Sub
End Class
End Namespace
Step 3 - User Interface
When the user populates the form fields and hits submit we create a User object and a List of Address Objects. We then call the transactional method InsertuserAndAddresses. If successful, we would move onto the next stage in the checkout process. Otherwise we display an error message back to the user.
Submit Code
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
' you would want to validate prior to inserting
' we assume all necessary fields are complete
Dim NewUser As New TransactionExample.User
NewUser.Forename = Me.txtForename.Text.Trim
NewUser.surname = Me.txtSurname.Text.Trim
NewUser.Email = Me.txtEmail.Text.Trim
Dim NewAddress As New Generic.List(Of TransactionExample.Address)
Dim tmpAddress As TransactionExample.Address
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
tmpAddress.City = Me.txtBillCity.Text.Trim
tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_BILL
NewAddress.Add(tmpAddress)
' check to see if we need to use the same address for shipping
If Me.chkUseBilling.Checked Then
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
tmpAddress.City = Me.txtBillCity.Text.Trim
tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
NewAddress.Add(tmpAddress)
Else
tmpAddress = New TransactionExample.Address
tmpAddress.Address1 = Me.txtShipAddress1.Text.Trim
tmpAddress.Address2 = Me.txtShipAddress2.Text.Trim
tmpAddress.Address3 = Me.txtShipAddress3.Text.Trim
tmpAddress.City = Me.txtShipCity.Text.Trim
tmpAddress.PostZipCode = Me.txtShipZip.Text.Trim
tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
NewAddress.Add(tmpAddress)
End If
Try
TransactionExample.UserManager.InsertUserAndAddresses(NewUser, NewAddress)
ClearFields(Page.Form)
Catch ex As Exception
' display errr in UI
Me.litError.Text = "<div class=""error"">There was a problem with the update. " _
& "If this persists then please contact the administrator.<br />" _
& ex.Message & "</div>"
End Try
End Sub
If you wish to test the rollback of the transaction simply change the SQL statement within the InsertAddress function. This will then fail and the successful User Insert will be rolled back.
Summary
There are many occasions where you will need to handle workflow as a transactional piece of work against a database. Hopefully this article has given you a feel for how you can perform such a task using the SQLTransaction object.
© Bracora 2013 . Powered by Bootstrap , Blogger templates and RWD Testing Tool