SQL SERVER with syntax [go]

See an example of SQL Server today on the forum. 1/25/50/100 cents, how many kinds could be pieced together into $2.

Looking at the first syntax, put it in SQL Server test, and found that it really enumerates all the ways to combine it into $2.

So carefully study the grammar, found that with the keyword.

The discovery of a long time without the use of SQL Server database is really a bit behind. So I saw that I learned the next with keyword.

1. Introduction

Real projects often encounter the need to deal with the problem of recursive parent-child relationship, if the hierarchical relationship between the separate, placed in multiple tables through the main foreign key relationship to join, the most obvious problem is the extension of inconvenient, for this situation, generally we will create a self-connected table to hold the data. For example, the data table structure for a member area may be:

Column Name	Description
location_id	Area number
Location_name	Region name
parentlocation_id	Parent area Number

or the staff table structure for a section might look like this:

Column Name	Description
employee_id	Employee number
Employee_Name	Employee Name
manager_id	The direct superior manager of the staff, and the employee_id for self-linkage

By using a table-like structure, we can theoretically manage the parent/child relationships of infinite series, but when we need to read this data out, whether it is filled into a tree, or using cascading displays, it takes some effort. The traditional approach is to make a recursive call, the first connection to the database to the top layer of data (that is, PARENT_XXX NULL records) read out, and then each data recursively access to populate the collection, this practice needs to connect the database multiple times, obviously not a better solution, So can we read all the data through a single database access, and in order to form a collection in accordance with the parent-child relationship, the returned data satisfies some form.

2. Analysis

Ideally, if the parent/child relationship data is added to the database strictly by the relational structure, that is, the parent record is added first, then the child record of the parent record is added, if the child record also contains child records, and the parent/child relationship in the final data table arranges the data by rules, we can populate the collection with an algorithm. But as we said, this is the ideal situation where the data is often changed, causing the data to be irregular, as shown, so it is not easy to read the data to populate the collection.

So what we're going to do is to satisfy this format by querying the data returned by the database, then our idea is to first find the top level (0 layer) record, then query the 1th level record, then the 2nd layer, 3rd layer and the nth layer. Because the number of layers is indeterminate, recursive access is still required.

A new with keyword is provided in SQL Server 2005 to specify a temporary named result set, which is called a common table expression (CTE). The expression originates from a simple query and is defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in the CREATE VIEW statement as part of the statement's SELECT definition statement. A common table expression can include a reference to itself. This type of expression is called a recursive common table expression.

Its syntax is:

[With <common_table_expression> [,... N]]<common_table_expression>::=expression_name[(column_name [, ... n])]as (cte_query_definition)

Using a simple example of the WITH key sub, the following code outputs the data source sample in the Tb_loc table:

With Locs (id,name,parent) as (    select * from Tb_loc) SELECT * FROM Locs

To create a well-structured set of records, the WITH keyword is used to first read the top-level record, and its child records are read for each top-level record, until it is read to the lowest level record, and all the records are combined together, where the union ALL keyword is used to combine multiple query results into a single result set.

You can then use this keyword to create a stored procedure to return a result set and attach the number of "tiers" where each record is located, as shown in:

Finally need to display it in the foreground interface, because the record has been returned hierarchically, the need to do is the first level of its output, first the No. 0 layer of data output, the next will traverse the No. 0 layer of data, add the first layer of data to the appropriate parent object, repeat the process until the result of filling. The problem here is how to find the parent object, and of course we can iterate through the collection, but doing so would be inefficient if the amount of data is very big. Now that we can get information on the layer where the current object is located, it is also the tree inverted tree is a layer of downward padding, we can define a temporary collection variable, store the current layer of all the parent objects, the current layer of objects to traverse the collection variable to insert into the appropriate location, At the same time, we must also ensure that the temporary collection variable holds all the objects at the top level of the current layer when the data is read by layer, and the program flowchart is as follows:

Based on the above analysis, we can write the implementation code (for convenience, the data table used in this article and the creation of records, such as SQL statements).

3. Implement

3.1 Open SQL Server 2005 Management Studio, select a database to enter the following statement to create the table structure:

CREATE TABLE [Tb_loc] (    [id] [int], [    name] [varchar] (+),    [parent] [int]) GO

3.2 Create test data:

Insert Tb_loc (id,name,parent) VALUES (1, ' Hebei province ', NULL) Insert Tb_loc (id,name,parent) VALUES (2, ' Shijiazhuang ', 1) Insert Tb_loc (ID, Name,parent) VALUES (3, ' Baoding ', 1) Insert Tb_loc (id,name,parent) VALUES (4, ' Shanxi Province ', NULL) Insert Tb_loc (id,name,parent) values (5, ' Taiyuan ', 4) Insert Tb_loc (Id,name,parent) VALUES (6, ' Xinhua District ', 2) Insert Tb_loc (id,name,parent) VALUES (7, ' Northern Focus Village ', 6) Insert Tb_loc (id,name , parent) VALUES (8, ' Big Guo Cun ', 6) Insert Tb_loc (id,name,parent) VALUES (9, ' Henan province ', NULL) Insert Tb_loc (id,name,parent) VALUES (10 , ' Big Guo Cun South ', 8) Insert Tb_loc (id,name,parent) VALUES (11, ' Big Guo Cun North ', 8) Insert Tb_loc (id,name,parent) VALUES (12, ' North Focus village East ', 7) insert Tb_loc (Id,name,parent) VALUES (13, ' Northern Focus village West ', 7) Insert Tb_loc (id,name,parent) VALUES (14, ' Qiaodong District ', 3) Insert Tb_loc (Id,name, Parent) VALUES (15, ' Qiaoxi District ', 3) GO

3.3 Create a pr_getlocations stored procedure:

CREATE PROCEDURE pr_getlocationsasbegin    with Locs (id,name,parent,loclevel)    as (        SELECT id,name, parent,0 as Loclevel from Tb_loc        WHERE parent was NULL        UNION all        SELECT l.id,l.name,l.parent,loclevel+1 from T B_loc l             INNER JOIN locs p on l.parent=p.id    )    SELECT * from Locsend

3.4 Create a new Web site in Visual Studio 2008 by creating a solution.

3.5 Add the App_Code directory to the site and create a location entity class that identifies the locality number and name, and holds the parent's location number and the collection of all child locations It contains:

public class location{Public    int Id    {        get;        Set;    }    public string Name    {        get;        Set;    }    Public locationcollection sublocations    {        get;        Set;    }    public int ParentID    {        get;        Set;    }    Public location ()    {        Id = 0;        Name = string. Empty;        Sublocations = new Locationcollection ();        parentid=0;}    }

More than 3.5 of the code uses the Locationcollection collection class to create the class using a generic collection (also in the App_Code directory):

Using System.collections.generic;public class locationcollection:list<location>{    }

3.6 Create a DAO class in the App_Code directory to access the database and add the necessary namespace references:

Using system;using system.data;using system.data.sqlclient;public class dao{}

3.7 Write the Getlocations method to return the Location collection object (Modify the database connection string as appropriate):

Public Locationcollection getlocations () {locationcollection locs = new Locationcollection (); using (SqlConnection conn = new SqlConnection ("server=.;    uid=sa;pwd=00000000;database=temp; ")) {Conn.        Open ();        SqlCommand cmd = new SqlCommand ();        Cmd.commandtext = "Pr_getlocations";        Cmd.commandtype = CommandType.StoredProcedure; Cmd.        Connection = conn; SqlDataReader reader = cmd.                            ExecuteReader ();        int level = 0;        int oldlevel = 1;        Locationcollection container=new locationcollection ();        Locationcollection current = new Locationcollection (); while (reader.            Read ()) {Location loc = Getlocationfromreader (reader, off level); if (level = = 0) {locs.                ADD (Loc); Container.                                ADD (Loc); } else {if (oldlevel! = level) {container. Clear(); foreach (location l at current) container.                    ADD (l); Current.                    Clear ();                Oldlevel = level; } current.                ADD (Loc);            Createlocation (container, loc); }}} return locs;}

In this method, follow these steps:

1. Executing a pr_getlocations stored procedure using a Command object object returns a result set

2. If the data reader reads the data (reader. The Read method returns True) execution:

2.1. Reads the location object from the data reader's current record and returns the layer information (out level)

2.2. If the first layer (level equals 0) fills the Locs collection and joins the container object

2.3. If the layer flag (oldlevel) is not the first layer to determine whether the current layer is a new layer

2.4 If the current layer is a new layer of empty container set merge to copy the entities in the existing collection into the container collection, empty the present collection and place the layer flag (Oldlevel)

2.5 Adding the current object to the present collection

2.6 Call the Createlocation method to match the current entity parent object and join the parent object's subset from the container upper-level collection

3. Repeat the 2nd step until you have finished reading all the data

You can see that the container collection always holds all the entity objects at the top of the current layer, and in order to update the container collection correctly after the number of layers have been replaced, use the current collection to save the entity objects for this layer.

3.8 Write the Getlocationfromreader method to return the location entity object from the data reader and return the layer information using the Out parameter:

Private location Getlocationfromreader (SqlDataReader Reader, out int.) {location    loc = new location ();    Loc. id = Convert.ToInt32 (reader["id"]);    Loc. Name = convert.tostring (reader["name"]);    Object o = reader["parent"];    if (o! = dbnull.value)        loc. ParentID = Convert.ToInt32 (o);    Level = Convert.ToInt32 (reader["Loclevel"]);    Return LOC;}

3.9 Write the Createlocation method, which iterates through the entity collection to find an entity that matches the parent number of the current entity object and joins the current entity to the subset of the parent entity:

private void Createlocation (Locationcollection container, location loc) {    foreach (location at container) c9/>{        if (location. Id = = loc. ParentID)        {location            . Sublocations.add (Loc);            Break;}}}    

3.10 Add the TreeView control to the Default.aspx page:

<asp:treeview id= "trvlocation" runat= "Server" font-size= "12px"    showlines= "True" ></asp:TreeView>

3.11 Write the Binddata data binding method in the Default.aspx page post code:

private void Binddata () {    dao DAO = new DAO ();    locationcollection locs = dao. Getlocations ();    TreeNodeCollection nodes = Createtreenodes (locs);    foreach (TreeNode node in nodes)    {        TRVLOCATION.NODES.ADD (node);}    }

The 3.12 Binddata method calls the Createtreenode method to return the node collection, which recursively calls itself to get all the local nodes:

Private TreeNodeCollection Createtreenodes (locationcollection locs) {    treenodecollection nodecoll = new TreeNodeCollection ();    foreach (Location loc in locs)    {        TreeNode node = new TreeNode (loc. Name, loc. Id.tostring ());                if (Loc. Sublocations.count > 0)        {            TreeNodeCollection subcoll = Createtreenodes (loc. sublocations);            foreach (TreeNode subnode in Subcoll)                node. Childnodes.add (subnode);        }        Nodecoll.add (node);    }    return nodecoll;}

3.13 Finally perform data binding in the page Load event:

protected void Page_Load (object sender, EventArgs e) {    if (! IsPostBack)    {this        . Binddata ();    }}

3.14 previewing the results in the browser:

4. Summary

Originally in dealing with a similar parent-child relationship is always unable to find a good solution, now through the new features in SQL Server 2005 can be more reasonable to solve the problem, where the use of the WITH keyword to implement recursive access, and in the output of the same recursive method of data. If you have a better way to achieve, please do not hesitate to enlighten.

Sample code download for this article: Sample code

Thank you for your support, in SQL Server Management Studio grabbed the query list and the above algorithm execution plan, for your reference.

1.select * from Tb_loc

2. Exec pr_getlocations

SQL SERVER with syntax [go]