using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;
[DataObject]
public class ProductsDAL
{
#region Select and Select Count
protected int _count = -1;
[DataObjectMethod(DataObjectMethodType.Select)]
public List<Product> LoadAll(int startIndex, int maxRows, string sortedBy)
{
List<Product> products = new List<Product>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
string commandText = @"
-- create a temp table for paging
CREATE TABLE #PageIndexForTable
(
IndexId int IDENTITY (0, 1) NOT NULL,
id int
)
-- insert into our temp table
INSERT INTO #PageIndexForTable
(
[id]
)
SELECT
[id]
FROM Products";
if (sortedBy != "") {
commandText += " ORDER BY " + sortedBy;
}
commandText += @"
SET @totalRecords = @@ROWCOUNT
-- get the requested registers
SELECT
src.[id],
src.[name],
src.[description],
src.[price]
FROM Products src, #PageIndexForTable p
WHERE
src.[id] = p.[id] AND
p.IndexId >= @StartIndex AND p.IndexId < (@startIndex + @maxRows)";
if (sortedBy != "") {
commandText += " ORDER BY " + sortedBy;
}
SqlCommand command = new SqlCommand(commandText, conn);
command.Parameters.Add(new SqlParameter("@startIndex", startIndex));
command.Parameters.Add(new SqlParameter("@maxRows", maxRows));
command.Parameters.Add(new SqlParameter("@totalRecords", SqlDbType.Int));
command.Parameters["@totalRecords"].Direction = ParameterDirection.Output;
conn.Open();
SqlDataReader dr = command.ExecuteReader();
while (dr.Read()) {
Product prod = new Product();
prod.Id = (int)dr["id"];
prod.Name = (string)dr["name"];
prod.Description = (string)dr["description"];
prod.Price = (decimal)dr["price"];
products.Add(prod);
}
dr.Close();
conn.Close();
_count = (int)command.Parameters["@totalRecords"].Value;
return products;
}
public int CountAll()
{
return _count;
}
#endregion
#region Insert using DataObjectTypeName
[DataObjectMethod(DataObjectMethodType.Insert)]
public static int Add(Product prod)
{
prod.Id = -1;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("INSERT INTO Products (name, description, price) VALUES (@name, @description, @price); set @generatedId = SCOPE_IDENTITY()", conn);
command.Parameters.Add(new SqlParameter("@name", prod.Name));
command.Parameters.Add(new SqlParameter("@description", prod.Description));
command.Parameters.Add(new SqlParameter("@price", prod.Price));
command.Parameters.Add(new SqlParameter("@generatedId", SqlDbType.Int));
command.Parameters["@generatedId"].Direction = ParameterDirection.Output;
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
if (affectedRows > 0) {
prod.Id = (int)command.Parameters["@generatedId"].Value;
}
return prod.Id;
}
#endregion
#region Edit and Update using DataObjectTypeName (No optimistic concurrency)
[DataObjectMethod(DataObjectMethodType.Update)]
public static int Edit(Product prod)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE Products SET name=@name, description=@description, price=@price WHERE id=@id", conn);
command.Parameters.Add(new SqlParameter("@name", prod.Name));
command.Parameters.Add(new SqlParameter("@description", prod.Description));
command.Parameters.Add(new SqlParameter("@price", prod.Price));
command.Parameters.Add(new SqlParameter("@id", prod.Id));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static int Delete(Product prod)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("DELETE Products WHERE id=@id", conn);
command.Parameters.Add(new SqlParameter("@id", prod.Id));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
#endregion
#region Edit and Update using DataObjectTypeName (With optimistic concurrency)
[DataObjectMethod(DataObjectMethodType.Update)]
public static int Edit(Product prod, Product old_prod)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE Products SET name=@name, description=@description, price=@price WHERE id=@old_id AND name=@old_name AND description=@old_description AND price=@old_price", conn);
command.Parameters.Add(new SqlParameter("@name", prod.Name));
command.Parameters.Add(new SqlParameter("@description", prod.Description));
command.Parameters.Add(new SqlParameter("@price", prod.Price));
command.Parameters.Add(new SqlParameter("@old_id", old_prod.Id));
command.Parameters.Add(new SqlParameter("@old_name", old_prod.Name));
command.Parameters.Add(new SqlParameter("@old_description", old_prod.Description));
command.Parameters.Add(new SqlParameter("@old_price", old_prod.Price));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static int DeleteOC(Product prod)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("DELETE Products WHERE id=@old_id AND name=@old_name AND description=@old_description AND price=@old_price", conn);
command.Parameters.Add(new SqlParameter("@old_id", prod.Id));
command.Parameters.Add(new SqlParameter("@old_name", prod.Name));
command.Parameters.Add(new SqlParameter("@old_description", prod.Description));
command.Parameters.Add(new SqlParameter("@old_price", prod.Price));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
#endregion
#region Insert using simple types
[DataObjectMethod(DataObjectMethodType.Insert)]
public static int Add(string name, string description, decimal price, out int generatedId)
{
generatedId = -1;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("INSERT INTO Products (name, description, price) VALUES (@name, @description, @price); set @generatedId = SCOPE_IDENTITY()", conn);
command.Parameters.Add(new SqlParameter("@name", name));
command.Parameters.Add(new SqlParameter("@description", description));
command.Parameters.Add(new SqlParameter("@price", price));
command.Parameters.Add(new SqlParameter("@generatedId", SqlDbType.Int));
command.Parameters["@generatedId"].Direction = ParameterDirection.Output;
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
if (affectedRows > 0) {
generatedId = (int)command.Parameters["@generatedId"].Value;
}
return affectedRows;
}
#endregion
#region Edit and Update using simple types (No optimistic concurrency)
[DataObjectMethod(DataObjectMethodType.Update)]
public static int Edit(string name, string description, decimal price, int old_id)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE Products SET name=@name, description=@description, price=@price WHERE id=@old_id", conn);
command.Parameters.Add(new SqlParameter("@name", name));
command.Parameters.Add(new SqlParameter("@description", description));
command.Parameters.Add(new SqlParameter("@price", price));
command.Parameters.Add(new SqlParameter("@old_id", old_id));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static int Delete(int old_id)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("DELETE Products WHERE id=@old_id", conn);
command.Parameters.Add(new SqlParameter("@old_id", old_id));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
#endregion
#region Edit and Update using simple types (With optimistic concurrency)
[DataObjectMethod(DataObjectMethodType.Update)]
public static int Edit(string name, string description, decimal price, int old_id, string old_name, string old_description, decimal old_price)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE Products SET name=@name, description=@description, price=@price WHERE id=@old_id AND name=@old_name AND description=@old_description AND price=@old_price", conn);
command.Parameters.Add(new SqlParameter("@name", name));
command.Parameters.Add(new SqlParameter("@description", description));
command.Parameters.Add(new SqlParameter("@price", price));
command.Parameters.Add(new SqlParameter("@old_id", old_id));
command.Parameters.Add(new SqlParameter("@old_name", old_name));
command.Parameters.Add(new SqlParameter("@old_description", old_description));
command.Parameters.Add(new SqlParameter("@old_price", old_price));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
[DataObjectMethod(DataObjectMethodType.Delete)]
public static int Delete(int old_id, string old_name, string old_description, decimal old_price)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("DELETE Products WHERE id=@old_id AND name=@old_name AND description=@old_description AND price=@old_price", conn);
command.Parameters.Add(new SqlParameter("@old_id", old_id));
command.Parameters.Add(new SqlParameter("@old_name", old_name));
command.Parameters.Add(new SqlParameter("@old_description", old_description));
command.Parameters.Add(new SqlParameter("@old_price", old_price));
conn.Open();
int affectedRows = command.ExecuteNonQuery();
conn.Close();
return affectedRows;
}
#endregion
}