/// <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>
/// 执行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();
}
}
}
private void DatabaseBackup()
{
if (UIMessageDialog.ShowAskDialog(this, "Are you sure to backup DB?", this.Style) != true)
{
return;
}
if (SqlCon.State == System.Data.ConnectionState.Closed)
{
SqlCon.Open();
}
string strSectionName = "MainFrame";
string strMsg, strCaption;
strCaption = LocalizationRecourceManager.GetString(strSectionName, "BackupDBCaption");
if (OVRDataBaseUtils.BackupDB(SqlCon))
{
strMsg = LocalizationRecourceManager.GetString(strSectionName, "BackupDBSuccessMsg");
}
else
{
strMsg = LocalizationRecourceManager.GetString(strSectionName, "BackupDBFailMsg");
}
UIMessageDialog.ShowMessageDialog(strMsg, strCaption, false, this.Style);
}
//bağlantı açılıyor
public void Ac()
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
}
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();
}
public bool SystemOpen()
{
if (SqlCon.State == System.Data.ConnectionState.Closed)
{
SqlCon.Open();
}
OVRDataBaseUtils.GetRoleModules(SqlCon, RoleID, out m_lstRoleModuleID);
GetSystemConfiguration();
foreach (int id in m_lstRoleModuleID)
{
if (id == (int)EMainFormUIType.emWndGeneralData)
{
GeneralDataOpen();
}
else if (id == (int)EMainFormUIType.emWndRegister)
{
RegisterOpen();
}
else if (id == (int)EMainFormUIType.emWndDrawArrange)
{
DrawArrangeOpen();
}
else if (id == (int)EMainFormUIType.emWndMatchSchedule)
{
MatchScheduleOpen();
}
else if (id == (int)EMainFormUIType.emWndPluginMgr)
{
PluginMgrOpen();
}
else if (id == (int)EMainFormUIType.emWndRankMedal)
{
RankMedalOpen();
}
else if (id == (int)EMainFormUIType.emWndReports)
{
}
else if (id == (int)EMainFormUIType.emWndCommunicate)
{
OnCommunication();
}
else if (id == (int)EMainFormUIType.emWndNetwork)
{
NetworkOpen();
}
else if (id == (int)EMainFormUIType.emWndRecord)
{
RecordOpen();
}
}
AddAside();
return(true);
}
public bool Ingresar(string usr, string pass)
{
SqlCon.Open();
SqlCommand cmdUsuario = new SqlCommand("Select * from Usuarios WHERE @usr=nombre_usuario AND @pass=clave", SqlCon);
cmdUsuario.Parameters.Add("@pass", SqlDbType.VarChar, 50).Value = pass;
cmdUsuario.Parameters.Add("@usr", SqlDbType.VarChar, 50).Value = usr;
SqlDataReader drUsuario = cmdUsuario.ExecuteReader();
return(drUsuario.Read());
}
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>
/// Method used to open the database connection
/// </summary>
public void OpenSqlConnection()
{
try
{
if (SqlCon.State == ConnectionState.Closed)
{
SqlCon.Open();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 开启事务
/// </summary>
public void BeginTrans()
{
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlTran = SqlCon.BeginTransaction();
SqlTransactionFlag = true;
}
catch (Exception ex)
{
throw ex;
}
}
//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 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);
}
}
}
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);
}
private void OnDatabaseBackupClick(object sender, EventArgs e)
{
if (SqlCon.State == System.Data.ConnectionState.Closed)
{
SqlCon.Open();
}
string strSectionName = "MainFrame";
string strMsg, strCaption;
strCaption = LocalizationRecourceManager.GetString(strSectionName, "BackupDBCaption");
if (OVRDataBaseUtils.BackupDB(SqlCon))
{
strMsg = LocalizationRecourceManager.GetString(strSectionName, "BackupDBSuccessMsg");
}
else
{
strMsg = LocalizationRecourceManager.GetString(strSectionName, "BackupDBFailMsg");
}
UIMessageDialog.ShowMessageDialog(strMsg, strCaption, false, this.Style);
}
/// <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();
}
}
}
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);
}
public bool SystemOpen()
{
if (SqlCon.State == System.Data.ConnectionState.Closed)
{
SqlCon.Open();
}
OVRDataBaseUtils.GetRoleModules(SqlCon, RoleID, out m_lstRoleModuleID);
Localization();
GetSystemConfiguration();
btnItemGenData.Visible = false;
btnItemRegister.Visible = false;
btnItemDrawArrange.Visible = false;
btnItemSchedule.Visible = false;
btnItemMatchData.Visible = false;
btnItemMedal.Visible = false;
btnItemReports.Visible = false;
btnItemBackup.Visible = false;
btnItemCommunicate.Visible = false;
btnItemNetwork.Visible = false;
btnItemRecord.Visible = false;
foreach (int id in m_lstRoleModuleID)
{
if (id == (int)EMainFormUIType.emWndGeneralData)
{
GeneralDataOpen();
btnItemGenData.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndRegister)
{
RegisterOpen();
btnItemRegister.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndDrawArrange)
{
DrawArrangeOpen();
btnItemDrawArrange.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndMatchSchedule)
{
MatchScheduleOpen();
btnItemSchedule.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndPluginMgr)
{
PluginMgrOpen();
btnItemMatchData.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndRankMedal)
{
RankMedalOpen();
btnItemMedal.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndReports)
{
ReportsOpen();
btnItemReports.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndBackupDB)
{
btnItemBackup.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndCommunicate)
{
btnItemCommunicate.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndNetwork)
{
NetworkOpen();
btnItemNetwork.Visible = true;
}
else if (id == (int)EMainFormUIType.emWndRecord)
{
RecordOpen();
btnItemRecord.Visible = true;
}
}
return(true);
}
