将NpgsqlDataReader转换为DataTable

港控/mmm° 2022-02-17 02:45 389阅读 0赞

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

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpeWF6aGVuMjAxMQ_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpeWF6aGVuMjAxMQ_size_16_color_FFFFFF_t_70]: /images/20220217/a5f187d2471f48e6ac004a9993b0613d.png