protected void lbSubmit_Click(object sender, EventArgs e)
{
try
{
if (ddlCourse.SelectedValue != "0" && ddlEmployee.SelectedValue != "0" && ddlref.SelectedValue != "0")
{
string id = cookie.Value.ToString();
string qry = "insert into InquiryMaster(Name,MobileNo,Discount,DOB,Email,InquiryDate,FollowUpdate,Remark,CourseId,UserId,RefId,Status) values('" + txtName.Text + "','" + txtno.Text + "','" + txtdis.Text + "','" + txtdob.Text + "','" + txtemail.Text + "','" + txtidate.Text + "','" + txtfdate.Text + "','" + txtdesc.Text + "','" + ddlCourse.SelectedValue + "','" + id + "','" + ddlref.SelectedValue + "',0)";
D.ExecuteQuery(qry);
divSuccess.Visible = true;
divError.Visible = false;
Clear();
BindData();
//SendSms sm = new SendSms();
//sm.sendotp("Your Inqiury is Registered", txtno.Text);
}
else
{
lberror.Visible = true;
}
}
catch (Exception ex)
{
divSuccess.Visible = false;
divError.Visible = true;
}
}
protected void Term()
{
PanelResult.Visible = false;
DataTable dt = com.ExecuteQuery("SELECT DISTINCT Term FROM RegResult");
ddlTerm.DataSource = dt;
ddlTerm.DataTextField = "Term";
ddlTerm.DataBind();
ddlTerm.Items.Insert(0, new System.Web.UI.WebControls.ListItem("--Select Term--", "0"));
}
protected void lbSubmit_Click(object sender, EventArgs e)
{
string qry = "insert into Category(Category) values('" + txtName.Text + "')";
D.ExecuteQuery(qry);
BindData();
Clear();
divSuccess.Visible = true;
divError.Visible = false;
}
//------------------------------------------REGISTER FUNCTION
public static void Register(string email, string password, string firstName, string lastName, string department)
{
// If this is a POST request, validate and process data
AntiForgery.Validate();
// Insert a new user into the database
// REGISTER
password = Connection.Hash(password);
string query = string.Format("INSERT INTO employee (Email, FirstName, LastName, Password) VALUES ('{0}','{1}','{2}','{3}')", email, firstName, lastName, password);
Connection.ExecuteNonQuery(query);
query = string.Format("SELECT Employee_ID FROM employee WHERE LOWER(Email) LIKE LOWER('{0}%')", email);
DataTable result = Connection.ExecuteQuery(query);
int e_id = int.Parse(result.Rows[0].ItemArray[0].ToString());
query = string.Format("SELECT Department_ID FROM Department WHERE LOWER(Name) = LOWER('{0}')", department);
result = Connection.ExecuteQuery(query);
int d_id = int.Parse(result.Rows[0].ItemArray[0].ToString());
query = string.Format("INSERT INTO Employee_has_department(Employee_ID, Department_ID) Values({0}, {1})", e_id, d_id);
Connection.ExecuteNonQuery(query);
//if (role == 4)
//{
// query = string.Format("INSERT INTO manager(Employee_ID, Department_ID) Values({0}, {1})", e_id, d_id);
// Connection.ExecuteNonQuery(query);
//}
//if (role == 5)
//{
// query = string.Format("INSERT INTO financepayer(Employee_ID) Values({0})", e_id);
// Connection.ExecuteNonQuery(query);
//}
}
public static Dictionary <User, int> PayerDropDown()
{
string query = string.Format("SELECT FirstName, LastName, Employee_ID FROM employee");
DataTable dt = Connection.ExecuteQuery(query);
Dictionary <User, int> usrList = new Dictionary <User, int>();
foreach (DataRow row in dt.Rows)
{
query = string.Format("SELECT COUNT(Trip_ID) FROM trip WHERE Employee_ID = {0} AND status LIKE 'accepted'", row[2]);
DataTable dtbl = Connection.ExecuteQuery(query);
DataRow rowCount = null;
if (dtbl.Rows.Count != 0)
{
rowCount = dtbl.Rows[0];
}
if (rowCount != null && int.Parse(rowCount[0].ToString()) > 0)
{
User usr = new User(int.Parse(row[2].ToString()));
usrList.Add(usr, int.Parse(rowCount[0].ToString()));
}
}
return(usrList);
}
public int GetViajesCount(DateTime fecha, int puertoOrigen, int puertoLlegada)
{
var query = ArmarSentenciaSP("P_Obtener_Cantidad_Viajes_Compra", new[] { GetParam(fecha), GetParam(puertoOrigen), GetParam(puertoLlegada) });
var result = Connection.ExecuteQuery(query);
return(result.Rows[0].GetValue <int>("count"));
}
public Viaje GetViaje(decimal id)
{
var query = ArmarSentenciaSP("P_Obtener_Viaje", new[] { GetParam(id) });
var result = Connection.ExecuteQuery(query);
return(new Viaje(result.Rows[0]));
}
public int GetViajesCount(string codigoCrucero, string codigoRecorrido)
{
var query = ArmarSentenciaSP("P_Obtener_Cantidad_Viajes", new[] { GetParam(codigoCrucero), GetParam(codigoRecorrido) });
var result = Connection.ExecuteQuery(query);
return(result.Rows[0].GetValue <int>("count"));
}
public void Add(Sell s)
{
if (s.purpose == "Sell")
{
gcon.getConnection();
gcon.ExecuteQuery("insert into Sell values('" + s.Oname + "','" + s.Oemail + "','" + s.Ophone + "','" + s.purpose + "','" + s.proptype + "','" + s.city + "','" + s.address + "','" + s.description + "','" + s.price + "','" + s.pic + "')");
}
else if (s.purpose == "Rent")
{
gcon.getConnection();
gcon.ExecuteQuery("insert into Rent values('" + s.Oname + "','" + s.Oemail + "','" + s.Ophone + "','" + s.purpose + "','" + s.proptype + "','" + s.city + "','" + s.address + "','" + s.description + "','" + s.price + "','" + s.pic + "')");
}
else
{
}
}
public Puerto GetPuerto(int puertoId)
{
var query = ArmarSentenciaSP("P_Obtener_Puerto", new[] { GetParam(puertoId) });
var result = Connection.ExecuteQuery(query);
return(new Puerto(result.Rows[0]));
}
public User(int EmplID)
{
EmployeeID = EmplID;
string query = "SELECT * FROM Employee WHERE Employee_ID = " + EmplID;
DataTable dt = new DataTable();
dt = Connection.ExecuteQuery(query);
try
{
FirstName = dt.Rows[0].ItemArray[1].ToString();
LastName = dt.Rows[0].ItemArray[2].ToString();
RoleID = int.Parse(dt.Rows[0].ItemArray[8].ToString());
EmployeeNumber = int.Parse(dt.Rows[0].ItemArray[3].ToString());
PayNumber = int.Parse(dt.Rows[0].ItemArray[4].ToString());
query = string.Format("SELECT department_ID FROM Employee_has_Department WHERE Employee_ID = {0}", EmplID);
dt = Connection.ExecuteQuery(query);
department_ID = int.Parse(dt.Rows[0].ItemArray[0].ToString());
}
catch (Exception e)
{
}
Exists();
}
/// <summary>
/// insert into database
/// </summary>
/// <param name="query"></param>
public static void ToDatabase(string query)
{
// Something went wrong, throw an exception
if (query == "")
{
Logger.Log("Query is empty. Not inserting to database.", Logger.Status.Warning, true);
return;
}
try
{
// Insert to db
if (Connection.Open())
{
if (Connection.IsAlive)
{
Connection.ExecuteQuery(query);
Logger.Log("Your creation has been saved to the database.", Logger.Status.Info, true);
return;
}
}
else
{
Logger.Log("There was an error connecting to the database. Your creation was not saved.", Logger.Status.Error, true);
}
}
catch (Exception ex)
{
Logger.Log("There was an error inserting the query to the database. See log file for more info.", Logger.Status.Error, true);
Logger.Log(ex.Message, Logger.Status.Error, false);
}
}
//public bool Save(cusuario usuario)
//{
// if (cusuario._ExistsInDatabase)
// return InternalUpdate(usuario);
// else
// {
// Sucursal._ExistsInDatabase = true;
// return InternalSave(usuario);
// }
//}
public List <cusuario> FindAll()
{
List <cusuario> usuarios = new List <cusuario>();
cusuario usuario = null;
try
{
SqlCommand comando = new SqlCommand();
comando.CommandText = "usp_Usuarios_FindAll";
comando.CommandType = CommandType.StoredProcedure;
DataTable table = _Conexion.ExecuteQuery(comando);
foreach (DataRow row in table.Rows)
{
usuario = new cusuario()
{
_ExistsInDatabase = true,
_intidusuario = int.Parse(row["UidSucursal"].ToString()),
Vchnombre = row["VchNombre"].ToString()
};
usuarios.Add(usuario);
}
}
catch (SqlException e)
{
throw new DatabaseException("Cannot load usuarios", e);
}
return(usuarios);
}
public ActionResult Login(LoginModel login, string returnUrl)
{
bool usuarioValido = false;
try
{
var usuario = Connection.ExecuteQuery <Usuario>(String.Format("select * from usuarios where Nome = '{0}' And Senha = '{1}'", login.Username, login.Password)).First();
usuarioValido = true;
UsuarioLogado = usuario;
}
catch (Exception)
{
// qualquer falha considera usuário inválido
}
if (usuarioValido)
{
FormsAuthentication.SetAuthCookie(UsuarioLogado.IdUsuario.ToString(), login.Connected);
if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/") &&
!returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
{
return(Redirect(returnUrl));
}
else
{
return(RedirectToAction("Index", "Home"));
}
}
else
{
ModelState.AddModelError("", "");
return(View(login));
}
}
public Response getAll(Response response)
{
try
{
var sql = getAllSql();
var connetion = new Connection();
DataSet dataset = connetion.ExecuteQuery(sql);
if (dataset.Tables[0].Rows.Count <= 0)
{
return(response.GenerateResult(true, "Erro ao buscar dados do carro"));
}
List <Carro> carros = new List <Carro>();
foreach (DataRow item in dataset.Tables[0].Rows)
{
var carro = new Carro();
carro.idUnidade = int.Parse(item["idUnidade"].ToString());
carro.placa = item["placa"].ToString();
carro.cor = item["cor"].ToString();
carro.modeloCarro.modelo = item["modelo"].ToString();
carro.modeloCarro.imagem = item["imagem"].ToString();
carro.modeloCarro.fabricante.fabricante = item["fabricante"].ToString();
carros.Add(carro);
}
return(response.GenerateResult(false, "Busca Efetuada com sucesso", carros));
}
catch (Exception ex)
{
return(new Response(true, ex.ToString(), null));
}
}
public Reserva GetReserva(decimal codigo)
{
var query = ArmarSentenciaSP("P_Obtener_Reserva", new[] { GetParam(codigo) });
var result = Connection.ExecuteQuery(query);
return(new Reserva(result.Rows[0]));
}
private void ClearDomainInfo(Connection connection)
{
string query = EntryControl.Resources.Sec.User.ClearDomainUser;
QueryParameters parameters = new QueryParameters("id", Id);
connection.ExecuteQuery(query, parameters);
}
public Marca GetMarca(int marcaId)
{
var query = ArmarSentenciaSP("P_Obtener_Marca", new[] { GetParam(marcaId) });
var result = Connection.ExecuteQuery(query);
return(new Marca(result.Rows[0]));
}
/// <summary>
/// insert into database
/// </summary>
/// <param name="query"></param>
public static void ToDatabase(string query)
{
if (QueryHasIssues(query))
{
return;
}
try
{
// Insert to db
if (Connection.Open())
{
if (Connection.IsAlive)
{
Connection.ExecuteQuery(query);
Logger.Log("Your creation has been saved to the database.", Logger.Status.Info, true);
return;
}
}
else
{
Logger.Log("There was an error connecting to the database. Your creation was not saved.", Logger.Status.Error, true);
}
}
catch (Exception ex)
{
Logger.Log("There was an error inserting the query to the database. See log file for more info.", Logger.Status.Error, true);
Logger.Log(ex.Message, Logger.Status.Error, false);
}
}
public Cliente GetCliente(int id)
{
var query = ArmarSentenciaSP("P_Obtener_Cliente", new[] { GetParam(id) });
var result = Connection.ExecuteQuery(query);
return(new Cliente(result.Rows[0]));
}
public void Signup(Admin u)
{
Connection con = new Connection();
con.getConnection();
con.ExecuteQuery("insert into Register values('" + u.name + "','" + u.email + "','" + u.password + "','" + u.role + "')");
}
private void button2_Click(object sender, EventArgs e)
{
string query = "";
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if ((bool)dataGridView1.Rows[i].Cells["XOA"].Value == true)
{
string idBanHang = dataGridView1.Rows[i].Cells["ID"].Value.ToString();
int soluongphieuxuat = int.Parse((con.GetDataInfo("select count(*) from PHIEUXUAT where PHIEUXUAT.MABANHANG = " + idBanHang + "")).Rows[0][0].ToString());
int soluongthutien = int.Parse((con.GetDataInfo("select count(*) from THUTIEN where THUTIEN.BANHANG_ID = " + idBanHang + "")).Rows[0][0].ToString());
for (int j = 0; j < soluongphieuxuat; j++)
{
query += "DELETE top(1) FROM PHIEUXUAT WHERE MABANHANG = '" + idBanHang + "'\n";
}
for (int j = 0; j < soluongthutien; j++)
{
query += "DELETE top(1) FROM THUTIEN where BANHANG_ID = '" + idBanHang + "'\n";
}
query += "DELETE FROM BANHANG WHERE BANHANG.ID = " + idBanHang + " \n";
}
}
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result = MessageBox.Show("", "XÁC NHẬN XOA", buttons);
if (result == DialogResult.Yes)
{
con.ExecuteQuery(query);
MessageBox.Show("XOA THÀNH CÔNG");
}
}
//public static int GetActive(Connection connection, string sUserID)
//{
// return ((int)(connection.ExecuteScalar("SELECT TimeLogID " +
// "FROM TimeLog " +
// "WHERE ActionTypeID = " + ActionTypes.Start.ToString("d") +
// " AND TimeLogID = (SELECT MAX(TimeLogID) " +
// "FROM TimeLog TML INNER JOIN TaskLog TKL ON (TML.TaskLogID = TKL.TaskLogID) " +
// "WHERE TKL.UserId = '" + sUserID + "')") ?? 0));
//}
#endregion
#endregion
public static TimeLogEntry LoadActive(Connection connection, string sUserID)
{
DataTable tblTimeLogEntry;
TimeLogEntry timeLogEntry;
tblTimeLogEntry = connection.ExecuteQuery("SELECT TML.TimeLogID, TML.ActionTime, TML.ActionTypeID, TKL.TaskLogID, " +
"TKL.TaskDate, TKL.UserID, TKL.TaskID, TKL.TaskStatusID " +
"FROM TaskLog TKL INNER JOIN TimeLog TML ON (TKL.TaskLogID = TML.TaskLogID) " +
"INNER JOIN (SELECT MAX(ActionTime) AS ActionTime, TKL.UserID " +
"FROM TimeLog TML INNER JOIN TaskLog TKL ON (TML.TaskLogID = TKL.TaskLogID) " +
"WHERE TKL.UserID = '" + sUserID + "' " +
"GROUP BY TKL.UserID " +
"HAVING SUM(CASE TML.ActionTypeID WHEN 1 THEN 1 WHEN 0 THEN -1 ELSE 0 END) < 0 " +
") ATML ON (TML.ActionTime = ATML.ActionTime AND TKL.UserID = ATML.UserID) " +
"WHERE TML.ActionTypeID = 0");
if (tblTimeLogEntry.Rows.Count == 0)
{
return(null);
}
else
{
return(new TimeLogEntry(Convert.ToInt32(tblTimeLogEntry.Rows[0]["TimeLogID"]),
Convert.ToDateTime(tblTimeLogEntry.Rows[0]["ActionTime"]),
(ActionTypes)Enum.Parse(typeof(ActionTypes), tblTimeLogEntry.Rows[0]["ActionTypeID"].ToString()),
new TaskLogEntry(Convert.ToInt32(tblTimeLogEntry.Rows[0]["TaskLogID"]),
Convert.ToDateTime(tblTimeLogEntry.Rows[0]["TaskDate"]),
tblTimeLogEntry.Rows[0]["UserID"].ToString(),
new Task(Convert.ToInt32(tblTimeLogEntry.Rows[0]["TaskID"])),
(TaskStatuses)Enum.Parse(typeof(TaskStatuses), tblTimeLogEntry.Rows[0]["TaskStatusID"].ToString()))));
}
}
public TipoCabina GetTipoCabina(int tipoCabinaId)
{
var query = ArmarSentenciaSP("P_Obtener_Tipo_Cabina", new[] { GetParam(tipoCabinaId) });
var result = Connection.ExecuteQuery(query);
return(new TipoCabina(result.Rows[0]));
}
public Recorrido GetRecorrido(decimal id)
{
var query = ArmarSentenciaSP("P_Obtener_Recorrido", new[] { GetParam(id) });
var result = Connection.ExecuteQuery(query);
return(new Recorrido(result.Rows[0]));
}
public Document GetDocumentByID(string documentID)
{
OracleParameter parameterDocumentID = new OracleParameter("DOCUMENTID", OracleDbType.Varchar2)
{
Value = documentID
};
DataTable dataTable = Connection.ExecuteQuery(Databases.SENSITIVE - DB, dbQueries.GET_DOCUMENT_BY_ID, new List<OracleParameter> { parameterDocumentID });
if (dataTable.Rows.Count > 0)
{
Document document = new Document()
{
data1 = dataTable.Rows[0].ItemArray[0].ToString(),
data2 = dataTable.Rows[0].ItemArray[1].ToString(),
data3 = dataTable.Rows[0].ItemArray[2].ToString(),
data4 = dataTable.Rows[0].ItemArray[3].ToString(),
data5 = dataTable.Rows[0].ItemArray[3].ToString()
};
return document;
}
else
{
throw new DocumentVaultDataException("Document not found!");
}
}
/// <summary>Refresh this instance to reflect the database connection.</summary>
public void Refresh()
{
simulationIDs.Clear();
checkpointIDs.Clear();
tables.Clear();
// Read in simulation ids.
if (Connection.TableExists("_Simulations"))
{
var data = Connection.ExecuteQuery("SELECT * FROM [_Simulations]");
foreach (DataRow row in data.Rows)
{
simulationIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// Read in checkpoint ids.
if (Connection.TableExists("_Checkpoints"))
{
var data = Connection.ExecuteQuery("SELECT * FROM [_Checkpoints]");
foreach (DataRow row in data.Rows)
{
checkpointIDs.Add(row["Name"].ToString(), Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture));
}
}
// For each table in the database, read in field names.
foreach (var tableName in Connection.GetTableNames())
{
tables.Add(tableName, Connection.GetTableColumns(tableName));
}
// Get the units table.
units = new DataView(GetData("_Units"));
}
public ActionResult Search(object entity)
{
var query = Request.QueryString["q"];
var results = new SearchResults();
results.QueryText = query;
if (!String.IsNullOrEmpty(query))
{
//Initialize the connection to the search index
Connection conn = new Connection("http://localhost:8080/solr/collection1");
var parameters = new NameValueCollection();
//Set the query
parameters["q"] = String.Format("\"{0}\"", query);
//Add highlighting - enables summary field be auto-generated if empty
parameters.Add("hl", "true");
//Add publication id
parameters.Add("fq", "publicationid:4");
//Set page size
parameters.Add("rows", "5");
//Add page number (default is 1)
String start = Request.QueryString["start"];
if (!String.IsNullOrEmpty(start))
{
parameters["start"] = start;
}
results = conn.ExecuteQuery(parameters);
}
return(View("SearchResults", results));
}
public Crucero GetCrucero(int cruceroId)
{
var query = ArmarSentenciaSP("P_Obtener_Crucero", new[] { GetParam(cruceroId) });
var result = Connection.ExecuteQuery(query);
return(new Crucero(result.Rows[0]));
}
} // end Fetch
public override DataTable Fetch(Connection connection, string sUserId, DateTime dtStartDate, DateTime dtEndDate)
{
string sSQL =
"SELECT SUM(CASE TML.ActionTypeID WHEN 1 THEN CONVERT(float, TML.ActionTime) ELSE (CONVERT(float, TML.ActionTime) * -1) END) * 24 AS TaskTime, " +
"TimeAllowed, " +
"CASE WHEN TimeAllowed = 0 " +
"THEN 100.00 " +
"ELSE (SUM(CASE TML.ActionTypeID WHEN 1 THEN CONVERT(float, TML.ActionTime) ELSE (CONVERT(float, TML.ActionTime) * -1) END) * 2400) / TimeAllowed END AS '% Complete', " +
"UserID, Task " +
"FROM " +
"( " +
"SELECT TKL.TaskDate, TKL.UserID, TKL.TaskID, TML.ActionTime, TML.ActionTypeID " +
"FROM TaskLog TKL INNER JOIN TimeLog TML ON (TKL.TaskLogID = TML.TaskLogID) " +
"UNION " +
// This is to generate inferred stops in order to get current time for active tasks
"SELECT TKL.TaskDate, TKL.UserID, TKL.TaskID, '" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") + "' AS ActionTime, 1 AS ActionTypeID " +
"FROM TaskLog TKL INNER JOIN TimeLog TML ON (TKL.TaskLogID = TML.TaskLogID) " +
"INNER JOIN (SELECT MAX(ActionTime) AS ActionTime, TKL.UserID " +
"FROM TimeLog TML INNER JOIN TaskLog TKL ON (TML.TaskLogID = TKL.TaskLogID) " +
"GROUP BY TKL.UserID " +
"HAVING SUM(CASE TML.ActionTypeID WHEN 1 THEN 1 WHEN 0 THEN -1 ELSE 0 END) < 0" +
") ATML ON (TML.ActionTime = ATML.ActionTime AND TKL.UserID = ATML.UserID) " +
"WHERE TML.ActionTypeID = 0 " +
") TML INNER JOIN Tasks T ON (TML.TaskID = T.TaskID) " +
GetFilter(sUserId, dtStartDate, dtEndDate) +
"GROUP BY UserID, Task, TimeAllowed " +
"ORDER BY Task ";
return(connection.ExecuteQuery(sSQL));
}
private Dictionary<string, int> _SyncMissingValues(Dictionary<string, int> vals, Type t, Connection conn)
{
t = (t.IsGenericType ? t.GetGenericArguments()[0] : t);
string[] keys = new string[vals.Count];
vals.Keys.CopyTo(keys, 0);
foreach (string str in Enum.GetNames(t))
{
if (!vals.ContainsKey(str))
{
conn.ExecuteNonQuery(string.Format("INSERT INTO {0}({1}) VALUES({2});",new object[]{
_enumTableMaps[t],
_pool.Translator.GetEnumValueFieldName(t, conn),
conn.CreateParameterName("value")}),
new System.Data.IDbDataParameter[]{
conn.Pool.CreateParameter(conn.CreateParameterName("id"),null,Org.Reddragonit.Dbpro.Structure.Attributes.FieldType.INTEGER,4),
conn.CreateParameter(conn.CreateParameterName("value"),str)
});
conn.ExecuteQuery("SELECT ID FROM " + _enumTableMaps[t] + " WHERE " + _pool.Translator.GetEnumValueFieldName(t, conn) + " = " + conn.CreateParameterName("value"),
new IDbDataParameter[]{
conn.CreateParameter(conn.CreateParameterName("value"),str)
});
conn.Read();
vals.Add(str, conn.GetInt32(0));
conn.Close();
}
}
return vals;
}
private void _CreateTablesForType(Type type, Connection conn)
{
List<Type> types = new List<Type>();
types.Add(type);
if (new List<Type>(type.GetInterfaces()).Contains(typeof(IClassView)))
{
ClassViewAttribute cva = conn.Pool[type];
foreach (Type t in cva.Query.RequiredTypes)
{
if (!types.Contains(t))
types.Add(t);
types = _RecurLoadTypesForTable(_pool.Mapping[t], types);
}
}
else
types = _RecurLoadTypesForTable(_pool.Mapping[type], types);
for (int x = 1; x < types.Count; x++)
{
if (_createdTypes.Contains(types[x]))
{
types.RemoveAt(x);
x--;
}
}
List<ExtractedTableMap> tables;
List<Trigger> triggers;
List<Generator> generators;
List<IdentityField> identities;
List<View> views;
List<StoredProcedure> procedures;
ExtractExpectedStructure(ref types, out tables, out triggers, out generators, out identities, out views, out procedures, conn);
List<string> createdTables = _UpdateStructure(tables, triggers, generators, identities, views, procedures,conn);
foreach (Type t in types)
{
if (Utility.IsEnum(t))
{
if (createdTables.Contains(_pool.Enums[t]))
_pool.Enums.InsertEnumIntoTable(t, conn);
else
_pool.Enums.LoadEnumsFromTable(t, conn);
}
}
_createdTypes.AddRange(types);
_UpdateAddiontalData(triggers, generators, identities, views, procedures);
if (!_pool.DebugMode && (_translations.Count > 0))
{
foreach (Type t in types)
{
if (_translations.ContainsKey(t))
{
foreach (EnumTranslationPair etp in _translations[t])
{
conn.ExecuteNonQuery(String.Format(
"UPDATE {0} SET {1} = '{3}' WHERE {1} = '{2}'",
new object[]{
_pool.Enums[t],
_pool.Translator.GetEnumValueFieldName(t,conn),
etp.OriginalName,
etp.NewName}
));
conn.Close();
}
}
Dictionary<string, int> enumValuesMap = new Dictionary<string, int>();
Dictionary<int, string> enumReverseValuesMap = new Dictionary<int, string>();
List<string> enumNames = new List<string>(Enum.GetNames(t));
List<int> deletes = new List<int>();
conn.ExecuteQuery(String.Format("SELECT ID,{1} FROM {0}",
_pool.Enums[t],
_pool.Translator.GetEnumValueFieldName(t, conn)));
while (conn.Read())
{
if (enumNames.Contains(conn[1].ToString()))
{
enumValuesMap.Add(conn[1].ToString(), (int)conn[0]);
enumReverseValuesMap.Add((int)conn[0], conn[1].ToString());
enumNames.Remove(conn[1].ToString());
}
else
deletes.Add((int)conn[0]);
}
conn.Close();
if (deletes.Count > 0)
{
foreach (int i in deletes)
{
conn.ExecuteNonQuery(String.Format("DELETE FROM {0} WHERE ID = {1}",
_pool.Enums[t],
i));
conn.Close();
}
}
if (enumNames.Count > 0)
{
foreach (string str in enumNames)
{
conn.ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES('{2}')",
_pool.Enums[t],
_pool.Translator.GetEnumValueFieldName(t, conn),
str));
conn.Close();
conn.ExecuteQuery(String.Format("SELECT ID FROM {0} WHERE {1}='{2}'",
_pool.Enums[t],
_pool.Translator.GetEnumValueFieldName(t, conn),
str));
conn.Read();
enumValuesMap.Add(str, (int)conn[0]);
enumReverseValuesMap.Add((int)conn[0], str);
conn.Close();
}
}
conn.Commit();
_pool.Enums.AssignMapValues(t, enumValuesMap, enumReverseValuesMap);
}
}
}
public void Init(Connection conn)
{
_tables = new List<ExtractedTableMap>();
_triggers = new List<Trigger>();
_generators = new List<Generator>();
_identities = new List<IdentityField>();
_views = new List<View>();
_procedures = new List<StoredProcedure>();
_createdTypes = new List<Type>();
conn.ExecuteQuery(conn.Pool.queryBuilder.SelectTriggers());
while (conn.Read())
{
//patch to handle long trigger code from mssql
if (_triggers.Count > 0)
{
if (_triggers[_triggers.Count - 1].Name == (string)conn[0])
_triggers[_triggers.Count - 1] = new Trigger((string)conn[0], (string)conn[1], _triggers[_triggers.Count - 1].Code + (string)conn[2]);
else
_triggers.Add(new Trigger((string)conn[0], (string)conn[1], (string)conn[2]));
}else
_triggers.Add(new Trigger((string)conn[0], (string)conn[1], (string)conn[2]));
}
conn.Close();
conn.ExecuteQuery(conn.Pool.queryBuilder.SelectProcedures());
while (conn.Read())
{
_procedures.Add(new StoredProcedure(conn[0].ToString(), conn[1].ToString(), conn[2].ToString(), conn[3].ToString(), conn[4].ToString()));
}
conn.Close();
conn.ExecuteQuery(conn.queryBuilder.SelectViews());
while (conn.Read())
_views.Add(new View((string)conn[0], (string)conn[1]));
conn.Close();
conn.ExecuteQuery(conn.queryBuilder.SelectTableNames());
while (conn.Read())
{
_tables.Add(new ExtractedTableMap((string)conn[0]));
}
conn.Close();
for (int x = 0; x < _tables.Count; x++)
{
ExtractedTableMap etm = _tables[x];
etm.Indices = conn.queryBuilder.ExtractTableIndexes(etm.TableName, conn);
conn.ExecuteQuery(conn.queryBuilder.SelectTableFields(etm.TableName));
while (conn.Read())
{
etm.Fields.Add(new ExtractedFieldMap(conn[0].ToString(), conn[1].ToString(),
long.Parse(conn[2].ToString()), bool.Parse(conn[3].ToString()), bool.Parse(conn[4].ToString()),
bool.Parse(conn[5].ToString()),
(conn.IsDBNull(6) ? null : conn[6].ToString())));
}
conn.Close();
conn.ExecuteQuery(conn.queryBuilder.SelectForeignKeys(etm.TableName));
while (conn.Read())
{
etm.ForeignFields.Add(new ForeignRelationMap(conn[5].ToString(), conn[0].ToString(), conn[1].ToString(),
conn[2].ToString(), conn[3].ToString(), conn[4].ToString()));
}
conn.Close();
_tables.RemoveAt(x);
_tables.Insert(x, etm);
}
if (conn.UsesGenerators)
{
conn.ExecuteQuery(conn.queryBuilder.SelectGenerators());
while (conn.Read())
{
_generators.Add(new Generator((string)conn[0]));
}
conn.Close();
for (int x = 0; x < _generators.Count; x++)
{
Generator gen = _generators[x];
conn.ExecuteQuery(conn.queryBuilder.GetGeneratorValue(gen.Name));
conn.Read();
gen.Value = long.Parse(conn[0].ToString());
conn.Close();
_generators.RemoveAt(x);
_generators.Insert(x, gen);
}
}
if (conn.UsesIdentities)
{
conn.ExecuteQuery(conn.queryBuilder.SelectIdentities());
while (conn.Read())
{
_identities.Add(new IdentityField((string)conn[0], (string)conn[1], (string)conn[2], (string)conn[3]));
}
conn.Close();
}
}
public void ExecutePaged(Type primaryTable, IDbDataParameter[] parameters, ulong? start, ulong? recordCount)
{
_conn = (_conn == null ? _pool.GetConnection() : _conn);
lock (_requiredTypes)
{
if (_requiredTypes.Count > 0)
{
while (_requiredTypes.Count > 0)
_pool.Updater.InitType(_requiredTypes.Dequeue(), _conn);
}
}
string query = "";
try
{
List<IDbDataParameter> pars = new List<IDbDataParameter>();
if (parameters != null)
pars.AddRange(parameters);
query = _conn.queryBuilder.SelectPaged(_outputQuery, _pool.Mapping[primaryTable], ref pars, start, recordCount);
List<IDbDataParameter> p = CorrectParameters(pars, ref query);
_conn.ExecuteQuery(query, p);
}
catch (Exception e)
{
throw new Exception("An error occured executing translated query: " + query, e);
}
}
public void Execute(IDbDataParameter[] parameters)
{
_conn = (_conn == null ? _pool.GetConnection() : _conn);
lock (_requiredTypes)
{
if (_requiredTypes.Count > 0)
{
while (_requiredTypes.Count > 0)
_pool.Updater.InitType(_requiredTypes.Dequeue(), _conn);
}
}
try
{
if ((parameters != null) && (parameters.Length > 0))
{
List<IDbDataParameter> pars = CorrectParameters(parameters, ref _outputQuery);
_conn.ExecuteQuery(_outputQuery, pars);
}
else
_conn.ExecuteQuery(_outputQuery);
}
catch (Exception e)
{
throw new Exception("An error occured executing translated query: " + _outputQuery, e);
}
}
internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
{
List<Index> ret = new List<Index>();
Dictionary<string, string> objIds = new Dictionary<string, string>();
List<string> indexIds = new List<string>();
conn.ExecuteQuery(@"select ind.object_id,ind.index_id,ind.name,ind.is_unique from sys.indexes ind, sysobjects tbl
WHERE ind.object_id = tbl.id AND tbl.xtype='U' AND ind.name IS NOT NULL AND ind.is_primary_key=0 AND tbl.name = '"+tableName+"'");
while (conn.Read())
{
ret.Add(new Index(conn[2].ToString(), null, conn[3].ToString() == "1", false));
objIds.Add(conn[2].ToString(), conn[0].ToString());
indexIds.Add(conn[1].ToString());
}
conn.Close();
for (int x = 0; x < ret.Count; x++)
{
List<string> fields = new List<string>();
bool asc = false;
conn.ExecuteQuery("SELECT c.COLUMN_NAME,indcol.is_descending_key FROM sys.index_columns indcol,INFORMATION_SCHEMA.COLUMNS c " +
"WHERE indcol.object_id = '"+objIds[ret[x].Name]+"' AND indcol.index_id = "+indexIds[x]+" AND c.table_name = '"+tableName+"' AND c.ORDINAL_POSITION = indcol.column_id ORDER BY index_id,key_ordinal");
while (conn.Read())
{
fields.Add(conn[0].ToString());
asc = conn[1].ToString() == "0";
}
conn.Close();
Index ind = ret[x];
ret.RemoveAt(x);
ret.Insert(x, new Index(ind.Name,fields.ToArray(),ind.Unique,asc));
}
return ret;
}
public NameTranslator(ConnectionPool pool, Connection conn)
{
_pool = pool;
_nameTranslations = new Dictionary<string, string>();
conn.ExecuteQuery(conn.queryBuilder.GetAllObjectDescriptions());
while (conn.Read())
{
Logger.LogLine(string.Format("Adding Description: {0} = {1}",
conn[0].ToString(),
conn[1].ToString()));
if (conn[0].ToString().Trim().StartsWith(string.Format(_VIEW_DESCRIPTION,""))&&conn[0].ToString().Trim().EndsWith("]"))
_nameTranslations.Add(conn[0].ToString().Trim().Substring(0,conn[0].ToString().Trim().IndexOf("\t")), conn[1].ToString().Trim());
else
_nameTranslations.Add(conn[0].ToString().Trim(), conn[1].ToString().Trim());
}
conn.Close();
_createDescriptions = new List<string>();
}
internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
{
List<Index> ret = new List<Index>();
conn.ExecuteQuery("SHOW INDEX FROM " + tableName + " FROM " + ((MySqlConnectionPool)conn.Pool).DbName+" WHERE Key_Name <> 'PRIMARY' AND "+
"Key_Name NOT IN (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = '" + ((MySqlConnectionPool)conn.Pool).DbName+"' AND TABLE_NAME='"+tableName+"')");
string curName = null;
bool unique=false;
List<string> fields = new List<string>();
while (conn.Read())
{
if (curName!=null){
if (curName!=conn[2].ToString()){
ret.Add(new Index(curName,fields.ToArray(),unique,false));
curName = conn[2].ToString();
fields=new List<string>();
}
}
fields.Add(conn[4].ToString());
unique = conn[1].ToString()=="0";
}
if (curName!=null){
ret.Add(new Index(curName,fields.ToArray(),unique,false));
}
conn.Close();
return ret;
}
internal void LoadEnumsFromTable(Type t, Connection conn)
{
t = (t.IsGenericType ? t.GetGenericArguments()[0] : t);
conn.ExecuteQuery("SELECT * FROM " + _enumTableMaps[t]);
Dictionary<string, int> vals = new Dictionary<string, int>();
while (conn.Read())
vals.Add(conn[1].ToString(), conn.GetInt32(0));
conn.Close();
if (_enumValuesMap.ContainsKey(t))
_enumValuesMap.Remove(t);
if (_enumReverseValuesMap.ContainsKey(t))
_enumReverseValuesMap.Remove(t);
if (vals.Count == 0)
InsertEnumIntoTable(t, conn);
else
{
foreach (string str in Enum.GetNames(t))
{
if (!vals.ContainsKey(str))
{
vals = _SyncMissingValues(vals, t, conn);
break;
}
}
_enumValuesMap.Add(t, vals);
Dictionary<int, string> revs = new Dictionary<int, string>();
foreach (string str in vals.Keys)
revs.Add(vals[str], str);
_enumReverseValuesMap.Add(t, revs);
}
}
internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
{
List<Index> ret = new List<Index>();
conn.ExecuteQuery("SELECT TRIM(ind.RDB$INDEX_NAME),ind.RDB$UNIQUE_FLAG,(CASE WHEN ind.RDB$INDEX_TYPE IS NULL THEN 0 ELSE 1 END) FROM RDB$INDICES ind "+
"WHERE ind.RDB$RELATION_NAME = '" + tableName + "' AND ind.RDB$INDEX_NAME NOT IN (SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$RELATION_NAME = '" + tableName + "' AND RDB$INDEX_NAME IS NOT NULL) ORDER BY ind.RDB$INDEX_ID");
while (conn.Read())
{
ret.Add(new Index(conn[0].ToString(), null, conn[1].ToString() == "1", conn[2].ToString() == "0"));
}
conn.Close();
for (int x = 0; x < ret.Count; x++)
{
conn.ExecuteQuery("SELECT TRIM(ind.RDB$FIELD_NAME) FROM RDB$INDEX_SEGMENTS ind WHERE TRIM(ind.RDB$INDEX_NAME) = '" + ret[0].Name + "' ORDER BY ind.RDB$FIELD_POSITION");
List<string> fields = new List<string>();
while (conn.Read())
{
fields.Add(conn[0].ToString());
}
conn.Close();
Index ind = ret[x];
ind.Fields = fields.ToArray();
ret.RemoveAt(x);
ret.Insert(x, ind);
}
return ret;
}
