using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace 封装类测试
{
public class OperaterDB
{
private SqlConnection objSqlConnection;
private SqlCommand objSqlCommand;
private SqlDataAdapter objSqlDataAdapter;
private SqlDataReader objSqlDataReader;
// private DataSet objDataSet;
private DataTable objDataTable = new DataTable();
private bool flag=false;
public OperaterDB(String strSqlConnection)
{
objSqlConnection=new SqlConnection(strSqlConnection);
}
//添加记录
public void AddData(string insertCmd,string message)
{
objSqlCommand=new SqlCommand(insertCmd,this.objSqlConnection);
try
{
objSqlConnection.Open();
//插入记录
objSqlCommand.ExecuteNonQuery();
MessageBox.Show("插入"+message+"成功!");
}
catch(SqlException ex)
{
MessageBox.Show(message+"addsql语句:"+insertCmd+"\n\r错误原因:"+ex.Message);
}
finally
{
objSqlConnection.Close();
}
}
//更新记录
public void UpdateData(string updataCmd,string message)
{
objSqlCommand=new SqlCommand(updataCmd,this.objSqlConnection);
try
{
objSqlConnection.Open();
//执行更新
objSqlCommand.ExecuteNonQuery();
MessageBox.Show("更新"+message+"成功!");
}
catch(SqlException ex)
{
MessageBox.Show(message+"updatesql语句:"+updataCmd+"\n\r错误原因:"+ex.Message);
}
finally
{
objSqlConnection.Close();
}
}
//删除成功
public void DeleteData(string deleteCmd,string message)
{
objSqlCommand=new SqlCommand(deleteCmd,this.objSqlConnection);
try
{
objSqlConnection.Open();
//执行删除
objSqlCommand.ExecuteNonQuery();
MessageBox.Show("删除"+message+"成功!");
}
catch(SqlException ex)
{
MessageBox.Show(message+"deletesql语句:"+deleteCmd+"\n\r错误原因:"+ex.Message);
}
finally
{
objSqlConnection.Close();
}
}
//登录查询方法
public string validateLogin(string selectCmd,int colName,int colpwd,int colRole, string password,string userName)
{
bool flag=false;
string role="";
try
{
objSqlConnection.Open();
this.objSqlCommand=new SqlCommand(selectCmd,this.objSqlConnection);
this.objSqlDataReader=this.objSqlCommand.ExecuteReader();
while(this.objSqlDataReader.Read())
{
if(this.objSqlDataReader.HasRows)
{
if(this.objSqlDataReader.GetValue(colpwd).Equals(password)&&this.objSqlDataReader.GetValue(colName).Equals(userName))
{
flag=true;
role=this.objSqlDataReader.GetValue(colRole).ToString();
break;
}
}
}
if(flag)
{
MessageBox.Show("登录成功");
}
else
{
MessageBox.Show("用户名密码错误\n\r请重新输入");
}
}
catch(SqlException ex)
{
MessageBox.Show(ex.Message);
}
return role;
}
//重载登录查询方法 不带权限参数 +1
public bool validateLogin(string selectCmd,int colName,int colpwd, string password,string userName)
{
bool flag=false;
try
{
objSqlConnection.Open();
this.objSqlCommand=new SqlCommand(selectCmd,this.objSqlConnection);
this.objSqlDataReader=this.objSqlCommand.ExecuteReader();
while(this.objSqlDataReader.Read())
{
if(this.objSqlDataReader.HasRows)
{
if(this.objSqlDataReader.GetValue(colpwd).Equals(password)&&this.objSqlDataReader.GetValue(colName).Equals(userName))
{
flag=true;
break;
}
}
}
if(flag)
{
MessageBox.Show("登录成功");
}
else
{
MessageBox.Show("用户名密码错误\n\r请重新输入");
}
}
catch(SqlException ex)
{
MessageBox.Show(ex.Message);
}
return flag;
}
//查询数据生成数据集
public SqlDataAdapter SelectData(string selectsql)
{
this.objSqlDataAdapter=new SqlDataAdapter(selectsql,this.objSqlConnection);
return objSqlDataAdapter;
}
//
public bool SelectData(string selectCmd,string tablename,string success,string failure,string colsname,string txtcolsname)
{
bool flag=false;
try
{
//打开连接
objSqlConnection.Open();
DataSet ojbDataSet=new DataSet();
objSqlDataAdapter=new SqlDataAdapter(selectCmd,this.objSqlConnection);
objSqlDataAdapter.Fill(ojbDataSet,tablename);
//创建显示、可重用的DELETE命令
objSqlDataAdapter.DeleteCommand=this.objSqlConnection.CreateCommand();
objSqlDataAdapter.DeleteCommand.CommandText=selectCmd;
foreach(DataRow dr in ojbDataSet.Tables[0].Rows)
{
//检查各值
if(dr[colsname].Equals(txtcolsname))
{
DataRow objDataRow=dr;
flag=true;
break;
}
}
if(flag)
{
if(!success.Equals(""))
{
MessageBox.Show(success);
}
}
else
{
MessageBox.Show(failure);
}
}
catch(SqlException ex)
{
MessageBox.Show(failure+"selectsql语句:"+selectCmd+"\n\r错误原因:"+ex.Message);
}
catch(Exception ex)
{
MessageBox.Show(failure+"selectsql语句:"+selectCmd+"\n\r错误原因:"+ex.Message);
}
finally
{
objSqlConnection.Close();
}
return flag;
}
//创建一个转帐的事务
public bool RunSqlTransaction(string outCmd,string inCmd)
{
this.objSqlConnection.Open();
SqlCommand myCommand=objSqlConnection.CreateCommand();
SqlTransaction myTrans;
//开始一个事务
myTrans=this.objSqlConnection.BeginTransaction();
//必须同时指定事务和连接对象给Command对象
myCommand.Connection=this.objSqlConnection;
myCommand.Transaction=myTrans;
try
{
myCommand.CommandText=outCmd;
myCommand.ExecuteNonQuery();
myCommand.CommandText=inCmd;
myCommand.ExecuteNonQuery();
//执行成功提交事务
myTrans.Commit();
MessageBox.Show("两条记录更新成功!");
flag=true;
}
catch(Exception e)
{
try
{
//执行事务回滚
myTrans.Rollback();
}
catch(SqlException ex)
{
if(myTrans.Connection!=null)
{
MessageBox.Show("在执行转帐事务的回滚过程中出现错误代号为:"+ex.GetType()+"的错误");
}
}
MessageBox.Show("sql语句:"+outCmd+"\n\r"+inCmd+"\n\r在添加数据过程中出现错误代码为:"+e.GetType()+"的错误\n\r没有更新数据到数据库中");
}
finally
{
this.objSqlConnection.Close();
}
return flag;
}
}
} |