C#编写SQLHelper

1.基本数据库访问方法

//不带产生增删改
public int Update(string sql)
{
	SqlConnection conn = new SqlConnection(conString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		return cmd.ExecuteNonQuery();
	}
	catch(Exception ex)
	{
		throw ex;
	}
	finally
	{
		conn.Close();
	}
}
//不带参数返回单一结果
public object GetSingle(string sql)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		return cmd.ExecuteScalar();
	}
	catch(Exception ex)
	{
		thorw ex;
	}
	finally
	{
		conn.Close();
	}
}
//不带参数返回SqlDataReader方法
public SqlDataReader GetReader(string sql)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		return cmd.ExcecuteReader(CommandBehavior.CloseConnection);
	}
	catch(Exception ex)
	{
		conn.Close();
		throw ex;
	}
}
//返回DataSet方法
public DataSet GetDataSet(string sql)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	SqlDataAdapter da = new SqlDataAdapter(cmd);
	DataSet ds = new DataSet();
	try
	{
		conn.Open();
		da.Fill(ds);
		return ds;
	}
	catch(Exception ex)
	{
		throw ex;
	}
	finally
	{
		conn.Close();
	}
}

2.带参数操作数据库方法

//带参数增删改
public int Update(string sql,SqlParameter[] param)
{
	SqlConnection conn = new SqlConnection(conString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		cmd.Parameters.AddRange(param);
		return cmd.ExecuteNonQuery();
	}
	catch(Exception ex)
	{
		throw ex;
	}
	finally
	{
		conn.Close();
	}
}
//带参数返回单一结果
public object GetSingleResult(string sql, SqlParameter[] param)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		cmd.Parameters.AddRange(param);
		return cmd.ExecuteScalar();
	}
	catch(Exception ex)
	{
		throw ex;
	}
	finally
	{
		conn.Close();
	}
}
//带参数返回SqlDataReader
public SqlDataReader GetReader(string sql, SqlParameter[] param)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand(sql,conn);
	try
	{
		conn.Open();
		cmd.Parameters.AddRange(param);
		return cmd.ExecuteReader(CommandBehavior.CloseConnection);
	}
	catch(Exception ex)
	{
		throw ex;
	}
	
}

3.使用数据存储过程操作数据库方法

//通过存储过程进行增删改
public int UpdateByProcedure(string procedureName,SqlParameter[] param)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	try
	{
		conn.Open();
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.CommandText = procedureName;
		cmd.Paramaters.AddRange(param);
		return cmd.ExecuteNonQuery();
	}
	catch(Exception ex)
	{
		throw ex;
	}
	finally
	{
		conn.Close();
	}
}
//通过存储过程获取单一结果
public object GetSingleResultByProcedure(string procedureName, SqlParamater[] param)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	try
	{
		conn.Open();
		cmd.CommandType = Command.StoredProcedure;
		cmd.CommandText = procedureName;
		cmd.Paramaters.AddRange(param);
		return cmd.ExecuteScalar();
	}
	catch(Exception ex)
	{
		throw ex;
	}
	fianlly
	{
		conn.Close();
	}
}
//通过存储过程获取SqlDataReader
public SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
{
	SqlConnection conn = new SqlConnection();
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	try
	{
		conn.Open();
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.CommandText = procedureName;
		cmd.Parameters.AddRange(param);
		return cmd.ExecuteReader(CommandBehavior.CloseConnection);
	}
	catch(Exception ex)
	{
		conn.Close();
		throw ex;
	}
}

4.使用事务对数据库进行操作

1.使用List sqlList (无参数)进行事务对数据库操作

//使用事务无参数操作数据库
public bool UpdateByTran(List<string> sqlList)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	try
	{
		conn.Open();
		cmd.Transaction = conn.BeginTransaction();
		foreach(string itemSql in sqlList)
		{
			cmd.CommandText = itemSql;
			cmd.ExecuteNonQuery();
		}
		cmd.Transaction.Commit();
		return true;
	}
	catch(Exception ex)
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction.RollBack();
			throw ex;
		}
	}
	finally
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction = null;
		}
		conn.Close();
	}
}

2.带参数使用事务对数据库操作

public bool UpdateByTran(string sql, List<SqlParameter[]> paramList)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	cmd.CommandText = sql;
	try
	{
		conn.Open();
		cmd.Transaction = conn.BeginTransaction();
		foreach(SqlParameter[] itemParam in paramList)
		{
			cmd.Parameters.Clear();
			cmd.Parameters.AddRange(itemParam);
			cmd.ExecuteNonQuery();
		}
		cmd.Transaction.Commit();
		return true;
	}
	catch(Exception ex)
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction.RollBack();
			throw ex;
		}
	}
	finally
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction = null;
		}
		conn.Close();
	}
}

3.使用事务对两个关联的数据表进行操作

public bool UpdateByTran(string mainSql, SqlParameter[] mainParam, string detailSql, List<SqlParameter[]> paramList)
{
	SqlConnection conn = new SqlConnection(connString);
	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	try
	{
		conn.Open();
		cmd.Transaction = conn.BegionTransaction();//开启事务
		//对主表进行操作
		cmd.CommandText = mainSql;
		cmd.Parameters.AddRange(mainParam);
		cmd.ExecuteNonQuery();
		//对从表进行操作
		cmd.CommandText = detailSql;
		foreach(SqlParameter[] paramArry in paramList)
		{
			cmd.Parameters.Clear();
			cmd.Parameters.AddRange(paramArry);
			cmd.ExecuteNonQuery();
		}
		cmd.Transaction.Commit();
		return true;
	}
	catch(Exception ex)
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction.RollBack();
		}
		throw ex;
	}
	finally
	{
		if(cmd.Transaction != null)
		{
			cmd.Transaction = null;
		}
		conn.Close();
	}
}