using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace CPRG254.Framework.Data
{
public class DBObject
{
public static IDataParameter CreateParameter
{
get
{
return new SqlParameter();
}
}
public static IDataReader Query(string commandText,
CommandType commandType,
IDataParameter[] parameters)
{
//connection string retrieved from config file
var connectionString =
ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;
SqlConnection connection = null;
SqlCommand command;
SqlDataReader reader = null;
try
{
//create connection
connection = new SqlConnection(connectionString);
//create the command and set properties
command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
//add any parameters to the command object
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
//open the connection
connection.Open();
//execute the reader and return the reader
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(Exception ex)
{
//log the error
if (reader != null) reader.Close();
if(connection != null && connection.State == ConnectionState.Open) connection.Close();
}
return reader;
}
public static bool NonQuery(string commandText,
CommandType commandType,
IDataParameter[] parameters)
{
//connection string retrieved from config file
var connectionString =
ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;
try
{
using (var connection = new SqlConnection(connectionString))
{
//instantiate the command
var command = new SqlCommand(commandText, connection);
command.CommandType = commandType;
//add any parameters to the command object
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
//open the connection
connection.Open();
//execute the command
command.ExecuteNonQuery();
return true;
} //closes the connection implicitly/automatically
}
catch (Exception ex)
{
//log the exception
return false;
}
}
}
}
----------------------------------------------------------------------
//business layer
using System;
//using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration; //used for ConfigurationManager class
using System.Data;
using CPRG254.Framework.Data;
namespace InventoryDomain
{
public class SupplierManager
{
public static bool Add(Supplier supplier)
{
//sql command text
var sql = "prSupplierInsert";
//create a SqlParameter object and and include it in an array
var par = DBObject.CreateParameter;
par.ParameterName = "@Name";
par.Value = supplier.Name;
par.DbType = DbType.String; //optional
var par2 = DBObject.CreateParameter;
par2.ParameterName = "@ID";
par2.DbType = DbType.Int32;
par2.Direction = ParameterDirection.Output;
var pars = new IDataParameter[] { par, par2 };
return DBObject.NonQuery(sql, CommandType.StoredProcedure, pars);
}
public static bool Update(Supplier supplier)
{
//sql command text
var sql = "UPDATE Supplier SET Name = @name WHERE ID = @id";
//create a SqlParameter object and and include it in an array
var par = DBObject.CreateParameter;
par.ParameterName = "@name";
par.Value = supplier.Name;
par.DbType = DbType.String; //optional
var par2 = DBObject.CreateParameter;
par2.ParameterName = "@id";
par2.DbType = DbType.Int32;
par2.Value = supplier.Id;
var pars = new IDataParameter[] { par, par2 };
return DBObject.NonQuery(sql, CommandType.Text, pars);
}
public static List<Supplier> GetAll()
{
var suppliers = new List<Supplier>();
Supplier supplier; //used in the while loop below
try
{
//execute the command
using (var reader = DBObject.Query("SELECT * FROM Supplier", CommandType.Text, null))
{
var idOrdinal = reader.GetOrdinal("ID");
var nameOrdinal = reader.GetOrdinal("Name");
while (reader.Read())
{
//instantiate and set the state of a Supplier
supplier = new Supplier();
supplier.Id = reader.GetInt32(idOrdinal);
supplier.Name = reader.GetString(nameOrdinal);
//add the supplier to the suppliers collection
suppliers.Add(supplier);
}
}//closes the data reader implicitly - which also closes the connection because of CommandBehavior enum set in ExecuteReader in DBObject
}
catch (Exception ex)
{
//log the exception
}
return suppliers;
}
No comments:
Post a Comment