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 'Products'.

/// </summary>

public abstract class Products

{

SqlConnection _cn;

public Products( string connectionString )

{

_cn = new SqlConnection( connectionString );

}

public Products( SqlConnection cn )

{

_cn = cn;

}

#region Select One Methods

public DataTable SelectOne_DataTable( SqlInt32 ProductID )

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Products");

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// Set the connection string

cmd.Connection = _cn;

try

{

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

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.[Products_SelectByID]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

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

}

finally

{

// Close connection.

_cn.Close();

cmd.Dispose();

adapter.Dispose();

}

public SqlDataReader SelectOne_DataReader( SqlInt32 ProductID )

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// Set the connection string

cmd.Connection = _cn;

try

{

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

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

// Open connection if needed.

if ( _cn.State == ConnectionState.Closed )

{

_cn.Open();

}

dr = cmd.ExecuteReader();

return dr;

}

catch(Exception ex)

{

// Throw the error

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

}

finally

{

// Clean up.

cmd.Dispose();

}

#endregion

#region Select All Methods

public DataTable SelectAll_DataTable()

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Products");

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// 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.[Products_SelectAll]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

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

}

finally

{

// Close connection.

_cn.Close();

cmd.Dispose();

adapter.Dispose();

}

public SqlDataReader SelectAll_DataReader()

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// Set the connection string

cmd.Connection = _cn;

try

{

// Open connection if needed.

if ( _cn.State == ConnectionState.Closed )

{

_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 Products::SelectAll_DataReader", ex);

}

finally

{

// Clean up

cmd.Dispose();

}

#endregion

#region Select Where Methods

public DataTable SelectWhere_DataTable( string whereClause )

{

SqlCommand cmd = new SqlCommand();

DataTable dt = new DataTable("Products");

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

// 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.[Products_SelectWhere]' reported the ErrorCode: " + errorCode);

}

return dt;

}

catch(Exception ex)

{

// Throw the error

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

}

finally

{

// Close connection.

_cn.Close();

cmd.Dispose();

adapter.Dispose();

}

public SqlDataReader SelectWhere_DataReader( string whereClause )

{

SqlCommand cmd = new SqlCommand();

SqlDataReader dr;

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// 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 if needed.

if ( _cn.State == ConnectionState.Closed )

{

_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 Products::SelectWhere_DataReader.", ex);

}

finally

{

// Clean up.

cmd.Dispose();

}

#endregion

#region Insert Methods

public bool Insert( out SqlInt32 ProductIDOutput, SqlString ProductName, SqlInt32 SupplierID, SqlInt32 CategoryID, SqlString QuantityPerUnit, SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlInt16 UnitsOnOrder, SqlInt16 ReorderLevel, SqlBoolean Discontinued, SqlDateTime LastUpdate )

{

SqlCommand cmd = new SqlCommand();

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

cmd.CommandType = CommandType.StoredProcedure;

int errorCode = 0;

// Set the connection

cmd.Connection = _cn;

try

{

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

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

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

cmd.Parameters.Add(new SqlParameter("@QuantityPerUnit", SqlDbType.NVarChar , 40 ,ParameterDirection.Input, true,0 ,0 , "", DataRowVersion.Proposed, QuantityPerUnit ));

cmd.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.Money , 8 ,ParameterDirection.Input, true,19 ,4 , "", DataRowVersion.Proposed, UnitPrice ));

cmd.Parameters.Add(new SqlParameter("@UnitsInStock", SqlDbType.SmallInt , 2 ,ParameterDirection.Input, true,5 ,0 , "", DataRowVersion.Proposed, UnitsInStock ));

cmd.Parameters.Add(new SqlParameter("@UnitsOnOrder", SqlDbType.SmallInt , 2 ,ParameterDirection.Input, true,5 ,0 , "", DataRowVersion.Proposed, UnitsOnOrder ));

cmd.Parameters.Add(new SqlParameter("@ReorderLevel", SqlDbType.SmallInt , 2 ,ParameterDirection.Input, true,5 ,0 , "", DataRowVersion.Proposed, ReorderLevel ));

cmd.Parameters.Add(new SqlParameter("@Discontinued", SqlDbType.Bit , 1 ,ParameterDirection.Input, false,1 ,0 , "", DataRowVersion.Proposed, Discontinued ));

cmd.Parameters.Add(new SqlParameter("@LastUpdate", SqlDbType.DateTime , 8 ,ParameterDirection.Input, true,23 ,3 , "", DataRowVersion.Proposed, LastUpdate ));

SqlParameter prmProductID;

prmProductID = new SqlParameter("@ProductID", SqlDbType.Int );

prmProductID.Direction = ParameterDirection.Output;

cmd.Parameters.Add(prmProductID);

// 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();

ProductIDOutput = (Int32)cmd.Parameters["@ProductID"].Value;

// Get the error code

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

if( errorCode != 0)

{

// Throw error.

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

}

return true;

}

catch(Exception ex)

{

// Throw the error

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

}

finally

{

// Close connection.

_cn.Close();

cmd.Dispose();

}

#endregion

#region Update Methods

public bool Update( SqlInt32 ProductID, SqlString ProductName, SqlInt32 SupplierID, SqlInt32 CategoryID, SqlString QuantityPerUnit, SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlInt16 UnitsOnOrder, SqlInt16 ReorderLevel, SqlBoolean Discontinued, SqlDateTime LastUpdate )

{

SqlCommand cmd = new SqlCommand();

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