how to use complex query to obtain data from sql server and bind to datagrid in c# app

Question

I have a SQl Server database which I have linked to a C# application. The data is bound to a datagridview in the application. This works fine for a simple select from one table command, as shown in the code below. However, I would like the datagridview to show data from more than one table based on a more complex query, but when I try to replace the command below with the complicated query, I get lots of errors:

SELECT DISTINCT f_name, l_name, grade, mentor_name
  FROM [Person] P, [Exam] E, [Mentor] M
 WHERE P.Person_id = E.Person_id
   AND P.Person_id = M.Person_id

I'm open to suggestions as to other ways of representing my data, if anyone has any.

public partial class Form1 : Form {

    private BindingSource bindingSource1 = new BindingSource();

    public Form1() {
        InitializeComponent();

        dataGridView1.Dock = DockStyle.Fill;
    }

    private void Form1_Load(object sender, EventArgs e) {

        dataGridView1.DataSource = bindingSource1;
        GetData("SELECT * from Person");
    }

    private void GetData(string command) {

        String connectionString = "Data Source=MY-PC\\USER;Initial Catalog=MyDatabase;Integrated Security=True";
        SqlDataAdapter dataAdapter = new SqlDataAdapter(command, connectionString);

        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(table);
        bindingSource1.DataSource = table;

        dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
    }
}

Thanks.

You've mentioned 'lots of errors'; can you show us what they are? — Adrian Wragg, Jul 16 '13 at 09:01
Without diving into your query, have you though about using an ORM for your app, NHibernate or Entity Framework? Not having to write your CRUDs makes life better! — Noich, Jul 16 '13 at 09:02
@AdrianWragg- errors include: "; expected", "Cannot use local variable M" before it is declared", "Invalid expression term ','", "Newline in constant", "Only assignment, call increment, decrement and new object expressions can be used as a statement", "The name Mentor does not exist in the current context", "The type or namespace name AND could not be found"-----That's just a selection, it throws up the same eroor for each part of the sql statement — bookthief, Jul 16 '13 at 09:04
@Noich Im pretty new to programming, so I'm afraid I dont know what you mean — bookthief, Jul 16 '13 at 09:08
I suggest you google a bit about NHibernate and read the tutorials. It's quite straight forward. The time you spend learning will pay off when you don't have queries like that to debug. — Noich, Jul 16 '13 at 09:16
@user2546029 +1 for the ORM idea. Even if you don't want to go down that route at this stage, it's generally considered bad practice to have 'raw' SQL within your code. As a minimum, look to put all relevant SQL in a stored procedure and call that instead (yes even though you're only doing a select here). This will help you separate the two layers. — Nick, Jul 16 '13 at 09:23
@Nick I've been looking into stored procedures, can you tell me generally how you would go about using one of these? — bookthief, Jul 16 '13 at 10:07
Good example here: http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-from-c-sharp-program — Nick, Jul 16 '13 at 10:09

score 1 · Answer 1 · answered Jul 16 '13 at 10:39

IF you able bind data in grid then something wrong with your SQL Query, Check out your query in SQL Server Management studio. It seem like two column name same in table.

Update your selected column name with there temporary table name i.e. :

SELECT distinct 
P.f_name, P.l_name, E.grade, mentor_name
FROM [Person] P, [Exam] E, [Mentor] M
WHERE P.Person_id = E.Person_id
AND P.Person_id = M.Person_id

score 0 · Accepted Answer · answered Jul 16 '13 at 09:09

First to improve your query we are going to use inner joins to fetch data

SELECT distinct P.f_name, P.l_name, E.grade, M.mentor_name
FROM [Person] P
INNER JOIN [Exam] E ON P.Person_id = E.Person_id
INNER JOIN [Mentor] M ON P.Person_id = M.Person_id

Your exceptions points that you are passing your query string to method in a wrong way and compiler is trying to interpret it as code , try this:

GetData("SELECT distinct P.f_name, P.l_name, E.grade, M.mentor_name FROM [Person] P INNER JOIN [Exam] E ON P.Person_id = E.Person_id INNER JOIN [Mentor] M ON P.Person_id = M.Person_id");

how to use complex query to obtain data from sql server and bind to datagrid in c# app

2 Answers2