remove duplicate column name before add it on DataTable

Here is my code :

private void executerRequete(string requete, string description, string niveauAlerte)
{
    RequetesSQLResult res = new RequetesSQLResult();
    res.Description = description;

    string StSQL = requete;
    res.Results = new DataTable();
    try
    {
        using (SqlConnection _oConnection = new SqlConnection(_dataService.ParamGlobaux.ConnectionString))
        {
            SqlCommand command = new SqlCommand(StSQL, _oConnection);
            command.Parameters.AddWithValue("@mat", _dataService.ParamGlobaux.StMatricule);
            command.Parameters.AddWithValue("@dd", dateDebut);
            command.Parameters.AddWithValue("@df", datefin);


            _oConnection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var row = res.Results.NewRow();
                    object[] values = new object[reader.FieldCount];
                    int fieldCount = reader.GetValues(values);

                    if (res.Results.Columns.Count == 0)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            res.Results.Columns.Add(new DataColumn(reader.GetName(i)));
                        }
                    }

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[i] = values[i];
                    }

                    res.Results.Rows.Add(row);
                }
            }
        }
    }
    catch (Exception x)
    {
        Debug.WriteLine(x.Message + ":" + x.StackTrace);
    }

    if (res.Results.Rows.Count > 0)
    {
        Results.Add(res);
    }
}

My problem is when the loop gets here :

res.Results.Columns.Add(new DataColumn(reader.GetName(i)));

Everything works fine as long as I use only one table. When I use two or more tables, I get an error message “column [i] already exists”.

Exemple :

SELECT * FROM Contrats X, Contrats Y
WHERE x.Avenant = 'CONTRAT' AND y.Avenant = 'CONTRAT' AND X.Matricule = Y.Matricule AND x.[Num Contrat] != y.[Num Contrat] AND
((x.[Date début] < y.[Date Fin] AND x.[Date Fin] > Y.[Date début]) OR
(x.[Date début] > y.[Date début] AND y.[Date Fin] IS NULL) OR
(x.[Date début] < y.[Date début] AND y.[Date Fin] IS NULL))
AND x.Matricule = @mat

=> error will be : “column ‘Name’ already exist”, because it is read twice (one per table).

How can i keep only one column if they have the same name ?

Answer

do you need both tables data?

if not – return only first table data

select X.* from ...

if yes – list all required fields in select, and give them unique names:

select X.Name as X_Name, Y.Name as Y_Name from ...