CONCAT 3 filed in SQL and in C#

Question

1.00/5 (1 vote)

See more:

I am contacting 3 table field using

CONCAT

they are showing single filed when running query in sql server management studio.

but they are showing different columns when using in C#

I want "FirstName + ,MiddleName + LastName, EmailAddress" in single field so that
i can assign it to DataGridViewComboBoxCell.displaymember

What I have tried:

C#

<pre>
String query3 = "Select CONCAT(CONCAT (FirstName ,' ',MiddleName , ' ' , LastName), ',',EmailAddress ) As Contacts ";

Query2 = query3 + "From Contacts ";
Query2 = Query2 + "WHERE  CompanyID=";
Query2 = Query2 + CompanyID;
Query2 = Query2 + "And (IsDelete Is null Or IsDelete = 0 )";


Here final query be like
/*
Select CONCAT(CONCAT (FirstName ,' ',MiddleName , ' ' , LastName), ',',EmailAddress ) As Contact
From Contacts WHERE  CompanyID=4000 And (IsDelete Is null Or IsDelete = 0 )"
*/

DataSet ContactDS = new DataSet();
ContactDS = StMethod.GetListDS<ManagerSetColumn2>(Query2);
               
DataGridViewComboBoxCell ContactCheckCmb = new DataGridViewComboBoxCell();
ContactCheckCmb.DataSource = ContactDS.Tables[0];


// here it is showing all filed as seprate one like FirstName,LastName,MiddleName,EmailAddress,Contacts

 foreach (DataColumn column in ContactDS.Tables[0].Columns)
{
	MessageBox.Show(column.ColumnName);
}


I want "FirstName + ,MiddleName +  LastName, EmailAddress" in single field so that
i can assign it to DataGridViewComboBoxCell.displaymember

Posted 2-Sep-21 18:46pm

Devendra Sarang

Updated 2-Sep-21 21:10pm

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)

Wendelius · Answer 1 · 2021-09-02T19:19:00

Since the query returns correct results and seems fine, the problem is most likely in method GetListDS. Using the debugger, go through the code, does it for example split a column if comma is found in the data...

Another observation, why do you use two separate concat functions, wouldn't only one suffice?

Third and perhaps the most important observation, never concatenate values directly to SQL statement. This leaves you open to SQL injection and other problems. Instead, use parameters. For examples, have a look at Properly executing database operations[^]

Maciej Los · Answer 2 · 2021-09-02T21:10:00

Your code is SQL Injection[^] vulnerable.

Never use concatenated string! Use parameterized queries[^] instead.

For example:

C#

string sConStr = @"your_connection_string_here!";
DataTable dt = new DataTable();

//1. create SqlConnection
using(SqlConnection connection  = new SqlConnection(sConStr))
{
    //open connection
    connection.Open()
    //set command text
    string sql = @"SELECT * FROM TableName WHERE TextField Like @SomeString;";
    //2. create command
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@SomeString", "%whatever%")
        //3. create reader
        using (SqlDataReader reader = command.ExecuteReader())
        {
            //4. load data into datatable
            dt.Load(reader)
        }
    }
}

For further details, please read this:
Data Security: Stop SQL Injection Attacks Before They Stop You | Microsoft Docs[^]

So, your final query should looks like:

C#

string query3 = @"Select CONCAT (FirstName, ' ', MiddleName, ' ', LastName, ', ', EmailAddress ) As ContactDetails 
From Contacts 
WHERE  CompanyID=@CompanyID And (IsDelete Is null Or IsDelete = 0)";

Then you have to pass @CompanyID parameter ;)

Good luck!

CONCAT 3 filed in SQL and in C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0