How to populate gridview with a custom sql query instead of selecting datasource and table

1 Answer 346 Views
GridView
Alexis
Top achievements
Rank 1
Iron
Iron
Alexis asked on 28 Nov 2023, 12:53 PM

Any way to fill datagrid with custom sql query? As i need to join 2 tables or you recommend me using a view or sp?

thank you

1 Answer, 1 is accepted

Sort by
1
Accepted
Dinko | Tech Support Engineer
Telerik team
answered on 29 Nov 2023, 12:28 PM

Hi Alexis,

If I have correctly understood your scenario, you want to create a custom query with the results you want to show in the RadGridView control. For your convenience, I have created a sample code to demonstrate how you can join two tables and show the result in the RadGridView. You can update the connection string with your database name and modify the SQL query to reflect the table names in your database.

public partial class Form1 : Form
{
    private string connectionString = "Server=(local);Database=Retail_POS_System;Trusted_Connection=True;";
    SqlConnection sqlConnection;
    SqlCommand sqlCommand;
    SqlDataReader sqlDataReader;
    SqlDataAdapter sqlDataAdapter;
    DataSet ds;
    public Form1()
    {
        InitializeComponent();
        this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill;
        sqlConnection = new SqlConnection(connectionString);
        sqlConnection.Close();
        sqlConnection.Open();
        sqlCommand = new SqlCommand("Select POSDeviceDefination.NAME, POSDeviceType.DeviceName From POSDeviceDefination INNER JOIN POSDeviceType On POSDeviceDefination.TYPE = POSDeviceType.DeviceID", sqlConnection);           
        sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

        if (sqlDataReader.HasRows)
        {
            ds = new DataSet();
            sqlDataReader.Close();
            sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            sqlDataAdapter.Fill(ds);
            DataTable table = ds.Tables[0];
            this.radGridView1.DataSource = table;
        }

        sqlConnection.Close();
    }
}

Here is the result:

I hope that this is what you are looking for.

Regards,
Dinko | Tech Support Engineer
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Alexis
Top achievements
Rank 1
Iron
Iron
commented on 29 Nov 2023, 01:54 PM

that works but you recommend having the query inside code? Or its best to use data wizard and use de framework to just select table, view etc from sql db?
Dinko | Tech Support Engineer
Telerik team
commented on 29 Nov 2023, 02:04 PM

Hi Alexis,

It depends on the case. Probably creating a Store Procedure (SP) and then binding the RadGridView to its result will be cleaner. Bascically, SP potentially could lead to better performance compared to SQL in code. If you have an option of creating SP, I would recommend this approach and bind the RadGridView to it using the Data Source Configuration Wizard.

Alexis
Top achievements
Rank 1
Iron
Iron
commented on 29 Nov 2023, 02:06 PM

thank you for the tip. Yeah i was between sp and a view not sure which is faster as i got just 200k recorda but client need the best performance possible.
Alexis
Top achievements
Rank 1
Iron
Iron
commented on 29 Nov 2023, 02:08 PM

as my query have left join 3 tables i can use sp ? Or just a view
Dinko | Tech Support Engineer
Telerik team
commented on 29 Nov 2023, 02:24 PM

The Views don't store data themselves. They are just saved queries that fetch data dynamically when queried. While SP are precompiled and optimized, which can enhance performance as compared to executing individual SQL statements. In many scenarios, using both can be beneficial. For instance, a view can be based on a complex query that fetches the required data, and a stored procedure can utilize that view to perform further manipulations or operations. The choice between them depends on your specific application requirements.
Tags
GridView
Asked by
Alexis
Top achievements
Rank 1
Iron
Iron
Answers by
Dinko | Tech Support Engineer
Telerik team
Share this question
or