ado.net提供了丰富的数据库操作,这些操作可以分为三个步骤:
- 使用SqlConnection对象连接数据库;
- 建立SqlCommand对象,负责SQL语句的执行和存储过程的调用;
- 对SQL或存储过程执行后返回的“结果”进行操作。
namespace DBU
{/**此接口的主要用途是释放非托管的资源。 垃圾回收器自动释放不再使用该对象时分配给托管对象的内存。
使用Dispose的此接口可显式释放垃圾回收器结合使用的非托管的资源的方法。
不再需要该对象时,对象的使用者可以调用此方法。
**/
public class DbHelper : IDisposable
{
//创建私有的连接对象:
private SqlConnection sqlConn = new SqlConnection();
//创建私有的命令对象:
private SqlCommand sqlCmd = new SqlCommand();
//公共的属性(对私有字段sqlCmd的封装,以便在类的外部通过SqlCmd属性访问):
public SqlCommand SqlCmd
{
get { return sqlCmd; }
}
//默认构造方法(初始化sqlConn和sqlCmd):
public DbHelper()
{
//方式一:将连接字符串直接写死在这里(NewsDB为数据库名)
//string connString = "Data Source=192.168.35.150;Initial Catalog=Books;User ID=DbUser;Password=123456";
//SQL验证登录:Server=服务器名称;user=登录SQL的用户名;pwd=登录SQL的用户名的密码;database=数据库名称;
//Data Source=.;Initial Catalog=Books;Integrated Security=SSPI
// Windows验证登录:Server=服务器名称;integrated security=SSPI;Initial Catalog=数据库名称;
string connString = "Data Source=.;Initial Catalog=Books;Integrated Security=SSPI";
sqlConn.ConnectionString = connString;
sqlCmd.Connection = sqlConn;
}
//带参数的构造方法(初始化sqlConn和sqlCmd):
public DbHelper(string connString)
{
sqlConn.ConnectionString = connString;
sqlCmd.Connection = sqlConn;
}
// 用于向sqlCmd对象中添加SQL命令的参数的方法之一:
public void AddParameter(string name, object value)
{
SqlParameter p = new SqlParameter();
p.ParameterName = name;
p.Value = value;
sqlCmd.Parameters.Add(p);//向sqlCmd对象添加数据库参数
}
// 用于向sqlCmd对象中添加SQL命令的参数的方法之二:
public void AddParameter(SqlParameter parameter)
{
sqlCmd.Parameters.Add(parameter);
}
//开启事务:
public void BeginTransaction()
{
if (sqlConn.State == ConnectionState.Closed)
sqlConn.Open();
sqlCmd.Transaction = sqlConn.BeginTransaction();
}
//提交(结束)事务:
public void CommitTransaction()
{
sqlCmd.Transaction.Commit();
sqlConn.Close();
}
//回滚事务:
public void RollbackTransaction()
{
sqlCmd.Transaction.Rollback();
sqlConn.Close();
}
//用于执行非查询的SQL命令(可以指定为sql语句或存储过程)的方法之一:
public int ExecuteNonQuery(string query, CommandType commandType, ConnState connectionState)
{
sqlCmd.CommandText = query;
sqlCmd.CommandType = commandType;//sql字符串、存储过程
try
{
if (sqlConn.State == ConnectionState.Closed)
sqlConn.Open();
int i = sqlCmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd.Parameters.Clear();
if (connectionState == ConnState.CloseOnExit)
sqlConn.Close();
}
}
//用于执行非查询的SQL命令(只能为SQL命令不含存储过程)的方法之二:
public int ExecuteNonQuery(string query)
{
return ExecuteNonQuery(query, CommandType.Text, ConnState.CloseOnExit);
}
//用于执行非查询的SQL命令(可以指定为sql语句或存储过程)的方法之三:
public int ExecuteNonQuery(string query, CommandType commandType)
{
return ExecuteNonQuery(query, commandType, ConnState.CloseOnExit);
}
//用于执行非查询的SQL命令(只能为SQL命令,不含存储过程)的方法之四:
public int ExecuteNonQuery(string query, ConnState connectionState)
{
return ExecuteNonQuery(query, CommandType.Text, connectionState);
}
//用于执行返回一个值的方法之一:
public object ExecuteScalar(string query, CommandType commandType, ConnState connectionState)
{
sqlCmd.CommandText = query;
sqlCmd.CommandType = commandType;
object o = null;
try
{
if (sqlConn.State == System.Data.ConnectionState.Closed)
sqlConn.Open();
o = sqlCmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd.Parameters.Clear();
if (connectionState == ConnState.CloseOnExit)
sqlConn.Close();
}
return o;
}
//用于执行返回一个值的方法之二:
public object ExecuteScalar(string query)
{
return ExecuteScalar(query, CommandType.Text, ConnState.CloseOnExit);
}
//用于执行返回一个值的方法之三:
public object ExecuteScalar(string query, CommandType commandType)
{
return ExecuteScalar(query, commandType, ConnState.CloseOnExit);
}
//用于执行返回一个值的方法之四:
public object ExecuteScalar(string query, ConnState connectionState)
{
return ExecuteScalar(query, CommandType.Text, connectionState);
}
//用于执行查询语句并返回一个结果集的方法之一:
public SqlDataReader ExecuteReader(string query, CommandType commandType, ConnState connectionState)
{
sqlCmd.CommandText = query;
sqlCmd.CommandType = commandType;
SqlDataReader reader = null;
try
{
if (sqlConn.State == System.Data.ConnectionState.Closed)
sqlConn.Open();
if (connectionState == ConnState.CloseOnExit)
reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
else
reader = sqlCmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd.Parameters.Clear();
}
return reader;
}
//用于执行查询语句并返回一个结果集的方法之二:
public SqlDataReader ExecuteReader(string query)
{
return ExecuteReader(query, CommandType.Text, ConnState.CloseOnExit);
}
//用于执行查询语句并返回一个结果集的方法之三:
public SqlDataReader ExecuteReader(string query, CommandType commandType)
{
return ExecuteReader(query, commandType, ConnState.CloseOnExit);
}
//用于执行查询语句并返回一个结果集的方法之四:
public SqlDataReader ExecuteReader(string query, ConnState connectionState)
{
return ExecuteReader(query, CommandType.Text, connectionState);
}
//用于执行查询语句并返回一个DataSet的方法之一:
public DataSet ExecuteDataSet(string query, CommandType commandType, ConnState connectionState)
{
SqlDataAdapter adapter = new SqlDataAdapter();
sqlCmd.CommandText = query;
sqlCmd.CommandType = commandType;
adapter.SelectCommand = sqlCmd;
DataSet ds = new DataSet();
try
{
adapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCmd.Parameters.Clear();
if (connectionState == ConnState.CloseOnExit)
{
if (sqlConn.State == System.Data.ConnectionState.Open)
{
sqlConn.Close();
}
}
}
return ds;
}
//用于执行查询语句并返回一个DataSet的方法之二:
public DataSet ExecuteDataSet(string query)
{
return ExecuteDataSet(query, CommandType.Text, ConnState.CloseOnExit);
}
//用于执行查询语句并返回一个DataSet的方法之三:
public DataSet ExecuteDataSet(string query, CommandType commandType)
{
return ExecuteDataSet(query, commandType, ConnState.CloseOnExit);
}
//用于执行查询语句并返回一个DataSet的方法之四:
public DataSet ExecuteDataSet(string query, ConnState connectionState)
{
return ExecuteDataSet(query, CommandType.Text, connectionState);
}
//定义枚举(包含两个常量):
public enum ConnState
{
KeepOpen, CloseOnExit
}
//释放数据库连接资源的方法(不需要连接数据时调用):
public void Dispose()
{
sqlConn.Close();
sqlConn.Dispose();
sqlCmd.Dispose();
}
}
}
然后在外面可以这么用:
namespace DAL
{
public class GameTypeDAL
{
public static GameType Get(String gameType)
{
GameType gametype=null;
DbHelper db=new DbHelper();
StringBuilder sql=new StringBuilder();
sql.Append("select * from GameType where GameType=@GameType");
db.AddParameter("@GameType",gameType);
SqlDataReader sdr=db.ExecuteReader(sql.ToString());
if(sdr.HasRows){
gametype=new GameType();
sdr.Read();
gametype.GameType1=sdr["GameType"].ToString();
gametype.GamePic=sdr["GamePic"].ToString();
gametype.GameContext=sdr["GameContext"].ToString();
}
sdr.Close();
db.Dispose();
return gametype;
}
public static bool Add(GameType gametype)
{
DbHelper db = new DbHelper();
StringBuilder sql = new StringBuilder();// StringBuilder构造字符串
sql.Append("Insert GameType values(@GameType,@GamePic,@GameContext)");
db.AddParameter("@GameType", gametype.GameType1);
db.AddParameter("@GamePic", gametype.GamePic);
db.AddParameter("@GameContext", gametype.GameContext);
//4.
int i = db.ExecuteNonQuery(sql.ToString());
//5.
db.Dispose();
if (i > 0)
{
return true; //表示添加用户成功了
}
else
{
return false;//表示添加用户失败了
}
}
//4. 删除用户的方法:
public static bool Delete(String gametype)
{
//1.
DbHelper db = new DbHelper();
//2.
StringBuilder sql = new StringBuilder();// StringBuilder构造字符串
sql.Append("Delete GameType Where GameType=@GameType");
//3.
db.AddParameter("@GameType", gametype);
//4.
int i = db.ExecuteNonQuery(sql.ToString());
//5.
db.Dispose();
if (i > 0)
return true; //表示删除用户成功了
else
return false;//表示删除用户失败了
}
public static bool Edit(GameType gametype)
{
DbHelper db = new DbHelper();
StringBuilder sql = new StringBuilder();// StringBuilder构造字符串
sql.Append("Update GameType set GameType=@GameType,GamePic=@GamePic,GameContext=@GameContext");
db.AddParameter("@GameType", gametype.GameType1);
db.AddParameter("@GamePic", gametype.GamePic);
db.AddParameter("@GameContext", gametype.GameContext);
int i = db.ExecuteNonQuery(sql.ToString());
db.Dispose();
if (i > 0)
return true; //表示修改用户成功了
else
return false;//表示修改用户失败了
}
}
}