Click here to Skip to main content
12,077,911 members (48,938 online)
Click here to Skip to main content
Articles » Web Development » ASP.NET » Data » Downloads

Stats

224.7K views
3.4K downloads
203 bookmarked
Posted

ObjectDataSource In Depth

, 22 Mar 2006 CPOL
An article to master the ObjectDataSource.
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
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manuel Abadia
Web Developer
Spain Spain
Manuel Abadia had his MS Degree in Computer Science (Univ. Murcia, Spain)

He is a Freelance Software Architect/Engineer and Trainer.

He sells his own components in his webpage (http://www.manuelabadia.com).

He was the Software Architect for the MoviTAP project that won the first prize in the Microsoft and Vodafone mobile web Services contest.

He has done some external work in companies like Namco America Inc. and Gaelco SA.

He has contributed to the MAME project (http://www.mamedev.com) for some years (and continues to do so eventually).

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.160212.1 | Last Updated 22 Mar 2006
Article Copyright 2006 by Manuel Abadia
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid