将NpgsqlDataReader转换为DataTable

最新推荐文章于 2024-08-08 08:19:13 发布

万里归来少年心

最新推荐文章于 2024-08-08 08:19:13 发布

阅读量1.7k

点赞数 2

分类专栏： PostgreSQL 文章标签： DataTable NpgsqlDataReader DataColumn DataRow

本文链接：https://blog.csdn.net/liyazhen2011/article/details/89383948

版权

PostgreSQL 专栏收录该内容

54 篇文章 8 订阅

订阅专栏

1.建表

 create table city (
  Id  int, 
  Name varchar(20)
 );

2.插入数据

 insert into city values(1,'BeiJing'),(2,'NewYork'),(3,'Hong kong'),(4,'ShaingHai');

3.Npgsql获取数据集

using Npgsql;
using System;
using System.Data;

namespace DataTableTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=localhost;Port=5433;User Id=postgres;Password=123456;Database=mydb;";
            NpgsqlConnection conn = new NpgsqlConnection(connectionString);
            try
            {
                conn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand("select * from city", conn);        
                DataTable datatable = new DataTable();
                using (NpgsqlDataReader reader = cmd.ExecuteReader())
                {
                    datatable = ConvertDataReaderToDataTable(reader);
                }
                PrintCityInfo(datatable);
            }
            finally
            {
                conn.Close();
            }
            Console.ReadLine();
        }

        //将DataReader 转化为 DataTable
        static DataTable ConvertDataReaderToDataTable(NpgsqlDataReader dataReader)
        {
            DataTable datatable = new DataTable();
            try
            {    //添加表的列类型和列名
                for (int i = 0; i < dataReader.FieldCount; i++)
                {
                    DataColumn column = new DataColumn();
                    column.DataType = dataReader.GetFieldType(i);
                    column.ColumnName = dataReader.GetName(i);
                    datatable.Columns.Add(column);
                }

                //添加表的数据
                while (dataReader.Read())
                {
                    DataRow row = datatable.NewRow();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        row[i] = dataReader[i].ToString();
                    }
                    datatable.Rows.Add(row);
                    row = null;
                }
                dataReader.Close();
                return datatable;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        //打印信息
        static void PrintCityInfo(DataTable dataTable)
        {
            if (dataTable == null)
                return;
            Console.WriteLine("Id  Name");
            foreach (DataRow row in dataTable.Rows)
            {
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    if (row[i].ToString() != "")
                    {
                        Console.Write(" " + row[i]);
                    }
                }
                Console.WriteLine(" ");
            }
        }
    }
}

执行结果