how to detect primary key column in dataTable ?

Article
2009-11-30

Question

_{Monday, November 30, 2009 11:31 AM}

Hi
how to detect pk column(s) in dataTable ?
thankshttp://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

All replies (15)

_{Monday, November 30, 2009 9:15 PM ✅Answered}

Hi again
i found it!
we can get pk info in SqlCommand.ExecuteReader method as follow :

SqlDataReader dr = sqlCommand1.ExecuteReader(CommandBehavior.KeyInfo);

thanks.http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 11:53 AM}

Hi,

You can get the list of Primary Key columns by using DataTable.PrimaryKey properties as follow:

DataTable table = new DataTable();
                table.Columns.Add("ProductID", typeof(int));
                table.PrimaryKey = new DataColumn[]{table.Columns["ProductID"]} ;

                // Create the array for the columns.
                DataColumn[] columns;
                columns = table.PrimaryKey;

                // Get the number of elements in the array.
                Console.WriteLine("Column Count: " + columns.Length);
                for (int i = 0; i < columns.Length; i++)
                {
                    Console.WriteLine(columns[i].ColumnName + columns[i].DataType);
                }

Please also check the below link for more details:

http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspxHi, I am Kyi.

_{Monday, November 30, 2009 12:12 PM}

Hi,

You can get the list of Primary Key columns by using DataTable.PrimaryKey properties as follow:

DataTable table = new DataTable();
                table.Columns.Add("ProductID", typeof(int));
                table.PrimaryKey = new DataColumn[]{table.Columns["ProductID"]} ;

                // Create the array for the columns.
                DataColumn[] columns;
                columns = table.PrimaryKey;

                // Get the number of elements in the array.
                Console.WriteLine("Column Count: " + columns.Length);
                for (int i = 0; i < columns.Length; i++)
                {
                    Console.WriteLine(columns[i].ColumnName + columns[i].DataType);
                }

Please also check the below link for more details:

http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx

Hi, I am Kyi.

Thanks Kyi
I tried this, but it give me array of dataColumns with 0 length!
here is my code :

            DataColumn[] pkColumns = this.dataSourceEx.DataSource.Tables[0].PrimaryKey;
            foreach(DataRow row in this.bcResult)
            {
                
            }

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 12:19 PM}

Here is the MSDN link:

DataTable.PrimaryKey Property :

http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx

Sample:

private void GetPrimaryKeys(DataTable table)
{
    // Create the array for the columns.
    DataColumn[] columns;
    columns = table.PrimaryKey;

    // Get the number of elements in the array.
    Console.WriteLine("Column Count: " + columns.Length);
    for(int i = 0; i < columns.Length; i++)
    {
        Console.WriteLine(columns[i].ColumnName + columns[i].DataType);
    }
}

private void SetPrimaryKeys()
{
    // Create a new DataTable and set two DataColumn objects as primary keys.
    DataTable table = new DataTable();
    DataColumn[] keys = new DataColumn[1];
    DataColumn column;

    // Create column 1.
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName= "FirstName";

// Add the column to the DataTable.Columns collection.
table.Columns.Add(column);

// Add the column to the array.
keys[0] = column;

    // Create column 2 and add it to the array.
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "LastName";
    table.Columns.Add(column);

// Add the column to the array.
keys[1] = column;

// Set the PrimaryKeys property to the array.
table.PrimaryKey = keys;
}

Regards,
Jai

_{Monday, November 30, 2009 12:25 PM}

Here is the MSDN link:

DataTable.PrimaryKey Property :

http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx

Sample:

private void GetPrimaryKeys(DataTable table)
{
    // Create the array for the columns.
    DataColumn[] columns;
    columns = table.PrimaryKey;

    // Get the number of elements in the array.
    Console.WriteLine("Column Count: " + columns.Length);
    for(int i = 0; i < columns.Length; i++)
    {
        Console.WriteLine(columns[i].ColumnName + columns[i].DataType);
    }
}

private void SetPrimaryKeys()
{
    // Create a new DataTable and set two DataColumn objects as primary keys.
    DataTable table = new DataTable();
    DataColumn[] keys = new DataColumn[1];
    DataColumn column;

    // Create column 1.
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName= "FirstName";

    // Add the column to the DataTable.Columns collection.
    table.Columns.Add(column);

    // Add the column to the array.
    keys[0] = column;

    // Create column 2 and add it to the array.
    column = new DataColumn();
    column.DataType = System.Type.GetType("System.String");
    column.ColumnName = "LastName";
    table.Columns.Add(column);

    // Add the column to the array.
    keys[1] = column;

    // Set the PrimaryKeys property to the array.
    table.PrimaryKey = keys;
}

Regards,
Jai

Hi Jai
as i told in my earlier post, i tried this but it give me an array of dataColumns with 0 length.http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 12:35 PM}

Hi,

Do you have any Primary Column in your DB Table.........? Check that first.......if exist then above posts should work.............Nagarjuna Dilip

_{Monday, November 30, 2009 12:48 PM}

Hi,

Are you dynamically creating your DataTable or retrieving from Database? Your table should have at least one column assigned as Primary Key. If not, PrimaryKey properties will return 0 length for datacolumn array.Hi, I am Kyi.

_{Monday, November 30, 2009 2:48 PM}

Hi,

Are you dynamically creating your DataTable or retrieving from Database? Your table should have at least one column assigned as Primary Key. If not, PrimaryKey properties will return 0 length for datacolumn array.

Hi, I am Kyi.

Hi
yes, i create dataTable dynamically. i have a simple query to get data from Northwind.Customers table.
here is my code :

public int ExecuteQuery(string connectionString,string query, out DataSourceEx dataSourceEx, out string msgResult)
        {
            int result = 0;            
            dataSourceEx = new DataSourceEx();

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = query;
                dataSourceEx.Adapter = new SqlDataAdapter(cmd);
                dataSourceEx.DataSource = new DataSet();
                try
                {
                    dataSourceEx.Adapter.Fill(dataSourceEx.DataSource);
                    result = dataSourceEx.DataSource.Tables[0].Rows.Count;
                    msgResult = string.Format("{0} row(s) affected.", result);
                }                
                catch (Exception ex)
                {
                    result = -1;
                    StringBuilder sb = new StringBuilder();                    
                    if (ex.GetType() == typeof(SqlException))
                    {
                        SqlException sqe = null;
                        if (ex.InnerException != null)
                            sqe = ex.InnerException as SqlException;
                        else
                            sqe = ex as SqlException;
                        sb.AppendLine(string.Format("Msg {0}, Level {1}, State {2}, Line {3} :", sqe.Number, sqe.Class, sqe.State, sqe.LineNumber));
                        sb.AppendLine(sqe.Message);
                        msgResult = sb.ToString();
                    }
                    else
                    {                        
                        sb.AppendLine("Error :");
                        sb.AppendLine(ex.Message);
                        msgResult = sb.ToString();
                    }
                }                
            }
            return result;
        }

and here is my custom class to use in above method :

public class DataSourceEx
    {
        public DbDataAdapter Adapter { get; set; }
        public DataSet DataSource { get; set; }
    }

regards

http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 2:55 PM}

No where are you setting the primary key here. You are merely populating a DataSet.

See
http://bytes.com/topic/c-sharp/answers/484295-set-primarykey-datatable

After you fill the DataSet, set the primary key.

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Monday, November 30, 2009 4:13 PM}

No where are you setting the primary key here. You are merely populating a DataSet.

See
http://bytes.com/topic/c-sharp/answers/484295-set-primarykey-datatable

After you fill the DataSet, set the primary key.

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

thanks john
but, actually i don't know which columns is pk, because it's dynamic query, it means that end-user can connect to any database and can get data from anyWhere! hence we can not set primary key in your approach.http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 6:04 PM}

DataTable.PrimaryKey gets/sets the primary key.

To find out which one is the primarykey, simple do this:

//First fill your DataSet, then do something like this:
DataColumn[] primaryKey = ds.Customers.PrimaryKey;

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Monday, November 30, 2009 7:57 PM}

DataTable.PrimaryKey gets/sets the primary key.

To find out which one is the primarykey, simple do this:

//First fill your DataSet, then do something like this:
DataColumn[] primaryKey = ds.Customers.PrimaryKey;

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Thanks john
but, as i told in my second post, it give me an array of dataColumns with 0 length.
can u give me a practical example ?
thanks in advancehttp://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Monday, November 30, 2009 8:14 PM}

I think there is a misunderstanding. If you are pulling data from a database and filling a DataSet, it will not set a primary key, for it does not know what column(s) is a primarykey in this disconnected state until you "set" it.

It can't detect such things. For this has to be "set" in the disconnected world "after" you fill it. It does not "know" which column or columns are the primary key just from the database extrapolation.

I hope this is clear.John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

_{Monday, November 30, 2009 8:23 PM}

I think there is a misunderstanding. If you are pulling data from a database and filling a DataSet, it will not set a primary key, for it does not know what column(s) is a primarykey in this disconnected state until you "set" it.

It can't detect such things. For this has to be "set" in the disconnected world "after" you fill it. It does not "know" which column or columns are the primary key just from the database extrapolation.

I hope this is clear.

John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com

Yes john, it's disconnected mode. but i think there is a way to pull data from database with detectable pks.
can i pull data from database with pk detectable ?http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

_{Thursday, December 10, 2009 10:47 AM}

hi there,
I'd like to use the same snippet but for windows compact framework 3.5. and using SqlCeConnection.

even though I use the same code as stated on the microsoft page or here, the reader is not able to retrieve information on primary keys.

primarykeys are null or the column.IsKey Property retrieves DBNull?

Any idea how to retrieve information on primary keys of the table for mobile devices?
thanks

Share via

how to detect primary key column in dataTable ?

Question

All replies (15)

Additional resources