using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public class CategoryDetails
{
public CategoryDetails() { }
public CategoryDetails(int id, DateTime addedDate, string addedBy, string title, string description)
{
this.ID = id;
this.AddedDate = addedDate;
this.AddedBy = addedBy;
this.Title = title;
this.Description = description;
}
private int _id = 0;
public int ID
{
get { return _id;}
set { _id = value;}
}
private DateTime _addedDate = DateTime.Now;
public DateTime AddedDate
{
get { return _addedDate; }
set { _addedDate = value; }
}
private string _addedBy = "";
public string AddedBy
{
get { return _addedBy; }
set { _addedBy = value; }
}
private string _title = "";
public string Title
{
get { return _title; }
set { _title = value; }
}
private string _description = "";
public string Description
{
get { return _description; }
set { _description = value; }
}
}
Class สำหรับการเรียกใช้งาน Code : class Categorys_DataProvided (C#)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
public abstract class Categorys_DataProvided :
{
public abstract List<CategoryDetails> GetCategories();
public abstract CategoryDetails GetCategoryByID(int categoryID);
public abstract bool DeleteCategory(int categoryID);
public abstract bool UpdateCategory(CategoryDetails category);
public abstract int InsertCategory(CategoryDetails category);
สำหรับรับค่าข้อมูลเพียง record เดียว
protected virtual CategoryDetails GetCategoryFromReader(IDataReader reader)
{
return new CategoryDetails(
(int)reader["CategoryID"],
(DateTime)reader["AddedDate"],
reader["AddedBy"].ToString(),
reader["Title"].ToString(),
reader["Description"].ToString());
}
/// <summary>
/// Returns a collection of CategoryDetails objects with the data read from the input DataReader
/// </summary>
protected virtual List<CategoryDetails> GetCategoryCollectionFromReader(IDataReader reader) <- สำหรับรับค่าหลายๆ records
{
List<CategoryDetails> categories = new List<CategoryDetails>();
while (reader.Read())
categories.Add(GetCategoryFromReader(reader));
return categories;
}
}
ส่วนติดต่อกับฐานข้อมูล ก็จะเอา function, procedure ที่ประกาศไว้ใน class ข้างบนมาเขียนขยายลงไปว่าเอาอะไรมาจากไหน Code : class SQLDataCategorys (C#)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Caching;
public class SQLDataCategorys : Categorys_DataProvided
{
public override List<CategoryDetails> GetCategories()
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("my_GetCategories", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
return GetCategoryCollectionFromReader(ExecuteReader(cmd));
}
}
public override CategoryDetails GetCategoryByID(int categoryID)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("my_GetCategoryByID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
cn.Open();
IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
if (reader.Read())
return GetCategoryFromReader(reader);
else
return null;
}
}
}
ส่วนนี้จะเป็น Store Procedure ที่ใช้กับ class ข้างบนครับ ลองเอาไปดูเป็นแนวทางครับ
** Store Procedure นี้ใช้กับ SQLServer2005 Express ครับ
Code
USE [MyDBSample] ---> StoreProc 1 : my_GetCategorys
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[my_GetCategories]
AS
SET NOCOUNT ON
SELECT CategoryID, AddedDate, AddedBy, Title, Description
FROM dbo.MyCategories
ORDER BY AddedBy DESC, Title ASC
<-------------------------------------------------------------------------------------------->
USE [MyDBSample] ---> StoreProc 2 : my_GetCategoryByID
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[my_GetCategoryByID]
(
@CategoryID int
)
AS
SET NOCOUNT ON
SELECT CategoryID, AddedDate, AddedBy, Title, Description
FROM MyCategories
WHERE CategoryID = @CategoryID
Dim objConn As New MySqlConnection
Dim objCmd As New MySqlCommand
Dim strConnString, strSQL As String
strConnString = "Server=localhost;User Id=root; Password=root; Database=database; Pooling=false"
objConn.ConnectionString = strConnString
objConn.Open()
Load balance : Server 03
