Why the function gives unwanted value

I have a function like this for binding the primary key from the selected database in a ComboBox:

//An instance of the connection string is created to manage the contents of the connection string.

var sqlConnection = new SqlConnectionStringBuilder();
sqlConnection.DataSource = "192.168.10.3";
sqlConnection.UserID = "gp";
sqlConnection.Password = "gp";
sqlConnection.InitialCatalog = Convert.ToString(cmbDatabases.SelectedValue);
string connectionString = sqlConnection.ConnectionString;

SqlConnection sConnection = new SqlConnection(connectionString);

//To Open the connection.
sConne开发者_如何学Cction.Open();

//Query to select the table_names that have PRIMARY_KEYS.
string selectPrimaryKeys = @"SELECT TABLE_NAME 
                             FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                             WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
                             ORDER BY TABLE_NAME";

//Create the command object
SqlCommand sCommand = new SqlCommand(selectPrimaryKeys, sConnection);

try
{
    //Create the dataset
    DataSet dsListOfPrimaryKeys = new DataSet("INFORMATION_SCHEMA.TABLE_CONSTRAINTS");

    //Create the dataadapter object
    SqlDataAdapter sDataAdapter = new SqlDataAdapter(selectPrimaryKeys, sConnection);

    //Provides the master mapping between the sourcr table and system.data.datatable
    sDataAdapter.TableMappings.Add("Table", "INFORMATION_SCHEMA.TABLE_CONSTRAINTS");

    //Fill the dataset
    sDataAdapter.Fill(dsListOfPrimaryKeys);

    //Bind the result combobox with primary key tables
    DataViewManager dvmListOfPrimaryKeys = dsListOfPrimaryKeys.DefaultViewManager;
    cmbResults.DataSource = dsListOfPrimaryKeys.Tables["INFORMATION_SCHEMA.TABLE_CONSTRAINTS"];
    cmbResults.DisplayMember = "TABLE_NAME";
    cmbResults.ValueMember = "TABLE_NAME";
}
catch(Exception ex)
{
    //All the exceptions are handled and written in the EventLog.
    EventLog log = new EventLog("Application");
    log.Source = "MFDBAnalyser";
    log.WriteEntry(ex.Message);
}
finally
{
    //If connection is not closed then close the connection
    if(sConnection.State != ConnectionState.Closed)
    {
        sConnection.Close();
}

But it is giving an unwanted result like dtproperties.Is anything wrong with the query.

---

dtproperties is a table used by SQL Server to store diagram information. In some versions of SQL Server it is marked as a user table (rather than a system table) and so will get returned by queries that look for user table.

Maybe just filter it out with something like this:

string selectPrimaryKeys = @"SELECT 
                                       TABLE_NAME 
                                   FROM
                                       INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                                  WHERE 
                                       CONSTRAINT_TYPE = 'PRIMARY KEY'
                                       AND TABLE_NAME <> 'dtproperties'
                               ORDER BY 
                                       TABLE_NAME";

---

Although primary keys are technically part of tables and not databases, assuming your query is correct, try this for your C# code. It has a few memory and performance improvements, as well as it would catch connection exceptions as well.

public void GetPrimaryKeyTable() {
    //An instance of the connection string is created to manage the contents of the connection string.

    var sqlConnection = new SqlConnectionStringBuilder();
    sqlConnection.DataSource = "192.168.10.3";
    sqlConnection.UserID = "gp";
    sqlConnection.Password = "gp";
    sqlConnection.InitialCatalog = Convert.ToString(cmbDatabases.SelectedValue);

    // Automatically close the connection
    using(SqlConnection sConnection = new SqlConnection(sqlConnection.ConnectionString)) {
        try {
            sConnection.Open();

            //Query to select the table_names that have PRIMARY_KEYS.
            string selectPrimaryKeys = @"SELECT TABLE_NAME 
                                        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                                        WHERE   CONSTRAINT_TYPE = 'PRIMARY KEY'
                                        ORDER   BY TABLE_NAME";

            //Create the command object
            using(SqlCommand sCommand = new SqlCommand(selectPrimaryKeys, sConnection)) {
                // Bind the combobox without destroying the data reader object after binding (no using statement)
                cmbResults.DisplayMember = "TABLE_NAME";
                cmbResults.ValueMember = "TABLE_NAME";  
                cmbResults.DataSource sReader = sCommand.ExecuteReader();
                cmbResults.DataBind();
            }
        }
        catch(Exception ex) {
            // All the exceptions are handled and written in the EventLog.
            EventLog log = new EventLog("Application");
            log.Source = "MFDBAnalyser";
            log.WriteEntry(ex.Message);
        }
        finally {
            // Read somewhere that the using statement takes care of this for you
            // but just in case
            if(sConnection.State != ConnectionState.Closed) {
                sConnection.Close();
            }
        }
    }
}

As for your query, in SQL Server 2008 R2, it returns a list of tables that have primary keys (each table in the list is a table with a primary key). What version of SQL Server are you using?

EDIT: If you want your query to only return USER tables and filter out things like system tables, try this query:

SELECT  tc.TABLE_NAME 
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER   JOIN sysobjects so
        ON tc.TABLE_NAME = so.name
WHERE   tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND so.xtype = 'U'
ORDER   BY TABLE_NAME;