可以实现数据库自由连接,返回Dataset
using System;
using System.Data.SqlClient;
using System.Data;
namespace MySql
{
public class mysql
{
private SqlDataAdapter myDataAdapter;
DataSet ds;
public static SqlConnection sqlConn;
public static string user_name;
public static bool sqlopen;
public static int _uaeradmin;
public static string _serverip;
/// <summary>
/// 数据库连接
/// </summary>
/// <param name="serverIP">服务器IP</param>
/// <param name="userID">用户名</param>
/// <param name="password">密码</param>
/// <param name="dataBaseName">表名</param>
public static bool sqlinfo(string serverIP, string userID, string password, string dataBaseName)
{
/*
string serverIP = @"localhost\SQLEXPRESS";//"127.0.0.1" = "localhost"
string userID = "sa";//访问数据库服务的用户名
string password = "mypass";//用户密码
string dataBaseName = "库存管理";//要访问的数据库名称
* */
try
{
sqlConn = new SqlConnection("Server=" + serverIP + ";Uid=" + userID + ";Pwd=" + password + ";Database=" + dataBaseName);
ServerIP = serverIP;
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 返回或者设置当前登录用户
/// </summary>
public static string User_name
{
get{return user_name;}
set{user_name=value;}
}
/// <summary>
/// 返回数据库是否连接
/// </summary>
public static bool SQLopen
{
get { return sqlopen; }
set { sqlopen = value; }
}
/// <summary>
/// 返回当前用户的操作权限
/// </summary>
public static int UserAdmin
{
get { return _uaeradmin; }
set { _uaeradmin = value; }
}
/// <summary>
/// 返回当前连接的IP
/// </summary>
public static string ServerIP
{
get { return _serverip; }
set { _serverip = value; }
}
/// <summary>
/// 返回一个DataSet
/// </summary>
/// <param name="sql">SQL命令</param>
/// <param name="table">表名</param>
/// <returns></returns>
public DataSet GetDataSet(string sql, string table)
{
sqlConn.Open();
myDataAdapter = new SqlDataAdapter(sql, sqlConn);
ds = new DataSet();
myDataAdapter.Fill(ds, table);
sqlConn.Close();
return ds;
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="ds">DataSet传递</param>
/// <param name="table">表名</param>
/// <returns></returns>
public bool sqlupdata(DataSet ds, string table)
{
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myDataAdapter);
// 生成Delete/Update/Insert操作的Command
myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
try
{
// 保存到数据库
myDataAdapter.Update(ds.Tables[table]);
myCommandBuilder.Dispose();
ds.Dispose();
myDataAdapter.Dispose();
return true;
}
catch (Exception )
{
myCommandBuilder.Dispose();
ds.Dispose();
myDataAdapter.Dispose();
return false;
}
}
///一下可以实现用户登录
public bool userLogin(string user ,string password)
{
DataSet userdt;
string sql="select * from 用户管理 where '用户名'= '"+user+"' and '密码'='"+password+"'";
userdt = GetDataSet("select * from 用户管理 where convert(nvarchar(255),用户名)='" + user + "'and convert(nvarchar(255),密码)='" + password + "'", "用户管理");
if (userdt.Tables[0].Rows.Count == 0)
{
return false;
}
else
{
User_name = userdt.Tables[0].Rows[0]["姓名"].ToString();
SQLopen = true;
_uaeradmin =Int32.Parse( userdt.Tables[0].Rows[0]["权限"].ToString().Trim());
return true;
}
//"SELECT * FROM `USER` WHERE `USER_NAME` = '" + textBox1.Text + "' AND `USER_PASSWORD` = '"
}
}
}