/// <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();
}
}
}
protected int PDInsertKTKL(decimal ktklid, string soqd, DateTime ngayqd, decimal manv, decimal loaikt, decimal hinhthuc, float sotien, string noidung, DateTime ngaycn)
{
try
{
SqlCon Scon = new SqlCon();
SqlCommand Sqlcom = new SqlCommand();
Sqlcom.CommandType = CommandType.StoredProcedure;
Sqlcom.CommandText = "spInsertKTKL";
Sqlcom.Parameters.AddWithValue("@ktklid", ktklid);
Sqlcom.Parameters.AddWithValue("@soqd", soqd);
Sqlcom.Parameters.AddWithValue("@ngayqd", ngayqd);
Sqlcom.Parameters.AddWithValue("@manv", manv);
Sqlcom.Parameters.AddWithValue("@loaikt", loaikt);
Sqlcom.Parameters.AddWithValue("@hinhthuc", hinhthuc);
Sqlcom.Parameters.AddWithValue("@sotien", sotien);
Sqlcom.Parameters.AddWithValue("@noidung", noidung);
Sqlcom.Parameters.AddWithValue("@ngaycn", ngaycn);
Sqlcom.Connection = Scon.OpenCon();
int R = 0;
R = Sqlcom.ExecuteNonQuery();
Scon.CloseCon();
return(R);
}
catch
{
return(0);
}
}
//bağlantı açılıyor
public void Ac()
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
}
/// <summary>
/// 接口对象
/// </summary>
public void Dispose()
{
if (SqlCon.State == ConnectionState.Open || SqlCon.State == ConnectionState.Broken)
{
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();
}
}
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);
}
public SqlContext(SqlCon con, Context.ICurrentContextFactory conFac)
{
this.Con = con;
this.ConFac = conFac;
this.Fac = new Manage.SqlServerModelFactory(con, this.ConFac);
this.Model = Fac.GetModel(typeof(T));
}
protected int PDInsertCHAMCONG(decimal chcongid, string manv, int thang, int nam, float luongcb, float bacluong, byte ngaycong, float thuclinh, float conlai, byte nghiphep, byte ngungviec, float tangca, float phucapkhac, float luongtangca, float phucapcv, DateTime ngaycn)
{
try
{
SqlCon Scon = new SqlCon();
SqlCommand Sqlcom = new SqlCommand();
Sqlcom.CommandType = CommandType.StoredProcedure;
Sqlcom.CommandText = "spInsertCHAMCONG";
Sqlcom.Parameters.AddWithValue("@chcongid", chcongid);
Sqlcom.Parameters.AddWithValue("@manv", manv);
Sqlcom.Parameters.AddWithValue("@thang", thang);
Sqlcom.Parameters.AddWithValue("@nam", nam);
Sqlcom.Parameters.AddWithValue("@luongcb", luongcb);
Sqlcom.Parameters.AddWithValue("@bacluong", bacluong);
Sqlcom.Parameters.AddWithValue("@ngaycong", ngaycong);
Sqlcom.Parameters.AddWithValue("@thuclinh", thuclinh);
Sqlcom.Parameters.AddWithValue("@conlai", conlai);
Sqlcom.Parameters.AddWithValue("@nghiphep", nghiphep);
Sqlcom.Parameters.AddWithValue("@ngungviec", ngungviec);
Sqlcom.Parameters.AddWithValue("@tangca", tangca);
Sqlcom.Parameters.AddWithValue("@phucapkhac", phucapkhac);
Sqlcom.Parameters.AddWithValue("@luongtangca", luongtangca);
Sqlcom.Parameters.AddWithValue("@phucapcv", phucapcv);
Sqlcom.Parameters.AddWithValue("@ngaycn", ngaycn);
Sqlcom.Connection = Scon.OpenCon();
int R = 0;
R = Sqlcom.ExecuteNonQuery();
Scon.CloseCon();
return(R);
}
catch
{
return(0);
}
}
static void Main(string[] args)
{
SqlCon RealSql = new SqlCon();
{
Console.Write("Пожалуйста дайте нужную комнаду\n\n1.Дабавить новий ползователь\n\n2.Удалить ползовтелья из БД\n\n3.Выбрать всё записи базаго данныха\n\n4.Выбрать один запис по Id\n\n5.Обновить запис по Id\n\n6.Закрыт программу\n\n");
var userCmd = Console.ReadLine();
if (userCmd == "1")
{
Console.Write("LastName: ");
string LastName = Console.ReadLine();
Console.Write("FirstName: ");
string FirstName = Console.ReadLine();
Console.Write("MiddleName: ");
string MiddleName = Console.ReadLine();
Console.Write("BirthDate: ");
string BirthDate = Console.ReadLine();
RealSql.InsertNewPer(FirstName, LastName, MiddleName, BirthDate);
}
else if (userCmd == "2")
{
Console.Write("Id: ");
int Id = int.Parse(Console.ReadLine());
RealSql.DeleteIdPer(Id);
}
else if (userCmd == "3")
{
RealSql.SelectAllPer();
}
else if (userCmd == "4")
{
Console.Write("Id: ");
int Id = int.Parse(Console.ReadLine());
RealSql.SelectIdPer(Id);
}
else if (userCmd == "5")
{
Console.Write("LastName: ");
string LastName = Console.ReadLine();
Console.Write("FirstName: ");
string FirstName = Console.ReadLine();
Console.Write("MiddleName: ");
string MiddleName = Console.ReadLine();
Console.Write("BirthDate ");
string BirthDate = Console.ReadLine();
Console.Write("Id: ");
int Id = int.Parse(Console.ReadLine());
RealSql.UpdateIdPer(Id, FirstName, LastName, MiddleName, BirthDate);
}
else if (userCmd == "6")
{
return;
}
else
{
Console.WriteLine("Вы выбрали неправилную комманду");
}
}
}
//bağlantı kapanıyor.
public void Kapat()
{
if (SqlCon.State != ConnectionState.Closed)
{
SqlCon.Close();
}
}
internal static SqlCon GetSqlCon(SqlCon con, Model model)
{
if (con != null)
{
return(con);
}
if (model != null)
{
if (model.SqlCon != null)
{
return(model.SqlCon);
}
else if (model.Module.SqlCon != null)
{
return(model.Module.SqlCon);
}
}
if (con != null)
{
return(con);
}
//if (Global.SqlCon != null)
// return Global.SqlCon;
return(null);
}
public AllMessageFac(ICurrentContextFactory conFac, SqlCon con, SqlCon appcon)
{
this.conFac = conFac;
this.con = con;
this.appcon = appcon;
}
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);
}
protected DataTable PDGetListNHANVIEN()
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spGetListNHANVIEN", Scon.OpenCon());
DataTable DTable = new DataTable();
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
public void CloseSqlCon()
{
try
{
SqlCon.Close();
}
catch (Exception Ex)
{
Common.ShowMsg(Ex.Message);
}
}
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());
}
internal static SqlCon GetSqlCon(Property index, SqlCon con, Model model)
{
if (index != null && index.PropertySqlCon != null)
{
return(index.PropertySqlCon);
}
else
{
return(GetSqlCon(con, model));
}
}
protected DataTable PDDetailsNHANVIEN(string manv)
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spDetailsNHANVIEN", Scon.OpenCon());
Sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable DTable = new DataTable();
Sqlda.SelectCommand.Parameters.AddWithValue("@manv", manv);
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
protected DataTable PDDetailsDONVI(decimal donviid)
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spDetailsDONVI", Scon.OpenCon());
Sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable DTable = new DataTable();
Sqlda.SelectCommand.Parameters.AddWithValue("@donviid", donviid);
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
protected DataTable PDDetailsMUCLUONG(float bacluong)
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spDetailsMUCLUONG", Scon.OpenCon());
Sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable DTable = new DataTable();
Sqlda.SelectCommand.Parameters.AddWithValue("@bacluong", bacluong);
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
protected DataTable PDDetailsTAIKHOAN(string Taikhoan)
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spDetailsTAIKHOAN", Scon.OpenCon());
Sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable DTable = new DataTable();
Sqlda.SelectCommand.Parameters.AddWithValue("@Taikhoan", Taikhoan);
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
protected DataTable PDDetailsByFieldNHANVIEN(string FieldName, string value)
{
SqlCon Scon = new SqlCon();
SqlDataAdapter Sqlda = new SqlDataAdapter("spDetailsByFieldNHANVIEN", Scon.OpenCon());
Sqlda.SelectCommand.CommandType = CommandType.StoredProcedure;
Sqlda.SelectCommand.Parameters.AddWithValue("@FieldName", FieldName);
Sqlda.SelectCommand.Parameters.AddWithValue("@Value", value);
DataTable DTable = new DataTable();
Sqlda.Fill(DTable);
Scon.CloseCon();
return(DTable);
}
/// <summary>
/// Method used to open the database connection
/// </summary>
public void OpenSqlConnection()
{
try
{
if (SqlCon.State == ConnectionState.Closed)
{
SqlCon.Open();
}
}
catch (Exception)
{
throw;
}
}
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();
}
public DynamicContext(String connectiongString, Soway.Model.Context.ICurrentContextFactory fac)
{
this.Fac = fac;
this.ConStr = connectiongString;
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(this.ConStr);
this.SqlCon = new SqlCon()
{
DataSource = builder.DataSource,
InitialCatalog = builder.InitialCatalog,
IntegratedSecurity = builder.IntegratedSecurity,
IsLocal = false,
Password = builder.Password,
UserID = builder.UserID
};
}
/// <summary>
/// 开启事务
/// </summary>
public void BeginTrans()
{
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlTran = SqlCon.BeginTransaction();
SqlTransactionFlag = true;
}
catch (Exception ex)
{
throw ex;
}
}
protected int PDInsertNHANVIEN(string manv, string honv, string tennv, decimal gioitinh, string noisinh, DateTime ngaysinh, string noiohientai, string socmnd, string hinh, string sodt, string email, decimal dantoc, decimal tongiao, decimal giadinh, string sohd, decimal loaihd, DateTime ngayhd, DateTime ngayct, DateTime ngaybd, decimal donviid, decimal phongid, decimal tdid, decimal cmid, decimal cvid, float bacluong, string bhxh, DateTime ngaykt, decimal trangthai)
{
try
{
SqlCon Scon = new SqlCon();
SqlCommand Sqlcom = new SqlCommand();
Sqlcom.CommandType = CommandType.StoredProcedure;
Sqlcom.CommandText = "spInsertNHANVIEN";
Sqlcom.Parameters.AddWithValue("@manv", manv);
Sqlcom.Parameters.AddWithValue("@honv", honv);
Sqlcom.Parameters.AddWithValue("@tennv", tennv);
Sqlcom.Parameters.AddWithValue("@gioitinh", gioitinh);
Sqlcom.Parameters.AddWithValue("@noisinh", noisinh);
Sqlcom.Parameters.AddWithValue("@ngaysinh", ngaysinh);
Sqlcom.Parameters.AddWithValue("@noiohientai", noiohientai);
Sqlcom.Parameters.AddWithValue("@socmnd", socmnd);
Sqlcom.Parameters.AddWithValue("@hinh", hinh);
Sqlcom.Parameters.AddWithValue("@sodt", sodt);
Sqlcom.Parameters.AddWithValue("@email", email);
Sqlcom.Parameters.AddWithValue("@dantoc", dantoc);
Sqlcom.Parameters.AddWithValue("@tongiao", tongiao);
Sqlcom.Parameters.AddWithValue("@giadinh", giadinh);
Sqlcom.Parameters.AddWithValue("@sohd", sohd);
Sqlcom.Parameters.AddWithValue("@loaihd", loaihd);
Sqlcom.Parameters.AddWithValue("@ngayhd", ngayhd);
Sqlcom.Parameters.AddWithValue("@ngayct", ngayct);
Sqlcom.Parameters.AddWithValue("@ngaybd", ngaybd);
Sqlcom.Parameters.AddWithValue("@donviid", donviid);
Sqlcom.Parameters.AddWithValue("@phongid", phongid);
Sqlcom.Parameters.AddWithValue("@tdid", tdid);
Sqlcom.Parameters.AddWithValue("@cmid", cmid);
Sqlcom.Parameters.AddWithValue("@cvid", cvid);
Sqlcom.Parameters.AddWithValue("@bacluong", bacluong);
Sqlcom.Parameters.AddWithValue("@bhxh", bhxh);
Sqlcom.Parameters.AddWithValue("@ngaykt", ngaykt);
Sqlcom.Parameters.AddWithValue("@trangthai", trangthai);
Sqlcom.Connection = Scon.OpenCon();
int R = 0;
R = Sqlcom.ExecuteNonQuery();
Scon.CloseCon();
return(R);
}
catch
{
return(0);
}
}
/// <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);
}
}
}
