populating array with data from database

[dupati1]

Hi All,

I am trying to create a simple bar chart using ASP.net and after searching online i got a sample code which does this with static data...but i want to fetch the data from my SQL database and not sure of how to change this below piece of code to populate the dynamic data from DB...

Any help would be appreciated...

Thanks in advance...
-VJ

Below is the code:

void Page_Load(Object sender, EventArgs e) {

// Declare our variables
String [] sItems = new String[10];
Int32 [] iValue = new Int32 [10];

// Populate our variables
sItems[0] = "Carrots";
iValue[0] = 23;

sItems[1] = "Peas";
iValue[1] = 53;

sItems[2] = "Celery";
iValue[2] = 11;

sItems[3] = "Onions";
iValue[3] = 21;

sItems[4] = "Radishes";
iValue[4] = 43;

// Set our axis values
dngchart.YAxisValues = iValue;

// Set our axis strings
dngchart.YAxisItems = sItems;
}

i tried something like this .....

created a db connection string, a data reader and looping thru the data but it didnt work...

Dim connString As String = "Data Source = STJSQL\STJDB; Database=STJData;User ID=****x;Password=****x"
Dim sql As String = "my sql string"

Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)

conn.Open()

Dim ReaderObj As SqlDataReader = command.ExecuteReader()

Do While ReaderObj.Read()

[Jimmy Turnip]

Try this instead of the Do While:

If ReaderObj.Read Then
Dim i As Integer
For i = 0 to ReaderObj.FieldCount - 1
sItems(i) = ReaderObj.GetName(i)
iValue(i) = ReaderObj.Items(i)
Next
End If

I know this is in VB, but I assume the method for C# will be pretty similar. Hope it helps.

Jimmy

[edited by: Jimmy_Turnip at 3:43 pm (utc) on July 6, 2004]

[Jimmy Turnip]

Sorry, the fourth line down should have read:

sItems(i) = ReaderObj.GetName(i)

[dupati1]

Hi Jimmy,

I am totally new to Asp.net and c#. I came across these below two files using which we can create a bar chart with static data...i was just trying to manipulate the code so that i can insert the data dynamically...

file 1:
Written in C#
**********************************
<script language="C#" runat="server">
private String _sXAxisTitle;
private String _sChartTitle;
private Int32 _iUserWidth = 300;
private String [] _sYAxisItems;
private Int32 [] _iYAxisValues;

public Int32 UserWidth {
get { return _iUserWidth; }
set { _iUserWidth = value; }
}

public Int32 [] YAxisValues {
get { return _iYAxisValues; }
set { _iYAxisValues = value; }
}

public String [] YAxisItems {
get { return _sYAxisItems; }
set { _sYAxisItems = value; }
}

public String XAxisTitle {
get { return _sXAxisTitle; }
set { _sXAxisTitle = value; }
}

public String ChartTitle {
get { return _sChartTitle; }
set { _sChartTitle = value; }
}

void Page_Load(Object sender, EventArgs e) {

// As long as we have values to display, do so
if (_iYAxisValues!= null) {

// Color array
String [] sColor = new String[9];
sColor[0] = "red";
sColor[1] = "lightblue";
sColor[2] = "green";
sColor[3] = "orange";
sColor[4] = "yellow";
sColor[5] = "blue";
sColor[6] = "lightgrey";
sColor[7] = "pink";
sColor[8] = "purple";

// Initialize the color category
Int32 iColor = 0;

// Display the chart title
lblChartTitle.Text = _sChartTitle;

// Get the largest value from the available items
Int32 iMaxVal = 0;
for (int i = 0; i < _iYAxisValues.Length; i++) {
if (_iYAxisValues[i] > iMaxVal)
iMaxVal = _iYAxisValues[i];
}

// Take the user-provided maximum width of the chart, and divide it by the
// largest value in our valueset to obtain the modifier
Int32 iMod = Math.Abs(_iUserWidth/iMaxVal);

// This will be the string holder for our actual bars.
String sOut = "";

// Render a bar for each item
for (int i = 0; i < _iYAxisValues.Length; i++) {

// Only display this item if we have a value to display
if (_iYAxisValues[i] > 0) {

sOut += "<tr><td align=right>" + _sYAxisItems[i] + "</td>";
sOut += "<td>" + RenderItem(_iYAxisValues[i], iMod, sColor[iColor]) + "</td></tr>";
iColor++;

// If we have reached the end of our color array, start over
if (iColor > 8) iColor = 0;
}
}

// Place the rendered string in the appropriate label
lblItems.Text = sOut;

// Drop in the Y Axis label
lblXAxisTitle.Text = _sXAxisTitle;
}
}

// Generates a horizontal bar graph for a given item
private String RenderItem (Int32 iVal, Int32 iMod, String sColor) {
String sRet = "";
sRet += "<table border=0 bgcolor=" + sColor + " cellpadding=0 cellspacing=0><tr>";
sRet += "<td align=center width=" + (iVal * iMod) + " nobr nowrap>";
sRet += "<b>" + iVal + "</b>";
sRet += "</tr><td></table>";
return sRet;
}

</script>

<table>
<tr>
<td align=center>
<asp:Label id=lblChartTitle runat=server />
</td>
</tr>
<tr>
<td>
<table border=1 bordercolor='#777777' cellspacing=0 cellpadding=0>
<tr>
<td>
<table>
<asp:Label id=lblItems runat=server />
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan=2 align=center>
<asp:Label id=lblXAxisTitle runat=server />
</td>
</tr>
</table>

**********************************

File 2:

***********************************

<%@ Register TagPrefix="DNG" TagName="DotNetGraph" Src="DotNetGraph.ascx" %>

<script language="C#" runat="server">

void Page_Load(Object sender, EventArgs e) {

// Declare our variables
String [] sItems = new String[10];
Int32 [] iValue = new Int32 [10];

// Populate our variables
sItems[0] = "Carrots";
iValue[0] = 23;

sItems[1] = "Peas";
iValue[1] = 53;

sItems[2] = "Celery";
iValue[2] = 11;

sItems[3] = "Onions";
iValue[3] = 21;

sItems[4] = "Radishes";
iValue[4] = 43;

// Set our axis values
dngchart.YAxisValues = iValue;

// Set our axis strings
dngchart.YAxisItems = sItems;

// Provide a title
dngchart.ChartTitle = "<b>Inventory Breakdown:</b>";

// Provide an title for the X-Axis
dngchart.XAxisTitle = "(units display actual numbers)";

}

</script>

<html>
<body>
<!-- Note UserWidth is set "in-line". It could just as easily been set on the page_load method. -->
<DNG:DotNetGraph id=dngchart UserWidth=200 runat=server />
</body>
</html>

***********************************

Can you help me modify this code....

Thanks in advance

-VJ

[Jimmy Turnip]

The only thing that needs altering to make it dynamically fill is the bit in file 2 that populates the variables.

I'm not sure how your name-value pairs that you want to use for the graph are held in the database. The code I gave you above assumes that the name is the column name and the value is the corresponding row item. Is this the case?

[dupati1]

Yes Jimmy,
The values i get from the table are as follows..

Inventory---Database Name

Field 1--- Name
Field 2--- TotItems

example

Computers---200
Monitors---300

and so on....

Now do u want me to change the file to as below:

Dim connString As String = "Data Source = STJSQL\STJDB; Database=STJData;User ID=****x;Password=****x"
Dim sql As String = "my sql string"

Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)

conn.Open()

Dim ReaderObj As SqlDataReader = command.ExecuteReader()

If ReaderObj.Read Then
Dim i As Integer
For i = 0 to ReaderObj.FieldCount - 1
sItems(i) = ReaderObj.GetName(i)
iValue(i) = ReaderObj.Items(i)
Next
End If

Thanks in advance

-VJ

[Jimmy Turnip]

ok, sorry I think we got some wires crossed somewhere.

Instead of this:

// Populate our variables
sItems[0] = "Carrots";
iValue[0] = 23;

sItems[1] = "Peas";
iValue[1] = 53;

sItems[2] = "Celery";
iValue[2] = 11;

sItems[3] = "Onions";
iValue[3] = 21;

sItems[4] = "Radishes";
iValue[4] = 43;

You will need to put:

// Populate our variables
Dim connString As String = "Data Source = STJSQL\STJDB; Database=STJData;User ID=****x;Password=****x"
Dim sql As String = "my sql string"

Dim conn As SqlConnection = New SqlConnection(connString)
Dim command As SqlCommand = New SqlCommand(sql, conn)

conn.Open()

Dim ReaderObj As SqlDataReader = command.ExecuteReader()
Integer i = 0;
while(ReaderObj.Read()) {
sItems[i] = ReaderObj.Items("Name");
iValue[i] = ReaderObj.Items("TotItems");
i++;
}
ReaderObj.Close();
conn.Close();

Sorry if some of my syntax is a bit off, I'm used to programming in VB.

HTH