using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.IO;
namespace comback
{
class ServerDataBase
{
MySqlConnection conn;
string connString = "Persist Security Info=False;database=tldb;server=127.0.0.1;user id=root;pwd=123456";
private void OpenConn()
{
try
{
conn = new MySqlConnection();
conn.ConnectionString = connString;
conn.Open();
}
catch (Exception ee)
{
System.Windows.Forms.MessageBox.Show("打开数据库异常:" + ee.Message);
}
}
/// <summary>
/// 修改功能
/// </summary>
/// <param name="sql">修改语句</param>
/// <returns></returns>
public int ExcuteUser(string sql)
{
MySqlCommand cmd = null;
MySqlTransaction trans = null;
int count = 0;
try
{
if (conn == null || conn.State == ConnectionState.Closed)
{
OpenConn();
}
cmd = conn.CreateCommand();
cmd.CommandText = sql;
//创建事务
trans = conn.BeginTransaction();
count = cmd.ExecuteNonQuery();
//事务提交
trans.Commit();
}
catch
{
//事务回滚
trans.Rollback();
}
finally
{
if (conn != null && (conn.State != ConnectionState.Closed))
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
return count;
}
//检验登录用户
public string CheckUser(string username,string password)
{
string role = "";
MySqlCommand cmd = null;
MySqlDataReader reader = null;
try
{
if (conn == null || conn.State == ConnectionState.Closed)
{
OpenConn();
}
cmd = conn.CreateCommand();
cmd.CommandText = "select role from tluser where username='" + username + "' and password='" + password + "'";
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (reader.Read())
{
role = reader.GetString("role");
}
}
else
{
role = "";
}
}
catch (Exception ee)
{
System.Windows.Forms.MessageBox.Show("服务器检查登录的用户合法性时异常:" + ee.Message);
}
finally
{
if (conn != null && (conn.State != ConnectionState.Closed))
{
if (reader.IsClosed)
{
reader.Close();
reader.Dispose();
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
return role;
}
}
}
/// <summary>
/// 获得数据集DataSet
/// </summary>
/// <param name="ds"></param>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet getDS(DataSet ds, string sql)
{
//打开连接
getConnection();
try
{
//查询结果
MySqlDataAdapter mda = new MySqlDataAdapter(sql, conn);
mda.Fill(ds, "data");
}
catch (System.Exception e)
{
MessageBox.Show("getDS()异常:" + e.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
return ds;
}