read sql query results into an array

Question

0.00/5 (No votes)

See more:

I want to run this query from C# and the results will be anywhere from 1 to maybe 20 integers less than 2000, and read those results into an integer array. How do I do that?

SQL

Select DATEDIFF (DAY, DeployedDate, GETDATE())
From Deployment
Where EmployeeID =1 
And
DeploymentID = 1";

Posted 22-Aug-11 17:52pm

Member 8105842

Updated 22-Aug-11 17:57pm

Nithin Sundar

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nithin Sundar · Answer 1 · 2011-08-22T18:02:00

Solution 2

You can make use of ADO .NET in the following steps:

1. Form a connection string.
2. Form your query
3. Add parameters
4. Execute the query with the help of DataReader

These two articles will help you:

Using ADO.NET for beginners[^]

The ADO.NET OleDbDataReader class[^]

EDIT: You can get values like this:

C#

SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING");

ArrayList valuesList = new ArrayList();


connection.Open();
//Read from the database
SqlCommand command = new SqlCommand("YOUR SQL QUERY HERE", connection);

SqlDataReader dataReader = command.ExecuteReader();

while (dataReader.Read())
{
    valuesList.Add(Convert.ToInt32(dataReader[0].ToString()));
}
connection.Close();

Note that for the above code, replace the index of dataReader with the column. Like to get the values of the second column give it a 1. Use an arraylist to get everything into an array.

Posted 22-Aug-11 18:02pm

Nithin Sundar

Updated 23-Aug-11 17:19pm

v4

Comments

RaisKazi 23-Aug-11 0:59am

This should be helpfull. My 5.

Nithin Sundar 23-Aug-11 1:35am

Thanks!

Member 8105842 23-Aug-11 20:17pm

The generalized reference doesn't help. I have ADO reference. I can use datareader and run the query and get results, but I just don't know how to put the results into an array. The results are integers.

Nithin Sundar 23-Aug-11 23:15pm

I have updated the solution. Check it.

Member 8105842 24-Aug-11 0:12am

I just need to dump the results into an integer array.One query calculates the difference in days between 2 date columns and another query calculates how many days ago that one of the dates happened. So neither query reads a column, rather it calculates from either 1 or 2 columns, so I can't reference them.

Nithin Sundar 24-Aug-11 0:41am

I think you get a temporary table with one column as a result when you calculate the difference or sum or something. So this could also work. If you haven't given it a try, do it. As far as I know, it should give you the result.

therichaverma 30-May-20 10:26am

Hey i tried this but its giving an error
It says FormatException was unhandled by the user

here is my code
SqlCommand scmd = new SqlCommand("Select RollNo From '"+ name+"'",conn);
SqlDataReader eader = cmd.ExecuteReader();
eader.Read();
while (eader.Read())
{
Roll.Add(Convert.ToInt32(eader[0].ToString()));
}
eader.Close();
foreach(int i in Roll)
{
TextBox1.Text = i.ToString();
}

Ganesan Senthilvel · Answer 2 · 2011-08-22T18:01:00

Solution 1

If your code base is in C# 3.0 and above, LInQ helps

C#

using System.Linq;

var query = from deploy in dbContext.Deployment
            where deploy.EmployeeID == 1 && DeploymentID == 1
            select datediff(deploy.day, deploy.deployeddate, getdate());

return query.toarray();

Posted 22-Aug-11 18:01pm

Ganesan Senthilvel

Updated 22-Aug-11 18:32pm

v2

Shahan Ayyub · Answer 3 · 2011-08-29T09:34:00

You can use DataTable for this purpose. ( since you have only one column, as a result)

1. Fill DataTable, using DataAdapter
2. Now you can access it like this:

Here 'dt' is DatTable filled by DataAdapter:

C#

foreach (DataRow row in dt.Rows) {
    string arrayItem = row[0];  // on every iteration you will be accessing it as an array item
}