none
DataTable - Find primary key column RRS feed

  • Question

  • Hi,

      Primary key column in DataTable. We are retrieving a table from SQL server, it has a primary key column. We are using the below code to find which is the primary key column in DT.

            SqlDataReader dr = sqlcmd.ExecuteReader();
            DataTable schemaTable = dr.GetSchemaTable();
           
            DataColumn[] columns = new DataColumn[1];
            columns[0] = schemaTable.Columns[0];      
            schemaTable.PrimaryKey = columns;

    But, It shows " The Table doesn't have primary key column ". Alternative, we using the Find() method in Datatable rows, it also shows the same message.

     DataRow drw = schemaTable.Rows.Find("primaryKeyValue");

    So, How can we find the primary key column from Datable?

    Kindly, post solutions for this.

    Thanks,

    Prabakaran G

    Friday, July 27, 2012 7:16 AM

Answers

  • Hi, Sezhiyan.

        Thank you for your response . Finally I got output.

           In DataRow class have the ItemArray property, in this property 6 th element is (object)System.boolean primary key value.

              DataTable schemaTable = dr.GetSchemaTable();

              DataRow dtrw = schemaTable.Rows[0]   // Using loop you can count the rows.

              bool IsprimaryKey = (bool)dtrw.ItemArray[6];

              If( ISprimaryKey == true)

               {

                  Console.WriteLine("This Column has primary key ");

               }

    Hopes this should work.

    Thanks,

    Prabakaran G.

    Maruti Computers P Ltd.



    Friday, July 27, 2012 1:06 PM

All replies

  • You seem to be setting the PrimaryKey property of a datatable. Instead you need to be querying the PrimaryKey property of the source table and the resulting column gives you the primary key column. Check http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx for code example as well.
    Friday, July 27, 2012 8:02 AM
  • Hi, Sezhiyan

          Thank you for your response. Yeah,the above code is wrong, I assigned the primary key.

    But, I used what you replied.

                 DataColumn[] columns ;

                columns = schemaTable.PrimaryKey;
                int ColumnLength = columns.Length;

    In this, It shows "Columns{System.Data.DataColumn[0]}" nothing in this and length is 0. But, Again I assured that this table have primary key for the first column. This is code is not working, any alternative please.

    Thanks,

    Prabakaran G

    Maruti Computers P Ltd.

    Friday, July 27, 2012 8:57 AM
  • Hi Govind,

    Can you set PrimaryKey column in your datatable as below

    sourceTable.PrimaryKey = new DataColumn[] {sourceTable.Columns["PrimaryKeyColumn"]};

    This should work for you.

    Regards,

    Sezhiyan

    Friday, July 27, 2012 10:31 AM
  • Hi, Sezhiyan.

        Thank you for your response . Finally I got output.

           In DataRow class have the ItemArray property, in this property 6 th element is (object)System.boolean primary key value.

              DataTable schemaTable = dr.GetSchemaTable();

              DataRow dtrw = schemaTable.Rows[0]   // Using loop you can count the rows.

              bool IsprimaryKey = (bool)dtrw.ItemArray[6];

              If( ISprimaryKey == true)

               {

                  Console.WriteLine("This Column has primary key ");

               }

    Hopes this should work.

    Thanks,

    Prabakaran G.

    Maruti Computers P Ltd.



    Friday, July 27, 2012 1:06 PM