Connacht Business Objects

There are 2 fundamental persistence operations that can be performed on a Client business object:

Create a new persistent Client object.
Update an existing persistent Client object.

A static method on the Clients collection class is used to retrieve a persistent Client object from the database.

The C# example code below uses ADO.NET

Creating a new Client object

Creating a new persistent Client obect is a two stage process. First a new memory resident Client object must be create using the C# new operator. Blah, blah...


public void Insert(string login) 
{
	SqlConnection connection = null;
  	try
	{
		string connectionString = "Initial Catalog=OnlineAccounts;Data Source=.;UID=Mary;PWD=BRISBANE";
		connection = new SqlConnection(connectionString);

		string commandText = "client_insert";
		SqlCommand cmd = new SqlCommand(commandText, connection);
		cmd.CommandType = CommandType.StoredProcedure;
		
		SqlParameter pReturnValue = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
		pReturnValue.Direction = ParameterDirection.ReturnValue;
		SqlParameter pMessage = cmd.Parameters.Add("@pMessage", SqlDbType.VarChar, 255);
		pMessage.Direction = ParameterDirection.InputOutput;			

		SqlParameter pLogin = cmd.Parameters.Add("@pLogin", SqlDbType.VarChar, 16); 
				
		SqlParameter pClientID = cmd.Parameters.Add("@pClientID", SqlDbType.Int);
		pClientID.Direction = ParameterDirection.InputOutput;
		SqlParameter pCompanyID = cmd.Parameters.Add("@pCompanyID", SqlDbType.Int);
		SqlParameter pName = cmd.Parameters.Add("@pName", SqlDbType.VarChar, 50);
		SqlParameter pAddress1 = cmd.Parameters.Add("@pAddress1", SqlDbType.VarChar, 50);
		SqlParameter pAddress2 = cmd.Parameters.Add("@pAddress2", SqlDbType.VarChar, 50);
		SqlParameter pPostCode = cmd.Parameters.Add("@pPostCode", SqlDbType.VarChar, 10);
		SqlParameter pemail = cmd.Parameters.Add("@pemail", SqlDbType.VarChar, 50);
		SqlParameter pTelephone = cmd.Parameters.Add("@pTelephone", SqlDbType.VarChar, 20);
		
		pLogin.Value = login;
		pCompanyID.Value = this.CompanyID;
		pName.Value = this.Name;
		pAddress1.Value = this.Address1;
		pAddress2.Value = this.Address2;
		pPostCode.Value = this.PostCode;
		pemail.Value = this.email;
		pTelephone.Value =	
		this.Telephone;
		
		connection.Open();
		cmd.ExecuteNonQuery();
		Version = 1;

		int returnValue = (int)pReturnValue.Value;
		string message = "No error message available.";
		if(!IsDBNull(pMessage))
			message = (string)pMessage.Value;
		int ClientID = (int)pClientID.Value;

		connection.Close();	
		if(returnValue != 0)
			throw new ConnachtException(message, returnValue);
		}
		catch(SqlException sqlException)
	{
		connection.Close();	
		throw sqlException;
	}	
}

Parameters

Login - the callers logon.
CompanyID - a unique ID speciying which company "owns" the client record.
ClientID - A unique client ID generated by the database when the record is first inserted, currently an integer (32bits)
Version - The version number used to detect updates based on out-of-date information. The version number starts at 0 and is incremented every time the client record is updated.
Name - the client's name.
Address1 - the first line of the client's address.
Address2 - the second line of the client's address.
PostCode - the client's post code.
email - the client's email address. Optional (field can be null)
Telephone - the client's telephone number. Optional (field can be null)
Password - the client's password.

top

Getting a Client object


public class Clients
{
	static public Client Get(string login, int ID)
	{
		SqlConnection connection = null;
		Client client = new Client();
		try
		{
			string connectionString = "Initial Catalog=OnlineAccounts;Data Source=.;UID=Mary;PWD=BRISBANE";
			connection = new SqlConnection(connectionString);

			string commandText = "client_get";
			SqlCommand cmd = new SqlCommand(commandText, connection);
			cmd.CommandType = CommandType.StoredProcedure;

			SqlParameter pReturnValue = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
			pReturnValue.Direction = ParameterDirection.ReturnValue;
			SqlParameter pMessage = cmd.Parameters.Add("@pMessage", SqlDbType.VarChar, 255);
			pMessage.Direction = ParameterDirection.Output;			
			SqlParameter pLogin = cmd.Parameters.Add("@pLogin", SqlDbType.VarChar, 16); 
			SqlParameter pClientID = cmd.Parameters.Add("@pClientID", SqlDbType.Int);

			pMessage.Value = "";
			pLogin.Value = login;

			connection.Open();
			SqlDataReader reader = cmd.ExecuteReader();
			if(reader.Read()) 
			{
				client.UniqueID = reader.GetInt32(0);
				client.Version = reader.GetInt32(1);
				client.CompanyID = reader.GetInt32(2);
				client.Name = reader.GetString(3);
				client.Address1 = reader.GetString(4);
				client.Address2 = reader.GetString(5);
				client.PostCode = reader.GetString(6);
				client.email = reader.GetString(7);
				client.Telephone = reader.GetString(8);
			}
			reader.Close();

			int returnValue = (int)pReturnValue.Value;
			string message = "No error message available.";
			if(!IsDBNull(pMessage))
				message = (string)pMessage.Value;

			connection.Close();	
			if(returnValue != 0)
				throw new ConnachtException(message, returnValue);
		}
		catch(SqlException sqlException)
		{
			connection.Close();	
			throw sqlException;
		}			
		return client;
	}
	public static bool IsDBNull(SqlParameter param)
	{
		Type t = param.Value.GetType();
		return Type.GetTypeCode(t) == TypeCode.DBNull;
	}
}

Parameters

Login - the callers logon.
CompanyID - a unique company ID, currently an integer (32 bits).
ClientID - the client's name.

Returned Records

The following fields are returned:
ClientID, CompanyID, Version, Name, Address1, Address2, PostCode, email, Telephone.
Please consult table documentation for further information about these database fields.

top

Updating a Client object


public void Update(string login)
{
	SqlConnection connection = null;
	try
	{
		string connectionString = 
		"Initial Catalog=OnlineAccounts;Data Source=.;UID=Mary;PWD=BRISBANE";
		connection = new SqlConnection(connectionString);

		string commandText = "client_update";
		SqlCommand cmd = new SqlCommand(commandText, connection);
		cmd.CommandType = CommandType.StoredProcedure;

		SqlParameter pReturnValue = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
		pReturnValue.Direction = ParameterDirection.ReturnValue;
		SqlParameter pMessage = cmd.Parameters.Add("@pMessage", SqlDbType.VarChar, 255);
		pMessage.Direction = ParameterDirection.Output;			

		SqlParameter pLogin = cmd.Parameters.Add("@pLogin", SqlDbType.VarChar, 16); 

		SqlParameter pClientID = cmd.Parameters.Add("@pClientID", SqlDbType.Int);
		SqlParameter pVersion = cmd.Parameters.Add("@pVersion", SqlDbType.Int);
		SqlParameter pCompanyID = cmd.Parameters.Add("@pCompanyID", SqlDbType.Int);
		SqlParameter pName = cmd.Parameters.Add("@pName", SqlDbType.VarChar, 50);
		SqlParameter pAddress1 = cmd.Parameters.Add("@pAddress1", SqlDbType.VarChar, 50);
		SqlParameter pAddress2 = cmd.Parameters.Add("@pAddress2", SqlDbType.VarChar, 50);
		SqlParameter pPostCode = cmd.Parameters.Add("@pPostCode", SqlDbType.VarChar, 10);
		SqlParameter pemail = cmd.Parameters.Add("@pemail", SqlDbType.VarChar, 50);
		SqlParameter pTelephone = cmd.Parameters.Add("@pTelephone", SqlDbType.VarChar, 20);	

		pMessage.Value = "";
		pLogin.Value = login;
		pClientID.Value = this.UniqueID;
		pVersion.Value = this.Version;
		pCompanyID.Value = this.CompanyID;
		pName.Value = this.Name;
		pAddress1.Value = this.Address1;
		pAddress2.Value = this.Address2;
		pPostCode.Value = this.PostCode;
		pemail.Value = this.email;
		pTelephone.Value =	
		this.Telephone;
				
		connection.Open();
		cmd.ExecuteNonQuery();

		int returnValue = (int)pReturnValue.Value;
		string message = "No error message available.";
		if(!IsDBNull(pMessage))
			message = (string)pMessage.Value;

		connection.Close();	
		if(returnValue != 0)
			throw new ConnachtException(message, returnValue);
	}
	catch(SqlException sqlException)
	{
		connection.Close();	
		throw sqlException;
	}
}

Parameters

Message (Output) - a user friendly message generated by the stored procedure if something goes wrong.
Login - the callers logon.
CompanyID - the unique ID of the company associated with the caller.
ClientID - the unique ID of the client being updated.

Version - the expected version number of the client record. The version number is used to detect updates based on out-of-date information. If the version number on the client record stored in the database is not the same as that supplied to the stored procedure, then the record has been updated by someone else since the caller originally obtained a copy of the client record. If the the update was allowed to continue, the changes made by the other user would almost certainly be overwritten. To stop this, a version number mismatch result in an aborted update and a return code = 6.

Name - the client's new name.
Address1 - the new first line of the client's address.
Address2 - the new second line of the client's address.
PostCode - the client's new post code.
email - the client's new email address. Optional (field can be null)
Telephone - the client's new telephone number. Optional (field can be null)
Password - the client's new password.

top