Dataset type problem

I've got a few datatables in an Microsoft SQL database. I get those with an dataadapter and put them into a dataset. After t开发者_C百科his I want to change a few of the values, like some I want to change to: "Na/Na". Now the problem in this is, that the normal Type of this column is INT. So it gives me an error.

Is there a way in which I can make sure all the columns get STRING as their type? Or can I make sure the schema of the datatables doesn't get used, I know I could make all the columns myself in C# and then get the values by an query, but this would be WAY to much work.

---

I can suggest a way but it isn't perfect. The way is to cast fields to varchar in query or stored procedure. It'll be better (if possible) that you make your "Na/Na" changes on MS SQL Server side in stored procedure.

---

You could try cloning the DataTable and altering the data types of each column, as suggested in this post. (Scroll about half way down for the answer).

The other alternative is to change (or create a) stored procedure that gets your data and make that pass back the correct "Na/Na" type values you want.

---

Is it just one column causing you the problem? You coudl always in the databse convert it to nvarchar or something before sending it to the C#. Then the column type would be a string. Alternatively you could almost certainyl write some code that would look at the types of a column and if it finds integer ones add a new column to the DataTable and copy the data across to it. That is coming perilously close to your suggestion of making columns for all of them yourself though.

Lastly if your integers are actually limited to a certain range (ie they are always positive) then you could use a magic value (like -1) to represent N/A and then in whatever code you use to process this make sure you recognise the magic value before doing stuff with it.

I don't think (though I would be delighted to be corrected) that you can just change what type a column is and have all the current values get converted automatically.

---

Are you working with a "typed dataset", and do you only need to display "Na/Na" on some occasions?

Then you can easily add a property to the partial class of your datatable which is a string and just returns what you need to display:

public partial class DataSet1 {
    partial class DataTable1DataTable
    {
        public string MyStringColumn {
            get {
                return IntColumn <= 0 ? "Na/Na" : IntColumn.ToString();
            } 
        }
    }
}

---

You cannot change the DataType of a column after the datatable has been filled with data. So you need to define the datatables first by adding the columns in C#. However you can still use the adapter.Fill command, but using the second parameter, to specify the name of the DataTable you want to fill in the Dataset.

DataSet DS = new DataSet();
DataTable DT = new DataTable("Table1");
DT.Columns.Add(new DataColumn("column1", typeof(string)));
DS.Tables.Add(DT);

adapter.Fill(ds , "Table1");