Re: binding tree view with sql databases and tables - C# Discussion Boards

Re: binding tree view with sql databases and tables

Henry Minute28-Mar-09 9:55

28-Mar-09 9:55

I haven't had time to read through all of your code, yet, but two things strike me immediately.

1) Learn to use the 'code block' item (below the text entry box), this preserves the formatting of your code and makes it easier to read.
The code snippets in item 2 use this. You should note the difference.

2) You code would be easier to read in any case if you replace this:

treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);
DB.Nodes[0].Nodes.Add(treenode);


DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Tables");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Stored Procedurs");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Views");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Roles");

with:

treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);

treenode.Nodes.Add("Tables");
treenode.Nodes.Add("Stored Procedures");
treenode.Nodes.Add("Views");
treenode.Nodes.Add("Roles");

DB.Nodes[0].Nodes.Add(treenode);

I will contact you further when I have had time for more study.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: binding tree view with sql databases and tables

Henry Minute28-Mar-09 10:17

Henry Minute

28-Mar-09 10:17

You have learned how to use sp_databases.
To solve at least part of your problem, research sp_tables and use it in your LoadSQL_Tables method. As you will see from the code below, you might also need to pass the database name to the method.
*********** WARNING *****************
sp_tables gets views, and some other stuff, as well as tables, if not used properly!!
*********** WARNING *****************

To make things a little easier for yourself, you should think about modifying the flow of your code something like:

while (SqlDR.Read())
{
    treenode = new TreeNode();
    treenode.Text = SqlDR.GetString(0);

    TreeNode tablesNode = treenode.Nodes.Add("Tables");
    LoadSQL_Tables(tablesNode, treeNode.Text); // Method call moved to here, with database name as extra parameter.
    treenode.Nodes.Add("Stored Procedures");
    treenode.Nodes.Add("Views");
    treenode.Nodes.Add("Roles");

    DB.Nodes[0].Nodes.Add(treenode);

    count ++;
}

If this works, and I hope it does, it should give you some ideas about how to get the other data you need.

Re: binding tree view with sql databases and tables

mariamkhaqan28-Mar-09 21:59

mariamkhaqan

28-Mar-09 21:59

Hi thank you so much Henry for the guide line but i am still having some problem and that is instead of my table's name, the name "master" is coming under the node of master table's node, more over the tables of other databases amd their tables are not coming.. i am very confused ..
the signature of my Load_sqlTable function is now as under

public void LoadSQL_Tables(TreeNode parentNode,string dbname)

is it correct?

regards,

Re: binding tree view with sql databases and tables

Henry Minute28-Mar-09 22:09

Henry Minute

28-Mar-09 22:09

If you are using sp_tables correctly, yes.
You need the dbname to limit the results to the table passed.

Just to be sure take a look at this[^], particularly the last example which shows an the use of dbname.

Keep experimenting, and if you are still stuck after a while, post your code for the main loop and the two methods for getdatabases and gettables.

Re: binding tree view with sql databases and tables [modified]

Henry Minute29-Mar-09 2:59

Henry Minute

29-Mar-09 2:59

If you are still having problems getting the correct databases, have a look at this code snippet from one of my apps.

SqlConnection SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=''");
SqlCommand SqlCom = new SqlCommand();
SqlDataReader reader;

SqlCom.CommandText = "sp_databases";
SqlComCommandType = CommandType.StoredProcedure;
SqlCom.Connection = sqlConnection1;

SqlCon.Open();

reader = SqlCom.ExecuteReader();
table = SQLBuilderUtilities.ReaderToTable(reader);

sqlConnection1.Close();

int count = 0;

// Get the Database Nodes
foreach (DataRow row in table.Rows)
{
    treenode = new TreeNode();
    treenode.Text = row["DATABASE_NAME"].ToString();

    TreeNode tablesNode = treenode.Nodes.Add("Tables");
    LoadSQL_Tables(tablesNode, treeNode.Text); // Method call moved to here, with database name as extra parameter.
    treenode.Nodes.Add("Stored Procedures");
    treenode.Nodes.Add("Views");
    treenode.Nodes.Add("Roles");

    DB.Nodes[0].Nodes.Add(treenode);

    count ++;
}

I have modified it to use your field names etc.

The ReaderToTable method converts the reader output to a DataTable. This is useful because, apart from anything else, it means that it is very easy to set up a test form with a DataGridView, so that you can actually have a look at the results when experimenting with different stored procedures.

Here is the code, it is quite simple really:

public static DataTable ReaderToTable(SqlDataReader reader)
{
    DataTable newTable = new DataTable();
    DataColumn col;
    DataRow row;
    int i;

    for (i = 0; i < reader.FieldCount; i++)
    {
        col = new DataColumn();
        col.ColumnName = reader.GetName(i);
        col.DataType = reader.GetFieldType(i);

        newTable.Columns.Add(col);
    }

    while (reader.Read())
    {
        row = newTable.NewRow();
        for (i = 0; i < reader.FieldCount; i++)
        {
            row[i] = reader[i];
        }

        newTable.Rows.Add(row);
    }

    return newTable;
}

I hope that this is of help for the database nodes. It should also give you some ideas for the tables etc.

modified on Monday, March 30, 2009 7:45 AM

Re: binding tree view with sql databases and tables

mariamkhaqan29-Mar-09 21:34

mariamkhaqan

29-Mar-09 21:34

Hi Thanks for the help but i am unable to get what is SQLBuilder utilities.
I am having this error

"The name 'SQLBuilderUtilities' does not exist in the current context "

kindly tell me what to do

regards,

Re: binding tree view with sql databases and tables

Henry Minute30-Mar-09 1:45

Henry Minute

30-Mar-09 1:45

Sorry, my fault. $Shucks | :-\$

In my App (called SQLBuilder) I have a separate class (Utilities), which contains the ReaderToTable method. Just delete the SQLBuilderUtilities bit and all should be well.

I have modified my post with the code in, by striking out the problem bits. Please note that this includes the static access modifier from the ReaderToTable method.

If you just put that in your class all should now be well.

Re: binding tree view with sql databases and tables

mariamkhaqan30-Mar-09 3:31

mariamkhaqan

30-Mar-09 3:31

Hi thank you so much for every thing but i am really sorry i am still having problem,actully now all the databases are also coming in the tree node but
inside the databases nodes only table is coming and the more big problem is all the databases are loading master tables.... i am sending you my code, i am calling it on the strip menu so i will u the code from i am calling aswell ,i am really sorry but i am unable to figure out..

// where i am calling the function
private void sQLServer2005ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            parentNode = new TreeNode();
            DBTree.Nodes[0].Text = Show_SqlConnection();
            parentNode.Text = "Databases";
            DBTree.Nodes[0].Nodes.Add(parentNode);
            LoadSQL_DB(parentNode); 
        }

// which loads databases and tables

public void LoadSQL_DB(TreeNode DB)
        {
            DataTable table = new DataTable();
            SqlConnection sqlConnection1 = new SqlConnection();
            TreeNode treeNode;
            TreeNode tablesNode;
            int count = 0;            
            SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database='' ");
            SqlCon.Open();
           
            SqlCom = new SqlCommand();
            SqlCom.Connection = SqlCon;
            SqlCom.CommandType = CommandType.StoredProcedure;
            SqlCom.CommandText = "sp_databases";
            SqlCom.Connection = SqlCon;
            SqlDR = SqlCom.ExecuteReader();
            table = ReaderToTable(SqlDR);
           

             //Get the Database Nodes
            foreach (DataRow row in table.Rows)
            {
                treeNode = new TreeNode();
                treeNode.Text = row["DATABASE_NAME"].ToString();
                DB.Nodes.Add(treeNode);                
                DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Tables");
                tablesNode = new TreeNode();
                LoadSQL_Tables(treeNode);
                DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Stored Procedure");
                DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Views");
                DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Roles"); 
 count++;
            }            
            SqlDR.Close();
            SqlCon.Close();

// function which loads tables
 public void LoadSQL_Tables(TreeNode arg_dbNode)
        {
            TreeNode table_node;
            SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master");
            SqlCon.Open();
            SqlCom = new SqlCommand();
            SqlCom.Connection = SqlCon;
            SqlCom.CommandType = CommandType.Text;
            SqlCom.CommandText = "select * from sysobjects where type = 's' order by name";
            
            SqlDR = SqlCom.ExecuteReader();
            while (SqlDR.Read())
            {
                table_node = new TreeNode();
                table_node.Text = SqlDR.GetString(0);
                arg_dbNode.Nodes.Add(table_node);            
            }
         

        }

regards,
mariam

Re: binding tree view with sql databases and tables

mariamkhaqan30-Mar-09 3:33

mariamkhaqan

30-Mar-09 3:33

and this is my DataTable ReaderToTable fuction

public DataTable ReaderToTable(SqlDataReader reader)
       {
           DataTable newTable = new DataTable();
           DataColumn col;
           DataRow row;
           int i;

           for (i = 0; i < reader.FieldCount; i++)
           {
               col = new DataColumn();
               col.ColumnName = reader.GetName(i);
               col.DataType = reader.GetFieldType(i);

               newTable.Columns.Add(col);
           }

           while (reader.Read())
           {
               row = newTable.NewRow();
               for (i = 0; i < reader.FieldCount; i++)
               {
                   row[i] = reader[i];
               }

               newTable.Rows.Add(row);
           }

           return newTable;
       }

Re: binding tree view with sql databases and tables

Henry Minute30-Mar-09 3:48

Henry Minute

30-Mar-09 3:48

Thanks for the code.
I'm going to set up a project to run it in. So far I have only been reading it.
This may take a little time, so be patient.

I'll get back to you later.

Re: binding tree view with sql databases and tables

Henry Minute30-Mar-09 7:12

Henry Minute

30-Mar-09 7:12

When I started trying to help you, I just tried to get your code working for you. Trying out your code has made me realize that part of what I was telling you to do would make your code run very slowly. Filling the TreeView with all of the data in one go would take a very long time.

So I am suggesting that you just get the names of the databases and add a node for each. Each of these database nodes has nodes for Tables, Views etc. But do not fetch the data for these until the user tries to expand them. So that the expand icon shows up for these nodes, they each have an empty node added. This is removed later when it is not needed.

This code works correctly on my system. The only difference between my system and yours is the name of the server. I have tried to change this code so that it will work on your system exactly as it is. Try to use it as it is first, before you alter the layout etc. to suit your style of working.

private TreeNode parentNode;
private readonly string connString = "server={0};Integrated Security=true;database={1}";

// where i am calling the function
private void sQLServer2005ToolStripMenuItem_Click(object sender, EventArgs e)
{
    parentNode = new TreeNode();
    DBTree.Nodes.Add(Show_SqlConnection());
    parentNode.Text = "Databases";
    DBTree.Nodes[0].Nodes.Add(parentNode);
    LoadSQL_DB(parentNode);
}

// Method which loads databases and tables
public void LoadSQL_DB(TreeNode DB)
{
    DataTable table;
    TreeNode treeNode;
    TreeNode tempNode;
    int count = 0;

                // Note the use of the 'using' statement. This ensures that the connection gets closed
                // and disposed of when it is finished with.
    //SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database='' ");
    using (SqlConnection SqlCon = GetConnection(@"Localhost", ""))
    {
        SqlCommand SqlCom = new SqlCommand();
        SqlCom.Connection = SqlCon;
        SqlCom.CommandType = CommandType.StoredProcedure;
        SqlCom.CommandText = "sp_databases";

        SqlCon.Open();
        SqlDataReader SqlDR = SqlCom.ExecuteReader();
        table = ReaderToTable(SqlDR);
    }

    //Get the Database Nodes
    foreach (DataRow row in table.Rows)
    {
        treeNode = new TreeNode();
        treeNode.Text = row["DATABASE_NAME"].ToString();
        tempNode = treeNode.Nodes.Add("Tables");
        tempNode.Nodes.Add(""); // All that this does, is add a dummy node
        tempNode = treeNode.Nodes.Add("Stored Procedure");
        tempNode.Nodes.Add(""); // adding a dummy node ensures that the '+' icon appears
        tempNode = treeNode.Nodes.Add("Views");
        tempNode.Nodes.Add("");
        tempNode = treeNode.Nodes.Add("Roles");
        tempNode.Nodes.Add("");
        count++;

        DB.Nodes.Add(treeNode);
    }
}

// function which loads tables
public void LoadSQL_Tables(TreeNode arg_dbNode, string database)
{
    DataTable table;

    using (SqlConnection SqlCon = GetConnection("Localhost", database))
    {
        SqlCommand tablesCommand = new SqlCommand();
        tablesCommand.CommandType = CommandType.StoredProcedure;
        tablesCommand.CommandText = "sp_tables";
        tablesCommand.Parameters.Add("@table_qualifier", SqlDbType.NVarChar, 384).Value = database;
        string paramString = "'TABLE'";  // The inner single quotes are required
        tablesCommand.Parameters.Add("@table_type", SqlDbType.VarChar, 32).Value = paramString;
        tablesCommand.Connection = SqlCon;

        SqlCon.Open();
        SqlDataReader SqlDR = tablesCommand.ExecuteReader();
        table = ReaderToTable(SqlDR);
    }

    foreach (DataRow row in table.Rows)
    {
        arg_dbNode.Nodes.Add(row["TABLE_NAME"].ToString());
    }

    // get rid of the dummy node. If we have found any tables we don't need it
                // and if there are no tables we don't need to show the '+' icon.
    arg_dbNode.Nodes.RemoveAt(0);
}

private SqlConnection GetConnection(string server, string database)
{
    return new SqlConnection(string.Format(this.connString, server, database));
}

public DataTable ReaderToTable(SqlDataReader reader)
{
    DataTable newTable = new DataTable();
    DataColumn col;
    DataRow row;
    int i;

    for (i = 0; i < reader.FieldCount; i++)
    {
        col = new DataColumn();
        col.ColumnName = reader.GetName(i);
        col.DataType = reader.GetFieldType(i);

        newTable.Columns.Add(col);
    }

    while (reader.Read())
    {
        row = newTable.NewRow();
        for (i = 0; i < reader.FieldCount; i++)
        {
            row[i] = reader[i];
        }

        newTable.Rows.Add(row);
    }

    return newTable;
}

        // Dont forget to add this to the BeforeExpand event of your TreeView
private void DBTree_BeforeExpand(object sender, TreeViewCancelEventArgs e)
{
    if ((e.Node.Nodes.Count == 1) && (e.Node.Nodes[0].Text == ""))
    {
        // You will have to add a case for any other types
        // that you want to get data for.
        // I have put an example in and commented it out
        switch (e.Node.Text)
        {
            case "Tables":
                LoadSQL_Tables(e.Node, e.Node.Parent.Text);
                break;
            //case "Views":
            //    LoadSQL_Views(e.Node, e.Node.Parent.Text);
            //    break;
        }
    }
}

Good Luck! Smile | :)

Re: binding tree view with sql databases and tables

mariamkhaqan30-Mar-09 17:33

mariamkhaqan

30-Mar-09 17:33

Hi Thank you so much i'll check it and then will tell you, but really thanks alot
regards,
mariam

Re: binding tree view with sql databases and tables

mariamkhaqan1-Apr-09 2:29

mariamkhaqan

1-Apr-09 2:29

Hi i have tried yor code but even then the tables and stuff were note coming,it was displaying empty table's node.but then with the help of my teacher i resolved the problem,u can also check it as under:

public void LoadSQL_DB(TreeNode DB)
       {
           DataTable table = new DataTable();
           SqlConnection sqlConnection1 = new SqlConnection();
           TreeNode treeNode;


           int count = 0;
           SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master ");
           SqlCon.Open();

           SqlCom = new SqlCommand();
           SqlCom.Connection = SqlCon;
           SqlCom.CommandType = CommandType.StoredProcedure;
           SqlCom.CommandText = "sp_databases";
           SqlCom.Connection = SqlCon;
           SqlDR = SqlCom.ExecuteReader();
           table = ReaderToTable(SqlDR);


            //Get the Database Nodes
           foreach (DataRow row in table.Rows)
           {
               treeNode = new TreeNode();
               treeNode.Text = row["DATABASE_NAME"].ToString();
               DB.Nodes.Add(treeNode);
               DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Tables");



               DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Stored Procedure");
               DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Views");
               DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Roles");

               if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count].Text == "Tables")
               {
                   LoadSQL_Tables(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count], treeNode.Text);

                   if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count+1].Text == "Stored Procedure")
                       LoadSQL_StoedProcedures(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 1], treeNode.Text);
                   if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 2].Text == "Views")
                       LoadSQL_Views(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 2], treeNode.Text);
               }

           }


           SqlDR.Close();
           SqlCon.Close();




       }// End of LoadSQL_DB....

thanks for the help
regards,

Re: binding tree view with sql databases and tables

Henry Minute1-Apr-09 4:16

Henry Minute

1-Apr-09 4:16

Hi,

I am glad that you got it working. Thumbs Up | :thumbsup:

There are two things that might make your life a little easier.

1) You keep using code like this:
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Stored Procedure");
which returns a reference to treeNode. You already have a reference to treeNode
so this:
treeNode.Nodes.Add("Stored Procedure");
does exactly the same thing. All you are doing, is making extra typing for yourself. Smile | :)

2) Do, and I really mean do, investigate the 'using' statement. You will write much, much safer code if you learn how to use it. It is a MicroSoft recommendation too.

Good Luck. Smile | :)

Re: binding tree view with sql databases and tables

mariamkhaqan3-Apr-09 2:24

mariamkhaqan

3-Apr-09 2:24

ok thanks again

regards,
mariam

Chart in windows

lnmca28-Mar-09 3:05

lnmca

28-Mar-09 3:05

Hi All,

i want to create the moving chart in windows application like it is displaying the current data getting from any hardware device,how will it be possible.

Please help

Thanks and Regards,
Lalit Narayan

Re: Chart in windows

dan!sh 28-Mar-09 5:32

dan!sh

28-Mar-09 5:32

Lots of useful results.[^]

जय हिंद

Re: Chart in windows

Natza Mitzi29-Mar-09 19:54

Natza Mitzi

29-Mar-09 19:54

The second most popular article in code-project:

A flexible charting library for .NET[^]

Natza Mitzi

Validating IP Addresses

Jammer28-Mar-09 2:02

Jammer

28-Mar-09 2:02

Hi All,

I'm just working with the Dns class and have a need to use IP addresses. At the moment I'm just retrieving a list of IP addresses using the GetHostByName method and then validing the format of each IP address.

The issue is that I'm then going to use this as a base for producing some service endpoints. I want to make sure that the IP address I use is correct for the machines identity on our network rather than just grabbing the first IP address in the list which seems a bit random.

Should I be calling a domain controller and confirming with it that the IP address I plan to use is the 'correct' one?

Cheers,

Jammer
My Blog | Article(s)

Re: Validating IP Addresses

quacks_a_lot28-Mar-09 9:59

quacks_a_lot

28-Mar-09 9:59

I'd imagine if the Dns class returned the IP address for the hostname you were looking for (or the other way around), then that would be the correct IP address. If it is the wrong IP address, the host you are looking for either changed names or is offline.

Re: Validating IP Addresses

Jammer30-Mar-09 2:12

Jammer

30-Mar-09 2:12

Hi,

Actually I'm using IPEntry on the machine I want to get the network IP for. When using IPEntry you get back IPAddress[] so currently I'm just taking the entry at position 0 which to me seems a bit prone to errors to rely on there being the actual machine network IP address at position 0.

Cheers,

Jammer
My Blog | Article(s)

Question about DCT

Flying_Doc28-Mar-09 1:09

Flying_Doc

28-Mar-09 1:09

i have a question about DCT..actually it's sound like silly question but it's important for me..
The question is:
1. Why on DCT the function use a cosine function, if we see on the image and the
function there is no connection with angle.
2. Why the function using cosine, why not using sine or tangen? what is the influence
for the compression ratio?.
3. Is it true that we using the pixel depth for making 8x8 matix as the input for DCT,
if it's true how to get a pixel depth on image in C# so we could make the 8x8 matrix.

thanks.. God bless you

Re: Question about DCT

Luc Pattyn28-Mar-09 6:31

Luc Pattyn

28-Mar-09 6:31

Hi,

with a general question like that, you should do some research first. Here[^] is something worth reading.

1. because it works well
2. sine could be used equally well, tangent would be harder to use due to it frequently becoming infinite
3. No

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Suggestion Needed : Creating Dynamic Build

Abhijit Jana28-Mar-09 0:51

Abhijit Jana

28-Mar-09 0:51

I need some Idea to implement this .

1.User will download One exe from my web sites. [Windows Application ]
2.When user run that exe, It will perform some operation on client system and it will call one of my web services to update my database with some information.

Is it possible to create the dynamic build with each user ID. Means I need to identify that user that who is calling my web services and based on that I will update my database.
Say when it call my webmethod updateDB, it will call like
updateDB(int userID, string systeminfo)
Here I need to identify the UserID.

Any Suggestion or any other idea to achieve this ?

Thanks in advance !!

cheers,
Abhijit
CodeProject MVP

Re: Suggestion Needed : Creating Dynamic Build

Eddy Vluggen28-Mar-09 1:33

Eddy Vluggen

28-Mar-09 1:33

Just reformulating your question, to verify that I've understood the question. You want to embed some UserID-marker in your excutable/class library, before it gets downloaded and installed?

I are troll Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.