How to read multiple rows in single column from table in c#?

Question

4.00/5 (1 vote)

See more:

I have a stored procedure that returns the path of data and log file for a particular database.
The sp returns a single row and multiple columns.I have to read the data from each and every row.
SqlDataReader is not helping me or may be i have not understood it's complete implementation.

How can i read the required rows?
Any explanation is welcomed...

Code:

C#

string dataFilePath;
string logFilePath;
SqlCommand cmdspText = new SqlCommand("GetAllPhysicalPath", conn);
cmdspText.CommandType = CommandType.StoredProcedure;
cmdspText.Parameters.AddWithValue("@txtDatabaseName", userSpecifiedDBNameInTxtBox);
cmdspText.ExecuteNonQuery();
SqlDataReader reader = new SqlDataReader();
while(reader.Read())
{
//what should i write here
}

Posted 17-Jun-14 23:28pm

Anshumaan Chaturvedi

Updated 18-Jun-14 2:12am

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 18-Jun-14 5:37am

what code can you share to see where you are stuck?

Anshumaan Chaturvedi 18-Jun-14 5:39am

string dataFilePath;
string logFilePath;
SqlCommand cmdspText = new SqlCommand("GetAllPhysicalPath", conn);
cmdspText.CommandType = CommandType.StoredProcedure;
cmdspText.Parameters.AddWithValue("@txtDatabaseName", userSpecifiedDBNameInTxtBox);
cmdspText.ExecuteNonQuery();
SqlDataReader reader = new SqlDataReader();
while(reader.Read())
{
//what should i write here
}

Dr Drastic 18-Jun-14 5:40am

I agree with digimanus,

If you post the code, we will be able to help you much easier.

Anshumaan Chaturvedi 18-Jun-14 5:40am

string dataFilePath;
string logFilePath;
SqlCommand cmdspText = new SqlCommand("GetAllPhysicalPath", conn);
cmdspText.CommandType = CommandType.StoredProcedure;
cmdspText.Parameters.AddWithValue("@txtDatabaseName", userSpecifiedDBNameInTxtBox);
cmdspText.ExecuteNonQuery();
SqlDataReader reader = new SqlDataReader();
while(reader.Read())
{
//what should i write here
}

King Fisher 18-Jun-14 14:17pm

use dataset instead of Readerr.

2 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)

Mandip Grewal · Answer 1 · 2014-06-18T01:57:00

The code that you have provided in comments is wrong. If your stored procedure returns the rows then you should not use ExecuteNonQuery function to execute the procedure because ExecuteNonQuery function returns only the number of row affected while executing your stored procedure.

You have to use SqlDataAdapter or ExecuteReader, if your stored procedure returns some rows. Please refer the below code using ExecuteReader.

C#

 SqlCommand cmdspText = new SqlCommand("GetAllPhysicalPath", conn);
 cmdspText.CommandType = CommandType.StoredProcedure;
 cmdspText.Parameters.AddWithValue("@txtDatabaseName", userSpecifiedDBNameInTxtBox);
// Assign the results to SqlDataReader by executing the ExecuteReader function
 SqlDataReader reader = cmdspText.ExecuteReader();

// While loop will continue to execute until all records in SQLDataReader got read from it one by one. 
 while(reader.Read())
 {
   //write code to fetch results from data reader..
 }
// Close the object of SqlDataReader
reader.Close();

Happy Coding :)

Dr Drastic · Answer 2 · 2014-06-17T23:58:00

You can add you data to a datatable

C#

DataTable dt = new DataTable();
dt.Rows.Add("Mydata1", "Mydata2", "Mydata3", "Mydata4");

while read your loop will continue adding rows

And then you retreive your data,

C#

int MyCounter = 0

if (dt.Rows.Count > 0)
{

foreach (DataRow dr in dt.Rows)
{
{

string RetreiveData1 = dt.Rows[MyCounter].ItemArray["Mydata1"].ToString();
string RetreiveData2 = dt.Rows[MyCounter].ItemArray["Mydata2"].ToString();
MyCounter += 1;

}
}

You use MyCounter to move to next row.

I hope this helps!

How to read multiple rows in single column from table in c#?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0