using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections;
using
YellowbridgeSoftwareInc.Info;
namespace
YellowbridgeSoftwareInc.PersistBase
{
/// <summary>
///
Purpose: Data Access class for the table 'Products'.
/// </summary>
public class
Products
{
SqlConnection _cn;
public Products( string connectionString )
{
_cn = new
SqlConnection( connectionString );
}
public Products(
SqlConnection cn )
{
_cn = cn;
}
public ProductsInfo
SelectOne( SqlInt32 ProductID )
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[Products_SelectByID]";
cmd.CommandType = CommandType.StoredProcedure;
int errorCode = 0;
// Create the info class
ProductsInfo data = new
ProductsInfo();
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);
if(dt.Rows.Count
> 0)
{
data.ProductID = dt.Rows[0]["ProductID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["ProductID"];
data.ProductName = dt.Rows[0]["ProductName"] ==
System.DBNull.Value ? SqlString.Null : (string)dt.Rows[0]["ProductName"];
data.SupplierID = dt.Rows[0]["SupplierID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["SupplierID"];
data.CategoryID = dt.Rows[0]["CategoryID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["CategoryID"];
data.QuantityPerUnit = dt.Rows[0]["QuantityPerUnit"] ==
System.DBNull.Value ? SqlString.Null : (string)dt.Rows[0]["QuantityPerUnit"];
data.UnitPrice = dt.Rows[0]["UnitPrice"] ==
System.DBNull.Value ? SqlMoney.Null :
(Decimal)dt.Rows[0]["UnitPrice"];
data.UnitsInStock = dt.Rows[0]["UnitsInStock"] ==
System.DBNull.Value ? SqlInt16.Null :
(Int16)dt.Rows[0]["UnitsInStock"];
data.UnitsOnOrder = dt.Rows[0]["UnitsOnOrder"] ==
System.DBNull.Value ? SqlInt16.Null :
(Int16)dt.Rows[0]["UnitsOnOrder"];
data.ReorderLevel = dt.Rows[0]["ReorderLevel"] ==
System.DBNull.Value ? SqlInt16.Null :
(Int16)dt.Rows[0]["ReorderLevel"];
data.Discontinued = dt.Rows[0]["Discontinued"] ==
System.DBNull.Value ? SqlBoolean.Null : (bool)dt.Rows[0]["Discontinued"];
}
// 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 the data
return data;
}
catch(Exception ex)
{
// Throw the error
throw new Exception("An exception has been raised in
YellowbridgeSoftwareInc.PersistBase Products::SelectOne.", ex);
}
finally
{
// Close
connection.
_cn.Close();
cmd.Dispose();
adapter.Dispose();
}
}
public void SelectOne( ref
ProductsInfo data, SqlInt32 ProductID )
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[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);
if(dt.Rows.Count
> 0)
{
data.ProductID = dt.Rows[0]["ProductID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["ProductID"];
data.ProductName = dt.Rows[0]["ProductName"] ==
System.DBNull.Value ? SqlString.Null : (string)dt.Rows[0]["ProductName"];
data.SupplierID = dt.Rows[0]["SupplierID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["SupplierID"];
data.CategoryID = dt.Rows[0]["CategoryID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dt.Rows[0]["CategoryID"];
data.QuantityPerUnit = dt.Rows[0]["QuantityPerUnit"] ==
System.DBNull.Value ? SqlString.Null : (string)dt.Rows[0]["QuantityPerUnit"];
data.UnitPrice = dt.Rows[0]["UnitPrice"] ==
System.DBNull.Value ? SqlMoney.Null :
(Decimal)dt.Rows[0]["UnitPrice"];
data.UnitsInStock = dt.Rows[0]["UnitsInStock"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dt.Rows[0]["UnitsInStock"];
data.UnitsOnOrder = dt.Rows[0]["UnitsOnOrder"] ==
System.DBNull.Value ? SqlInt16.Null :
(Int16)dt.Rows[0]["UnitsOnOrder"];
data.ReorderLevel = dt.Rows[0]["ReorderLevel"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dt.Rows[0]["ReorderLevel"];
data.Discontinued = dt.Rows[0]["Discontinued"] ==
System.DBNull.Value ? SqlBoolean.Null : (bool)dt.Rows[0]["Discontinued"];
}
// 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);
}
}
catch(Exception ex)
{
// Throw the error
throw new Exception("An exception has been raised in
YellowbridgeSoftwareInc.PersistBase Products::SelectOne.", ex);
}
finally
{
// Close
connection.
_cn.Close();
cmd.Dispose();
adapter.Dispose();
}
}
public DataTable SelectOne_DataTable(
SqlInt32 ProductID )
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[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.[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();
}
}
public
ProductsInfoCollection SelectAll()
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[Products_SelectAll]";
cmd.CommandType = CommandType.StoredProcedure;
int errorCode = 0;
//Create a collection
object
ProductsInfoCollection dataCollection = new ProductsInfoCollection();
SqlDataAdapter adapter = new
SqlDataAdapter(cmd);
// Set the connection
string
cmd.Connection = _cn;
try
{
// Open connection.
_cn.Open();
cmd.Parameters.Add(new
SqlParameter("@ErrorCode", SqlDbType.Int, 4,
ParameterDirection.Output, false, 10, 0,
"", DataRowVersion.Proposed, errorCode));
// Execute query.
adapter.Fill(dt);
foreach (
DataRow dr in dt.Rows )
{
ProductsInfo data = new ProductsInfo();
data.ProductID = dr["ProductID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dr["ProductID"];
data.ProductName = dr["ProductName"] ==
System.DBNull.Value ? SqlString.Null : (string)dr["ProductName"];
data.SupplierID = dr["SupplierID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dr["SupplierID"];
data.CategoryID = dr["CategoryID"] == System.DBNull.Value
? SqlInt32.Null : (int)dr["CategoryID"];
data.QuantityPerUnit = dr["QuantityPerUnit"] ==
System.DBNull.Value ? SqlString.Null : (string)dr["QuantityPerUnit"];
data.UnitPrice = dr["UnitPrice"] ==
System.DBNull.Value ? SqlMoney.Null : (Decimal)dr["UnitPrice"];
data.UnitsInStock = dr["UnitsInStock"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dr["UnitsInStock"];
data.UnitsOnOrder = dr["UnitsOnOrder"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dr["UnitsOnOrder"];
data.ReorderLevel = dr["ReorderLevel"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dr["ReorderLevel"];
data.Discontinued = dr["Discontinued"] ==
System.DBNull.Value ? SqlBoolean.Null : (bool)dr["Discontinued"];
// Return the
data
dataCollection.Add( data );
}
// 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
dataCollection;
}
catch(Exception ex)
{
// Throw the error
throw new Exception("An exception has been raised in
YellowbridgeSoftwareInc.PersistBase Products::SelectAll.", ex);
}
finally
{
// Close
connection.
_cn.Close();
cmd.Dispose();
adapter.Dispose();
}
}
public void SelectAll( ref
ProductsInfoCollection dataCollection )
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[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();
cmd.Parameters.Add(new
SqlParameter("@ErrorCode", SqlDbType.Int, 4,
ParameterDirection.Output, false, 10, 0,
"", DataRowVersion.Proposed, errorCode));
// Execute query.
adapter.Fill(dt);
foreach (
DataRow dr in dt.Rows )
{
ProductsInfo data = new ProductsInfo();
data.ProductID = dr["ProductID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dr["ProductID"];
data.ProductName = dr["ProductName"] ==
System.DBNull.Value ? SqlString.Null : (string)dr["ProductName"];
data.SupplierID = dr["SupplierID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dr["SupplierID"];
data.CategoryID = dr["CategoryID"] ==
System.DBNull.Value ? SqlInt32.Null : (int)dr["CategoryID"];
data.QuantityPerUnit = dr["QuantityPerUnit"] ==
System.DBNull.Value ? SqlString.Null : (string)dr["QuantityPerUnit"];
data.UnitPrice = dr["UnitPrice"] ==
System.DBNull.Value ? SqlMoney.Null : (Decimal)dr["UnitPrice"];
data.UnitsInStock = dr["UnitsInStock"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dr["UnitsInStock"];
data.UnitsOnOrder = dr["UnitsOnOrder"] ==
System.DBNull.Value ? SqlInt16.Null : (Int16)dr["UnitsOnOrder"];
data.ReorderLevel = dr["ReorderLevel"] == System.DBNull.Value
? SqlInt16.Null : (Int16)dr["ReorderLevel"];
data.Discontinued = dr["Discontinued"] ==
System.DBNull.Value ? SqlBoolean.Null : (bool)dr["Discontinued"];
dataCollection.Add( data );
}
// 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);
}
}
catch(Exception ex)
{
// Throw the error
throw new Exception("An exception has been raised in
YellowbridgeSoftwareInc.PersistBase Products::SelectAll", ex);
}
finally
{
// Close
connection.
_cn.Close();
cmd.Dispose();
adapter.Dispose();
}
}
public DataTable
SelectAll_DataTable()
{
SqlCommand cmd = new
SqlCommand();
DataTable dt = new
DataTable("Products");
cmd.CommandText =
"dbo.[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();