Solved

Need help with adjusting code that stores nodes and their child nodes in a database.

Posted on 2014-01-29
20
597 Views
Last Modified: 2014-01-30
So I have some excellent code/videos that will loop through directories/files and place them in a similar style of Windows Explorer in a listview and treeview:

http://msdn.microsoft.com/en-us/library/vstudio/ms171645(v=vs.100).aspx
http://www.youtube.com/watch?v=55PCdfvlyYk

But what I want to do is store that structure in a database.   I'm assuming the best database structure to store this as shown in this youtube video where each node's name is stored with a primary key to store where each parent's is:  

http://www.youtube.com/watch?v=9Iy37_KRb1M

Please note only sections from :30 to 1:18 section of video.

But is this how you would do it?   Is there a sample code out there already that stores the files/subfolders and folders in a database so that a listview and treeview could be loaded from the data instead of directory.name?  

This is exactly what I am trying to create... any tips are appreciated!
0
Comment
Question by:stephenlecomptejr
  • 11
  • 9
20 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39818460
Yeah, having the parent node structure allows everything to be stored in one table.

you can get the entire structure from a CTE.

This link shows supervisors and employees, but it's all the same (parent - child):
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39818558
Ok.  But my final question in conjunction with this is how do I then save the data?

In the youtube example they use letters 'a', 'b', 'c' to assign the parent to the other and use a primary key as does your Microsoft - library link.

If I wanted the run though the directory.name and then start coming up with unique values for each node - how do I attempt to do that?  

And as I run this for another folder in the future - I will delete all the info and attempt to load the table with temporary data again - so I won't be able to use a primary key except - that for every time I add a node I would then have to find what the new primary key each time I'm adding data....

Please let me know if this explanation is satisfactory or needs some explanation.

REally what I would like is Visual Basic or C# sharp coding that would add nodes to a database - would you be able to provide a sample to do that?  And then I can provide more of a succinct question.  Thanks.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39818741
So lets assume you have the following properties:

ID int (the pk - identity (1,1))
FolderName  varchar(128)
ParentID int

I would create a stored proc in the database, say add entry:

create procedure AddEntry
@FolderName varchar(128)
@ParentId int = null
as

insert into Folders (folderName, ParentID) values (@FolderName, @ParentID)
select @@identity -- returns primary key of the value just inserted.


C# Code (note this was done by hand not in a compiler so I might have a few typos):

string myConnstr = ""; //populate DB conn str
private int addEntry(string strFolderName, int? ParentID)
{
SqlConnection conn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("AddEntry", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add (new SqlParameter("@FolderName", strFolderName));
cmd.Parameters.Add (new SqlParameter("@ParentID", ParentID));

int id =-1;
  try
  { 
      conn.Open();
      id = cmd.ExecuteScalar();

   }
   catch (Exception ex)
  {
    //handle exception
   }
  finally   
  {      
    conn.Close();
  } 
   return id;
}


private void RecurseDirectories(string strPath, int? parentID = null)
{  
    // first save the directory you're on.
   DirectoryInfo di = new DirectoryInfo(strPath);
   int id = addEntry(di.Name, parentID);

   //error if ID = -1
   if (id == -1)
     return; 

   foreach (DirectoryInfo dir in di.GetDirectories())
   {
      RecurseDirectores(dir.FullName, id);
   }
     
}

private void main()
{
   RecurseDirectories (@"C:\\");
}

Open in new window



Hope that helps.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39819307
I'm almost complete with applying the above.

Everything went through for 2 things - 1 was the misspelling RecurseDirectories (@"C:\\"); so that was easy.  The 2nd one has an error that says cannot implicitly convert type 'object' to 'int'.  An explicit conversion exists (are you missing a cast?)

Please note attachment with highlight on cmd.ExecuteScalar();
Last-Error.png
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39819310
another view of the error.
Last-Error.png
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39819412
You have to cast it as an int:
id = (int) cmd.ExecuteScalar();
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39819566
I'm trying to overcome still that cast error.
Please note this attachment.

Sorry a little new to C sharp.

Thanks for still trying.
Last-Error-3.png
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39820955
That code looks fine.

Can you run this from SqlServer Management Studio and let me know what you get:

exec AddEntry 'TestFolder'


Also can you "script table -> create" on The table in question and the stored proc?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39821177
"That code looks fine."

Huh?   It is Visual Studio stating that the code is incorrect in syntax.

========================================

Please note visual attachments for what you requested.

========================================


And here is the create script for the table:

USE [TreeView]
GO

/****** Object:  Table [dbo].[TreeData]    Script Date: 1/30/2014 10:12:52 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TreeData](
	[ID] [int] NULL,
	[FolderName] [varchar](128) NULL,
	[ParentID] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


Here is the stored proc:

USE [TreeView]
GO

/****** Object:  StoredProcedure [dbo].[AddEntry]    Script Date: 1/30/2014 10:13:41 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create procedure [dbo].[AddEntry]
@FolderName varchar(128),
@ParentId int = null

as

insert into TreeData (FolderName, ParentID) values (@FolderName, @ParentID)
select @@identity
GO

Open in new window

Stored-Proc-Running-View.PNG
Stored-Proc-View.PNG
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39821304
Here is the Visual Studio 2010 code in a single Windows application form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace TreeView
{
    public partial class Form1 : Form
    {

        string myConnstr = "Server=T431S-SLECOMPTE\\SQLSERVER2012;Database=TreeView;User Id=TreeView;Password=tr33v1ew"; //populate DB conn str

        private int addEntry(string strFolderName, int? ParentID)
        {
            SqlConnection conn = new SqlConnection(myConnstr);
            SqlCommand cmd = new SqlCommand("AddEntry", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add (new SqlParameter("@FolderName", strFolderName));
            cmd.Parameters.Add (new SqlParameter("@ParentID", ParentID));

            int id =-1;
              //try
              //{ 
                  conn.Open();
                  id = (int)cmd.ExecuteScalar();

              // }
              // catch (Exception)
              //{
              //    MessageBox.Show("Help");
              // }
              //finally   
              //{      
                conn.Close();
              //} 
               return id;
        }


        public Form1()
        {
            InitializeComponent();
            RecurseDirectories("C:\\_Stephens Files\\Work");


        }


        private void RecurseDirectories(string strPath, int? parentID = null)
        {
            // first save the directory you're on.
            DirectoryInfo di = new DirectoryInfo(strPath);
            int id = addEntry(di.Name, parentID);

            //error if ID = -1
            if (id == -1)
                return;

            foreach (DirectoryInfo dir in di.GetDirectories())
            {
                RecurseDirectories(dir.FullName, id);
            }

        }

    }
}

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 39821424
The reason why you're getting null back from the stored proc is because the ID is not an identity field.

to set the identity right click on TreeData  -> design.
Click on the ID column:

Do 2 things:
1)  In the top left hand corner click the "Key" -> this will set it as the primary key.
2)  In the column properties, expand identity specification -> (is Identity)  yes -> leave increment and seed at 1 and 1.

Column Properties
Save the table.

then run:
truncate table TreeData  in sql

to clear the records and try it again.

Resources:
Primary key:
http://technet.microsoft.com/en-us/library/ms191236(v=sql.105).aspx
http://www.youtube.com/watch?v=T_CUPOZevhI (about 50 seconds in . . . the column can't be null and about 1:55 he's using the right click action to set the key, but you'll also notice the key icon in the top left is highlighted as well. )


Identity:
http://technet.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39821579
Wow Kyle I see why they call you a genius.
Those stinking Nulls again keep messing stuff up.

I'm sorry to do this to you and ask another question.
The program won't continue - please note error message via attachment and maximize.

What then do I do so that it will allow this to continue - why do you think I'm getting a null in this situation?

And then when I tried to see what it had added to the tables despite the error - I tried to see if any data had been entered up to that point but had none.
NULL-error-still.PNG
Data-Shown-No.PNG
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39821768
let's focus on the DB for now:

Can you screenshot re-execing the stored proc and let's see if you get an int:

If you get 1 for this it means it's working.
  exec AddEntry 'TestFolder'  -- (screen shot the result)

and if you get a 1 try for a child table:
  exec AddEntry 'TestFolderChild', 1


do a select to confirm:
  select * from TreeData  -- (screen shot)

if you get results :
  truncate  table TreeData



You learn all this stuff over time . . . take heart, 10 years ago I didn't know what any of this was either.  

We'll get it working eventually :-).
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39821804
Here's the result - since ID is primary key it cannot allow for NULLs.
Test-Result.png
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 39821834
rescript the table for me?  It still seems as though the identity property for the ID column isn't set.

this is what the Id should look like when the identity is set:


CREATE TABLE [dbo].[appZipCodes](
      [ID] [int] IDENTITY(1,1) NOT NULL,
     [ZipCode] [varchar](15) NOT NULL,
      [ZipCodeType] [varchar](1024) NOT NULL,
      [City] [varchar](50) NOT NULL,
      [State] [varchar](50) NULL,
      [Country] [varchar](50) NULL,
      [LocationType] [varchar](50) NULL,
)
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39821945
Yes you are correct.  The identity was not set.

Thus I was able to do what you asked as above in the database....  Please see attachment WorkingAsAbove attachment.

But then I went back to the visual studio and did a rebuild and try running it....but still get the error.    Please see attachment.... Still an error.png

Also wanted to check the data - please note attachment.... Data Now.Png as to the new results.

Sure appreciate all your help thus far!
WorkingAsAbove.PNG
Still-an-error.PNG
Data-Now.PNG
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 39821978
take out the cast  eg:  remove (int) and see if that works.  The first one might have been because you were getting a null when I was thinking you were getting an actual value.

line should read
   id = cmd.ExecuteScalar();

if it STILL throws an error:
   id = int.Parse(cmd.ExecuteScalar().ToString());

Good news is where now working the first time.

Just for sanity sake run
 truncate table TreeData

so it clears the row (this will also reset the id to 1)
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 39822059
Thank you.  That works well.
Didn't even know about Truncat either and put it as code at the beginning each time.

Thank you.  Thank you.   Thank you very much for this!  I appreciate it.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 39822065
Please keep replying to my future questions.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39822139
Glad you were finally able to get it.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now