❤️❤️❤️利用NHibernate和MySQL交互的命令语句（增删改查）

1、查询一条数据

string connect Str = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "select * from goods";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();//读取第一条
Console.WriteLine(reader[0] + "   " + reader[1]+ "   " + reader[2]+ "   " + reader[3]);
reader.Read();//读取第二条
Console.WriteLine(reader[0] + "   " + reader[1]+ "   " + reader[2]+ "   " + reader[3]);
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下

2、查询所有数据

string connect Str = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "select * from goods";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{//Read()方法会返回当前条是否有数据，没有数据就返回false
    //Console.WriteLine(reader[0] + "   " + reader[1]+ "   " + reader[2]+ "   " + reader[3]);
 Console.WriteLine(reader.GetInt32("goodsid")+reader.GetString("name")+reader.GetString("goodstype")+reader.GetInt32("hp"));//推荐此方法进行输出
}
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下

3、只查询某几项的所有数据

string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "select name,goodstype from goods";//读取name项和goodstype项的数据
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    //Console.WriteLine(reader[0] + "   " + reader[1]);
Console.WriteLine(reader.GetInt32("goodsid")+reader.GetString("name")+reader.GetString("goodstype")+reader.GetInt32("hp"));//推荐此方法进行输出
}
connect.Close();//关闭链接
Console.ReadKey();//让程序暂停一下

4、插入一行数据

string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "insert into users(username,password,registertime) 
                                                                                 values('hhhH','rrrr','"+DateTime.Now+"')";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接

5、修改数据

string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "update users set username='lllllll' where id='2'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接

6、删除数据

 string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "delete from users where id='1347'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
int result = cmd.ExecuteNonQuery();//返回值是数据库中受影响的数据行数
Console.WriteLine(result);
connect.Close();//关闭链接

7、在查询的时候添加参数

方法一：

static bool isHaveUser(string username,string password)
{
string connectStr =          "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "select * from users where username='"+username+"'and password='"+password+"'";
MySqlCommand cmd = new MySqlCommand(sql, connect);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
    connect.Close();//关闭链接
    return true;
}
    connect.Close();//关闭链接
    return false;
}

方法二：

static bool isHaveUser(string username,string password)
{
string connectStr = "server=127.0.0.1;port=3306;database=mygamedb;user=root;password=root";
MySqlConnection connect = new MySqlConnection(connectStr);//并没有跟数据库建立链接
connect.Open();//跟数据库建立链接
Console.WriteLine("数据库链接成功！");
string sql = "select * from users where username = @username and password = @password";
MySqlCommand cmd = new MySqlCommand(sql, connect);
cmd.Parameters.AddWithValue("username", username);
cmd.Parameters.AddWithValue("password", password);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
     connect.Close();//关闭链接
     return true;
}
    connect.Close();//关闭链接
    return false;
}