/// <summary>
/// 更新 Apq_Tables, Apq_Columns 表内容
/// </summary>
/// <param name="DBName">目标数据库名</param>
/// <returns>影响行数</returns>
public int Refresh(string DBName)
{
#region 参数检测
if (string.IsNullOrEmpty(DBName))
{
throw new ArgumentNullException("DBName");
}
#endregion
System.Data.Common.DbCommand Command = Connection.CreateCommand();
Apq.Data.Common.DbCommandHelper CommandHelper = new Common.DbCommandHelper(Command);
CommandHelper.AddParameter("@Apq_Tables", Apq_Tables);
Command.CommandText = "SELECT OBJECT_ID( @Apq_Tables )";
Common.DbConnectionHelper.Open(Connection);
#region Apq_Tables
if (System.Convert.IsDBNull(Command.ExecuteScalar()))
{
Command.CommandText = string.Format(Sqls.Apq_TablesCreate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
}
Command.CommandText = string.Format(Sqls.Apq_TablesUpdate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
#endregion
#region Apq_Columns
CommandHelper.AddParameter("@Columns", Apq_Columns);
Command.CommandText = "SELECT OBJECT_ID( @Columns )";
if (System.Convert.IsDBNull(Command.ExecuteScalar()))
{
Command.CommandText = string.Format(Sqls.Apq_ColumnsCreate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
Command.ExecuteNonQuery();
}
Command.CommandText = string.Format(Sqls.Apq_ColumnsUpdate,
Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
Apq.Data.SqlClient.Common.EncodeString(DBName)
);
return(Command.ExecuteNonQuery());
#endregion
}
public object ExecuteScalar()
{
List <string> sqlList = new List <string>();
sqlList.Add(CommandText);
try
{
if (IsCheckCommandText && CommandType == System.Data.CommandType.Text)
{
string msg = "";
if (!Common.SqlCheck.IsSelectSql(CommandText, ref msg))
{
throw new Exception(msg);
}
}
dbCommand.CommandText = CommandText;
dbCommand.CommandTimeout = CommandTimeout;
dbCommand.CommandType = CommandType;
FireExecuteSql(new DbExecuteSqlEventArgs(
DbKey,
sqlList,
DbExecuteSqlType.QUERY,
"OK"));
return(dbCommand.ExecuteScalar());
}
catch (Exception ex)
{
FireExecuteSql(new DbExecuteSqlEventArgs(
dbCommand.Connection.ConnectionString,
sqlList,
DbExecuteSqlType.QUERY,
ex.Message));
throw ex;
}
}
// AnySqlWebAdmin.Code.Trash.PgIntegratedSecurityTest.TestNpgSql();
public static void TestNpgSql()
{
var a = new { Test = 5, Result = "Success" };
var b = new { Test = 3, Result = "foo" };
var c = new { Test1 = 3, Result = "foo" };
System.Type t = a.GetType();
System.Console.WriteLine(t);
if (object.ReferenceEquals(a.GetType(), b.GetType()))
{
System.Console.WriteLine("Two anony = equal");
}
Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();
csb.Database = "osm_test"; // must be set
csb.Host = "localhost";
// csb.Host = "127.0.0.1"; // doesn't work
// csb.Host = System.Environment.MachineName; // doesn't work
csb.Port = 5432;
csb.IntegratedSecurity = true;
csb.Username = System.Environment.UserName; // Works when user exists
// csb.Username = "******"; // works as root
bool ret = false;
string sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'; ";
using (System.Data.Common.DbConnection conn = Npgsql.NpgsqlFactory.Instance.CreateConnection())
{
conn.ConnectionString = csb.ConnectionString;
if (conn.State != System.Data.ConnectionState.Open)
{
conn.Open();
}
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
int?foo = (int?)cmd.ExecuteScalar();
if (foo.HasValue)
{
ret = foo.Value != 0;
}
} // End Using cmd
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
} // End Using conn
System.Console.WriteLine(ret);
} // End Sub TestNpgSql
private void btnIniciar_Click(object sender, EventArgs e)
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "IniciarSesion";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Username", txtUsername.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("Password", txtPassword.Text.Trim()));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Usuario o contraseña incorrecta", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MainForm principal = new MainForm();
this.Hide();
principal.Show();
}
}
catch (Exception)
{
throw;
}
}
public static byte[] GetFlagFromDb(string iso)
{
byte[] baResult = null;
using (System.Data.Common.DbConnection con = GetConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT SUBSTRING(b64, 23, 40000000000000000) AS b64 FROM flags WHERE flag = @iso";
System.Data.Common.DbParameter country = cmd.CreateParameter();
country.ParameterName = "@iso";
country.Value = iso;
country.DbType = System.Data.DbType.String;
cmd.Parameters.Add(country);
object obj = cmd.ExecuteScalar();
string b64 = System.Convert.ToString(obj);
baResult = System.Convert.FromBase64String(b64);
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End Using con
return(baResult);
}
private void btnCambiar_Click(object sender, EventArgs e)
{
if (txtPassword.Text != "" && txtRepetirPass.Text != "")
{
if (txtPassword.Text != txtRepetirPass.Text)
{
MessageBox.Show("Las contraseñas no coinciden", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "CambiarPassword";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Id", idUser));
cmd.Parameters.Add(new SqlParameter("Password", txtPassword.Text.Trim()));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res > 0)
{
MessageBox.Show("Contraseña cambiada con éxito", "Cambio de contraseña", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
}
}
private void btnGenerar_Click(object sender, EventArgs e)
{
if (dtpHasta.Value.Year < dtpDesde.Value.Year || dtpHasta.Value.Year == dtpDesde.Value.Year)
{
MessageBox.Show("El año de la fecha hasta debe ser mayor al año de la fecha desde");
}
else
{
if (dtpHasta.Value.Year - dtpDesde.Value.Year > 1)
{
MessageBox.Show("El periodo de la indemnización no debe ser mayor a un año");
}
else
{
DialogResult dialog;
dialog = MessageBox.Show("¿Seguro que desea generar la indemnización para todos sus empleados?", "Generar Indemnización", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarIndemnizacion";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Desde", dtpDesde.Value));
cmd.Parameters.Add(new SqlParameter("Hasta", dtpHasta.Value));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe una indemnización creada para el año indicado", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
LimpiarGrid();
CargarTodas();
CalcularPago();
MessageBox.Show("Indemnización generada correctamente para todos los empleados", "Correcto", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
}
public DataTable GetBySqlQuery(string sqlQuery, bool dbNullParams, PagingProperties currentPaging, params SqlParameter[] _params)
{
if (dbNullParams)
{
_params = this.DbNullNonSetParams(sqlQuery, _params);
}
this.NullToDbNull(_params);
DataTable dataTable = new DataTable();
System.Data.Common.DbDataReader reader = null;
if (currentPaging != null)
{
System.Data.Common.DbCommand command = null;
string commandText;
if (currentPaging.HasPaging)
{
string countQuery = (sqlQuery.Contains("order") && (!sqlQuery.ToLower().Contains("top") && !sqlQuery.ToLower().Contains("offset"))) ? $"{sqlQuery} OFFSET 0 ROWS " : sqlQuery;
//get row count
command = this.GetDbCommand($"select count(*) from ({countQuery}) as returntable ", _params);
currentPaging.RowsCount = command.ExecuteScalar().ToIntObj();
command.Dispose();
//get data with paging and sorting
commandText = string.Format($@"{sqlQuery} { (!string.IsNullOrWhiteSpace(currentPaging.SortColumn) ? $"order by {currentPaging.SortColumn} {currentPaging.SortType}" : "")} OFFSET {((currentPaging.PageIndex - 1) * currentPaging.PageSize)} ROWS FETCH NEXT {currentPaging.PageSize} ROWS ONLY ");
}
else
{
//get data with paging and sorting
commandText = string.Format($@"{sqlQuery} { (!string.IsNullOrWhiteSpace(currentPaging.SortColumn) ? $"order by {currentPaging.SortColumn} {currentPaging.SortType}" : "")}");
}
command = this.GetDbCommand(commandText, _params);
reader = command.ExecuteReader();
dataTable.Load(reader);
reader.Close();
}
else
{
System.Data.Common.DbCommand command = this.GetDbCommand(sqlQuery, _params);
reader = this.GetDbCommand(sqlQuery, _params).ExecuteReader();
dataTable.Load(reader);
reader.Close();
}
return(dataTable);
}
/// <summary>
///
/// </summary>
/// <param name="pFileString"></param>
/// <param name="pTableName"></param>
/// <param name="pIsConnectionString"></param>
/// <returns></returns>
public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
{
System.Data.Common.DbConnection Conn = null;
//System.Data.Common.DbDataAdapter Adapter = null;
System.Data.DataTable DataTable = new System.Data.DataTable();
bool result = false;
string connString = pFileString;
string[] restrictions = new string[] { null, null, pTableName };
if (DataSource != null)
{
IDbDriver driver = this;
driver.ConnectionString = connString;
Conn = (System.Data.Common.DbConnection)driver.GetConnection();
try
{
Conn.Open();
Conn.ChangeDatabase("master");
System.Data.Common.DbCommand cmd = Conn.CreateCommand();
cmd.CommandText = "SELECT database_id FROM sys.databases WHERE Name = @name";
System.Data.Common.DbParameter parameter = cmd.CreateParameter();
parameter.ParameterName = "@name";
parameter.Value = driver.DbName;
cmd.Parameters.Add(parameter);
object ret = cmd.ExecuteScalar();
if (ret != null)
{
int databaseID = (int)ret;
result = databaseID > 0;
}
}
catch (Exception ex)
{
// do nothing
}
finally
{
Conn.Close();
}
}
return(result);
}
int VerificarPlanillaJefes()
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "VerificarPlanillaJefes";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", dtpFechaInicio.Value));
cmd.Parameters.Add(new SqlParameter("IdEmpleado", int.Parse(idEmpleado)));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
return(res);
}
int DeterminarDepto()
{
int depto;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "DeterminarDepartamento";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("idEmpleado", int.Parse(idEmpleado)));
depto = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
return(depto);
}
private void btnRegistrar_Click(object sender, EventArgs e)
{
if (txtPassword.Text != txtRepetirPass.Text)
{
MessageBox.Show("Las contraseñas no coinciden", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarUsuarios";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Username", txtUsername.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("Password", txtPassword.Text.Trim()));
cmd.Parameters.Add(new SqlParameter("Rol_Id", cbxRoles.SelectedValue));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un usuario con ese nombre", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Usuario registrado con éxito", "Registrado", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtUsername.Text = txtPassword.Text = txtRepetirPass.Text = "";
CargarUsuarios();
}
}
catch (Exception)
{
throw;
}
}
}
public bool IsExisted(CheckUniqueDTO checkUniqueDTO)
{
StringBuilder Query = new StringBuilder("SELECT COUNT(*) FROM ");
Query.Append(checkUniqueDTO.TableName + " WHERE ");
for (int i = 0; i < checkUniqueDTO.Fields.Length; i++)
{
Query.Append(checkUniqueDTO.Fields[i] + " LIKE '" + checkUniqueDTO.Values[i] + "'");
if (checkUniqueDTO.Fields.Length - 1 != i)
{
Query.Append(" AND ");
}
}
string FinalQuery = Query.ToString();
using (System.Data.Common.DbCommand command = _Context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = FinalQuery;
_Context.Database.OpenConnection();
object result = command.ExecuteScalar();
_Context.Database.CloseConnection();
return(((int)result) > 0);
}
}
public object ExecuteScalar(System.Data.Common.DbCommand cmd, System.Data.Common.DbConnection connection)
{
cmd.Connection = connection;
this.OpenConnection(connection);
return(cmd.ExecuteScalar());
}
private void btnAguinaldo_Click(object sender, EventArgs e)
{
DialogResult dialog;
dialog = MessageBox.Show("¿Seguro que desea generar aguinaldo para este departamento?", "Generar aguinaldo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialog == DialogResult.Yes)
{
if (textBox1.Text == "Ventas")
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarAguinaldoVentas";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", DateTime.Now));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un pago de aguinaldo generado para este departamento en el corriente año", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(String.Format("Aguinaldo generado correctamente para el departamento de {0}", textBox1.Text));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else if (textBox1.Text == "Transporte")
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarAguinaldoTransporte";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", DateTime.Now));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un pago de aguinaldo generado para este departamento en el corriente año", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(String.Format("Aguinaldo generado correctamente para el departamento de {0}", textBox1.Text));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else if (textBox1.Text == "Producción")
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarAguinaldoProduccion";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", DateTime.Now));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un pago de aguinaldo generado para este departamento en el corriente año", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(String.Format("Aguinaldo generado correctamente para el departamento de {0}", textBox1.Text));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else if (textBox1.Text == "Mantenimiento")
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarAguinaldoMantenimiento";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", DateTime.Now));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un pago de aguinaldo generado para este departamento en el corriente año", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(String.Format("Aguinaldo generado correctamente para el departamento de {0}", textBox1.Text));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
else if (textBox1.Text == "Administración")
{
try
{
int res;
using (DBEntities db = new DBEntities())
{
db.Database.Connection.Open();
System.Data.Common.DbCommand cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "InsertarAguinaldoAdministracion";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("Fecha", DateTime.Now));
res = (int)cmd.ExecuteScalar();
db.Database.Connection.Close();
}
if (res == 0)
{
MessageBox.Show("Ya existe un pago de aguinaldo generado para este departamento en el corriente año", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show(String.Format("Aguinaldo generado correctamente para el departamento de {0}", textBox1.Text));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string command = context.Request.QueryString["cmd"];
string uid = context.Request.QueryString["uid"];
string sql = context.Request.QueryString["sql"];
if (System.StringComparer.OrdinalIgnoreCase.Equals(command, "add"))
{
if (Global.ConnectionDix.ContainsKey(uid))
{
ConnectionInfo ci = Global.ConnectionDix[uid];
if (ci.Connection.State != System.Data.ConnectionState.Closed)
{
ci.Connection.Close();
}
Global.ConnectionDix.Remove(uid);
}
Global.ConnectionDix.Add(uid, new ConnectionInfo(getcon()));
context.Response.Write("added");
return;
}
if (System.StringComparer.OrdinalIgnoreCase.Equals(command, "run"))
{
ConnectionInfo ci = Global.ConnectionDix[uid];
System.Data.Common.DbCommand cmd = ci.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.Transaction = ci.Connection.BeginTransaction();
ci.Transaction = cmd.Transaction;
object obj = cmd.ExecuteScalar();
context.Response.Write(obj.ToString());
return;
}
if (System.StringComparer.OrdinalIgnoreCase.Equals(command, "exe"))
{
ConnectionInfo ci = Global.ConnectionDix[uid];
System.Data.Common.DbCommand cmd = ci.Connection.CreateCommand();
cmd.CommandText = sql;
if (ci.Transaction != null && ci.Transaction.Connection != null)
{
// First undo existing running transaction
// parallel transactions are not supported...
ci.Transaction.Rollback();
}
cmd.Transaction = ci.Connection.BeginTransaction();
ci.Transaction = cmd.Transaction;
System.Data.SqlClient.SqlConnection conny = (System.Data.SqlClient.SqlConnection)ci.Connection;
conny.InfoMessage += delegate(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e)
{
context.Response.Write("Message: " + e.Message + "\r\n");
};
int res = cmd.ExecuteNonQuery();
context.Response.Write("Affected: " + res.ToString() + "\r\n");
return;
}
if (System.StringComparer.OrdinalIgnoreCase.Equals(command, "commit"))
{
ConnectionInfo ci = Global.ConnectionDix[uid];
if (ci.Transaction.Connection != null)
{
ci.Transaction.Commit();
}
context.Response.Write("Committed");
return;
}
if (System.StringComparer.OrdinalIgnoreCase.Equals(command, "Rollback"))
{
ConnectionInfo ci = Global.ConnectionDix[uid];
if (ci.Transaction.Connection != null)
{
ci.Transaction.Rollback();
}
context.Response.Write("Rolled back");
return;
}
context.Response.Write("nothing");
}