Thursday, November 23, 2006

Manage Hierarchical data in SQL Server

Representing hierarchical database in SQL Server, involves the following steps
First, Create a table which represents the hierarchical information.
Second, Insert few information.
Third, Create the trigger which fires whenever any row is deleted, and this
trigger recursively delete all the sub tasks associated with it.

Step 1: Create Table

CREATE TABLE Tasks
(
TaskID int IDENTITY (1, 1) NOT NULL,
TaskName varchar(100),
Description varchar(1000),
ParentTaskID int NULL
)

Step 2: Insert Information

INSERT INTO Tasks VALUES(‘task 1’, ‘task 1 Description’,NULL)
INSERT INTO Tasks VALUES(‘task 2’, ‘task 2 Description’,1)
INSERT INTO Tasks VALUES(‘task 3’, ‘task 3 Description’,1)
INSERT INTO Tasks VALUES(‘task 4’, ‘task 4 Description’,1)
INSERT INTO Tasks VALUES(‘task 5’, ‘task 5 Description’,2)
INSERT INTO Tasks VALUES(‘task 6’, ‘task 6 Description’,2)
INSERT INTO Tasks VALUES(‘task 7’, ‘task 7 Description’,5)
INSERT INTO Tasks VALUES(‘task 8’, ‘task 8 Description’,5)

GO



Step 3: Create Trigger

ALTER DATABASE <db_name> SET RECURSIVE_TRIGGERS ON

GO

CREATE TRIGGER triggerDeleteTask ON
Tasks FOR DELETE
AS

IF @@rowcount = 0 RETURN
DELETE FROM T
FROM Tasks AS T JOIN deleted AS D
ON T. ParentTaskID = D. TaskID

GO

Thursday, October 26, 2006

Import Excel Data to SQL Server using ASP.NET

If you want to import data from excel file to SQL Server in asp.net, then first you have to upload the excel file to server and call the following function with correct arguments.

filePath -- full server path of excel file
tableName -- name of table in which you want to import
sheetName -- specify the sheet name (e.g. Sheet1, Sheet2)
mode -- if you want to replace the data, set mode to "replace", otherwise set ""

private void ImportToSqlServer(string filePath,string tableName,string sheetName, string mode)
{

StringBuilder query = new StringBuilder();
string conStr = "Data Source=(local);Initial Catalog=DatabaseName;User Id=myUsername;Password=myPassword";
SqlConnection cn = new SqlConnection(conStr);
if (mode == "replace")
{
query.Append(string.Format("delete from {0};",tableName));
}
query.Append(string.Format("Insert into {0} Select * FROM OPENROWSET",tableName));
query.Append(string.Format("('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database={0};",filePath));
query.Append(string.Format("HDR = No','SELECT * FROM [{0}$]')",sheetName));
SqlCommand cmd = new SqlCommand(query.ToString(),cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}