How to add progammatically data from database to datagridview in C#

Question

1.00/5 (1 vote)

See more:

hi guys!

i want load data in datagridview from database in c#

for example i have this query:

SQL

Select sale.SaleID,item.ItemID, invoice.InvoiceID, Item.ItemName as item,  
            invoice.Qty ,invoice.SalePrice
	    as [Sale Price] FROM invoice
            JOIN item ON invoice.ItemID = item.ItemID
            JOIN sale ON invoice.SaleID = sale.SaleID
            where sale.SaleID = '" + textBox1.Text + "'";

now i want to load through sqldataReader not by sqldataAdapter

actually i want that first:
i make each column in datagridview
and then load the respective data against each column in datagridview from database in c#

What I have tried:

i tried a lot but don't find something beautiful

Posted 7-Feb-19 2:52am

Member 13985914

Updated 7-Feb-19 3:45am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2019-02-07T03:16:00

Firstly, you should not use string concatenation to build SQL queries; see http://bobby-tables.com/about.html[^] for reasons why not. Use properly formatted parameterised queries, with validated user input.

Secondly, you can copy items to your datagridview just by looping through the datareader returned rows, and adding each field to the relevant column in a new datagridview row. All you need in the datagridview at the beginning is the correct number, and type, of columns. So the process is:

Create the DataGridView
Add the Columns
For Each record returned by the SQLDataReader
    Create a new DataGridRow
    For each item in the SQL record
        Copy the item into the appropriate column in the row
    End For
    Add the new row to the DataGridView
End For

Maciej Los · Answer 2 · 2019-02-07T03:45:00

Solution 2

Quote:
now i want to load through sqldataReader not by sqldataAdapter

actually i want that first:
i make each column in datagridview

Why to force doors wide open? You should use a DataGridView functionality instead creating columns manually!

C#

//before you use below code change AutoGenerateColumns property of DataGridView to true!

DataTable dt = new DataTable();
string sSqlConn = "enter connection details here";
using (SqlConnection connection = new SqlConnection(sSqlConn))
{
    string sql = @"SELECT * FROM YourTable";
    connection.Open();
    
    using (SqlCommand command = new SqlCommand(sql, connection))
    using (SqlDataReader reader = command.ExecuteReader())
    {
        dt.Load(reader); //load data into datatable!	
    }
}

this.dataGridView1.DataSource = dt;

That's all!

Posted 7-Feb-19 3:45am

Maciej Los

Updated 7-Feb-19 11:07am

v2

Comments

Richard Deeming 7-Feb-19 17:10pm

There were a couple of mistakes in your code:

* SqlCommand.ExecuteReader doesn't return an OleDbDataReader;
* You can't call command.Dispose from outside of the using block where command is defined;
* You don't need to call command.Dispose when it's wrapped in a using block;
* You can wrap the SqlDataReader in a using block, so that you don't need to call Close or Dispose on it;

I've taken the liberty of fixing those for you. :)

Maciej Los 7-Feb-19 17:14pm

Thank you, Richard.
:beer:

Member 13985914 9-Feb-19 5:41am

actually i want that i have data in my database and i want to load data in my datagridview column but i'll show the column in the datagridview... i make the following columns in the datagridview in the design view.
Sale.SaleID, item.ItemID, invoice.InvoiceID, Item.ItemName ,invoice.Qty ,invoice.SalePrice and one empty column..

Maciej Los 9-Feb-19 8:14am

Please, read my answer carefully and step through the instruction.