How to return array or list 2 dimensional from SQL Query

Question

2.00/5 (1 vote)

See more:

How to return array dimensional from SQL Query? or can use 2 dimensional List?
here my code , but I dont have any idea for get column name from SQLiteDataReader

C#

public string[][] Select(string query)
        {
            string[,] data = new string[100, 100];
            int i;
            if (this.OpenConnection() == true)
            { //Create Command
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                //Create a data reader and Execute the command
                SQLiteDataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    i++;
                    data["title"][i] = dataReader["title"][i];
                    data["author"][i]= dataReader["author"][i];
                    data["isbn"][i] = dataReader["isbn"][i]; 
                }
                 return data;

                //close Data Reader
                dataReader.Close();
                //close Connection
                this.CloseConnection();
            }

Posted 3-Nov-14 15:17pm

payjo

Add a Solution

Comments

syed shanu 3-Nov-14 21:54pm

what do you want exactly,Why do you store db data to array,You can return as datatable or dataset

PIEBALDconsult 3-Nov-14 22:55pm

Please define a class and return a List of them. Why, in Bob's name, are you trying to use arrays?! This isn't the '70s!

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)

DamithSL · Answer 1 · 2014-11-03T16:22:00

C#

//create class as below 
public class Book
{
   public string Title { get; set; }

   public string Author { get; set; }
   
   public string Isbn { get; set; }
}

//change your method to return list of books 
//when you call, set the query as "select titlle, author, isbn from yourtable"
public List<Book> Select(string query)
{
  List<Book> books = new List<Book>();
  if (this.OpenConnection() == true)
  { //Create Command
      SQLiteCommand cmd = new SQLiteCommand(query, con);
      //Create a data reader and Execute the command
      SQLiteDataReader dataReader = cmd.ExecuteReader();
      while (dataReader.Read())
      {
          Book book = new Book();
          book.Title = dataReader[0] as string;
          book.Author = dataReader[1] as string;
          book.Isbn  = dataReader[2] as string; 
		  books.Add(book);
      }
       
      //close Data Reader
      dataReader.Close();
      //close Connection
      this.CloseConnection();
	  return books;
  }
}

PIEBALDconsult · Answer 2 · 2014-11-03T17:16:00

To add more flexibility to DamithSL's solution, consider this:

C#

//create class as below 
public class Book
{
   public string Title  { get; private set; }
   public string Author { get; private set; }
   public string Isbn   { get; private set; }

   public Book ( System.Data.IDataRecord Data )
   {
       Title  = (string) Data [ 0 ] ;
       Author = (string) Data [ 1 ] ;
       Isbn   = (string) Data [ 2 ] ;
   }
}
 
public List<Book> Select(string query)
{
  List<Book> books = new List<Book>();
  if (this.OpenConnection() )
  {
      using ( SQLiteCommand cmd = new SQLiteCommand(query, con) )
      {
        using ( SQLiteDataReader dataReader = cmd.ExecuteReader() )
        {
          while (dataReader.Read())
          {
             books.Add ( new Book ( dataReader ) ) ;
          }
        }
      }

      this.CloseConnection();
  }
  return books;
}

There may be a way to make it generic, but it escapes me at the moment.

How to return array or list 2 dimensional from SQL Query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0