How can I get each row from database and add it to table

Question

1.00/5 (1 vote)

See more:

string qamm = "Guide,Transport";
         ArrayList list2 = new ArrayList();
         for (int k = 0; k < qamm.Split(',').Length; k++)
         {
             string rooo = "";
             rooo = qamm.Split(',')[k];

             list2.Add(rooo);
         }



         d123.CONNECTER();
         for (int e =0;e<list2.Count;e++) {

         d123.dap = new SqlDataAdapter("select * from  whats_included where Title_of_whats_in =" + "'" + list2[e].ToString() + "'", d123.con);
         d123.dap.Fill(d123.dt);


         }

         d123.DECONNECTER();

What I have tried:

it just return the first row from data base

Posted 4-May-20 13:18pm

Member 14626494

Updated 4-May-20 14:35pm

Add a Solution

2 solutions

Solution 1

The first thing I can see in your code is an SQL Injection Vulnerability
! NEVER EVER ! should you be create a query concatenating commands and variables together.

The proper way to do that section of code is by using an Sql Parameter[^]

This is a rough version of what it could look like, just the simple addition of 1 line within that block.

C#

d123.dap = new SqlDataAdapter("SELECT * FROM whats_included WHERE Title_of_whats_in = @title", d123.con);
d123.dap.SelectCommand.Parameters.AddWithValue("@title", list2[e].ToString());
d123.dap.Fill(d123.dt);

Reference:
SqlParameter Class (System.Data.SqlClient) | Microsoft Docs[^]

As for the problem you are experiencing; without seeing the data that is coming in or in the table makes it hard to pinpoint.
What I would recommend would be to set a Breakpoint on that method and run the application in Debug mode so that you can see what the values going into that command are.

Posted 4-May-20 14:33pm

MadMyche

Comments

Maciej Los 5-May-20 9:04am

5ed!

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)

phil.o · Accepted Answer · 2020-05-04T14:36:00

First part: there is a much simpler way:

C#

string qamm = "Guide,Transport";
var list = new List<string>(qamm.Split(','));

Only two lines, and the Split() method is called only once.
As a general rule, you don't want to call this kind of method in a loop when you can do it only once and store its result in a variable.

Second part: I'm having trouble to see what you want to get here. The content of the datatable will be replaced by each subsequent call to Fill. Moreover, you must not build SQL queries by concatenating strings, because you leave your application open to SQL injection attacks. Better used parametrized queries instead. You should also avoid SELECT * as much as possible, but rather clearly specify the columns which you want. Also, you should give you variables some meaningful names, and stay consistent with the casing of method names. You should also use disposable objects (connections, commands) in using blocks, to properly free resource intensive objects when you don't need them anymore.

C#

d123.CONNECTER();
using (SqlCommand cmd = new SqlCommand())
{
   cmd.Connection = d123.con;
   // Build command string
   var builder = new StringBuilder("SELECT * FROM whats_included WHERE Title_of_whats_in IN (");
   for (int i = 0; i < list.Count; ++i)
   {
      builder.Append($"@cat{i},");
   }
   builder.Remove(builder.Length - 1, 1); //Remove the last comma
   builder.Append(")");
   cmd.CommandText = builder.ToString();
   // Add parameters
   for (int i = 0; i < list.Count; ++i)
   {
      cmd.Parameters.Add($"@cat{i}", SqlDbType.NVarChar).Value = list[i];
   }
   // Create the adapter using prepared command
   d123.dap = new SqlDataAdapter(cmd);
   // Fill the table
   d123.dap.Fill(d123.dt);
}
d123.DECONNECTER();

This code should fill your table with rows filtered by the values in the original qamm string.