需要用到的命名空间
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using MySql.Data.MySqlClient;
连接方法和操作类
/* sqlserver 数据库
SqlConnection sqlconn = new SqlConnection();
SqlDataAdapter sqladapter = new SqlDataAdapter();
SqlCommand sqlcomm = new SqlCommand();
public DBConn()
{
sqlconn = new SqlConnection("server=.;database=MyStu;uid=sa;pwd=");
}
public void OpenConn()
{
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();
}
}
public void CloseConn()
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
}
public DataSet getDataTable(string sql, string tablename)
{
this.OpenConn();
try
{
sqladapter = new SqlDataAdapter(sql, sqlconn);
DataSet ds = new DataSet();
sqladapter.Fill(ds, tablename);
return ds;
}
catch (Exception exp)
{
exp.Message.ToString();
return null;
}
finally
{
CloseConn();
}
}
public bool sqlExce(string sql)
{
this.OpenConn();
try
{
sqlcomm = new SqlCommand(sql, sqlconn);
sqlcomm.ExecuteNonQuery();
}
catch (Exception exp)
{
exp.Message.ToString();
return false;
}
finally
{
CloseConn();
}
return true;
}
public int checkUserLogin(string uname, string upwd)
{
this.OpenConn();
try
{
this.sqlcomm = new SqlCommand("select count(*) from UserInfo where UserName=@loginname and UserPwd=@loginpwd", oleconn);
this.sqlcomm.Parameters.Add(new SqlParameter("@loginname", OleDbType.VarChar, 30));
this.sqlcomm.Parameters["@loginname"].Value = uname;
this.sqlcomm.Parameters.Add(new SqlParameter("@loginpwd", OleDbType.VarChar, 50));
this.sqlcomm.Parameters["@loginpwd"].Value = upwd;
int i = (int)this.sqlcomm.ExecuteScalar();
return i;
}
catch (Exception ex)
{
ex.Message.ToString();
return 0;
}
finally
{
this.CloseConn();
}
}
*/
/* Access数据库
private OleDbConnection oleconn;
private OleDbDataAdapter oleadapter = new OleDbDataAdapter();
private OleDbCommand olecomm = new OleDbCommand();
public DBConn()
{
string strpath = HttpContext.Current.Server.MapPath("Data/WuHan.mdb");
// oleconn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=" + StrConn);
oleconn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDN.4.0;DATA Source=" + strpath);
}
//打开数据库
public void OpenDB()
{
if (oleconn.State == ConnectionState.Closed)
{
oleconn.Open();
}
}
//关闭数据库
public void CloseDB()
{
if (oleconn.State == ConnectionState.Open)
{
oleconn.Close();
}
}
/// <summary>
/// 执行SQL语句(insert,update,delete)
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回所影响的行数</returns>
public int OraExecute(string sql)
{
OpenDB();
try
{
olecomm.Connection = oleconn;
olecomm.CommandType = CommandType.Text;
olecomm.CommandText = sql;
return olecomm.ExecuteNonQuery();
}
catch (Exception exp)
{
exp.Message.ToString();
return -1;
}
finally
{
CloseDB();
}
}
///<summary>
///获取查询结果的第一行第一列值
///</summary>
///<param name="sql">sql语句</param>
///<returns></returns>
public string GetValue(string sql)
{
OpenDB();
try
{
olecomm.Connection = oleconn;
olecomm.CommandType = CommandType.Text;
olecomm.CommandText = sql;
return olecomm.ExecuteScalar().ToString();
}
catch (Exception exp)
{
exp.Message.ToString();
return "error";
}
finally
{
CloseDB();
}
}
*/
private OracleConnection oracleconn;
private OracleDataAdapter oracleadapter = new OracleDataAdapter();
private OracleCommand oraclecomm = new OracleCommand();
public DBConn()
{
// sqlconn = new SqlConnection("server=.;database=MyStu;uid=sa;pwd=");
oracleconn=new OracleConnection("Data Source=hmoradb;User Id=tfds;Password=tfds");
}
//打开数据库
public void OpenDB()
{
if (oracleconn.State == ConnectionState.Closed)
{
oracleconn.Open();
}
}
//关闭数据库
public void CloseDB()
{
if (oracleconn.State == ConnectionState.Open)
{
oracleconn.Close();
}
}
///<summary>
///获取查询结果的第一行第一列值
///</summary>
///<param name="sql">sql语句</param>
///<returns></returns>
public string GetValue(string sql)
{
OpenDB();
try
{
oraclecomm.Connection = oracleconn;
oraclecomm.CommandType = CommandType.Text;
oraclecomm.CommandText = sql;
return oraclecomm.ExecuteOracleScalar().ToString();
}
catch (Exception exp)
{
exp.Message.ToString();
return "error";
}
finally
{
CloseDB();
}
}
/// <summary>
/// 执行SQL语句(insert,update,delete)
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>返回所影响的行数</returns>
public int OraExecute(string sql)
{
OpenDB();
try
{
oraclecomm.Connection = oracleconn;
oraclecomm.CommandType = CommandType.Text;
oraclecomm.CommandText = sql;
return oraclecomm.ExecuteNonQuery();
}
catch (Exception exp)
{
exp.Message.ToString();
return -1;
}
finally
{
CloseDB();
}
}
//返回一个数据集
public DataSet getDataSet(string sql, string tablename)
{
this.OpenDB();
try
{
this.oracleadapter = new OracleDataAdapter(sql, oracleconn);
DataSet ds = new DataSet();
this.oracleadapter.Fill(ds, tablename);
return ds;
}
catch (Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.CloseDB();
}
}
/* sqlserver 数据库
MySqlConnectionsqlconn = new MySqlConnection();
MySqlDataReadersqladapter = new MySqlDataReader();
MySqlCommandsqlcomm = new MySqlCommand();
public DBConn()
{
sqlconn = new MySqlConnection("server=.;database=MyStu;uid=sa;pwd=");
}
public void OpenConn()
{
if (sqlconn.State == ConnectionState.Closed)
{
sqlconn.Open();
}
}
public void CloseConn()
{
if (sqlconn.State == ConnectionState.Open)
{
sqlconn.Close();
}
}
public DataSet getDataTable(string sql, string tablename)
{
this.OpenConn();
try
{
sqladapter = new MySqlDataReader(sql, sqlconn);
DataSet ds = new DataSet();
sqladapter.Fill(ds, tablename);
return ds;
}
catch (Exception exp)
{
exp.Message.ToString();
return null;
}
finally
{
CloseConn();
}
}
public bool sqlExce(string sql)
{
this.OpenConn();
try
{
sqlcomm = new MySqlCommand(sql, sqlconn);
sqlcomm.ExecuteNonQuery();
}
catch (Exception exp)
{
exp.Message.ToString();
return false;
}
finally
{
CloseConn();
}
return true;
}
public int checkUserLogin(string uname, string upwd)
{
this.OpenConn();
try
{
this.sqlcomm = new MySqlCommand("select count(*) from UserInfo where UserName=@loginname and UserPwd=@loginpwd", oleconn);
this.sqlcomm.Parameters.Add(new MySqlDataReader("@loginname", OleDbType.VarChar, 30));
this.sqlcomm.Parameters["@loginname"].Value = uname;
this.sqlcomm.Parameters.Add(new MySqlDataReader("@loginpwd", OleDbType.VarChar, 50));
this.sqlcomm.Parameters["@loginpwd"].Value = upwd;
int i = (int)this.sqlcomm.ExecuteScalar();
return i;
}
catch (Exception ex)
{
ex.Message.ToString();
return 0;
}
finally
{
this.CloseConn();
}
}
*/
