public List <Product> GetAllRecords()
{
string connectionString = null;
//IDbConnection connection = null;
SqlConnection connection = null;
//IDbCommand command = null;
SqlCommand command = null;
//DataTable productTable = null;
List <Product> products = null;
SqlDataReader reader = null;
try
{
connectionString = DaoUtility.CreateConnectionString();
if (connectionString != null && connectionString != string.Empty)
{
connection = (SqlConnection)DaoUtility.CreateConnection(connectionString);
if (connection != null)
{
//preferrable
//string procedureName = DaoUtility.GetProcedure("GETPRODUCTS");
//command = (SqlCommand)DaoUtility.CreateCommand(
// connection,
// procedureName,
// CommandType.StoredProcedure
// );
command = (SqlCommand)DaoUtility.CreateCommand(
connection,
Procedures.GETPRODUCTS,
CommandType.StoredProcedure
);
if (command != null)
{
DaoUtility.OpenConnection(connection);
//productTable = ExecuteAndGetData(command);
reader = command.ExecuteReader();
if (reader.HasRows)
{
CreateListFromReader(reader, ref products);
}
reader.Close();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
DaoUtility.CloseConnection(connection);
}
return(products);
}
public bool Insert(Product data)
{
SqlConnection connection = null;
string connectionString = null;
SqlCommand command = null;
int result = 0;
try
{
connectionString = DaoUtility.CreateConnectionString();
if (connectionString != null && connectionString != string.Empty)
{
connection = (SqlConnection)DaoUtility.CreateConnection(connectionString);
if (connection != null)
{
string procedureName = DaoUtility.GetProcedure("ADDPRODUCT");
command = (SqlCommand)DaoUtility.CreateCommand(connection, procedureName, CommandType.StoredProcedure);
/*
* //string query = "insert into products(productname,productdescription,price) values(@pname,@pdesc,@price)";
* string query = $"insert into products(productname,productdescription,price) values({data.ProductName},{data.Description},{data.Price})";
* command = (SqlCommand)DaoUtility.CreateCommand(connection, query, CommandType.Text);
*/
//create parameters defined in the query or stored procedure
SqlParameter nameParameter = (SqlParameter)DaoUtility.CreateParameter("@pname", data.ProductName, SqlDbType.VarChar, ParameterDirection.Input);
SqlParameter descParameter = (SqlParameter)DaoUtility.CreateParameter("@pdesc", data.Description, SqlDbType.VarChar, ParameterDirection.Input);
SqlParameter priceParameter = (SqlParameter)DaoUtility.CreateParameter("@price", data.Price, SqlDbType.Decimal, ParameterDirection.Input);
//add them in the collection of parameters of command object
command.Parameters.Add(nameParameter);
command.Parameters.Add(descParameter);
command.Parameters.Add(priceParameter);
if (command != null)
{
DaoUtility.OpenConnection(connection);
result = command.ExecuteNonQuery();
}
}
}
return(result > 0);
}
catch (Exception ex)
{
throw ex;
}
finally
{
DaoUtility.CloseConnection(connection);
}
}