Xây dựng cơ sở dữ liệu cho một website phần 1

Loại: Tạo database

Các bài cần theo dõi của bài viết: Xây dựng cơ sở dữ liệu cho một website

Phần 1: Tạo database

Phần 2: Viết Trigger

Phần 3: Viết store procedure truy vấn, thêm, sửa, xóa một chuyên mục

Phần 4: Viết store procedure hiển thị menu đa cấp

Phần 5: Viết store Procedure thêm, sửa, xóa một bài viết

Phần 6: Viết store procedure hiển thị danh sách bài viết

Phần 7: Viết store procedure tìm kiếm bài viết

Phần 8: Viết store procedure top 10 các bài viết mới và cũ

Khi bắt đầu xây dựng một website. Công việc đầu tiên cần phải làm là phân tích, thiết kế cơ sở dữ liệu. Mình sẽ viết hướng dẫn xây dựng cơ sở dữ liệu và code từng bước để có thể xây dựng 1 website hoàn chỉnh

Chúng ta hãy bắt đầu với 2 bảng cơ bản của 1 website. TB_Categoty, TB_News

Bạn hãy thiết kế theo Diagram như sau:

-- Ngoài các trường như trong Diagram có thể có thêm các trường khác tùy theo yêu cầu của website

 1. Category

CateID: Mã chuyên mục - sẽ được sinh tự động khi tạo mới
ParentID: int Mã chuyên mục cha.
CateName: Tên chuyên mục. VD: Tin tức, Tin thể thao …
Decen: phân cấp của chuyên mục (thể hiện menu)
Depth: độ sâu của chuyên mục (Bắt đầu từ 0)
Link: Liên kết thực
Picture: Hình ảnh đại diện cho chuyên mục
CssClass: Định dạng hiển thị
Active: Trạng thái.

  • Trường Decen và Depth cần được sinh tự động bằng các viết các TRIGGER. Mình sẽ giới thiệu ở bài sau
  • Trường CssClass cũng sẽ được sinh tự động khi thêm mới hoặc cập nhật nhưng sẽ được thực hiện trong Procedure spTB_Category_Edit sẽ được giới thiệu ở bài sau

Vì bảng TB_Category cũng là bảng thể hiện menu đa cấp được mô tả như sau (Minh họa):

 

-- Create by  webmaster@hmweb.com.vn
 CREATE TABLE [dbo].[TB_Category](
      [CateID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
      [ParentID] [int] NULL,
      [CateName] [nvarchar](250) NOT NULL,
      [Decen] [int] NULL,
      [Depth] [varchar](150) NULL,
      [Link] [nvarchar](250) NULL,
      [Picture] [varbinary](250) NULL,
      [Target] [nvarchar](10) NULL
            CONSTRAINT [DF_TB_Category_Targer] 
            DEFAULT (N'_self'),
      [CssClass] [nvarchar](50) NULL,
      [Active] [int] NOT NULL
            CONSTRAINT [DF_TB_Category_Flag] 
            DEFAULT (1),
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[TB_Category] 

      WITH NOCHECK ADD 

      CONSTRAINT [FK_TB_Category_TB_Category] FOREIGN KEY([ParentID])

REFERENCES [dbo].[TB_Category] ([CateID])

GO

ALTER TABLE [dbo].[TB_Category]

      CHECK CONSTRAINT [FK_TB_Category_TB_Category]

GO

ALTER TABLE [dbo].[TB_Category] 

      WITH NOCHECK ADD 

      CONSTRAINT [CK_TB_Category] CHECK  (([CateID] <> [ParentID]))

GO

ALTER TABLE [dbo].[TB_Category] CHECK CONSTRAINT [CK_TB_Category]

2. TB_News

idNews: Mã bài viết - Sẽ được sinh tự động khi tạo mới
CateID: Mã chuyên mục
UserName: Tên đăng nhập (Người viết bài)
Title: Tiêu đề bài viết
Description: Tóm tắt nội dung bài viết
Content: Nội dung bài viết
Author: Tác giả vài viết
Picture:  Ảnh đại diện cho bài viết
CreateDate: Thời gian viết bài
PublishDate: Thời gian đăng bài
Status: Trạng thái bài viết
Numberreads: Số lần xem bài viết
Link: Url của bài viết 

-- Create by webmaster@hmweb.com.vn

 CREATE TABLE [dbo].[TB_News](

      [idNews] [int] IDENTITY(1,1) PRIMARY KEY  NOT NULL,

      [CateID] [int] NOT NULL,

      [UserName] [nvarchar](50) NOT NULL,

      [Title] [nvarchar](500) NOT NULL,

      [Description] [ntext] NULL,

      [Content] [ntext] NULL,

      [Author] [nvarchar](150) NULL,

      [Picture] [nvarchar](255) NULL,

      [CreateDate] [datetime] NOT NULL,

      [PublishDate] [datetime] NULL,

      [Status] [int] NULL,

      [Numberreads] [int] NULL,

      [Link] [nvarchar](150) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



GO

ALTER TABLE [dbo].[TB_News]  WITH NOCHECK ADD  CONSTRAINT [FK_TB_News_TB_Category] FOREIGN KEY([CateID])

REFERENCES [dbo].[TB_Category] ([CateID])

GO

ALTER TABLE [dbo].[TB_News] CHECK CONSTRAINT [FK_TB_News_TB_Category]

 

Phân trang trong SQL

Xin giới thiệu với các bạn Procedure phân trang dữ liệu trong SQL server
Giả sử ta có 1 bảng trong cơ sở dữ liệu chỉ gốm 2 trường là CateID và CateName. sau đây là Store phân trang khi cần truy vấn dữ liệu có phân trang

CREATE PROCEDURE [dbo].[spPhanTrang_Table] (
    @PageNumber int,
    @PageSize int
)

AS
    DECLARE @Start int, @End int
    BEGIN TRANSACTION GetDataSet
    SET @Start = (((@PageNumber - 1) * @PageSize) + 1)
    IF @@ERROR <> 0
        GOTO ErrorHandler
    SET @End = (@Start + @PageSize - 1)
    IF @@ERROR <> 0
        GOTO ErrorHandler
    CREATE TABLE #TemporaryTable (
        Row int IDENTITY(1,1) PRIMARY KEY,
        CateID int, CateName nvarchar(100)
    )
    IF @@ERROR <> 0
            GOTO ErrorHandler
    INSERT INTO #TemporaryTable
        SELECT CateID, CateName FROM [TB_Category] 
    IF @@ERROR <> 0
        GOTO ErrorHandler
    SELECT CateID, CateName  
        FROM #TemporaryTable
        WHERE (Row >= @Start) AND (Row <= @End)
    IF @@ERROR <> 0
        GOTO ErrorHandler
    DROP TABLE #TemporaryTable
    COMMIT TRANSACTION GetDataSet
    RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @@ERROR
Để sử dụng Store trên Bạn dùng lệnh:
EXEC spPhanTrang_Table (1,20) -- Nếu bạn muốn hiển thị dữ liệu trang 1 và 20 bản ghi trên 1 trang
Chúc các bạn thành công

Chuyển dữ liệu dạng text (Char or nvarchar) sang datetime

Bạn muốn chuyển đổi dữ liệu từ text (Char or nvarchar) sang datetime. Bạn có thể xem hàm sau đây. Tôi viết hàm chuyển dạng text (dd/MM/yyyy) còn các định dạng khác bạn có thể Alter hàm để dùng cho đúng mục đích.

 

--Input: char/varchar
--Output: datetime

CREATE FUNCTION dbo.ChuyenTextSangDateTime
(@mNgay nvarchar(10))  
RETURNS datetime
AS  

BEGIN 
    DECLARE @mDate   datetime
    if (len(@mNgay)>=8)--đủ ngày tháng năm
    Set @mDate=  cast(substring(@mNgay,
    CHARINDEX('/',@mNgay,0)+1,
    CHARINDEX('/',@mNgay, CHARINDEX('/',@mNgay,0)+1)
    -CHARINDEX('/',@mNgay,0)-1 )+'/'+
    substring( @mNgay,0,CHARINDEX('/',@mNgay,0))+'/'+
    substring(@mNgay, 
    CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1)+1 , 
    len(@mNgay)-CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1))
    as datetime)
    else
    if (len(@mNgay)>4 AND len(@mNgay)<8)--có tháng năm
    Set @mDate=cast(substring(@mNgay,0,CHARINDEX('/',@mNgay,0))+'/01/'+
        substring(@mNgay, CHARINDEX('/',@mNgay,0)+1, 
        len(@mNgay)-CHARINDEX('/',@mNgay,0))
               as datetime)
    else-- có năm
        Set @mDate= cast( '01/01/'+ @mNgay  as datetime)

    return @mDate
END

Bạn có thể chạy thử hàm trên như sau:

SELECT dbo.ChuyenTextSangDateTime ('22/08/2009')

Chúc các bạn thành công!

 

Chuyển dữ liệu dạng text (Char or nvarchar) sang datetime

Bạn muốn chuyển đổi dữ liệu từ text (Char or nvarchar) sang datetime. Bạn có thể xem hàm sau đây. Tôi viết hàm chuyển dạng text (dd/MM/yyyy) còn các định dạng khác bạn có thể Alter hàm để dùng cho đúng mục đích.

 

--Input: char/varchar
--Output: datetime

CREATE FUNCTION dbo.ChuyenTextSangDateTime
(@mNgay nvarchar(10))  
RETURNS datetime
AS  

BEGIN 
    DECLARE @mDate   datetime
    if (len(@mNgay)>=8)--đủ ngày tháng năm
    Set @mDate=  cast(substring(@mNgay,
    CHARINDEX('/',@mNgay,0)+1,
    CHARINDEX('/',@mNgay, CHARINDEX('/',@mNgay,0)+1)
    -CHARINDEX('/',@mNgay,0)-1 )+'/'+
    substring( @mNgay,0,CHARINDEX('/',@mNgay,0))+'/'+
    substring(@mNgay, 
    CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1)+1 , 
    len(@mNgay)-CHARINDEX('/',@mNgay,CHARINDEX('/',@mNgay,0)+1))
    as datetime)
    else
    if (len(@mNgay)>4 AND len(@mNgay)<8)--có tháng năm
    Set @mDate=cast(substring(@mNgay,0,CHARINDEX('/',@mNgay,0))+'/01/'+
        substring(@mNgay, CHARINDEX('/',@mNgay,0)+1, 
        len(@mNgay)-CHARINDEX('/',@mNgay,0))
               as datetime)
    else-- có năm
        Set @mDate= cast( '01/01/'+ @mNgay  as datetime)

    return @mDate
END

Bạn có thể chạy thử hàm trên như sau:

SELECT dbo.ChuyenTextSangDateTime ('22/08/2009')

Chúc các bạn thành công!

 

Chuyển đổi xâu ký tự từ TCVN3 sang Unicode C#

Trong nhiều trường hợp cần phải chuyển đổi mã từ TCVN 3 sang Unicode. Bài này giới thiệu một thuật toán đơn giản cho phép chuyển đổi một xâu ký tự từ TCVN 3 sang Unicode.  Ý tưởng của thuật toán là xây dựng hai mảng chứa các ký tự tiếng Việt có dấu cho mã TCVN 3 và Unicode. Để không phải tìm kiếm mỗi khi gặp ký tự có dấu, cần xây dựng một mảng trung gian để chuyển đổi, điều này làm giảm đáng kể thời gian chuyển đổi.

using System;
using System.Collections.Generic;
using System.Text;

class Converter {
    private static char[] tcvnchars = {
        ‘µ’, ‘¸’, ‘¶’, ‘·’, ‘¹’, 
        ‘¨’, ‘»’, ‘¾’, ‘¼’, ‘½’, ‘Æ’, 
        ‘©’, ‘Ç’, ‘Ê’, ‘È’, ‘É’, ‘Ë’, 
        ‘®’, ‘Ì’, ‘Ð’, ‘Î’, ‘Ï’, ‘Ñ’, 
        ‘ª’, ‘Ò’, ‘Õ’, ‘Ó’, ‘Ô’, ‘Ö’, 
        ‘×’, ‘Ý’, ‘Ø’, ‘Ü’, ‘Þ’, 
        ‘ß’, ‘ã’, ‘á’, ‘â’, ‘ä’, 
        ‘«’, ‘å’, ‘è’, ‘æ’, ‘ç’, ‘é’, 
        ‘¬’, ‘ê’, ‘í’, ‘ë’, ‘ì’, ‘î’, 
        ‘ï’, ‘ó’, ‘ñ’, ‘ò’, ‘ô’, 
        ‘­’, ‘õ’, ‘ø’, ‘ö’, ‘÷’, ‘ù’, 
        ‘ú’, ‘ý’, ‘û’, ‘ü’, ‘þ’, 
        ‘¡’, ‘¢’, ‘§’, ‘£’, ‘¤’, ‘¥’, ‘¦’
    };

    private static char[] unichars = {
        ‘à’, ‘á’, ‘ả’, ‘ã’, ‘ạ’, 
        ‘ă’, ‘ằ’, ‘ắ’, ‘ẳ’, ‘ẵ’, ‘ặ’, 
        ‘â’, ‘ầ’, ‘ấ’, ‘ẩ’, ‘ẫ’, ‘ậ’, 
        ‘đ’, ‘è’, ‘é’, ‘ẻ’, ‘ẽ’, ‘ẹ’, 
        ‘ê’, ‘ề’, ‘ế’, ‘ể’, ‘ễ’, ‘ệ’, 
        ‘ì’, ‘í’, ‘ỉ’, ‘ĩ’, ‘ị’, 
        ‘ò’, ‘ó’, ‘ỏ’, ‘õ’, ‘ọ’, 
        ‘ô’, ‘ồ’, ‘ố’, ‘ổ’, ‘ỗ’, ‘ộ’, 
        ‘ơ’, ‘ờ’, ‘ớ’, ‘ở’, ‘ỡ’, ‘ợ’, 
        ‘ù’, ‘ú’, ‘ủ’, ‘ũ’, ‘ụ’, 
        ‘ư’, ‘ừ’, ‘ứ’, ‘ử’, ‘ữ’, ‘ự’, 
        ‘ỳ’, ‘ý’, ‘ỷ’, ‘ỹ’, ‘ỵ’, 
        ‘Ă’, ‘Â’, ‘Đ’, ‘Ê’, ‘Ô’, ‘Ơ’, ‘Ư’
    };

    private static char[] convertTable;

    static Converter() {
        convertTable = new char[256];
        for (int i = 0; i < 256; i++)
            convertTable[i] = (char)i;
        for (int i = 0; i < tcvnchars.Length; i++)
            convertTable[tcvnchars[i]] = unichars[i];
    }

    public static string TCVN3ToUnicode(string value) {
        char[] chars = value.ToCharArray();
        for (int i = 0; i < chars.Length; i++)
            if (chars[i] < (char)256)
                chars[i] = convertTable[chars[i]];
        return new string(chars);
    }
}