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;
精彩评论