Retrieve data from SQL server based on existing data in column C#

Question

0.00/5 (No votes)

See more:

My program can read excel into datatable and there's a barcode column with data. I need to get the bookcode from sql server based on data in barcode column. How the sql query able to get each cell data from the barcode column? I am confused to get the data by column or row. I managed to get the sql connection and retrieve the general records so far. Hopefully you guys are able to assist me in this matter. thank you

What I have tried:

C#

public void filldatagridview(ExcelWorksheet workSheet)
        {
            DataTable dt = new DataTable();

            //Create the data column
            for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
            {
                dt.Columns.Add(col.ToString());
            }
           // for (int row = workSheet.Dimension.Start.Row; row <= workSheet.Dimension.End.Row; row++)
           for (int row = 12; row <= 26; row++)
            {
                DataRow newRow = dt.NewRow(); //Create a row
                int i = 0;
                for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
                {
                    newRow[i++] = workSheet.Cells[row, col].Text;
                }
                dt.Rows.Add(newRow);

            }

            //// dataGridView2.ColumnHeadersVisible = false; // dataGridView1.Rows.Remove(dataGridView1.Rows[12]);

            dt.Columns.RemoveAt(0); //remove No 
            dt.Columns.RemoveAt(0); //remove article
            
n            //Get BookCode
            SqlConnection conn = new SqlConnection("Server=10.0.0.10;Database=;UserID=;Trusted_Connection=False");
            string query = "SELECT TOP 14 BookCode FROM Inventory";            

            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            dataGridView2.DataSource = dt;

            conn.Close();
            da.Dispose();
}

Posted 23-Jan-19 14:59pm

Member 14127871

Updated 28-Jan-19 4:59am

MadMyche

v2

Add a Solution

Comments

Member 14127871 25-Jan-19 2:00am

UPDATE, I managed to get the Bookcode using below query
string query = "SELECT BookCode FROM Inventory WHERE Barcode='" + dt.Rows[1]["3"].ToString().Trim() + "'"; //3 is column for barcode

And now i think i have to do looping to get all the bookcode for all the barcode column data but I'm not sure with the sequence and sql connection

Richard Deeming 28-Jan-19 10:52am

Don't do it like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

3 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)

RickZeeland · Answer 1 · 2019-01-23T22:42:00

Solution 1

Use the SQL WHERE clause or IN clause, see: SQL IN Operator[^]

Before using the barcode value, you should first check if it is NULL:

var barcode = dt.Rows[0]["Barcode"];

if (barcode != NULL)
{
  // your code ...
}

Posted 23-Jan-19 22:42pm

RickZeeland

Updated 24-Jan-19 20:11pm

v2

Comments

Member 14127871 24-Jan-19 19:21pm

Like this? I'm not sure how to get the barcode column in ()

string query1 = "SELECT Bookcode FROM Inventory WHERE Barcode IN (Barcode column in datatable here?)";

I thought it something to do like foreach(DataRow dr in datatable.Rows) to get barcode?

RickZeeland 25-Jan-19 2:13am

See updated solution, you can use WHERE Barcode IN (1, 2, 3, ...)

Richard Deeming · Answer 2 · 2019-01-28T04:59:00

Solution 3

Something like this should work, and will avoid the SQL Injection vulnerability:

C#

using (SqlConnection conn = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand(null, conn))
{
    StringBuilder sb = new StringBuilder("SELECT BookCode FROM Inventory WHERE BarCode In (");
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        if (i != 0) sb.Append(", ");
        
        string name = "@P" + i;
        cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]);
        sb.Append(name);
    }
    sb.Append(")");
    
    cmd.CommandText = sb.ToString();
    
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(dt);
    
    dataGridView2.DataSource = dt;
}

Posted 28-Jan-19 4:59am

Richard Deeming

Updated 28-Jan-19 5:00am

v2

Comments

Member 14127871 28-Jan-19 19:58pm

Thank you I got it. May I ask why the data I receive doesnt follow the order in barcode column? And there supposedly a blank cell or null since theres a data in barcode that doesnt have the bookcode, how I am supposed to add the null value?

Richard Deeming 29-Jan-19 6:14am

You don't have an ORDER BY clause on your query, so the order of the results is not guaranteed.

If you want to exclude records without a BookCode, you'll need to add And BookCode Is Not Null to the WHERE clause.

Member 14127871 29-Jan-19 19:30pm

I did order by barcode and still the order doesnt match the bookcode with barcode. And I want if there's no bookcode that match with barcode in sql, it will write 'no bookcode found' in the cell. Not to exclude it. I really appreciate ur help thank you

Member 14127871 30-Jan-19 3:58am

Currently this is my code to get the 'no data' cell if no bookcode found but still Im not able to get.

StringBuilder sb = new StringBuilder("WITH cte AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer WHERE Barcode In (");

for (int i = 0; i < dt.Rows.Count; i++)
{
if (i != 0) sb.Append(",");

string name = "@P" + i;

cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]);
sb.Append(name);
}
//sb.Append(") ");
sb.Append(")) SELECT InvtID,BOOKCODE = coalesce(InvtID, 'nodata') FROM cte WHERE rid = 1 UNION SELECT InvtID='', BOOKCODE='nodata' WHERE NOT EXISTS (SELECT 1 FROM CTE)");

Ravi Gaur 11 · Answer 3 · 2019-01-27T23:59:00

Solution 2

create a comma seperated string of barcodes and

like

C#

string str = "1111, 11212, 2155";

this can be done by

C#

foreach(DataRow dr in dt.Rows)
{
  str += dr["barcode"].ToString() + ",";
}

use query

C#

string query = "SELECT TOP 14 BookCode FROM Inventory where barcode in ('"+ str + "')";

Posted 27-Jan-19 23:59pm

Ravi Gaur 11

Updated 28-Jan-19 0:33am

v2

Comments

Richard Deeming 28-Jan-19 10:46am

Not like that!

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Member 14127871 28-Jan-19 20:34pm

I tried ths method too. Same problem happened which the order doesnt follow the barcode column data and there no cell blank or Null as some of barcode have no bookcode.