ADO.NET读取MySQL数据库的三种方式：DataReader、DataSet、DataView

https://blog.csdn.net/lilongsy/article/details/127351421

ADO.NET读取MySQL数据库有多种方式：DataReader、DataSet、DataView。
Command对象的ExecuteScalar方法查询数据库获取某个单个值，但是如果获取多行、多列可以用ExcecuteReader，ExcecuteReader返回一个DataReader的数据流对象。
DataSet是存在于内存中的数据库，不依赖于数据库的独立数据集合，内部是用XML来描述数据的，需要与DataAdapter来填充数据。
DataView表示用于排序、筛选、搜索、编辑和排序的DataTable的可绑定数据的自定义视图。
本文使用以上三种方式做了一个实例：

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

MySqlConnection conn;
string sql = @"SELECT * FROM users WHERE 1=1 ";
DataSet dataSet;

private void Form1_Load(object sender, EventArgs e)
{
string connStr = "server=localhost;Database=pikachu;uid=root;pwd=root;charset=utf8";
conn = new MySqlConnection(connStr);
}

 

private void btnDataReader_Click(object sender, EventArgs e)
{
try
{
MySqlDataReader dataReader;

if(conn.State != ConnectionState.Open)
{
conn.Open();
}

if (conn != null && conn.State == ConnectionState.Open)
{
MySqlCommand command = new MySqlCommand(sql, conn);
dataReader = command.ExecuteReader();
// MessageBox.Show("数据准备完毕！");
StringBuilder stringBuilder = new StringBuilder();

if (dataReader.HasRows)
{
while (dataReader.Read())
{
stringBuilder.Append(string.Format("ID:{0} 用户名：{1}\r\n", dataReader[0].ToString(),
dataReader[1].ToString()));
}
}
dataReader = null;
conn.Close();
MessageBox.Show(stringBuilder.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void btnDataSet_Click(object sender, EventArgs e)
{
try {
if (conn.State != ConnectionState.Open)
{
conn.Open();
}

if (conn != null && conn.State == ConnectionState.Open)
{
MySqlDataAdapter dataAdapter = new MySqlDataAdapter();
dataSet = new DataSet();
dataAdapter.SelectCommand = new MySqlCommand(sql, conn);
dataAdapter.Fill(dataSet);
// 拼接字符串
StringBuilder stringBuilder = new StringBuilder();
// DataTable对象
DataTable dataTable = dataSet.Tables[0];
for (int i = 0; i < dataTable.Rows.Count; i++)
{
stringBuilder.Append(string.Format("ID:{0}, 用户名：{1}\r\n",
dataTable.Rows[i][0], dataTable.Rows[i][1]));
}
conn.Close();
MessageBox.Show(stringBuilder.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}

private void btnDataView_Click(object sender, EventArgs e)
{
if (dataSet == null)
{
MessageBox.Show("dataSet不能为空");
return;
}
DataView dataView = dataSet.Tables[0].DefaultView;
dataView.RowFilter = "id>1";
dataView.Sort = "id DESC";
StringBuilder stringBuilder = new StringBuilder();
foreach(DataRowView dataRowView in dataView)
{
for (int i = 0; i < dataView.Table.Columns.Count; i++)
{
stringBuilder.Append(dataRowView[i].ToString() + "\r\n");
}
}
MessageBox.Show(stringBuilder.ToString());
}
}
}