How to find primary key(s) of the table in database C#

Question

Hi I am working i C# window form and mySQL database, I was able to connect to the database, retrieve data and display it in DataGridView.

Now if user click to a row in the DataGridView and press Delete button, I want that selected row to be deleted in database.

Problem is I need to know which column(s) is primary key(s) in order to delete it from database by using this SQL query:

DELETE FROM (selected table) WHERE (Primary column's name(s) = value(s) of primary key column(s) of selected row)

the Bold part is where I am stuck at, can some body help me out or know how other way to delete the selected row in database?

Check this one http://msdn.microsoft.com/en-us/library/system.data.datatable.primarykey.aspx may be it will help you :) — andy, Feb 18 '13 at 04:58
@andy I spent few hours and also looked at what you gave me - the Data table with Primary Key Properties but still can't figure out. Help me! — Ronaldinho Learn Coding, Feb 18 '13 at 05:04

Core · Answer 1 · 2013-02-18T07:27:14.047

4

You can use sql query to get primary keys from a table SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

edited Feb 18 '13 at 07:27

answered Feb 18 '13 at 05:04

Core

601
2
7
19

I tried to execute this query in mySQL and it does returns information of primary key column(s), I wonder how it look like (what are return value and return type) of it in C# because it need a variable to store it for later using. – Ronaldinho Learn Coding Feb 18 '13 at 05:20
@RonaldinhoState, the query returns the data in table format and you can get primary keys in a table from **Column_name**. – Core Feb 22 '13 at 14:05

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

If you do not know ahead of time what the primary key column is, then you need to examine the table's information to find it. The answers in this question should give you a clue on how to proceed with MySQL, using the SHOW INDEX MySQL call which is documented here.

edited May 23 '17 at 12:12

Community

1
1

answered Feb 18 '13 at 05:00

Corey

15,524
2
35
68

I tried to execute this query: SHOW INDEX FROM tableName WHERE Key_name = 'PRIMARY'; in mySQL and it does returns information of primary key column(s), I wonder how it look like (what are return value and return type) of it in C# because it need a variable to store it for later using. – Ronaldinho Learn Coding Feb 18 '13 at 05:21
The results of the call will probably be a block of text, not a recordset. I don't have a MySQL server to hand to test on, sorry. – Corey Feb 18 '13 at 06:04

score 1 · Answer 3 · answered Feb 18 '13 at 04:54

1

You can assign the value of the Primary Key in the datagridview in another column (hidden) so that when the Delete() function is called, you can get the value of the primary key:

Int64 iRecordId =0;
int selectedIndex = 0;

if (datagridview1.SelectedRows.Count > 0)
{
   selectedIndex = datagridview1.SelectedRows[0].Index;
   iRecordId = Convert.toInt64(datagridview1.Rows[selectedIndex].Cells[0].Value.toString());
   // insert delete commands heere
}

Assuming the first column is the primary key.

answered Feb 18 '13 at 04:54

whastupduck

1,156
11
25

what you mean by "assuming first column is the primary key", it may has more than one column as primary key or the primary key is not in 1st column, that why I need to find it. – Ronaldinho Learn Coding Feb 18 '13 at 04:57
@RonaldinhoState The index of the Cells in this line `datagridview1.Rows[selectedIndex].Cells[0].Value.toString()` assumes the index of the primary column is the first one. – whastupduck Feb 18 '13 at 04:58
@RonaldinhoState, he want to know which column is primary key column. – andy Feb 18 '13 at 04:59
@GianAcuna So it will not good if 2 or more columns act as primary key, also sometime users don't define primary key in first column – Ronaldinho Learn Coding Feb 18 '13 at 05:00
@andy I don't know which column(s) is primary key since different tables have different contents (See my GUI on the picture). – Ronaldinho Learn Coding Feb 18 '13 at 05:03
Hmm, so you want to delete records depending on the table selected? If that is the case, you would need to get the primary column/s of the table selected before executing your delete command. – whastupduck Feb 18 '13 at 05:06
@GianAcuna Yes I need to locate which column(s) of table is primary key then the rest would be easy. – Ronaldinho Learn Coding Feb 18 '13 at 05:09
You can refer to @Corey 's answer or UnKnow 's answer – whastupduck Feb 18 '13 at 05:10
@GianAcuna What return value in C# if I execute the query: SHOW INDEX FROM orderline WHERE Key_name = 'PRIMARY'; ? – Ronaldinho Learn Coding Feb 18 '13 at 05:22
@RonaldinhoState Refer to this article http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html . You can return the column name as a string. – whastupduck Feb 18 '13 at 06:43

How to find primary key(s) of the table in database C#

3 Answers3