3_05_MSSQL课程_Ado.Net_SQLDataAdapter

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _01SQLAndAdo.Net
{
    public partial class SqlData2DataGirdView : Form
    {
        public SqlData2DataGirdView()
        {
            InitializeComponent();
        }

        private void SqlData2DataGirdView_Load(object sender, EventArgs e)
        {

            string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            string commandText = @"SELECT TOP 1000 [User_ID]
                                    ,[User_Name]
                                    ,[User_Gender]
                                    ,[User_Age]
                                    ,[User_Address]
                                    ,[User_Birthday]
                                    ,[User_Remark]
                                    ,[User_Money]
                                    ,[User_GUIDDemo]
                                    ,[User_Pwd]
                                     FROM[LJK_SQLServerDB].[dbo].[User_Info]";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(commandText, connStr))
                {
                    //DataSet方式，查询多张表的时候应用
                    //DataSet dataSet=new DataSet();
                    //adapter.Fill(dataSet,"demo");
                    //this.dgvUserInfo.DataSource = dataSet.Tables["demo"];

                    DataTable dataTable = new DataTable();
                    //填充之前不需要打开数据库连接，Adaper会自动打开连接，并执行SQL
                    //Fill方法内部：
                    //1. 先判断SqlConnection是否初始化，如果没有打开链接，那么打开连接。
                    //2. 初始化一个Select(commandText) SqlCommand对象
                    //3. 通过cmd对象执行，然后返回一个SqlReader对象。
                    //4. 读取数据库中的数据，然后填充到DataTable上去
                    adapter.Fill(dataTable); //把数据库中的数据 填充到 内存表中
                    //把内存表显示到DataGirdView上去
                    // this.dgvUserInfo.DataSource = dataTable;

                    // var o = dataTable.Rows[0][1];

                    // dataRow["列名"];       //弱类型
                    //user.UserInfo           //强类型，出错概率下降
                    List<UserInfo> userInfoList = new List<UserInfo>();
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        Console.WriteLine(dataRow["User_Id"] + "         " + dataRow[0]);
                        Console.WriteLine(dataRow["User_Name"] + "         " + dataRow[1]);
                        Console.WriteLine(dataRow["User_Age"] + "         " + dataRow[2]);
                        Console.WriteLine(dataRow["User_Pwd"] + "         " + dataRow[3]);
                        UserInfo userInfo = new UserInfo();
                        userInfo.UserId = int.Parse(dataRow["User_ID"].ToString());
                        userInfo.UserName = dataRow["User_Name"].ToString();
                        if (!string.IsNullOrEmpty(dataRow["User_Age"].ToString()))
                        {
                            userInfo.UserAge = int.Parse(dataRow["User_Age"].ToString());
                            // userInfo.UserAge = dataRow["User_Age"].ToString();
                            // userInfo.UserAge = 1;
                        }
                        else
                        {
                            userInfo.UserAge = 0;
                        }

                        if (!string.IsNullOrEmpty(dataRow["User_Pwd"].ToString()))
                        {
                            userInfo.UserPwd = int.Parse(dataRow["User_Pwd"].ToString());
                        }
                        else
                        {
                            userInfo.UserPwd = 11;
                        }

                        userInfoList.Add(userInfo);


                        //把DataTabel的数据转成List<UserInfo>

                    }

                    this.dgvUserInfo.DataSource = userInfoList;

                }

            }


        }
    }
}