pgilfeather
asked on
Having dynamically created an array how do I use a datareader to assign values to it
I have managed to dynamically create an array size.
What is the best way in VB.NET to fill each array member with data.
How can I accomplish this with a datareader?
nCountOfRecords =countIDs
ReDim MyArray(nCountOfRecords)
The following code generates an error saying that objreader cannot be converted to type integer.
For I = 1 To nCountOfRecords
MyArray(I) = objreader
Next
Thanks very much in advance.
PG
What is the best way in VB.NET to fill each array member with data.
How can I accomplish this with a datareader?
nCountOfRecords =countIDs
ReDim MyArray(nCountOfRecords)
The following code generates an error saying that objreader cannot be converted to type integer.
For I = 1 To nCountOfRecords
MyArray(I) = objreader
Next
Thanks very much in advance.
PG
ASKER
arif_eqbal,
I want to store the values from 1 database column into the array
If you could demonstrate what you are saying in code, this would be very helpful
I know that I need to loop through each row and assign each row value to the array, but how do you accomplish this in code.
Kind Regards
PG
I want to store the values from 1 database column into the array
If you could demonstrate what you are saying in code, this would be very helpful
I know that I need to loop through each row and assign each row value to the array, but how do you accomplish this in code.
Kind Regards
PG
ASKER
Sorry...forgot to say...
I do want to store EVERY value of the datareader into the array.
Cheers
I do want to store EVERY value of the datareader into the array.
Cheers
I hope this will give you entire code which u r searching for
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace Anil
{
public class SqlBrowser : System.Windows.Forms.Form
{
private System.Windows.Forms.ListB ox orderIDsList;
private System.Windows.Forms.ListV iew orderDetailsList;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.ComponentModel.Cont ainer components = null;
private System.Data.SqlClient.SqlC onnection con;
private System.Windows.Forms.Butto n btnStart;
private bool _columnsSet = false;
public SqlBrowser()
{
// Create Controls
InitializeComponent();
// Setup Connection
string strCon = "server=xeon;uid=sa;" +
"pwd=manager; database=northwind";
con = new SqlConnection(strCon);
}
.....
.....
// The main entry point for the application.
static void Main()
{
Application.Run(new SqlBrowser());
}
// Fill orderIDsList with OrderID's
private void btnStart_Click(object sender, System.EventArgs e)
{
int iOrderID;
// Create a command to select the Order IDs from the ORDERS table
SqlCommand command = new SqlCommand
("SELECT OrderID from ORDERS", con);
// Open the connection
con.Open();
// Get the data reader
SqlDataReader reader = command.ExecuteReader();
// Process each result
while (reader.Read())
{
// Add each order ID in the result to the list
// view containing the orders IDs. We have only
// selected a single column in this code so we
// can be pretty save in using reader.GetInit32(0)
// there are no more columns in the data reader.
iOrderID = reader.GetOrdinal("OrderID ");
orderIDsList.Items.Add (reader.GetInt32(iOrderID) );
}
// Close the reader and the connection
reader.Close();
this.con.Close();
}
// Populate the list view with the order details
//
// Used Stored Procedure:
// ----------------------
// CREATE PROCEDURE CustOrdersDetail @OrderID int
// AS
// SELECT ProductName,
// UnitPrice=ROUND(Od.UnitPri ce, 2),
// Quantity,
// Discount=CONVERT(int, Discount * 100),
// ExtendedPrice=ROUND(CONVER T(money, Quantity *
// (1 - Discount) * Od.UnitPrice), 2)
// FROM Products P, [Order Details] Od
// WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
//
private void orderIDsList_SelectedIndex Changed
(object sender, System.EventArgs e)
{
// Get the selected item and convert it to the correct order ID
int orderID = Convert.ToInt32(orderIDsLi st.Selecte dItem);
// Create a command to execute a stored procedure
SqlCommand storedProcCommand =
new SqlCommand ("CustOrdersDetail", con);
// Let the command know it will be executed a stored procedure
storedProcCommand.CommandT ype = CommandType.StoredProcedur e;
// Add a parameter that's passed to the stored proc,
// this is the order ID we selected
storedProcCommand.Paramete rs.Add("@O rderID",or derID);
// Open the connection
con.Open ();
// Create an ArrayList to hold the results
ArrayList rowList = new ArrayList();
// Get the reader
SqlDataReader reader = storedProcCommand.ExecuteR eader();
// Process each result in the result set
while (reader.Read())
{
// Create an array big enough to hold the column values
object[] values = new object[reader.FieldCount];
// Get the column values into the array
reader.GetValues(values);
// Add the array to the ArrayList
rowList.Add(values);
}
// Have the columns already being added to the list view?
if (_columnsSet == false)
{
// No, so get the schema for this result set
DataTable schema = reader.GetSchemaTable();
// And set the list view to reflect the
// contents of the schema
SetColumnHeaders(schema);
}
// Close the reader and the connection
reader.Close ();
con.Close ();
// Now populate the list view with the order details
PopulateOrderDetails(rowLi st);
}
// Populate the list view with the order details
private void PopulateOrderDetails (ArrayList rowList)
{
// Clear any exsisting items from the orders view
orderDetailsList.Items.Cle ar();
// Now process each array in the arraylist
foreach (object[] row in rowList)
{
// Create a string array large enough to hold all
// the column values in this array
string[] orderDetails = new string[row.Length];
// Create a column index into the array
int columnIndex = 0;
// Now process each column value
foreach (object column in row)
{
// Convert the value to a string and stick
// it in the string array
orderDetails[columnIndex++ ] = Convert.ToString (column);
}
// Now use the string array to create a new item
// to go in the list view
ListViewItem newItem = new ListViewItem (orderDetails);
// Finally add the new item to the view
orderDetailsList.Items.Add (newItem);
}
}
// Set the list view to reflect the contents of the schema
private void SetColumnHeaders (DataTable schema)
{
// Process each row in the schema table
foreach (DataRow row in schema.Rows)
{
// For each column add a new column to the list view
orderDetailsList.Columns.A dd((string )row["Colu mnName"],
50, HorizontalAlignment.Left);
}
// Set this flag to stop the method being called again
_columnsSet = true;
}
}
}
Thanks
Anil Mane
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace Anil
{
public class SqlBrowser : System.Windows.Forms.Form
{
private System.Windows.Forms.ListB
private System.Windows.Forms.ListV
private System.Windows.Forms.Label
private System.Windows.Forms.Label
private System.ComponentModel.Cont
private System.Data.SqlClient.SqlC
private System.Windows.Forms.Butto
private bool _columnsSet = false;
public SqlBrowser()
{
// Create Controls
InitializeComponent();
// Setup Connection
string strCon = "server=xeon;uid=sa;" +
"pwd=manager; database=northwind";
con = new SqlConnection(strCon);
}
.....
.....
// The main entry point for the application.
static void Main()
{
Application.Run(new SqlBrowser());
}
// Fill orderIDsList with OrderID's
private void btnStart_Click(object sender, System.EventArgs e)
{
int iOrderID;
// Create a command to select the Order IDs from the ORDERS table
SqlCommand command = new SqlCommand
("SELECT OrderID from ORDERS", con);
// Open the connection
con.Open();
// Get the data reader
SqlDataReader reader = command.ExecuteReader();
// Process each result
while (reader.Read())
{
// Add each order ID in the result to the list
// view containing the orders IDs. We have only
// selected a single column in this code so we
// can be pretty save in using reader.GetInit32(0)
// there are no more columns in the data reader.
iOrderID = reader.GetOrdinal("OrderID
orderIDsList.Items.Add (reader.GetInt32(iOrderID)
}
// Close the reader and the connection
reader.Close();
this.con.Close();
}
// Populate the list view with the order details
//
// Used Stored Procedure:
// ----------------------
// CREATE PROCEDURE CustOrdersDetail @OrderID int
// AS
// SELECT ProductName,
// UnitPrice=ROUND(Od.UnitPri
// Quantity,
// Discount=CONVERT(int, Discount * 100),
// ExtendedPrice=ROUND(CONVER
// (1 - Discount) * Od.UnitPrice), 2)
// FROM Products P, [Order Details] Od
// WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID
//
private void orderIDsList_SelectedIndex
(object sender, System.EventArgs e)
{
// Get the selected item and convert it to the correct order ID
int orderID = Convert.ToInt32(orderIDsLi
// Create a command to execute a stored procedure
SqlCommand storedProcCommand =
new SqlCommand ("CustOrdersDetail", con);
// Let the command know it will be executed a stored procedure
storedProcCommand.CommandT
// Add a parameter that's passed to the stored proc,
// this is the order ID we selected
storedProcCommand.Paramete
// Open the connection
con.Open ();
// Create an ArrayList to hold the results
ArrayList rowList = new ArrayList();
// Get the reader
SqlDataReader reader = storedProcCommand.ExecuteR
// Process each result in the result set
while (reader.Read())
{
// Create an array big enough to hold the column values
object[] values = new object[reader.FieldCount];
// Get the column values into the array
reader.GetValues(values);
// Add the array to the ArrayList
rowList.Add(values);
}
// Have the columns already being added to the list view?
if (_columnsSet == false)
{
// No, so get the schema for this result set
DataTable schema = reader.GetSchemaTable();
// And set the list view to reflect the
// contents of the schema
SetColumnHeaders(schema);
}
// Close the reader and the connection
reader.Close ();
con.Close ();
// Now populate the list view with the order details
PopulateOrderDetails(rowLi
}
// Populate the list view with the order details
private void PopulateOrderDetails (ArrayList rowList)
{
// Clear any exsisting items from the orders view
orderDetailsList.Items.Cle
// Now process each array in the arraylist
foreach (object[] row in rowList)
{
// Create a string array large enough to hold all
// the column values in this array
string[] orderDetails = new string[row.Length];
// Create a column index into the array
int columnIndex = 0;
// Now process each column value
foreach (object column in row)
{
// Convert the value to a string and stick
// it in the string array
orderDetails[columnIndex++
}
// Now use the string array to create a new item
// to go in the list view
ListViewItem newItem = new ListViewItem (orderDetails);
// Finally add the new item to the view
orderDetailsList.Items.Add
}
}
// Set the list view to reflect the contents of the schema
private void SetColumnHeaders (DataTable schema)
{
// Process each row in the schema table
foreach (DataRow row in schema.Rows)
{
// For each column add a new column to the list view
orderDetailsList.Columns.A
50, HorizontalAlignment.Left);
}
// Set this flag to stop the method being called again
_columnsSet = true;
}
}
}
Thanks
Anil Mane
ASKER
Sorry but I use VB.NET. I said this in my opening question. I am not really familiar with C#.
I will try my best to glean what I can from your code. Thanks.
Kind Regards
PG
I will try my best to glean what I can from your code. Thanks.
Kind Regards
PG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I hope you want to store all the value in the datareader into the array.
You can either store each column values separately, or one row at a time into the array.
For getting the values from a DataReader you'll have to use its Read method to advance to next row and then store the values in the array
In your code you are trying to put the dataReader object itself in one element of the array which is perhaps not what you want.