/// <summary>
/// Creates new office record in database.
/// </summary>
/// <param name="details">Office details.</param>
/// <returns>ID of new record.</returns>
public override int CreateOffice( OfficeDetails details )
{
int id = -1;
using( SqlConnection connection = new SqlConnection( this.ConnectionString ) )
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText =
string.Format(
@"INSERT INTO {0} (OfficeName, StatusesServiceURL, StatusesServiceUserName, StatusesServicePassword, MeteoInformer) VALUES (@OfficeName, @StatusesServiceURL, @StatusesServiceUserName, @StatusesServicePassword, @MeteoInformer) SELECT @@IDENTITY",
DBOfficesTableName );
command.Parameters.Add( "@OfficeName", SqlDbType.NVarChar ).Value =
details.OfficeName;
command.Parameters.Add( "@StatusesServiceURL", SqlDbType.NVarChar ).Value =
details.StatusesServiceURL;
command.Parameters.Add( "@StatusesServiceUserName", SqlDbType.NVarChar ).Value =
details.StatusesServiceUserName;
command.Parameters.Add( "@StatusesServicePassword", SqlDbType.NVarChar ).Value =
details.StatusesServicePassword;
command.Parameters.Add( "@MeteoInformer", SqlDbType.NVarChar ).Value =
details.MeteoInformer;
try
{
id = Convert.ToInt32( ExecuteScalar( command ) );
transaction.Commit();
details.ID = id;
m_Cache[ id ] = details;
}
catch
{ transaction.Rollback(); }
}
return id;
}
/// <summary>
/// Returns all offices details.
/// </summary>
/// <returns>Array of all offices details.</returns>
public override OfficeDetails[] GetAllOffices()
{
if( m_Cache.Count == 0 )
return new OfficeDetails[ 0 ];
OfficeDetails[] offices = new OfficeDetails[ m_Cache.Values.Count ];
m_Cache.Values.CopyTo( offices, 0 );
return offices;
}
/// <summary>
/// Returns office details from data reader.
/// </summary>
/// <param name="reader">Data reader.</param>
/// <returns>Office details from data reader.</returns>
protected virtual OfficeDetails GetOfficeDetailsFromReader( IDataReader reader )
{
OfficeDetails details = new OfficeDetails();
details.ID = (int) reader[ "ID" ];
details.OfficeName = (string) reader[ "OfficeName" ];
if( reader[ "StatusesServiceURL" ] == DBNull.Value )
details.StatusesServiceURL = null;
else
details.StatusesServiceURL = (string) reader[ "StatusesServiceURL" ];
if( reader[ "StatusesServiceUserName" ] == DBNull.Value )
details.StatusesServiceUserName = null;
else
details.StatusesServiceUserName = (string) reader[ "StatusesServiceUserName" ];
if( reader[ "StatusesServicePassword" ] == DBNull.Value )
details.StatusesServicePassword = null;
else
details.StatusesServicePassword = (string) reader[ "StatusesServicePassword" ];
if( reader[ "MeteoInformer" ] == DBNull.Value )
details.MeteoInformer = null;
else
details.MeteoInformer = (string) reader[ "MeteoInformer" ];
return details;
}
/// <summary>
/// Updates office information in database.
/// </summary>
/// <param name="details">Office details.</param>
/// <returns>True if record was successfully updated; false, otherwise.</returns>
public override bool UpdateOffice( OfficeDetails details )
{
using( SqlConnection connection = new SqlConnection( this.ConnectionString ) )
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText =
string.Format(
"UPDATE {0} SET OfficeName=@OfficeName, StatusesServiceURL=@StatusesServiceURL, StatusesServiceUserName=@StatusesServiceUserName, StatusesServicePassword=@StatusesServicePassword, MeteoInformer=@MeteoInformer WHERE ID=@ID",
DBOfficesTableName );
command.Parameters.Add( "@ID", SqlDbType.Int ).Value = details.ID;
command.Parameters.Add( "@OfficeName", SqlDbType.NVarChar ).Value =
details.OfficeName;
command.Parameters.Add( "@StatusesServiceURL", SqlDbType.NVarChar ).Value =
details.StatusesServiceURL;
command.Parameters.Add( "@StatusesServiceUserName", SqlDbType.NVarChar ).Value =
details.StatusesServiceUserName;
command.Parameters.Add( "@StatusesServicePassword", SqlDbType.NVarChar ).Value =
details.StatusesServicePassword;
command.Parameters.Add( "@MeteoInformer", SqlDbType.NVarChar ).Value =
details.MeteoInformer;
try
{
bool result = ( ExecuteNonQuery( command ) == 1 );
if( result )
{
transaction.Commit();
m_Cache[ details.ID ] = details;
}
else
transaction.Rollback();
return result;
}
catch
{
transaction.Rollback();
return false;
}
}
}
