如何从SQL查询返回数组或列表二维 [英] How to return array or list 2 dimensional from SQL Query

问题描述

如何从SQL查询返回数组维?或可以使用二维列表?
这是我的代码，但是我不知道要从SQLiteDataReader获取列名

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

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();
            }

推荐答案

//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;
  }
}

要为DamithSL的解决方案增加更多灵活性，请考虑以下事项:

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

//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.

这篇关于如何从SQL查询返回数组或列表二维的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！