/// <summary>
/// 执行SQL语句操作(Update,Delete,Insert)，返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parms">参数集合</param>
/// <returns>受影响的行数</returns>
public int ExectueNonQueryBySql(string sql, SqlParameter[] parms = null)
{
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd = SqlTransactionFlag ? new SqlCommand(sql, SqlCon, SqlTran) : new SqlCommand(sql, SqlCon);
SqlCmd.CommandType = CommandType.Text;
if (parms != null)
{
AddParameters(parms);
}
return(SqlCmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlCmd.Dispose();
if (!SqlTransactionFlag)
{
SqlCon.Close();
SqlCon.Dispose();
}
}
}
//bağlantı kapanıyor.
public void Kapat()
{
if (SqlCon.State != ConnectionState.Closed)
{
SqlCon.Close();
}
}
/// <summary>
/// 执行查询SQL语句，返回DataSet
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parms">参数集合</param>
/// <returns>返回 DataSet</returns>
public DataSet ExecuteQueryBySql_DS(string sql, SqlParameter[] parms = null)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
DataSet ds = new DataSet();
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd = new SqlCommand(sql, SqlCon);
AddParameters(parms);
SqlCmd.CommandType = CommandType.Text;
da.SelectCommand = SqlCmd;
da.Fill(ds);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
da.Dispose();
SqlCmd.Dispose();
SqlCon.Close();
SqlCon.Dispose();
}
}
/// <summary>
/// 接口对象
/// </summary>
public void Dispose()
{
if (SqlCon.State == ConnectionState.Open || SqlCon.State == ConnectionState.Broken)
{
SqlCon.Close();
}
}
public void Eliminar(int id)
{
SqlCon.Open();
SqlCommand cmdDelete = new SqlCommand("Delete Usuarios Where @id = id_usuario", SqlCon);
cmdDelete.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmdDelete.ExecuteNonQuery();
SqlCon.Close();
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
public void CloseSqlCon()
{
try
{
SqlCon.Close();
}
catch (Exception Ex)
{
Common.ShowMsg(Ex.Message);
}
}
public void Agregar(Entidades.Usuario usuarioNuevo)
{
SqlCon.Open();
SqlCommand cmdInsert = new SqlCommand("INSERT INTO Usuarios(clave,email,tipo_usuario,ultimo_ingreso,nombre_usuario) " +
"VALUES(@clave,@email,@tipoUsuario,@ultimoIngreso,@nombreUsuario)", SqlCon);
cmdInsert.Parameters.Add("@clave", SqlDbType.VarChar, 50).Value = usuarioNuevo.Clave;
cmdInsert.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = usuarioNuevo.Email;
cmdInsert.Parameters.Add("@tipoUsuario", SqlDbType.Int).Value = usuarioNuevo.TipoUsuario;
cmdInsert.Parameters.Add("@ultimoIngreso", SqlDbType.VarChar, 50).Value = DateTime.Today;
cmdInsert.Parameters.Add("@nombreUsuario", SqlDbType.VarChar, 50).Value = usuarioNuevo.NombreUsuario;
cmdInsert.ExecuteNonQuery();
SqlCon.Close();
}
/// <summary>
/// 事务回滚
/// </summary>
public void RollBack()
{
try
{
SqlTran.Rollback();
SqlTransactionFlag = false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlCon.Close();
SqlCon.Dispose();
}
}
public void DeleteCpu(Cpu cpu)
{
string commandString = "USE [DB_PcPartPicker] DELETE FROM [dbo].[Cpu] WHERE Cpu_id =" + cpu.Cpu_id;
using (SqlCommand command = new SqlCommand(commandString, SqlCon))
{
try
{
SqlCon.Open();
command.ExecuteNonQuery();
SqlCon.Close();
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
}
}
//properties
//constructor
//methoden
public void AddCpu(Cpu cpu)
{
string commandString = "USE [DB_PcPartPicker] INSERT INTO [dbo].[Cpu]([Clockspeed],[Cores],[Brand],[Price],[Socket],[Name])VALUES ( " + cpu.Clockspeed + " , " + cpu.Cores + " , ' " + cpu.Brand.ToString() + "' , " + cpu.Price + " , '" + cpu.Socket.ToString() + " ',' " + cpu.Name.ToString() + "' )";
using (SqlCommand command = new SqlCommand(commandString, SqlCon))
{
try
{
SqlCon.Open();
command.ExecuteNonQuery();
SqlCon.Close();
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
}
}
public Entidades.Usuario Recuperar(string nombre)
{
Entidades.Usuario usr = new Entidades.Usuario();
SqlCon.Open();
SqlCommand cmdUsuario = new SqlCommand("Select * From Usuarios WHERE @nombre_usuario = nombre_usuario", SqlCon);
cmdUsuario.Parameters.Add("@nombre_usuario", SqlDbType.VarChar, 50).Value = nombre;
SqlDataReader drUsuario = cmdUsuario.ExecuteReader();
drUsuario.Read();
usr.Clave = (string)drUsuario["clave"];
usr.Email = (string)drUsuario["Email"];
usr.NombreUsuario = (string)drUsuario["Nombre_Usuario"];
usr.TipoUsuario = (int)drUsuario["Tipo_Usuario"];
usr.UltimoIngreso = (DateTime)drUsuario["Ultimo_Ingreso"];
drUsuario.Close();
SqlCon.Close();
return(usr);
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
try
{
if (SqlTran != null)
{
SqlTran.Commit();
SqlTransactionFlag = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlCon.Close();
SqlCon.Dispose();
}
}
public List <Entidades.Usuario> RecuperarTodos()
{
List <Entidades.Usuario> lista = new List <Entidades.Usuario>();
SqlCon.Open();
SqlCommand cmdUsuario = new SqlCommand("Select * from Usuarios", SqlCon);
SqlDataReader drUsuario = cmdUsuario.ExecuteReader();
while (drUsuario.Read())
{
Entidades.Usuario usr = new Entidades.Usuario();
usr.Clave = (string)drUsuario["clave"];
usr.Email = (string)drUsuario["email"];
usr.Id = (int)drUsuario["id_usuario"];
usr.NombreUsuario = (string)drUsuario["nombre_usuario"];
usr.TipoUsuario = (int)drUsuario["tipo_usuario"];
usr.UltimoIngreso = (DateTime)drUsuario["ultimo_ingreso"];
lista.Add(usr);
}
SqlCon.Close();
return(lista);
}
/// <summary>
/// 执行查询存储过程，返回DataSet
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="parms">参数集合</param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteQuery_DS(string procName, SqlParameter[] parms = null)
{
SqlDataAdapter da = new SqlDataAdapter();
try
{
DataSet ds = new DataSet();
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd = SqlTransactionFlag ? new SqlCommand(procName, SqlCon, SqlTran) : new SqlCommand(procName, SqlCon);
SqlCmd.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
AddParameters(parms);
}
da.SelectCommand = SqlCmd;
da.Fill(ds);
return(ds);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
da.Dispose();
SqlCmd.Dispose();
if (!SqlTransactionFlag)
{
SqlCon.Close();
SqlCon.Dispose();
}
}
}
/// <summary>
/// Method used to close the database connection
/// </summary>
public void CloseSqlConnection()
{
SqlCon.Close();
}
public void CloseCon()
{
SqlCon.Close();
SqlCon.Dispose();
SqlCmd.Dispose();
}
