public static TIncidencia GetIncidenciaFromDr(SqlCeDataReader dr)
{
TIncidencia inci = new TIncidencia();
inci.incidenciaId = dr.GetInt32(0);
inci.nombre = dr.GetString(1);
return inci;
}
public static TVigilante GetVigilanteFromDr(SqlCeDataReader dr)
{
TVigilante vig = new TVigilante();
vig.vigilanteId = dr.GetInt32(0);
vig.nombre = dr.GetString(1);
return vig;
}
public static TGrupo GetGrupoFromDr(SqlCeDataReader dr)
{
TGrupo g = new TGrupo();
g.grupoId = dr.GetInt32(0);
g.nombre = dr.GetString(1);
return g;
}
private POSUser POSUserDeserializer(SqlCeDataReader reader)
{
var username = reader["username"].ToString();
string[] roles = reader["roles"].ToString().DecryptString().Replace("\n", "").Split(',');
string[] rules = reader["rules"].ToString().DecryptString().Replace("\n", "").Split(',');
return new POSUser(username, roles, rules);
}
private CivInfoBO ReadRecord(SqlCeDataReader reader)
{
CivInfoBO result = new CivInfoBO();
result.Name = reader.GetString(0);
result.Value = reader.GetString(1);
return result;
}
public static Customer GetCustomerFromReader(SqlCeDataReader reader)
{
if (reader != null)
{
Customer customer = new Customer();
customer.CustomerID = (long)reader["CustomerID"];
if (!reader.IsDBNull(reader.GetOrdinal("ResponsiblePartyID")))
{
customer.ResponsiblePartyID = (long)reader["ResponsiblePartyID"];
}
Customer.Types type;
Enum.TryParse<Customer.Types>(reader["Type"].ToString(), out type);
customer.Type = type;
customer.PersonID = (long)reader["PersonID"];
customer.FirstName = reader["FirstName"].ToString();
customer.LastName = reader["LastName"].ToString();
customer.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
Person.PersonTypes personType;
Enum.TryParse<Person.PersonTypes>(reader["PersonType"].ToString(), out personType);
customer.PersonType = personType;
customer.Email = reader["Email"].ToString();
customer.Password = reader["Password"].ToString();
customer.SetAddress(AddressDB.GetAddressFromReader(reader));
return customer;
}
return null;
}
private Product ProductDeserializer(SqlCeDataReader reader)
{
var id = Guid.Parse(reader["id"].ToString());
var name = reader["name"].ToString();
var barcode = reader["barcode"].ToString();
var price = Convert.ToDouble(reader["price"]);
return new Product(id, name, barcode, price);
}
private AutoNumber AutoNumberDeserializer(SqlCeDataReader reader)
{
string organizationId = reader["organizationid"].ToString();
string clientId = reader["clientid"].ToString();
DateTime date = Convert.ToDateTime(reader["date"].ToString());
long number = Convert.ToInt64(reader["number"]);
return new AutoNumber(organizationId, clientId, date, number);
}
public static TEdificio GetEdificioFromDr(SqlCeDataReader dr)
{
TEdificio edif = new TEdificio();
edif.edificioId = dr.GetInt32(0);
edif.nombre = dr.GetString(1);
edif.grupoId = dr.GetInt32(2);
return edif;
}
public static TVigilante GetVigilanteFromDr(SqlCeDataReader dr)
{
TVigilante v = new TVigilante();
v.vigilanteId = dr.GetInt32(0);
v.nombre = dr.GetString(1);
v.tag = dr.GetString(2);
v.tagf = dr.GetString(3);
return v;
}
private CompanyProfile CompanyProfileDeserializer(SqlCeDataReader reader)
{
string organizationId = reader["organizationid"].ToString();
string clientId = reader["clientid"].ToString();
string name = reader["name"].ToString();
string address = reader["address"].ToString();
byte[] logo = Encoding.ASCII.GetBytes(reader["logo"].ToString());
return new CompanyProfile(organizationId, clientId, name, address,logo);
}
public static TRonda GetRondaFromDr(SqlCeDataReader dr)
{
TRonda r = new TRonda();
r.rondaId = dr.GetInt32(0);
r.nombre = dr.GetString(1);
r.tag = dr.GetString(2);
r.tagf = dr.GetString(3);
return r;
}
public static TRondaPunto GetRondaPuntoFromDr(SqlCeDataReader dr)
{
TRondaPunto rp = new TRondaPunto();
rp.rondaPuntoId = dr.GetInt32(0);
rp.rondaId = dr.GetInt32(1);
rp.orden = dr.GetInt32(2);
rp.puntoId = dr.GetInt32(3);
return rp;
}
public static TAdministrador GetAdministradorFromDr(SqlCeDataReader dr)
{
TAdministrador adm = new TAdministrador();
adm.administradorId = dr.GetInt32(0);
adm.nombre = dr.GetString(1);
adm.login = dr.GetString(2);
adm.password = dr.GetString(3);
adm.email = dr.GetString(4);
adm.nivel = dr.GetInt32(5);
return adm;
}
private POSSession POSSessionDeserializer(SqlCeDataReader reader)
{
var id = Guid.Parse(reader["id"].ToString());
var username = reader["username"].ToString();
var openDate = Convert.ToDateTime(reader["opendate"].ToString());
var closeDate = Convert.ToDateTime(reader["closedate"].ToString());
var state = (SessionState)Convert.ToInt32(reader["sessionstate"]);
var session = new POSSession(id, username, openDate, closeDate, state);
return session;
}
public static Address GetAddressFromReader(SqlCeDataReader reader)
{
Address address = new Address();
address.AddressID = (long)reader["AddressID"];
address.Street = reader["Street"].ToString();
address.City = reader["City"].ToString();
address.State = reader["State"].ToString();
address.Zip = (int)reader["Zip"];
return address;
}
public static TPunto GetPuntoFromDr(SqlCeDataReader dr)
{
TPunto p = new TPunto();
p.puntoId = dr.GetInt32(0);
p.nombre = dr.GetString(1);
p.edificioId = dr.GetInt32(2);
p.tag = dr.GetString(3);
p.cota = dr.GetString(4);
p.cubiculo = dr.GetString(5);
p.observaciones = dr.GetString(6);
return p;
}
public static TTerminal GetTerminalFromDr(SqlCeDataReader dr)
{
TTerminal t = new TTerminal();
t.terminalId = dr.GetInt32(0);
t.numero = dr.GetString(1);
t.nombre = dr.GetString(2);
if (dr[3] != DBNull.Value)
t.fechaAlta = dr.GetDateTime(3);
if (dr[4] != DBNull.Value)
t.fechaBaja = dr.GetDateTime(4);
return t;
}
private void PopulateList(SqlCeDataReader reader, List<Business.CommApplication> list)
{
while (reader.Read())
{
var cap = new Business.CommApplication();
cap.ID = Convert.ToInt32(reader["ID"]);
cap.Name = reader["Name"].ToString();
cap.Path = reader["Path"].ToString();
cap._recordExists = true;
cap._hasChanged = false;
list.Add(cap);
}
}
public RecordSet(SqlCeDataReader reader)
{
this.reader = reader;
EndOfLines = false;
/*
if (reader.HasRows)
{
EndOfLines = false;
}
else
{
EndOfLines = true;
}*/
}
public static TDescargaLinea GetDescargaLineaFromDr(SqlCeDataReader dr)
{
TDescargaLinea dl = new TDescargaLinea();
dl.descargaLineaId = dr.GetInt32(0);
dl.descargaId = dr.GetInt32(1);
dl.tag = dr.GetString(2);
dl.fechaHora = dr.GetDateTime(3);
dl.tipo = dr.GetString(4);
dl.tipoId = dr.GetInt32(5);
dl.nombre = dr.GetString(6);
dl.incidenciaId = dr.GetInt32(7);
dl.observaciones = dr.GetString(8);
return dl;
}
public static TRonda GetRondaFromDr(SqlCeDataReader dr)
{
TRonda r = new TRonda();
bool primero = true;
while (dr.Read())
{
if (primero)
{
r.rondaId = dr.GetInt32(0);
r.nombre = dr.GetString(1);
r.tag = dr.GetString(2);
r.tagf = dr.GetString(3);
r.RondasPuntos = new List<TRondaPunto>();
primero = false;
}
TRondaPunto rp = new TRondaPunto();
TPunto p = new TPunto();
TEdificio e = new TEdificio();
TGrupo g = new TGrupo();
rp.rondaPuntoId = dr.GetInt32(4);
rp.orden = dr.GetInt32(5);
p.puntoId = dr.GetInt32(6);
p.nombre = dr.GetString(7);
e.edificioId = dr.GetInt32(8);
p.tag = dr.GetString(9);
e.nombre = dr.GetString(10);
g.grupoId = dr.GetInt32(11);
g.nombre = dr.GetString(12);
p.cota = dr.GetString(13);
p.cubiculo = dr.GetString(14);
r.mintime = dr.GetInt32(15);
r.maxtime = dr.GetInt32(16);
p.csnmax = dr.GetInt32(17);
p.csnmargen = dr.GetInt32(18);
p.lastcontrol = dr.GetDateTime(19);
e.Grupo = g;
p.Edificio = e;
rp.Punto = p;
rp.Ronda = r;
r.RondasPuntos.Add(rp);
}
return r;
}
public static TPunto GetPuntoFromDr(SqlCeDataReader dr)
{
TGrupo g = new TGrupo();
TEdificio e = new TEdificio();
TPunto p = new TPunto();
p.puntoId = dr.GetInt32(0);
p.nombre = dr.GetString(1);
e.edificioId = dr.GetInt32(2);
p.tag = dr.GetString(3);
p.cota = dr.GetString(4);
p.cubiculo = dr.GetString(5);
p.observaciones = dr.GetString(6);
e.nombre = dr.GetString(7);
g.grupoId = dr.GetInt32(8);
g.nombre = dr.GetString(9);
p.csnmax = dr.GetInt32(10);
p.csnmargen = dr.GetInt32(11);
p.lastcontrol = dr.GetDateTime(12);
return p;
}
public static Person GetPersonFromReader(SqlCeDataReader reader)
{
if (reader != null)
{
Person person = new Person();
person.PersonID = (long)reader["PersonID"];
person.FirstName = reader["FirstName"].ToString();
person.LastName = reader["LastName"].ToString();
person.SetAddress(AddressDB.GetAddressFromReader(reader));
person.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
Person.PersonTypes personType;
Enum.TryParse<Person.PersonTypes>(reader["PersonType"].ToString(), out personType);
person.PersonType = personType;
person.Email = reader["Email"].ToString();
person.Password = reader["Password"].ToString();
return person;
}
return null;
}
public void FindPreviousJob()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlCeCommand cmd = new SqlCeCommand("select * from job where status = 1", con);
cmd.CommandType = CommandType.Text;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr["jobName"].ToString() == "Background Operation")
{
GlobalFunc.showGetBKDataBtn = true;
BKManager.jobID = Convert.ToInt32(dr["jobID"]);
}
}
dr.Close();
con.Close();
}
public List <Abonos> GetAbonos(int id_cliente)
{
List <Abonos> correo = new List <Abonos>();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\DB\DB_local.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM abonos_clientes WHERE id_cliente=" + id_cliente + ";";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
correo.Add(new Abonos(
int.Parse(reader["id_cliente"].ToString()),
reader["fecha"].ToString(),
float.Parse(reader["abono"].ToString())
));
}
return(correo);
}
List <AnswersModel> CreateAnswers(int questionId)
{
List <AnswersModel> result = new List <AnswersModel>();
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT AnswerId, QuestionId, AnswerText, Rightness FROM Answer WHERE QuestionId=@id"; //AnswerImage,
cmd.Parameters.AddWithValue("@id", questionId);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = Convert.ToInt32(dr["AnswerId"]);
string text = dr["AnswerText"].ToString();
bool rightness = Convert.ToBoolean(dr["Rightness"]);
result.Add(new AnswersModel(id, questionId, text, rightness));
}
}
}
return(result);
}
public static TTipoAnomalia GetTTipoAnomalia(int id, SqlCeConnection conn)
{
TTipoAnomalia ta = null;
string sql = String.Format("SELECT * FROM TipoAnomalia WHERE tipo_anomalia_id={0}", id);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TTipoAnomalia();
ta.TipoAnomaliaId = dr.GetInt32(0);
ta.Nombre = dr.GetString(1);
ta.Abm = dr.GetByte(2);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
static private List <SubBalanceDetails> querySubBalanceDetails(string query)
{
List <SubBalanceDetails> queryResults = new List <SubBalanceDetails>();
SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.FFDBConnectionString);
SqlCeCommand command = new SqlCeCommand(query, connection);
connection.Open();
SqlCeDataReader reader = command.ExecuteReader();
// Iterate through the results
while (reader.Read())
{
queryResults.Add(new SubBalanceDetails(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2)));
}
// Always call Close the reader and connection when done reading
reader.Close();
command.Dispose();
connection.Close();
return(queryResults);
}
private void Form1_Shown(object sender, EventArgs e)
{
try
{
cn.Open();
string SqlCeQuery = "SELECT * FROM tblUsers";
SqlCeCommand cm = new SqlCeCommand(SqlCeQuery, cn);
SqlCeDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
lbuserName.Items.Add(dr.GetValue(0).ToString());
lbPass.Items.Add(dr.GetValue(2).ToString());
lbEmail.Items.Add(dr.GetValue(3).ToString());
lbId.Items.Add(dr.GetValue(4).ToString());
}
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
}
public static bool CheckUnique(string jmbg)
{
bool flag = false;
try
{
SqlCeCommand command = new SqlCeCommand(@"SELECT Jmbg FROM Students
WHERE @jmbg = Jmbg", Connection);
command.Parameters.AddWithValue("@jmbg", jmbg);
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read() && reader["Jmbg"].ToString() == jmbg)
{
flag = true;
MessageBox.Show("Jmbg " + "'" + jmbg + "'" + " već postoji !");
}
}
catch (Exception ex)
{
flag = false;
MessageBox.Show(ex.Message);
}
return(flag);
}
public static TUsuario Login(string login, string password, SqlCeConnection conn)
{
TUsuario usuario = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Usuarios WHERE login='******'", login);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
usuario = GetTUsuario(dr.GetInt32(0), conn);
}
if (usuario.Password != GetHashCode(password))
{
usuario = null;
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(usuario);
}
/// <summary>
/// returns the id of the previous member
/// </summary>
/// <param name="currentMemberID"></param>
/// <returns></returns>
public static int GetPrevMember(int currentMemberID)
{
int prevId = 0;
string query = "SELECT MAX(Id) FROM Members WHERE Id<@currentId";
using (SqlCeConnection con = DB.GetSqlCeConnection())
{
SqlCeCommand cmd = new SqlCeCommand(query, con);
cmd.Parameters.AddWithValue("@currentId", currentMemberID);
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
prevId = reader.GetInt32(0);
}
}
return(prevId);
}
}
public ProveedoresDeudas SeleccionarDeudaProveedor(int id)
{
ProveedoresDeudas prov = new ProveedoresDeudas();
SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\DB\DB_local.sdf");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM proveedor_deuda WHERE id=" + id + ";";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
prov = new ProveedoresDeudas(
int.Parse(reader["id"].ToString()),
reader["nombre"].ToString(),
float.Parse(reader["deuda"].ToString())
);
}
return(prov);
}
void infos()
{
string queryString = "SELECT * FROM patient ORDER BY id DESC";
SqlCeCommand command = new SqlCeCommand(queryString, conn);
try
{
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read())
{
label12.Text += reader["nom"].ToString();
label13.Text += reader["prenom"].ToString();
label14.Text += reader["dateNaissance"].ToString();
label15.Text += reader["domicile"].ToString();
pictureBox2.Image = Image.FromFile("Z:\\Pictures\\" + reader["matricule"] + ".jpg"); // remplacer pa le matricule de l'occurence
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public ForgotPassword(string username)
{
InitializeComponent();
user = username;
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT securityQuestion, answer FROM Accounts WHERE username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", username);
SqlCeDataReader dr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
if (dr.Read())
{
int securityQuestionIndex = dr.GetOrdinal("securityQuestion");
question = Convert.ToString(dr.GetValue(securityQuestionIndex));
int answerIndex = dr.GetOrdinal("answer");
answer = Convert.ToString(dr.GetValue(answerIndex));
securityQuestion.Content = question;
}
}
}
public static TPrioridad GetTPrioridad(string nombre, SqlCeConnection conn)
{
TPrioridad tp = null;
string sql = String.Format("SELECT * FROM Prioridad WHERE nombre='{0}'", nombre);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tp = new TPrioridad();
tp.PrioridadId = dr.GetInt32(0);
tp.Nombre = dr.GetString(1);
tp.Abm = dr.GetByte(2);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(tp);
}
public static TGrupoTrabajo GetGrupoTrabajo(int id, SqlCeConnection conn)
{
TGrupoTrabajo grupot = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Grupo_Trabajo WHERE grupo_trabajo_id = {0}", id);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
grupot = new TGrupoTrabajo()
{
Grupo_trabajo_id = dr.GetInt32(0),
Nombre = dr.GetString(1)
};
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(grupot);
}
private DataTable ReaderToDT(SqlCeDataReader dataReader)
{
DataTable dt = new DataTable();
foreach (DataRow dr in dataReader.GetSchemaTable().Rows)
{
dt.Columns.Add(dr["ColumnName"] as string, (Type)(dr["DataType"]));
}
while (dataReader.Read())
{
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc] = dataReader[dc.ColumnName];
}
dt.Rows.Add(dr);
}
return(dt);
}
public string[][] CzytajNadajniki(string nazwaFirmy, int numerMiesiaca, int numerRoku)
{
string zapytanie = "SELECT modele.nazwa, nadajniki.ilosc, modele.cena, faktury.wartosc FROM faktury LEFT OUTER JOIN nadajniki " +
"ON nadajniki.id_faktury = faktury.id LEFT OUTER JOIN firmy ON firmy.id = nadajniki.id_firmy LEFT OUTER JOIN " +
"modele ON modele.id = nadajniki.id_modelu WHERE (firmy.nazwa = '" + nazwaFirmy + "') AND " +
"(faktury.data_zaplaty BETWEEN '" + numerRoku + "-" + numerMiesiaca + "-01' AND '" + numerRoku + "-"
+ numerMiesiaca + "-" + DateTime.DaysInMonth(numerRoku, numerMiesiaca) + "')";
List <string[]> tablica = new List <string[]>();
SqlCeDataReader rdr = Zapytanie(zapytanie);
while (rdr.Read())
{
string[] s = new string[4];
s[0] = rdr[0].ToString(); // nazwa modelu
s[1] = rdr[1].ToString(); // ilosc
s[2] = rdr[2].ToString(); // cena modelu
s[3] = rdr[3].ToString(); // stawka
tablica.Add(s);
}
return(tablica.ToArray());
}
static void Main(string[] args)
{
SqlCeConnection cn = new SqlCeConnection(@"Data Source = C:\Users\Виктор\Documents\Db-Test.db");
string sqlExpression = "SELECT * FROM Account WHERE CreateON > 2015";
cn.Open();
SqlCeCommand command = new SqlCeCommand(sqlExpression, cn);
SqlCeDataReader reader = command.ExecuteReader();
// выводим названия столбцов
Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));
while (reader.Read()) // построчно считываем данные
{
object name = reader.GetValue(0);
object create = reader.GetValue(1);
object id = reader.GetValue(2);
Console.WriteLine("{0} \t{1} \t{2}", name, create, id);
}
reader.Close();
Console.Read();
}
//#############################################################################################################################
//END OF HELP PAGE TAB SECTION
//#############################################################################################################################
//#############################################################################################################################
//START OF ORDER PAGE TAB SECTION
//#############################################################################################################################
//Method to show all the orders made
private void ShowOrder()
{
SqlCeDataReader readorder = null;
listView1.Items.Clear();
readorder = or.RetrievewithCompany();
while (readorder.Read())
{
DateTime estiorder = DateTime.ParseExact(Convert.ToString(readorder["Date"]), "dd/MM/yyyy", null);
DateTime date = estiorder.AddDays(2);
string Date = date.Date.ToString("dd/MM/yyyy");
ListViewItem item = new ListViewItem(Convert.ToString(readorder["OrderID"]));
item.SubItems.Add(Convert.ToString(readorder["CompanyName"]));
item.SubItems.Add(Convert.ToString(readorder["EmployeeID"]));
item.SubItems.Add(Convert.ToString(readorder["Date"]));
item.SubItems.Add(Convert.ToString(readorder["PaymentStatus"]));
item.SubItems.Add(Convert.ToString(readorder["OrderStatus"]));
item.SubItems.Add(Date);
listView1.Items.Add(item);
}
}
public static TEstado GetTEstado(string nombre, SqlCeConnection conn)
{
TEstado Estado = null;
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = String.Format("SELECT * FROM Estado WHERE nombre = '{0}'", nombre);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Estado = new TEstado()
{
EstadoId = dr.GetInt32(0),
Nombre = dr.GetString(1),
};
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(Estado);
}
internal static bool IsEmailFree(string email)
{
if (dbCon.State == ConnectionState.Closed)
{
dbCon.Open();
}
string sqlCommand = String.Format(@"SELECT Email FROM Users WHERE Email = '{0}'", email);
SqlCeCommand command = new SqlCeCommand(sqlCommand, dbCon);
SqlCeDataReader reader = command.ExecuteReader();
using (reader)
{
bool isFree = true;
if (reader.Read())
{
isFree = false;
}
dbCon.Close();
return(isFree);
}
}
public List <KonacanPlasman> findSpraveTak1(string ime, string prezime)
{
SqlCeCommand cmd = new SqlCeCommand(findSpraveTak1SQL);
cmd.Parameters.Add("@ime", SqlDbType.NVarChar).Value = ime;
cmd.Parameters.Add("@prezime", SqlDbType.NVarChar).Value = prezime;
SqlCeDataReader rdr = SqlCeUtilities.executeReader(cmd, Strings.DATABASE_ACCESS_ERROR_MSG, ConnectionString);
List <KonacanPlasman> result = new List <KonacanPlasman>();
while (rdr.Read())
{
// NOTE: Vracam sve rezultate koji postoje (ne proveravam postojiTak3 i odvojenoTak3).
KonacanPlasman kp = new KonacanPlasman();
loadCommonData(kp, rdr);
loadSprava(kp, rdr);
kp.TipTakmicenja = (TipTakmicenja)rdr["tip_takmicenja"];
result.Add(kp);
}
rdr.Close();
return(result);
}
List <QuestionsModel> CreateQuestions(int testId)
{
List <QuestionsModel> result = new List <QuestionsModel>();
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT TOP (@taskCount) QuestionId, QuestText FROM Question WHERE TestId=@id ORDER BY newid();";//, QuestImage
cmd.Parameters.AddWithValue("@id", testId);
cmd.Parameters.AddWithValue("@taskCount", TaskCount);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = Convert.ToInt32(dr["QuestionId"]);
string text = dr["QuestText"].ToString();
result.Add(new QuestionsModel(id, testId, text));
}
}
}
return(result);
}
public virtual T GetItem(int id)
{
string statement = SqlStatememtGenerator.Select_ReadAllColumnsById(tableName, idColumnName);
SqlCeParameter[] parameters = new SqlCeParameter[1];
parameters[0] = MakeSqlParameter(idColumnName, id);
SqlCeDataReader reader = executer.ExecuteSelectStatement(statement, parameters);
if (reader == null)
return null;
T result = null;
try
{
reader.Read();
result = ReadRecord(reader);
}
catch { }
reader.Close();
return result;
}
private void subCatagoryInitiation_Load(object sender, EventArgs e)
{
try {
SqlCeDataReader reader = null;
dboperation operation = new dboperation();
SqlCeConnection conn = null;
conn = operation.dbConnection(Settings.Default.DatabasePath);
string query = "SELECT Catagory FROM catagory";
SqlCeCommand cmd = new SqlCeCommand(query, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
// Add Catagory List
selectcatcombo.Items.Add(reader.GetString(0));
}
load_subcat_gridview(conn);
operation.closeDBConnection(conn);
}
catch (Exception ex)
{
}
}
//вибір всіх стандартних лекцій
List <LectionsModel> CreateLectionsList()
{
//lections.Clear();
List <LectionsModel> list = new List <LectionsModel>();
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
connection.Open();
using (SqlCeCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT LectionId, Name, OwnerId, LectionType FROM Lection";
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int id = Convert.ToInt32(dr["LectionId"]);
string name = dr["Name"].ToString();
int ownerId = Convert.ToInt32(dr["OwnerId"]);
string type = dr["LectionType"].ToString();
list.Add(new LectionsModel(id, name, ownerId, type, "", new byte[0]));
}
}
}
return(list);
}
public String[] lodeRoom()
{
try
{
String[] strroom = new String[1000];
cn.Open();
//SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT Room_No FROM Room", cn);
SqlCeCommand oSqlCommand = new SqlCeCommand("SELECT Room_No FROM Room", cn);
SqlCeDataReader oSqlDataReader = oSqlCommand.ExecuteReader();
countRoom = 0;
while (oSqlDataReader.Read())
{
strroom[countRoom] = oSqlDataReader[0].ToString();
countRoom++;
}
cn.Close();
return(strroom);
}
catch
{
throw;
}
}
public String[] lodeDepartment()
{
try
{
String[] strDept = new String[1000];
cn.Open();
//SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT Room_No FROM Room", cn);
SqlCeCommand oSqlCommand = new SqlCeCommand("SELECT distinct Dept_Name FROM Students", cn);
SqlCeDataReader oSqlDataReader = oSqlCommand.ExecuteReader();
countDept = 0;
while (oSqlDataReader.Read())
{
strDept[countDept] = oSqlDataReader[0].ToString();
countDept++;
}
cn.Close();
return(strDept);
}
catch
{
throw;
}
}
public String[] lodeStudentHomeTown()
{
try
{
String[] strhome = new String[1000];
cn.Open();
//SqlCeDataAdapter a = new SqlCeDataAdapter("SELECT Room_No FROM Room", cn);
SqlCeCommand oSqlCommand = new SqlCeCommand("SELECT distinct Home_Town FROM Students", cn);
SqlCeDataReader oSqlDataReader = oSqlCommand.ExecuteReader();
countHomeTown = 0;
while (oSqlDataReader.Read())
{
strhome[countHomeTown] = oSqlDataReader[0].ToString();
countHomeTown++;
}
cn.Close();
return(strhome);
}
catch
{
throw;
}
}
private void DepoDoldur()
{
CeConn.Open();
string sql = "SELECT DISTINCT Depo FROM DEPO_StokDurum";
SqlCeCommand cmd = new SqlCeCommand(sql, CeConn);
try
{
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr["Depo"].ToString());
}
}
finally
{
CeConn.Close();
}
comboBox1.SelectedIndex = 1;
}
public Correo ObtenerCorreoPorDefault()
{
Correo correo = null;
SqlCeConnection conn = new SqlCeConnection(@"Data Source=|DataDirectory|\DB\DB_local.sdf");
conn.Open();
//commands represent a query or a stored procedure
SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM correos WHERE predeterminado=1";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
correo = new Correo(
int.Parse(reader["id_correo"].ToString()),
reader["correo"].ToString(),
int.Parse(reader["predeterminado"].ToString()));
}
conn.Close();
return(correo);
}
// getById
public IList<DocCheckProductDetail> getDocCheckProductDetailByDCodeAndPName(object _dCode,string _pName)
{
IList<DocCheckProductDetail> docCheckProductDetails = new List<DocCheckProductDetail>();
DocCheckProductDetail docCheckProductDetail = null;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" select d.ID,d.DCode,d.PCode,d.NumProduct,d.CreateDate ");
sb.Append(" from DocCheckProductDetails d,Products p");
sb.Append(" where d.PCode = p.BarCode");
sb.Append(" and d.DCode='" + _dCode + "'");
sb.Append(" and p.Name like '%" + _pName + "%'");
string sql = "";
sql = sb.ToString();
com = new SqlCeCommand();
com.Connection = Conn;
com.CommandText = sql;
dr = com.ExecuteReader();
{
DataTable dt = new DataTable();
dt.Load(dr);
foreach (DataRow ds in dt.Rows)
{
docCheckProductDetail = new DocCheckProductDetail();
docCheckProductDetail.ID = Convert.ToInt32(ds[0].ToString());
DocCheckProduct docCheckProduct = getDocCheckProductByCode(Convert.ToString(ds[1].ToString()));
docCheckProductDetail.DocCheckProduct = docCheckProduct;
Product product = getByBarCode(Convert.ToString(ds[2].ToString()));
docCheckProductDetail.Product = product;
docCheckProductDetail.NumProduct = Convert.ToInt32(ds[3].ToString());
docCheckProductDetail.CreateDate = Convert.ToDateTime(ds[4].ToString());
docCheckProductDetails.Add(docCheckProductDetail);
}
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
return docCheckProductDetails;
}
public void populateListBox()
{
mySqlConnection = new SqlCeConnection(@"Data Source=C:\temp\Mydatabase.sdf ");
//writes a select sql statement to access everything
String selcmd = "SELECT * FROM tbl_bug ORDER BY bug_id";
SqlCeCommand mySqlCommand = new SqlCeCommand(selcmd, mySqlConnection);
try
{
//opens connection
mySqlConnection.Open();
mySqlDataReader = mySqlCommand.ExecuteReader();
}
catch (SqlCeException ex)
{
MessageBox.Show("Failure" + ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private object DataReaderToObject(SqlCeDataReader reader, EntityType entity)
{
object entityObject = null;
switch (entity)
{
case EntityType.Processor:
entityObject = new EntProcessor();
break;
case EntityType.OS:
entityObject = new EntOS();
break;
case EntityType.Bios:
entityObject = new EntBios();
break;
case EntityType.MotherBoard:
entityObject = new EntMotherBoard();
break;
case EntityType.Disk:
entityObject = new EntDisk();
break;
case EntityType.Memory:
entityObject = new EntMemory();
break;
case EntityType.LogicalDrive:
entityObject = new EntLogicalDrive();
break;
case EntityType.CDRom:
entityObject = new EntCDRom();
break;
case EntityType.Video:
entityObject = new EntVideo();
break;
case EntityType.Multimedia:
entityObject = new EntMultimedia();
break;
case EntityType.Monitor:
entityObject = new EntMonitor();
break;
case EntityType.Share:
entityObject = new EntShare();
break;
case EntityType.StartUp:
entityObject = new EntStartUp();
break;
case EntityType.Hotfix:
entityObject = new EntHotfixes();
break;
case EntityType.Processes:
entityObject = new EntProcesses();
break;
case EntityType.Softwares:
entityObject = new EntSoftwares();
break;
case EntityType.Services:
entityObject = new EntServices();
break;
case EntityType.IPRoutes:
entityObject = new EntIPRoutes();
break;
case EntityType.EnvironmentVar:
entityObject = new EntEnvironmentVars();
break;
case EntityType.Computer:
entityObject = new EntComputer();
break;
case EntityType.Printer:
entityObject = new EntPrinter();
break;
case EntityType.UserGroup:
entityObject = new EntUserGroups();
break;
case EntityType.NetworkAdapter:
entityObject = new EntNetworkAdapter();
break;
}
Type t = entityObject.GetType();
PropertyInfo[] pi = t.GetProperties();
foreach (PropertyInfo prop in pi)
{
if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
continue;
switch (prop.PropertyType.Name)
{
case "String":
string strValue = reader.GetString(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, strValue, null);
break;
case "Int32":
int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, intValue, null);
break;
case "Double":
double dValue = reader.GetDouble(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, dValue, null);
break;
case "DateTime":
DateTime dtValue = DateTime.MinValue;
if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value)
{
dtValue = DateTime.MinValue;
}
else
{
dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name));
}
prop.SetValue(entityObject, dtValue, null);
break;
case "Boolean":
bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, bValue, null);
break;
}
}
return entityObject;
}
public DataTable getDocCheckProductDetailByDCodeAndPNameByDataTable(object _dCode, string _pName)
{
DataTable table = new DataTable();
DataColumn IdCol = new DataColumn();
IdCol.ColumnName = "Index";
IdCol.DataType = Type.GetType("System.Int32");
IdCol.ReadOnly = true;
IdCol.AllowDBNull = false;
IdCol.Unique = true;
IdCol.AutoIncrement = true;
IdCol.AutoIncrementSeed = 1;
IdCol.AutoIncrementStep = 1;
table.Columns.Add(IdCol);
DataColumn NameCol = new DataColumn();
NameCol.ColumnName = "Name";
NameCol.DataType = Type.GetType("System.String");
table.Columns.Add(NameCol);
DataColumn NumCol = new DataColumn();
NumCol.ColumnName = "Num";
NumCol.DataType = Type.GetType("System.String");
table.Columns.Add(NumCol);
DataColumn DcodeCol = new DataColumn();
DcodeCol.ColumnName = "DCode";
DcodeCol.DataType = Type.GetType("System.String");
table.Columns.Add(DcodeCol);
DataColumn PcodeCol = new DataColumn();
PcodeCol.ColumnName = "PCode";
PcodeCol.DataType = Type.GetType("System.String");
table.Columns.Add(PcodeCol);
DataColumn UnitCol = new DataColumn();
UnitCol.ColumnName = "NnitCode";
UnitCol.DataType = Type.GetType("System.String");
table.Columns.Add(UnitCol);
DataColumn BlankCol = new DataColumn();
BlankCol.ColumnName = "Blank";
BlankCol.DataType = Type.GetType("System.String");
table.Columns.Add(BlankCol);
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" select d.ID,d.DCode,d.PCode,d.NumProduct,d.CreateDate ");
sb.Append(" from DocCheckProductDetails d,Products p");
sb.Append(" where d.PCode = p.BarCode");
sb.Append(" and d.DCode='" + _dCode + "'");
sb.Append(" and p.Name like '%" + _pName + "%'");
string sql = "";
sql = sb.ToString();
com = new SqlCeCommand();
com.Connection = Conn;
com.CommandText = sql;
dr = com.ExecuteReader();
{
table.Load(dr);
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
/*
table.Rows.Add(i,
p.Product.Name,
p.NumProduct,
p.DocCheckProduct.Code,
p.Product.BarCode,
p.Product.Unit,
"ź");*/
return table;
}
public DataTable getDocCheckProductDetailByDCodeDataTable(object _dCode)
{
DataTable table = new DataTable();
/*
DataColumn IdCol = new DataColumn();
IdCol.ColumnName = "Index";
IdCol.DataType = Type.GetType("System.Int32");
IdCol.ReadOnly = true;
IdCol.AllowDBNull = false;
IdCol.Unique = true;
IdCol.AutoIncrement = true;
IdCol.AutoIncrementSeed = 1;
IdCol.AutoIncrementStep = 1;
table.Columns.Add(IdCol);*/
DataColumn NameCol = new DataColumn();
NameCol.ColumnName = "PName";
NameCol.DataType = Type.GetType("System.String");
table.Columns.Add(NameCol);
DataColumn NumCol = new DataColumn();
NumCol.ColumnName = "Num";
NumCol.DataType = Type.GetType("System.String");
table.Columns.Add(NumCol);
DataColumn DcodeCol = new DataColumn();
DcodeCol.ColumnName = "DCode";
DcodeCol.DataType = Type.GetType("System.String");
table.Columns.Add(DcodeCol);
DataColumn PcodeCol = new DataColumn();
PcodeCol.ColumnName = "PCode";
PcodeCol.DataType = Type.GetType("System.String");
table.Columns.Add(PcodeCol);
DataColumn UnitCol = new DataColumn();
UnitCol.ColumnName = "NnitCode";
UnitCol.DataType = Type.GetType("System.String");
table.Columns.Add(UnitCol);
DataColumn BlankCol = new DataColumn();
BlankCol.ColumnName = "Blank";
BlankCol.DataType = Type.GetType("System.String");
table.Columns.Add(BlankCol);
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" select PName,DCode,PCode,NumProduct,CreateDate ");
sb.Append(" from DocCheckProductDetails ");
sb.Append(" where DCode ='1'");
string sql = "";
sql = sb.ToString();
com = new SqlCeCommand();
com.Connection = Conn;
com.CommandText = sql;
dr = com.ExecuteReader();
{
table.Load(dr);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
return table;
}
public IList<Product> searchByProductName(string _pname)
{
IList<Product> products = new List<Product>();
Product product = null;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append(" select ID,Name,Code,BarCode,Unit from Products p ");
sb.Append(" where p.Name like '%" + _pname + "_%'");
string sql = "";
sql = sb.ToString();
com = new SqlCeCommand();
com.Connection = Conn;
com.CommandText = sql;
dr = com.ExecuteReader();
{
DataTable dt = new DataTable();
dt.Load(dr);
int i = 1;
foreach (DataRow ds in dt.Rows)
{
product = new Product();
product.Index = i;
product.ID = Convert.ToInt32(ds[0].ToString());
product.Name = Convert.ToString(ds[1].ToString());
product.Code = Convert.ToString(ds[2].ToString());
product.BarCode = Convert.ToString(ds[3].ToString());
product.Unit = Convert.ToString(ds[4].ToString());
products.Add(product);
i++;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
return products;
}