none
How to use DataTable in SMO. RRS feed

  • Question

  • I have created connection on one page using SMO and i am returning server name and database name from connection page to other page to iterate database. I want to use DataTable property to iterate database.

    How i can do it.

     

    Ajit

    Friday, December 22, 2017 9:06 AM

Answers

  • Is this question related to the other one you asked?

    https://social.msdn.microsoft.com/Forums/en-US/709184d3-5e74-4484-9f03-ec697148a905/how-to-get-a-list-of-all-tables-having-different-records-in-two-database-on-hold?forum=adodotnetdataset


    I'm not 100% sure that I understand your question, but maybe this is what you want:

    private SqlConnectionStringBuilder csBuilder;
    private DataTable dtTableNames;
    
    // This probably goes in your constructor somewhere, but if you mean web page
    // when you say "page", then I don't know where you'd put this (I don't know web stuff)
    // Anyway, this uses the ServerName and DatabaseName that you have returned from the other "page"
    string security;
    if (HaveUserIdAndPwd)
        security = string.Format("uid={0};pwd={1}", UserID, Password);
    else
        security = "Integrated Security=True";
    
    this.csBuilder = new SqlConnectionStringBuilder(string.Format("server={0};{1}", ServerName, security));
    this.csBuilder.InitialCatalog = DatabaseName;
    using (SqlConnection connection = new SqlConnection(this.csBuilder.ConnectionString))
    {
        connection.Open();
     this.dtTableNames = connection.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });
        connection.Close();
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Risu Raj Friday, December 29, 2017 11:19 AM
    Friday, December 22, 2017 5:07 PM
  • OK, Ajit ... so it sounds like you already know how to pass the server name and database name to the other Form.

    I already showed you how to create a new connection on the other Form with the information you passed to it and how to fill a DataTable with the names of all the tables in the database using the connection.GetSchema() method.

    All that's left to do is iterate through that DataTable and retrieve the rows for each table. How you plan to display that data would be a separate question in a separate thread in a different forum (for WinForms), unless you already know how to do that (such as databinding to a DataGridView, for example).

    using (SqlConnection connection = new SqlConnection(this.csBuilder.ConnectionString))
    {
        SqlDataAdapter da = new SqlDataAdapter("", connection);
    
        DataTable dtForDisplay = new DataTable();
        foreach (DataRow row in this.dtTableNames.Rows)
        {
            dtForDisplay.Clear();
            da.SelectCommand.CommandText = "SELECT * FROM " + row["TABLE_NAME"];
            da.Fill(dtForDisplay);
    
            // now do something with the data in dtForDisplay
        }
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, December 23, 2017 5:12 PM

All replies

  • Is this question related to the other one you asked?

    https://social.msdn.microsoft.com/Forums/en-US/709184d3-5e74-4484-9f03-ec697148a905/how-to-get-a-list-of-all-tables-having-different-records-in-two-database-on-hold?forum=adodotnetdataset


    I'm not 100% sure that I understand your question, but maybe this is what you want:

    private SqlConnectionStringBuilder csBuilder;
    private DataTable dtTableNames;
    
    // This probably goes in your constructor somewhere, but if you mean web page
    // when you say "page", then I don't know where you'd put this (I don't know web stuff)
    // Anyway, this uses the ServerName and DatabaseName that you have returned from the other "page"
    string security;
    if (HaveUserIdAndPwd)
        security = string.Format("uid={0};pwd={1}", UserID, Password);
    else
        security = "Integrated Security=True";
    
    this.csBuilder = new SqlConnectionStringBuilder(string.Format("server={0};{1}", ServerName, security));
    this.csBuilder.InitialCatalog = DatabaseName;
    using (SqlConnection connection = new SqlConnection(this.csBuilder.ConnectionString))
    {
        connection.Open();
     this.dtTableNames = connection.GetSchema("Tables", new string[] { null, null, null, "BASE TABLE" });
        connection.Close();
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Risu Raj Friday, December 29, 2017 11:19 AM
    Friday, December 22, 2017 5:07 PM
  • Hi Bonnie,

    I am using windows form. I have created one form where user can select sql server name from a list of server and then database name present in selected server. I am returning server and database name from this form to other form. So, how i can use connection on other form. I want to display records of each table present in that database.


    Ajit

    Saturday, December 23, 2017 9:23 AM
  • OK, Ajit ... so it sounds like you already know how to pass the server name and database name to the other Form.

    I already showed you how to create a new connection on the other Form with the information you passed to it and how to fill a DataTable with the names of all the tables in the database using the connection.GetSchema() method.

    All that's left to do is iterate through that DataTable and retrieve the rows for each table. How you plan to display that data would be a separate question in a separate thread in a different forum (for WinForms), unless you already know how to do that (such as databinding to a DataGridView, for example).

    using (SqlConnection connection = new SqlConnection(this.csBuilder.ConnectionString))
    {
        SqlDataAdapter da = new SqlDataAdapter("", connection);
    
        DataTable dtForDisplay = new DataTable();
        foreach (DataRow row in this.dtTableNames.Rows)
        {
            dtForDisplay.Clear();
            da.SelectCommand.CommandText = "SELECT * FROM " + row["TABLE_NAME"];
            da.Fill(dtForDisplay);
    
            // now do something with the data in dtForDisplay
        }
    }
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, December 23, 2017 5:12 PM