using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using System.Collections;

namespace YellowbridgeSoftwareInc.PersistBase

{

/// <summary>

/// Purpose: Data Access class for the table 'Categories'.

/// </summary>

public class Categories

{

#region Select One Methods

public static DataTable SelectOne_DataTable( string ConnectionString, SqlInt32 CategoryID )

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Categories");

cmd.CommandText = "dbo.[yb_Categories_SelectByID]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

cmd.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int , 4 ,ParameterDirection.Input, false,10 ,0 , "", DataRowVersion.Proposed, CategoryID ));

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

// Open connection.

cn.Open();

// Execute query.

adapter.Fill(dt);

// Get the error code

errorCode = (Int32)cmd.Parameters["@ErrorCode"].Value;

if( errorCode != 0)

{

// Throw error.

throw new Exception("Stored Procedure 'dbo.[Categories_SelectByID]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectOne_DataTable.", ex);

}

finally

{

// Close connection.

cn.Close();

cmd.Dispose();

adapter.Dispose();

}

}

public static SqlDataReader SelectOne_DataReader( string ConnectionString, SqlInt32 CategoryID )

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

cmd.CommandText = "dbo.[yb_Categories_SelectByID]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

cmd.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int , 4 ,ParameterDirection.Input, false,10 ,0 , "", DataRowVersion.Proposed, CategoryID ));

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

cn.Open();

dr = cmd.ExecuteReader();

return dr;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectOne_DataReader.", ex);

}

finally

{

// Clean up.

cmd.Dispose();

}

}

#endregion

#region Select All Methods

public static DataTable SelectAll_DataTable( string ConnectionString )

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Categories");

cmd.CommandText = "dbo.[yb_Categories_SelectAll]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

// Open connection.

cn.Open();

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

// Execute query.

adapter.Fill(dt);

// Get the error code

errorCode = (Int32)cmd.Parameters["@ErrorCode"].Value;

if( errorCode != 0)

{

// Throw error.

throw new Exception("Stored Procedure 'dbo.[Categories_SelectAll]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectAll_DataTable", ex);

}

finally

{

// Close connection.

cn.Close();

cmd.Dispose();

adapter.Dispose();

}

}

public static SqlDataReader SelectAll_DataReader( string ConnectionString )

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

cmd.CommandText = "dbo.[yb_Categories_SelectAll]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// Create a connection

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

cn.Open();

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

dr = cmd.ExecuteReader();

return dr;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectAll_DataReader", ex);

}

finally

{

// Clean up

cmd.Dispose();

}

}

#endregion

#region Select Where Methods

public static DataTable SelectWhere_DataTable( string ConnectionString, string whereClause )

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Categories");

cmd.CommandText = "dbo.[yb_Categories_SelectWhere]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

// Add the parameter

cmd.Parameters.Add(new SqlParameter("@WhereClause", SqlDbType.VarChar , 500 ,ParameterDirection.Input, false ,0 ,0 , "", DataRowVersion.Proposed, whereClause.ToString() ));

// Open connection.

cn.Open();

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

// Execute query.

adapter.Fill(dt);

// Get the error code

errorCode = (Int32)cmd.Parameters["@ErrorCode"].Value;

if( errorCode != 0)

{

// Throw error.

throw new Exception("Stored Procedure 'dbo.[Categories_SelectWhere]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectWhere_DataTable.", ex);

}

finally

{

// Close connection.

cn.Close();

cmd.Dispose();

adapter.Dispose();

}

}

public static SqlDataReader SelectWhere_DataReader( string ConnectionString, string whereClause )

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

cmd.CommandText = "dbo.[yb_Categories_SelectWhere]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection string

cmd.Connection = cn;

try

{

// Add the parameter

cmd.Parameters.Add(new SqlParameter("@WhereClause", SqlDbType.VarChar , 500 ,ParameterDirection.Input, false ,0 ,0 , "", DataRowVersion.Proposed, whereClause.ToString() ));

cn.Open();

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

dr = cmd.ExecuteReader();

return dr;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::SelectWhere_DataReader.", ex);

}

finally

{

// Clean up.

cmd.Dispose();

}

}

#endregion

#region Insert Methods

public static bool Insert( string ConnectionString, out SqlInt32 CategoryIDOutput, SqlString CategoryName, SqlString Description, SqlBinary Picture )

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "dbo.[yb_Categories_Insert]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlConnection cn = new SqlConnection( ConnectionString );

// Set the connection

cmd.Connection = cn;

try

{

// Get the length of the Picture column

int PictureLength =0;

if( !( Picture.IsNull ) )

{

PictureLength = Picture.Length;

}

// Get the length of the Description column

int DescriptionLength =0;

if( !( Description.IsNull ) )

{

DescriptionLength = Description.Value.Length;

}

cmd.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar , 30 ,ParameterDirection.Input, false,0 ,0 , "", DataRowVersion.Proposed, CategoryName ));

cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.NText , DescriptionLength ,ParameterDirection.Input, true,0 ,0 , "", DataRowVersion.Proposed, Description ));

cmd.Parameters.Add(new SqlParameter("@Picture", SqlDbType.Image , PictureLength ,ParameterDirection.Input, true,0 ,0 , "", DataRowVersion.Proposed, Picture ));

SqlParameter prmCategoryID;

prmCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int );

prmCategoryID.Direction = ParameterDirection.Output;

cmd.Parameters.Add(prmCategoryID);

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

// Open connection.

cn.Open();

// Execute query.

cmd.ExecuteNonQuery();

CategoryIDOutput = (Int32)cmd.Parameters["@CategoryID"].Value;

// Get the error code

errorCode = (Int32)cmd.Parameters["@ErrorCode"].Value;

if( errorCode != 0)

{

// Throw error.

throw new Exception("Stored Procedure 'dbo.[Categories_Insert]' reported the ErrorCode: " + errorCode);

}

return true;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::Insert.", ex);

}

finally

{

// Close connection.

cn.Close();

cmd.Dispose();

}

}

public static bool Insert( SqlTransaction transaction, out SqlInt32 CategoryIDOutput, SqlString CategoryName, SqlString Description, SqlBinary Picture )

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "dbo.[yb_Categories_Insert]";

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// Set the connection

cmd.Connection = transaction.Connection;

cmd.Transaction = transaction;

try

{

// Get the length of the Picture column

int PictureLength =0;

if( !( Picture.IsNull ) )

{

PictureLength = Picture.Length;

}

// Get the length of the Description column

int DescriptionLength =0;

if( !( Description.IsNull ) )

{

DescriptionLength = Description.Value.Length;

}

cmd.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar , 30 ,ParameterDirection.Input, false,0 ,0 , "", DataRowVersion.Proposed, CategoryName ));

cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.NText , DescriptionLength ,ParameterDirection.Input, true,0 ,0 , "", DataRowVersion.Proposed, Description ));

cmd.Parameters.Add(new SqlParameter("@Picture", SqlDbType.Image , PictureLength ,ParameterDirection.Input, true,0 ,0 , "", DataRowVersion.Proposed, Picture ));

SqlParameter prmCategoryID;

prmCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int );

prmCategoryID.Direction = ParameterDirection.Output;

cmd.Parameters.Add(prmCategoryID);

// Add paramter to capture the error code

cmd.Parameters.Add(new SqlParameter("@ErrorCode", SqlDbType.Int, 4, ParameterDirection.Output, false, 10, 0, "", DataRowVersion.Proposed, errorCode));

// Execute query.

cmd.ExecuteNonQuery();

CategoryIDOutput = (Int32)cmd.Parameters["@CategoryID"].Value;

// Get the error code

errorCode = (Int32)cmd.Parameters["@ErrorCode"].Value;

if( errorCode != 0)

{

// Throw error.

throw new Exception("Stored Procedure 'dbo.[Categories_Insert]' reported the ErrorCode: " + errorCode);

}

return true;

}

catch(Exception ex)

{

// Throw the error

throw new Exception("An exception has been raised in YellowbridgeSoftwareInc.PersistBase Categories::Insert.", ex);

}

finally

{

// Clean up'

cmd.Dispose();

}

}

#endregion

#region Update Methods