using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace WpfApplication1
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
//string constr = "server=localhost;User Id=root;password=1;Database=hibernate_day01";
//MySqlConnection mycon = new MySqlConnection(constr);
//mycon.Open();
//MySqlCommand mycmd = new MySqlCommand("insert into `t_user` (`uid`, `username`, `password`, `address`, `user_id`, `user_name`, `user_password`) values('4','hanmeimei','123','北京1228','0',NULL,NULL);", mycon);
//if (mycmd.ExecuteNonQuery() > 0)
//{
// Console.WriteLine("数据插入成功!");
//}
//Console.ReadLine();
//mycon.Close();
//GetEntityListByDT();
//HasRows1();
//1 连接字符串
string connectionString
// = "server=127.0.0.1;integrated security=true;database=MSPetShop4";
// = "server=.;uid=sa;pwd=SQL@5;database=AdventureWorks2012";
= "server=localhost;User Id=root;password=1;Database=hibernate_day01";
//2 实例化数据库连接
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
//定义执行SQL语句,可以为select查询,也可以为存储过程,我们要的只是返回的结果集.
string sql = "SELECT USERNAME,PASSWORD,ADDRESS FROM t_user";
//强大的SqlDataAdapter
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
//Fill 方法会执行一系列操作 connection.open command.reader 等等
//反正到最后就把 sql语句执行一遍,然后把结果集插入到 ds 里.
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
List<user> userList = TableToEntity<user>(dt);
}
Console.ReadLine();
}
private static List<T> TableToEntity<T>(DataTable dt) where T : class,new()
{
Type type = typeof(T);
List<T> list = new List<T>();
foreach (DataRow row in dt.Rows)
{
PropertyInfo[] pArray = type.GetProperties();
T entity = new T();
foreach (PropertyInfo p in pArray)
{
if (row[p.Name] is Int64)
{
p.SetValue(entity, Convert.ToInt32(row[p.Name]), null);
continue;
}
p.SetValue(entity, row[p.Name], null);
}
list.Add(entity);
}
return list;
}
private static void HasRows1()
{
String mysqlStr = "Database=hibernate_day01;Data Source=127.0.0.1;User Id=root;Password=1;pooling=false;CharSet=utf8;port=3306";
//MySql连接类
MySqlConnection mysql = new MySqlConnection(mysqlStr);
//查询sql
//account表格
String abc = "t_user";
String sqlSearch = "select * from ";
sqlSearch = sqlSearch + abc;
//sql 插入数据
String sqlinsert = "insert into `t_user` (`uid`, `username`, `password`, `address`, `user_id`, `user_name`, `user_password`) values('5','hanmeimei','123','北京1228','0',NULL,NULL);";
//MySql的命令类
//查询语句
MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, mysql);
//插入数据
// MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql);
//打开连接
mysql.Open();
//建立流
//数据读取类
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{//每次读取一个字节
while (reader.Read())
{
if (reader.HasRows)//是否读取玩一行
{
//从数据库读取的数据要进行类型转换
//0,1,2,表示在数据库表中的位置
// Console.WriteLine("ID:" + reader.GetString(1) + "|账号:" + reader.GetString(2) + "|密码:" + reader.GetString(2));
MessageBox.Show("username:" + reader.GetString(1) + "|password:" + reader.GetString(2) + "|address:" + reader.GetString(3));
}
}
}
catch (Exception)
{
Console.WriteLine("查询失败了!");
}
finally
{
reader.Close();//关闭流
}
mysql.Close();
Console.Read();
}
public static List<T> GetEntityListByDT<T>(DataTable srcDT)
{
List<T> list = null;
T destObj = default(T);
if (srcDT != null && srcDT.Rows.Count > 0)
{
list = new List<T>();
//foreach (DataRow row in srcDT.Rows)
//{
// destObj = GetEntityListByDT<T>(row, relation);
// list.Add(destObj);
//}
}
return list;
}
//#region 建立MySql数据库连接
/ <summary>
/ 建立数据库连接.
/ </summary>
/ <returns>返回MySqlConnection对象</returns>
//public MySqlConnection getmysqlcon()
//{
// //http://sosoft.cnblogs.com/
// string M_str_sqlcon = "server=localhost;user id=root;password=root;database=abc"; //根据自己的设置
// MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
// return myCon;
//}
//#endregion
//#region 执行MySqlCommand命令
/ <summary>
/ 执行MySqlCommand
/ </summary>
/ <param name="M_str_sqlstr">SQL语句</param>
//public void getmysqlcom(string M_str_sqlstr)
//{
// MySqlConnection mysqlcon = this.getmysqlcon();
// mysqlcon.Open();
// MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
// mysqlcom.ExecuteNonQuery();
// mysqlcom.Dispose();
// mysqlcon.Close();
// mysqlcon.Dispose();
//}
//#endregion
//#region 创建MySqlDataReader对象
/ <summary>
/ 创建一个MySqlDataReader对象
/ </summary>
/ <param name="M_str_sqlstr">SQL语句</param>
/ <returns>返回MySqlDataReader对象</returns>
//public MySqlDataReader getmysqlread(string M_str_sqlstr)
//{
// MySqlConnection mysqlcon = this.getmysqlcon();
// MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
// mysqlcon.Open();
// MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
// return mysqlread;
//}
//#endregion
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace WpfApplication1
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
//string constr = "server=localhost;User Id=root;password=1;Database=hibernate_day01";
//MySqlConnection mycon = new MySqlConnection(constr);
//mycon.Open();
//MySqlCommand mycmd = new MySqlCommand("insert into `t_user` (`uid`, `username`, `password`, `address`, `user_id`, `user_name`, `user_password`) values('4','hanmeimei','123','北京1228','0',NULL,NULL);", mycon);
//if (mycmd.ExecuteNonQuery() > 0)
//{
// Console.WriteLine("数据插入成功!");
//}
//Console.ReadLine();
//mycon.Close();
//GetEntityListByDT();
//HasRows1();
//1 连接字符串
string connectionString
// = "server=127.0.0.1;integrated security=true;database=MSPetShop4";
// = "server=.;uid=sa;pwd=SQL@5;database=AdventureWorks2012";
= "server=localhost;User Id=root;password=1;Database=hibernate_day01";
//2 实例化数据库连接
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
//定义执行SQL语句,可以为select查询,也可以为存储过程,我们要的只是返回的结果集.
string sql = "SELECT USERNAME,PASSWORD,ADDRESS FROM t_user";
//强大的SqlDataAdapter
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
DataSet ds = new DataSet();
//Fill 方法会执行一系列操作 connection.open command.reader 等等
//反正到最后就把 sql语句执行一遍,然后把结果集插入到 ds 里.
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
List<user> userList = TableToEntity<user>(dt);
}
Console.ReadLine();
}
private static List<T> TableToEntity<T>(DataTable dt) where T : class,new()
{
Type type = typeof(T);
List<T> list = new List<T>();
foreach (DataRow row in dt.Rows)
{
PropertyInfo[] pArray = type.GetProperties();
T entity = new T();
foreach (PropertyInfo p in pArray)
{
if (row[p.Name] is Int64)
{
p.SetValue(entity, Convert.ToInt32(row[p.Name]), null);
continue;
}
p.SetValue(entity, row[p.Name], null);
}
list.Add(entity);
}
return list;
}
private static void HasRows1()
{
String mysqlStr = "Database=hibernate_day01;Data Source=127.0.0.1;User Id=root;Password=1;pooling=false;CharSet=utf8;port=3306";
//MySql连接类
MySqlConnection mysql = new MySqlConnection(mysqlStr);
//查询sql
//account表格
String abc = "t_user";
String sqlSearch = "select * from ";
sqlSearch = sqlSearch + abc;
//sql 插入数据
String sqlinsert = "insert into `t_user` (`uid`, `username`, `password`, `address`, `user_id`, `user_name`, `user_password`) values('5','hanmeimei','123','北京1228','0',NULL,NULL);";
//MySql的命令类
//查询语句
MySqlCommand mySqlCommand = new MySqlCommand(sqlSearch, mysql);
//插入数据
// MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql);
//打开连接
mysql.Open();
//建立流
//数据读取类
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{//每次读取一个字节
while (reader.Read())
{
if (reader.HasRows)//是否读取玩一行
{
//从数据库读取的数据要进行类型转换
//0,1,2,表示在数据库表中的位置
// Console.WriteLine("ID:" + reader.GetString(1) + "|账号:" + reader.GetString(2) + "|密码:" + reader.GetString(2));
MessageBox.Show("username:" + reader.GetString(1) + "|password:" + reader.GetString(2) + "|address:" + reader.GetString(3));
}
}
}
catch (Exception)
{
Console.WriteLine("查询失败了!");
}
finally
{
reader.Close();//关闭流
}
mysql.Close();
Console.Read();
}
public static List<T> GetEntityListByDT<T>(DataTable srcDT)
{
List<T> list = null;
T destObj = default(T);
if (srcDT != null && srcDT.Rows.Count > 0)
{
list = new List<T>();
//foreach (DataRow row in srcDT.Rows)
//{
// destObj = GetEntityListByDT<T>(row, relation);
// list.Add(destObj);
//}
}
return list;
}
//#region 建立MySql数据库连接
/ <summary>
/ 建立数据库连接.
/ </summary>
/ <returns>返回MySqlConnection对象</returns>
//public MySqlConnection getmysqlcon()
//{
// //http://sosoft.cnblogs.com/
// string M_str_sqlcon = "server=localhost;user id=root;password=root;database=abc"; //根据自己的设置
// MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
// return myCon;
//}
//#endregion
//#region 执行MySqlCommand命令
/ <summary>
/ 执行MySqlCommand
/ </summary>
/ <param name="M_str_sqlstr">SQL语句</param>
//public void getmysqlcom(string M_str_sqlstr)
//{
// MySqlConnection mysqlcon = this.getmysqlcon();
// mysqlcon.Open();
// MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
// mysqlcom.ExecuteNonQuery();
// mysqlcom.Dispose();
// mysqlcon.Close();
// mysqlcon.Dispose();
//}
//#endregion
//#region 创建MySqlDataReader对象
/ <summary>
/ 创建一个MySqlDataReader对象
/ </summary>
/ <param name="M_str_sqlstr">SQL语句</param>
/ <returns>返回MySqlDataReader对象</returns>
//public MySqlDataReader getmysqlread(string M_str_sqlstr)
//{
// MySqlConnection mysqlcon = this.getmysqlcon();
// MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
// mysqlcon.Open();
// MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
// return mysqlread;
//}
//#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WpfApplication1
{
public class user
{
//public int id { get; set; }
public string username { get; set; }
public string password { get; set; }
public string address { get; set; }
}
}