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

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.

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:\\");
}

Select allOpen in new window

Hope that helps.

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

another view of the error.
Last-Error.png

You have to cast it as an int:
id = (int) cmd.ExecuteScalar();

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

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?

"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

Select allOpen 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

Select allOpen in new window

Stored-Proc-Running-View.PNG
Stored-Proc-View.PNG

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);
            }

        }

    }
}

Select allOpen in new window

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

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 :-).

Here's the result - since ID is primary key it cannot allow for NULLs.
Test-Result.png

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

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.

Please keep replying to my future questions.

Glad you were finally able to get it.