How to retrive multiple records from database to gridview

Question

0.00/5 (No votes)

See more:

I created button on Frontend. Button Name: Show Records

I have two tables created in Database

First Table Name "A" in this table "A" ID is PrimaryKey
Second Table Name: "B" in this "B" table User_Id is first table of PrimaryKey in second table Foreignkey

if A table ID = B Table User_Id is same.

(above condition i created StoredProcedure using JOIN Clause)
Stored ProcedureName: SP_GetDetails. @userid 1
after execute StoreProcedure.
12 records show in SQL Database.

based on above stored Procedure, when user click on Show Records Button, show 12 records in gridView

What I have tried:

C#

private void ShowRcords_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=\\SQLEXPRESS;Database=CSharpUserLogin; Integrated Security=true");
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_GetUserDetail", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@userId", SqlDbType.VarChar,50).Value = txtId.Text);
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataTable dt = new DataTable();
            sda.Fill(dt);
            BindingSource bs = new BindingSource();
            bs.DataSource = dt;
            dgvEmployee.DataSource = bs;
            sda.Update(dt);

Posted 30-Dec-21 17:39pm

2011999

Updated 31-Dec-21 3:49am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 31-Dec-21 9:51am

This looks like are post of
https://www.codeproject.com/Questions/5321154/How-to-call-stored-PROCEDURE-in-Csharp-code

1 solution

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)

#realJSOP · Answer 1 · 2021-12-30T23:46:00

0) My suspicion is that the datatable you're binding to goes out of scope when the method exits and becomes null, thus forcing the bound control to display no rows.

1) Calling sda.Update is not necessary if you're just retrieving data.

2) I never bind directly to a DataTable. Instead use the MVVM pattern to make binding less messy and more convenient.

3) Do the binding in the XAML.

4) Are you sure your sql parameter is really a string, and not an integer? That's highly weird.

5) My method would look something like this:

C#

SqlConnection con = null;
SqlCommand    cmd = null;
string text = "1234";
DataTable     dt  = new DataTable();
try
{
    using (con = new SqlConnection("Data Source=\\SQLEXPRESS;Database=CSharpUserLogin; Integrated Security=true"))
    {
        using (cmd = new SqlCommand("sp_GetUserDetail", con){ CommandType=CommandType.StoredProcedure })
        {
            cmd.Parameters.Add(new SqlParameter("@userId", text));
            SqlDataAdapter sda = new SqlDataAdapter(){ SelectCommand = cmd };
            con.Open();
            sda.Fill(dt);
        }
    }
}
catch (Exception ex)
{
    // do something with exception
}