-3

I am using the following code to read from a SELECT Sql query and then fill my DataTable from it (dt is my DataTable, cs is my connection string and query is the Sql script):

using (OleDbConnection conn = new OleDbConnection(cs))
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand(query, conn);
    dt.Load(cmd.ExecuteReader());
    conn.Close();
}

It works fine and my DataTable will be filled with what my query returns. But I want my DataTable to contain only string (varchar) values regardless of what my query says. E.g. If my query says:

SELECT 1 AS ID, 'John' AS Name

Then the ID value of my Datatable will be an integer whereas I want everything to be string. How can I achieve this?

Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
disasterkid
  • 6,948
  • 25
  • 94
  • 179
  • Does it need to be a string in the `DataTable`, or does it need to be a string somewhere else such as when displayed in the UI? Taking a step back, what's the problem being solved here? – David Jan 08 '15 at 15:13
  • @David it needs to be string in the `DataTable`. The problem is that I do not want a `DataTable` with columns of type integer or anything else other than string. – disasterkid Jan 08 '15 at 15:15
  • 1
    In that case the most direct approach would be to convert everything to character data in the query so that the conversion is entirely transparent to the `DataTable`. I suspect there may be a better/cleaner way to solve the problem, but without knowing anything about the problem it's tough to say. As long as the "why" is "because I feel like it" then the "how" is going to be pretty vague and could easily cause other problems outside the scope of the question. – David Jan 08 '15 at 15:17
  • @David I never said "Because I feel like it". I just need my `DataTable` to be uniformly string because that's how I need my data to be. – disasterkid Jan 08 '15 at 15:37
  • You need your data to be a string because you need your data to be a string? That's called a tautology. "Because I feel like it" is a pretty apt interpretation of that. I'm not accusing you of anything here, just pointing out the difference between solving a problem and solving a whim. The latter can very easily cause other problems. – David Jan 08 '15 at 15:39
  • If you need to know, I later in the program, will run another function over my `DataTable` to remove all the duplicate values of a certain column. So if my `SELECT` query is populating my database with something other than string then my other method will not function correctly. Fix my other method? That's possible. But either we fix it here or there. There is no middle way. But how about just trying to answer the question rather than putting people in boxes and asking why they are even asking the questions. Saves time for both of us, doesn't it? Unless you have a lot of free time at hand. – disasterkid Jan 08 '15 at 15:47
  • Attempting to get useful information about the problem being solved is part of answering a question. (Well, part of arriving at a meaningful answer instead of a quick hack.) And fixing your other method (or, as suggested twice already, returning strings from the query in the first place) sounds like a much more viable approach than unnecessarily looping over the same data, but it's your project so do what you want. – David Jan 08 '15 at 15:51
  • You're welcome, but it wasn't my downvote. The question isn't *bad* per se (though the asker is a bit of an ass, but votes are applied to content not users), it just lacks a little bit of information. – David Jan 08 '15 at 16:00

2 Answers2

2
// Create a temporary table
DataTable dtNew = new DataTable();
for(int i=0;dt.Columns.Count;i++)
{
    dtNew.Columns.Add(dt.Columns[i].ColumnName,typeof(string));
}


// Add data to new table
for(int i=0;dt.Rows.Count;i++)
{
   dtNew.Rows.Add();
   for(int j=0;dt.Columns.Count;j++)
   {
      dtNew.Rows[i][j] = dt.Rows[i][j];
   }        
}

dt=null;
dt=dtNew.Copy();
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
  • Looks OK. But this will double the processing time if I'm not mistaken, right? I mean, you first have to fill the DataTable and then go through the entire thing once to do that check. However, if this is the only way left, then your answer is correct and I will gladly select that. – disasterkid Jan 08 '15 at 15:38
  • 1
    Yes. Its has to loop through each row. But you have no other way because once data is filled to table, you cannot change column type. @Pedram – Sarath Subramanian Jan 08 '15 at 15:41
  • Welcome. Feel free to ask for any alteration :) @Pedram – Sarath Subramanian Jan 08 '15 at 15:44
0

There is no direct way to specify that results should be all string. You can't simply instruct DataTable.Load to convert every type to string.

Your options are:

  • Convert every value in the DataTable to string by iterating each row and column.
  • If possible then, convert/cast each column in your SQL query to varchar

This could get you started. 

DataTable dtAllString = new DataTable();
foreach (DataColumn column in dt.Columns)
{
    dtAllString.Columns.Add(column.ColumnName, typeof(string));
}

foreach (DataRow row in dt.Rows)
{
    List<string> columnValues = new List<string>();
    foreach (DataColumn col in dt.Columns)
    {
        columnValues.Add(Convert.ToString(row[col]));
    }
    dtAllString.Rows.Add(columnValues.ToArray());
}
Habib
  • 219,104
  • 29
  • 407
  • 436