dynamic 2d array from sqlDataReader

[havelock]

hi i can't seem to pin this one down, any thoughts.. thanks

I'm trying to set the values of the 2d array in one line like so:

myarr.SetValue((string)("Anam"+(i)),rdr["MedName"].ToString().Trim());


i'm guess i need to define the placeholder for the array better then just stating a comma, i've tried implementing with {i}{0} to no avail.


if (rdr.HasRows)
{
string[,] myarr = new string[26, 2];

while (rdr.Read())
{

for (int i = 0; i <= 26; i++)
{
if (rdr["dateStart"] != null && rdr["MedName"] != null)
{
DateTime dateStart = Convert.ToDateTime(rdr["dateStart"].ToString());
DateTime dateEnd = Convert.ToDateTime(rdr["dateEnd"].ToString());
DateTime dt = System.DateTime.Today.Date;

if (dateEnd.CompareTo(System.DateTime.Now) < 0)
{
myarr.SetValue((string)("Anam"+(i)),rdr["MedName"].ToString().Trim());
myarr.SetValue((string)("Ados"+(i)), rdr["PillDose"].ToString().Trim() + " " + rdr["UnitTypeDesc"].ToString().Trim());
myarr.SetValue((string)("Afre" + (i)), rdr["freq"].ToString().Trim() + " " + rdr["Admin"].ToString().Trim());
}
}
}
}

}

[JonnyPoet]

Hi !
The first thing I can see is you are not checking for IsDBNull
As far as I know checking for null only doesn't prevent DBIsnull problems

if (!reader.IsDBNull(colIndex)) {
DateTime dateStart = Convert.ToDateTime(rdrcolIndex].ToString());

}
Additional if you have a twodim array you need to set values like
int j;
for (int i = 0;i < rdr.VisibleFieldCount; i++){
myarr[i,0]="Anam"+ i.ToString();
myarr[i, 1] = "Pills" +i.ToString();
}
But this in reality doesn't fit to your needs. I'll show you the solution son hold on.

[JonnyPoet]

Hi !
Here is the code
string sqlQuery = // any needed string
using (OleDbConnection connection = new OleDbConnection()) {
OleDbCommand command = new OleDbCommand(sqlQuery, connection);
connection.Open();
// execute the reader
OleDbDataReader rdr = command.ExecuteReader();
/ the followig values are assumptions
int indexDateStart = 0;
int indexMedName = 1;
int indexDateEnd = 2;
int indexPillDose = 3;
int indexFreq = 4;
int indexUnitTypeDesc = 5;
int indexAdmin = 6;
Dictionary<string,string> myArr = new Dictionary<string,string>();
for (int i = 0; i <= 26; i++){
if (!(rdr.IsDBNull(indexDateStart) || rdr.IsDBNull(indexMedName))) {
DateTime dateStart = rdr.GetDateTime(indexDateStart);
DateTime dateEnd = rdr.GetDateTime(indexDateEnd);
DateTime dt = System.DateTime.Today.Date;
if (dateEnd.CompareTo(System.DateTime.Now) < 0) {
myArr.Add("Anam"+i.ToString(),rdr.GetString( indexMedName));
myArr.Add ("Ados"+i.ToString(),rdr[indexPillDose].ToString().Trim() + " " + rdr[indexUnitTypeDesc].ToString().Trim());
myArr.Add ("Afre" + i.ToString(),rdr[ indexFreq].ToString().Trim() + " " + rdr[indexAdmin].ToString().Trim());
}
}
}
}
}If you want to have it in a more dimensional way I'll show you a bit later

string sqlQuery = // any needed string
using (OleDbConnection connection = new OleDbConnection()) {
OleDbCommand command = new OleDbCommand(sqlQuery, connection);
connection.Open();
// execute the reader
OleDbDataReader rdr = command.ExecuteReader();
/ the followig values are assumptions
int indexDateStart = 0;
int indexMedName = 1;
int indexDateEnd = 2;
int indexPillDose = 3;
int indexFreq = 4;
int indexUnitTypeDesc = 5;
int indexAdmin = 6;
List<Dictionary<string, string>> allItems = new List<Dictionary<string, string>>();

for (int i = 0; i <= 26; i++){
if (!(rdr.IsDBNull(indexDateStart) || rdr.IsDBNull(indexMedName))) {
DateTime dateStart = rdr.GetDateTime(indexDateStart);
DateTime dateEnd = rdr.GetDateTime(indexDateEnd);
DateTime dt = System.DateTime.Today.Date;
if (dateEnd.CompareTo(System.DateTime.Now) < 0) {
Dictionary<string, string> myArr = new Dictionary<string, string>();
myArr.Add("Anam"+i.ToString(),rdr.GetString( indexMedName));
myArr.Add ("Ados"+i.ToString(),rdr[indexPillDose].ToString().Trim() + " " + rdr[indexUnitTypeDesc].ToString().Trim());
myArr.Add ("Afre" + i.ToString(),rdr[ indexFreq].ToString().Trim() + " " + rdr[indexAdmin].ToString().Trim());
allItems.Add(myArr);
}
}
}
}
}Each datarow you will find in a myArr Dictionary which alway has three items each. The list contains the rows the index is the rownumber beginning with 0

[cjard]

a dynamic 2D array from a sql data reader is actually called a "DataTable"

Take a read up on it and tell us what you think

[havelock]

Thanks JonnyPoet for helping guiding me to the light, question in regards to
the loop involving:
"Pills" +i.ToString();

What if Pills0 or Pills1, etc is a public string ?

how would a public string be used in a loop like that. I keep getting that Pills does not exist in the current context,

I'm trying to set public string effeciently in a for loop, thanks

[JonnyPoet]

Thanks JonnyPoet for helping guiding me to the light, question in regards to
the loop involving:
"Pills" +i.ToString();

What if Pills0 or Pills1, etc is a public string ?

how would a public string be used in a loop like that. I keep getting that Pills does not exist in the current context,

I'm trying to set public string effeciently in a for loop, thanksThe easiest way to hanlde all this data is using a DataTable as CJard offered to you, if you want to read trough all the pills columns, what I think you want to do is looking for "Pills1, Pills2 ... " as a key and you will get the items as a result, because it is Dictionary<key,value> always.
The second example is easier and maybe faster not tested ) to go through as you can run from item to item in your List and each item is one Dictionary which holds all data from one row.
The column is then got by using Key "Pills" and reading the value.

But why in the world you keep all that with such an redundance ? Think if you have 200 Pills you 200 times store the word pill here in the memory. Not to think on a database which keeps 100.000 Pills

If you want to do it for your own without usage of a Datatable then create your own class that reads keeps the data and the Columnames. One linear array for the Columnames and then an 2D array or the data all indexed like myItem = myArr[3,4] ; meaning third row, 4th column and when you want to serch by name use an Indexer for this class where you can take access using the names getting out your data.

Here is such a class and some data to test it

myDatas newData = new myDatas(5,new string[]{"Anam","Ados","Afre"});
newData[0, "Anam"] = "Druggie";
newData[0, "Ados"] = "Methadon";
newData[0, "Afre"] = "daily";
string myresult = newData[0, "Ados"];
// this is the class
public class myDatas{
private string[,] _dataArr = null;
private string [] _colNames;
// have a Ctor for initializing size
public myDatas(int rows, string [] colNames) {
_colNames = colNames;
int cols = _colNames.GetUpperBound(0)+1;
_dataArr = new string[rows,cols];
}
// the indexer
public string this[int row,string colName]{
get {
if (row > _dataArr.GetUpperBound(0)) {
throw new ArgumentOutOfRangeException(row.ToString(), "RowSize exceeds limit");
}
try {
int index = GetIndex(colName);
return _dataArr[row, index];
}catch {
return null;
}
}
set {
try {
int index = GetIndex(colName);
_dataArr[row, index] = value;
}catch {
if (row >= _dataArr.GetUpperBound(0)) {
throw new ArgumentOutOfRangeException(row.ToString(), "RowSize exceeds limit");
}else {
throw new ArgumentOutOfRangeException(colName , "ColName doesn't exist");
}
}
}
}
private int GetIndex(string colName) {
for (int i = 0; i <= _colNames.GetUpperBound(0); i++){
if (_colNames[i] == colName) {
return i;
}
}
throw new ArgumentOutOfRangeException("colName doesn't exist");
}
}

This should do the work. You have a sort of Table here where you can insert Data like in an array. and read them like you read an array
Per sure you can add an Enumerator to read it using foreach if you want and you can add the code to this class to fill it with data. This would be a sort of selfcreated class instead of a DataTable. It per sure needs additional work but this is a first idea of it.