Add a column in the DataTable of a DataSet when it's already full? 1

[huckfinn19]

Hi!

Here's my problem: I currently fill a single column in a DataSet from an SQL DB. I can get up to 10000 records. So I have a Table in the DataSet like this:
|MyCol|
---------
Val1
Val2
...

The thing is that in my case, it would be very helpful to have another column containing boolean values to display in a DataGrid and let the users select some of the values in my DataSet. The result would be something like:

|Selected|MyCOl|
----------------
true | Val1 |
...

So far, I create another DataTable after my DataSet has retrieved the data (MyCol) values, I add 2 columns in that table, one boolean, one string, and I loop through the two DataSets to copy the Data. I find this incredibly expensive and I believe there must be another way somehow!

1- When loading data in the DataSet, can I tell it in which column the Data should be filled in? This way I could create the two tables and fill in only the second one.

2- Is there a way to copy the Data in a column from a DataSet to another? Should I use different data structures?

3- Could I tweak my SQL command so that I would retrive an artificial bool value in the first row? (something like: SELECT 1>0, MyCol FROM MyTable)

4- Any other idea on how I could do this?

Thanks in advance!

Huck

 

[obislavu]

An easy solution is to use the following select :

string strSQL="SELECT Convert(bit,0) as 'Selected', MyCol from myTable" ;
string sConnString="..";
DataTable dt = new DataTable();
FillData(ref dt,strSQL, sConnString);
Now if you bind a DataGrid to dt the first column will be 'Selected' as unchecked box.
To manage it well use DataGridTableStyle.
To check/uncheck it at run time:
int iRow;
bool bCheck=true;
dataGrid1[iRow,0]=(bCheck) ? false : true;

Use

string strSQL="SELECT Convert(bit,1) as 'Selected', MyCol from myTable" ;

if you want all the ckeckbox to be selected when the grid is displayed.

public void FillDataSet(ref DataTable dt, string sqlStatement, string sConnString)
{
	SqlConnection sConn		= null;
	SqlCommand WorkingCommand	= null;
	SqlDataAdapter WorkingDataAdapter= null;
	try
	{
		sConn			= new SqlConnection(sConnString);
		WorkingCommand	= new SqlCommand();
		WorkingDataAdapter= new SqlDataAdapter();
		sConn.Open();
		WorkingCommand.Connection= sConn;
		WorkingCommand.CommandText = sqlStatement;
		WorkingDataAdapter.SelectCommand = WorkingCommand;
		WorkingDataAdapter.Fill(dt);

	}
	catch(Exception e)
	{						
		string sErr=e.GetType() + e.Message;
	}						
	finally 
	{
		if (sConn!=null)
		{
			sConn.Close();
			sConn.Dispose();
		}
	}

}

obislavu