ObjectDataSource In Depth

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
}