Marten’s blog

This is really common task in any database project. You always need to get initial data into database. Maybe most common file format is CSV.

We have CSV file in drive C: with name import.csv with following content. The location of the file is C:\import.csv.

id,login,name,surname
1,user1,James,White
2,user2,Johny,Walker
3,user3,Jim,Beam

We will create table with these four columns.

CREATE TABLE CSVImport (
  ID INT,
  Login VARCHAR(20),
  FirstName VARCHAR(40),
  LastName VARCHAR(40),
  BirthDate SMALLDATETIME
);

Now we have everything we need to import the file into database. We will use BULK INSERT to put the data into table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVImport
FROM 'c:\import.csv'
WITH
(
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)
GO

Parameter FIRSTROW will tell BULK LOAD that we don’t want to import first line (= 1) because it contains headers.

If you will have table what have less columns than the csv file these columns will be added after text in the last column. If this column will reach maximum length, this line will not be inserted. With MAXERRORS you can specify the maximum number of error lines what are allowed before whole INSERT will be considered as failed.

Possible problems

Permissions

• User which runs bulk insert must have BULKINSERT permissions.
• SQL Sever must have access rights to the file.

Database MSSQL, MSSQL 2005, MSSQL 2008

In the first part of this article I wrote about tree traversal database structure. Now I want to show you how to prepare all stored procedures and triggers to be able to work with it effectively. From the first part we have already database structure and we filled the table with data. Now we need to have these data always with correct informations.

What can happen

There are many scenarios what can happen and you need to recreate lft and rtg in the tree. For inserting new category, deleting old one and move category under another parent, we can use one procedure:

ALTER PROCEDURE dbo.RecreateCategoryTree
  @id INT = NULL,
  @cnt INT = 0
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @actId INT,
    @actLft INT,
    @actRtg INT;
  DECLARE categories_cursor CURSOR LOCAL
  FOR
    SELECT ID, lft, rtg
    FROM dbo.Categories
    WHERE (ParentID = @id) OR (@id IS NULL AND ParentID IS NULL);
  OPEN categories_cursor
  FETCH NEXT FROM categories_cursor
  INTO @actId, @actLft, @actRtg

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @cnt = @cnt + 1;

    UPDATE dbo.Categories SET lft = @cnt WHERE ID = @actID;

    EXEC @cnt = [dbo].[RecreateCategoryTree] @actId, @cnt;
    SET @cnt = @cnt + 1;

    UPDATE dbo.Categories SET rtg = @cnt WHERE ID = @actID;

    FETCH NEXT FROM categories_cursor
    INTO @actId, @actLft, @actRtg
  END
  CLOSE categories_cursor
  DEALLOCATE categories_cursor

  RETURN @cnt;
END
GO

When we call this stored procedure it will recreate all lft and rtg information in Categories table. Second step will be to create trigger what will be fired after insert, update or delete.

CREATE TRIGGER dbo.RecreateCategories
ON  dbo.Categories
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  EXEC [dbo].[RecreateCategoryTree]
END
GO

This is realy the easiest way. It will not be fast with many records. For table with a few hundred record and occasional update it’s enought. Now we can try if everything is working as we need. Try to insert into category:

INSERT INTO dbo.Categories (CategoryName, ParentID) VALUES ('Celeron', 2);

C#, Database Database, MSSQL, MSSQL 2005, MSSQL 2008, SQL, Stored procedures

Tree traversal is a systematic way of ordering the nodes of a tree. When you are going throw three traversal structure, you will visit each node exactly once.

I use this method in work a few years ago and using it till now. Imagine that you have category table (i’ll be using MSSQL syntax for this post):

CREATE TABLE Categories
(
  ID int IDENTITY(1,1) NOT NULL,
  CategoryName NVARCHAR(100) NOT NULL,
  ParentID INT NULL,
  CONSTRAINT pk_CategoryID PRIMARY KEY (ID)
)

INSERT INTO Categories (CategoryName, ParentID) VALUES ('Computers', NULL); -- ID: 1
INSERT INTO Categories (CategoryName, ParentID) VALUES ('PC', 1); -- ID: 2
INSERT INTO Categories (CategoryName, ParentID) VALUES ('AMD', 2); -- ID: 3
INSERT INTO Categories (CategoryName, ParentID) VALUES ('Pentium', 2); -- ID: 4
INSERT INTO Categories (CategoryName, ParentID) VALUES ('Notebooks', 1); -- ID: 5
INSERT INTO Categories (CategoryName, ParentID) VALUES ('Books', NULL); -- ID: 6
INSERT INTO Categories (CategoryName, ParentID) VALUES ('Sci-fi', 6); -- ID: 7
INSERT INTO Categories (CategoryName, ParentID) VALUES ('Fantasy', 7); -- ID: 8

We have two parent categories ‘Computers’ and ‘Books’. Both of them have subcategories and ‘Computers’ category has subsubcategories as well. Now you will need to get all products from ‘Computers’ category and all subcategories. With this database structure it will be realy hard to get all products. You will need to get all category Ids and join them in IN clause. There is better way how to get them, it’s called tree traversal.

Tree traversal example

Here is image which shows previous data we inserted into category table.

Now we will add numbers to left and right side of each elipse, begining from left parent category, go to subcategories and continuing to right side.

Numbers on left side will be called ‘lft, on right side ‘rtg’. We can now update database structure to contain these informations.

CREATE TABLE Categories
(
  ID INT NOT NULL,
  CategoryName NVARCHAR(100) NOT NULL,
  ParentID INT NULL,
  lft INT NOT NULL,
  rtg INT NOT NULL,
  CONSTRAINT pk_CategoryID PRIMARY KEY (ID)
)

INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Computers', NULL, 1, 10); -- ID: 1
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('PC', 1, 2, 7); -- ID: 2
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('AMD', 2, 3, 4); -- ID: 3
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Pentium', 2, 5, 6); -- ID: 4
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Notebooks', 1, 8, 9); -- ID: 5
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Books', NULL, 11, 16); -- ID: 6
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Sci-fi', 6, 12, 13); -- ID: 7
INSERT INTO Categories (CategoryName, ParentID, lft, rtg) VALUES ('Fantasy', 7, 14, 15); -- ID: 8

Get child categories

Now we can get all child categories of ‘Computers’ category.

SELECT * FROM Categories WHERE lft >= 1 AND rtg <= 10;

Get all parent categories

Another posibility is when we need to create breadcrumbs for actual product page. We know, that the product is in ‘Pentium’ category. To get all parent categories we will use similar sql to the one above

SELECT * FROM Categories WHERE lft <= 5 AND rtg >= 6 ORDER BY lft ASC;

Have the category any childs?

If you have the category data and need to know if the category have any child categories you will need to make another select which will search for all categories which have ParentID same as your category. With this you can only compare ‘lft’ and ‘rtg’. If ‘lft’ +1 = ‘rtg’ than the category doesn’t have any subcategories.

Database Database, MSSQL, MSSQL 2005, MSSQL 2008, SQL