public SnippetData Get(Guid id)
{
using (var connection = GetOpenConnection()) {
SqlCeCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Snippets WHERE Id = ?";
cmd.Parameters.Add(new SqlCeParameter("Id", SqlDbType.NVarChar));
cmd.Parameters["Id"].Value = id.ToString();
SqlCeDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
long length = reader.GetBytes(1, 0, null, 0, 0);
byte[] assemblyBytes = new byte[length];
reader.GetBytes(1, 0, assemblyBytes, 0, assemblyBytes.Length);
return(new SnippetData()
{
Id = id,
AssemblyBytes = assemblyBytes,
SnippetSource = reader.GetString(2),
UsingDirectives = reader.GetString(3),
SnippetHealth = (SnippetHealth)reader.GetInt32(4),
CompilerVersion = reader.IsDBNull(5) ? String.Empty : reader.GetString(5),
SnippetClasses = reader.IsDBNull(6) ? String.Empty : reader.GetString(6)
});
}
}
return(null);
}
public IEnumerable <SnippetData> All()
{
using (var connection = GetOpenConnection()) {
SqlCeCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Snippets";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
long length = reader.GetBytes(1, 0, null, 0, 0);
byte[] assemblyBytes = new byte[length];
reader.GetBytes(1, 0, assemblyBytes, 0, assemblyBytes.Length);
yield return(new SnippetData()
{
Id = Guid.Parse(reader.GetString(0)),
AssemblyBytes = assemblyBytes,
SnippetSource = reader.GetString(2),
UsingDirectives = reader.GetString(3),
SnippetHealth = (SnippetHealth)reader.GetInt32(4),
CompilerVersion = reader.IsDBNull(5) ? String.Empty : reader.GetString(5),
SnippetClasses = reader.IsDBNull(6) ? String.Empty : reader.GetString(6)
});
}
}
}
public List <ReportData> GetAllReportData(int reportId)
{
List <ReportData> list = new List <ReportData>();
SqlCeConnection _Conn = new SqlCeConnection(conn);
_Conn.Open();
SqlCeCommand cmd = new SqlCeCommand("Select * from ReportData Where PatientReportId=" + reportId, _Conn);
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ReportData obj = new ReportData()
{
Id = rdr.GetInt32(0),
PatientReportId = rdr.GetInt32(1),
Mode = rdr.IsDBNull(2) == true ? "" : rdr.GetString(2),
Img = rdr.GetString(3),
Eye = rdr.IsDBNull(4) == true ? "" : rdr.GetString(4),
Prediction = rdr.IsDBNull(5) == true ? "" : rdr.GetString(5),
Size = rdr.IsDBNull(6) == true ? "" : rdr.GetString(6),
PatientId = rdr.GetInt32(7)
};
list.Add(obj);
}
_Conn.Close();
return(list);
}
public List <ReportData> GetAnteriorReportData(int reportId)
{
string sql = string.Format("select * from ReportData where PatientReportId ={0} and Mode='ANTERIOR_MODE' order by Id desc;", reportId);
List <ReportData> list = new List <ReportData>();
SqlCeConnection _Conn = new SqlCeConnection(conn);
_Conn.Open();
SqlCeCommand cmd = new SqlCeCommand(sql, _Conn);
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ReportData obj = new ReportData()
{
Id = rdr.GetInt32(0),
PatientReportId = rdr.GetInt32(1),
Mode = rdr.IsDBNull(2) == true ? "" : rdr.GetString(2),
Img = rdr.GetString(3),
Eye = rdr.IsDBNull(4) == true ? "" : rdr.GetString(4),
Prediction = rdr.IsDBNull(5) == true ? "" : rdr.GetString(5),
Size = rdr.IsDBNull(6) == true ? "" : rdr.GetString(6)
};
list.Add(obj);
}
_Conn.Close();
return(list);
}
//maak een leeg rooster
private static Klas[][] MaakRooster(int aantalBlokken, int aantalLessen)
{
Klas[][] klassen = new Klas[aantalBlokken][];
for (byte i = 0; i < aantalBlokken; i++)
{
klassen[i] = new Klas[aantalLessen];
}
using (SqlCeConnection conn = new SqlCeConnection(Global.connectionString))
{
conn.Open();
//krijg alle informatie van elk blok/les
using (SqlCeCommand cmd = new SqlCeCommand("SELECT Voorlichtingen.Vakid, Bloktijden.blokId, Vakken.Vakid, Lokalen.maxPersonen FROM Bloktijden, Lokalen, Vakken, Voorlichtingen WHERE Vakken.Vaknaam = Voorlichtingen.Vak AND Vakken.Niveau = Voorlichtingen.Niveau AND Bloktijden.tijd = Voorlichtingen.rondeId AND Voorlichtingen.Lokaal = Lokalen.lokaal;", conn))
{
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!(reader.IsDBNull(0) || reader.IsDBNull(1) || reader.IsDBNull(2) || reader.IsDBNull(3)))
{
int ruimte = reader.GetInt32(3);
int blok = reader.GetInt32(1);
int les = reader.GetInt32(2);
int lesid = reader.GetInt32(0);
klassen[blok][les] = new Klas(ruimte, blok, les, lesid);
}
}
}
}
return(klassen);
}
public UserEntity Validate(string userNm, string pwd)
{
UserEntity model = new UserEntity();
string sql = string.Format("SELECT * FROM [Users] where usernm='{0}' and pwd='{1}';", userNm, pwd);
SqlCeConnection _Conn = new SqlCeConnection(conn);
_Conn.Open();
SqlCeCommand cmd = new SqlCeCommand(sql, _Conn);
SqlCeDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
model.Id = rdr.GetInt32(0);
model.UserNm = rdr.GetString(1);
model.Pwd = rdr.GetString(2);
model.IsConfigured = rdr.GetBoolean(3);
if (!rdr.IsDBNull(4))
{
model.InstallID = rdr.GetGuid(4);
}
model.Location = rdr.IsDBNull(5) == true ? "" : rdr.GetString(5);
model.PinCode = rdr.IsDBNull(6) == true ? "" : rdr.GetString(6);
}
_Conn.Close();
return(model);
}
public static User CreateUserFromId(int id)
{
User user = null;
string command = "select id, login, firstname, lastname, isadmin from users where id=@id";
using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString))
{
SqlCeCommand com = new SqlCeCommand(command, con);
com.Parameters.Add("id", id);
try
{
con.Open();
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
user = new User(
(int)reader.GetDecimal(0), reader.GetString(1)
, reader.IsDBNull(2) ? "" : reader.GetString(2)
, reader.IsDBNull(3) ? "" : reader.GetString(3)
, reader.IsDBNull(4) ? false : reader.GetBoolean(4));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(user);
}
public static Funcionario PegarFuncionario(int id)
{
string sql = "SELECT * FROM [Funcionario] WHERE Id = @id";
SqlCeCommand comando = new SqlCeCommand(sql, con);
comando.Parameters.Add("@id", id);
con.Open();
SqlCeDataReader resposta = comando.ExecuteReader();
Funcionario funcionario = new Funcionario();
while (resposta.Read())
{
funcionario.Id = resposta.GetInt32(0);
funcionario.Nome = resposta.GetString(1);
funcionario.Email = resposta.GetString(2);
funcionario.Salario = resposta.GetDecimal(3);
funcionario.Sexo = resposta.GetString(4);
funcionario.TipoContrato = resposta.GetString(5);
funcionario.DataCadastro = resposta.GetDateTime(6);
if (resposta.IsDBNull(7))
{
funcionario.DataAtualizacao = null;
}
else if (!resposta.IsDBNull(7)) // pog
{
funcionario.DataAtualizacao = resposta.GetDateTime(7);
}
}
con.Close();
return(funcionario);
}
private static void AddToListColumns(ref List <Column> list, SqlCeDataReader dr)
{
if (!dr.GetString(0).StartsWith("__sys"))
{
list.Add(new Column
{
ColumnName = dr.GetString(0)
, IsNullable = (YesNoOption)Enum.Parse(typeof(YesNoOption), dr.GetString(1))
, DataType = dr.GetString(2)
, CharacterMaxLength = (dr.IsDBNull(3) ? 0 : dr.GetInt32(3))
, NumericPrecision = (dr.IsDBNull(4) ? 0 : Convert.ToInt32(dr[4], CultureInfo.InvariantCulture))
#if V31
#else
, AutoIncrementBy = (dr.IsDBNull(5) ? 0 : Convert.ToInt64(dr[5], CultureInfo.InvariantCulture))
, AutoIncrementSeed = (dr.IsDBNull(6) ? 0 : Convert.ToInt64(dr[6], CultureInfo.InvariantCulture))
, AutoIncrementNext = (dr.IsDBNull(12) ? 0 : Convert.ToInt64(dr[12], CultureInfo.InvariantCulture))
#endif
, ColumnHasDefault = (dr.IsDBNull(7) ? false : dr.GetBoolean(7))
, ColumnDefault = (dr.IsDBNull(8) ? string.Empty : dr.GetString(8).Trim())
, RowGuidCol = (dr.IsDBNull(9) ? false : dr.GetInt32(9) == 378 || dr.GetInt32(9) == 282)
, NumericScale = (dr.IsDBNull(10) ? 0 : Convert.ToInt32(dr[10], CultureInfo.InvariantCulture))
, TableName = dr.GetString(11)
, Ordinal = dr.GetInt32(13)
});
}
}
private static void InstellingenInladen()
{
//database connectie
using (SqlCeConnection conn = new SqlCeConnection(Global.connectionString))
{
//verbind met de database
conn.Open();
//krijg het totaal aantal lessen
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) FROM(SELECT DISTINCT Vak, niveau FROM Voorlichtingen) x;", conn))
{
//voer de sql command uit
SqlCeDataReader reader = cmd.ExecuteReader();
//loop door elke row
while (reader.Read())
{
//als de waarde niet gelijk is aan null
if (!(reader.IsDBNull(0)))
{
Global.GlobalAL = reader.GetInt32(0);
}
}
}
//krijg het totaal aantal blokken
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) FROM(SELECT DISTINCT rondeId FROM Voorlichtingen) x;", conn))
{
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!(reader.IsDBNull(0)))
{
if (!(reader.IsDBNull(0)))
{
Global.GlobalAB = reader.GetInt32(0);
}
}
}
}
//krijg het totaal aantal dagen
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(*) FROM(SELECT DISTINCT datum FROM Voorlichtingen) x;", conn))
{
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (!(reader.IsDBNull(0)))
{
if (!(reader.IsDBNull(0)))
{
Global.GlobalAD = reader.GetInt32(0);
}
}
}
}
}
}
public static List <User> GetUsers(int user)
{
List <User> users = new List <User>();
string command = "select id, lastname, firstname from users where showinlist=1";
if (user != 0)
{
command += " and id=@id";
}
command += " order by lastname";
using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString))
{
SqlCeCommand com = new SqlCeCommand(command, con);
if (user != 0)
{
com.Parameters.AddWithValue("id", user);//com.Parameters.Add("id", user);
}
try
{
con.Open();
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
string lName = "";
string fName = "";
if (!reader.IsDBNull(1))
{
lName = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
fName += reader.GetString(2);
}
users.Add(new User((int)reader.GetDecimal(0), "", fName, lName, false));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(users);
}
public static User Create(string login, string password, bool passwordRequired)
{
User user = null;
string command = "select id, login, firstname, lastname, isadmin from users where login=@login";
if (passwordRequired)
{
command += " and coalesce(password, '', '')=@password";
}
using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString))
{
SqlCeCommand com = new SqlCeCommand(command, con);
com.Parameters.AddWithValue("login", login);
//com.Parameters.Add("login", login);
if (passwordRequired)
{
com.Parameters.AddWithValue("password", password);
//com.Parameters.Add("password", password);
}
try
{
con.Open();
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
user = new User(
(int)reader.GetDecimal(0), reader.GetString(1)
, reader.IsDBNull(2) ? "" : reader.GetString(2)
, reader.IsDBNull(3) ? "" : reader.GetString(3)
, reader.IsDBNull(4) ? false : reader.GetBoolean(4));
}
}
catch (System.Data.SqlServerCe.SqlCeException se)
{
MessageBox.Show("Perhaps you need to update the database. Use the UPDATE parameter. Bye!");
Environment.Exit(1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(user);
}
public IEnumerable <Employee> GetEmployees()
{
string selectCommand = @" SELECT id_gamma, ename, case when barcode is not null then barcode else '' END barcode FROM Employee order by ename";
using (SqlCeConnection connect = new SqlCeConnection(Datasource))
{
connect.Open();
using (SqlCeCommand command = new SqlCeCommand(selectCommand, connect))
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (reader != null)
{
try
{
while (reader.Read())
{
yield return(new Employee()
{
GammaID = (int)reader.GetInt32(reader.GetOrdinal("id_gamma")),
Barcode = (reader.IsDBNull(reader.GetOrdinal("barcode")))?"" : reader.GetString(reader.GetOrdinal("barcode")),
Name = reader.GetString(reader.GetOrdinal("ename"))
});
}
}
finally
{
reader.Close();
}
}
}
}
}
}
public static bool MemberHasPrevious(int currentMemberID)
{
bool hasprev = false;
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))
{
hasprev = true;
}
else
{
hasprev = false;
}
}
return(hasprev);
}
}
private static BSP_Ticket DataReaderAObjeto(SqlCeDataReader rdrLector)
{
BSP_Ticket oBSP_Ticket = new BSP_Ticket();
oBSP_Ticket.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID"));
oBSP_Ticket.Billete = rdrLector.GetInt64(rdrLector.GetOrdinal("Billete"));
oBSP_Ticket.Tipo = rdrLector.GetString(rdrLector.GetOrdinal("Tipo"));
if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("FechaVenta")))
{
oBSP_Ticket.FechaVenta = rdrLector.GetDateTime(rdrLector.GetOrdinal("FechaVenta"));
}
oBSP_Ticket.TarContado = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarContado"));
oBSP_Ticket.TarCredito = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarCredito"));
oBSP_Ticket.IVA105 = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVA105"));
oBSP_Ticket.ComPorcentaje = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComPorcentaje"));
oBSP_Ticket.ComValor = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComValor"));
oBSP_Ticket.ComOver = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComOver"));
oBSP_Ticket.ComIVA = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComIVA"));
oBSP_Ticket.Total = rdrLector.GetDecimal(rdrLector.GetOrdinal("Total"));
oBSP_Ticket.Rg = rdrLector.GetBoolean(rdrLector.GetOrdinal("Rg")) ? BSP_Rg.Internacional : BSP_Rg.Doméstico;
oBSP_Ticket.Moneda = rdrLector.GetBoolean(rdrLector.GetOrdinal("Moneda")) ? Moneda.Dolar : Moneda.Peso;
oBSP_Ticket.Concepto = new Concepto {
ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdConcepto"))
};
oBSP_Ticket.Compania = new Compania {
ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdCompania"))
};
oBSP_Ticket.SemanaID = rdrLector.GetInt64(rdrLector.GetOrdinal("SemanaID"));
return(oBSP_Ticket);
}
protected virtual DateTime? ReadDateTimeOrNull(SqlCeDataReader reader, string columnName)
{
if (reader.IsDBNull(reader.GetOrdinal(columnName)))
return null;
return reader.GetDateTime(reader.GetOrdinal(columnName));
}
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;
}
protected virtual Guid? ReadGuidOrNull(SqlCeDataReader reader, string columnName)
{
if(reader.IsDBNull(reader.GetOrdinal(columnName)))
return null;
return ReadGuid(reader, columnName);
}
protected virtual int? ReadIntOrNull(SqlCeDataReader reader, string columnName)
{
if (reader.IsDBNull(reader.GetOrdinal(columnName)))
return null;
return reader.GetInt32(reader.GetOrdinal(columnName));
}
// returns single record by emailId and password
public List <Teacher> GetSingleRecordByName(string emailId, string password)
{
List <Teacher> teachers = new List <Teacher>();
SqlCeConnection connection = getConnection();
string selectStatement = "SELECT EmailId, FirstName, LastName, [Password] FROM tblTeacher " + "WHERE EmailId = @EmailId and Password = @Password";
SqlCeCommand command = new SqlCeCommand(selectStatement, connection);
command.Parameters.Add("EmailId", SqlDbType.NVarChar, 256).Value = emailId;
//command.Parameters.AddWithValue("EmailId", emailId);
command.Parameters.Add("Password", SqlDbType.NVarChar, 50).Value = password;
try
{
connection.Open();
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Teacher t = new Teacher();
t.EmailId = reader["EmailId"].ToString();
if (!reader.IsDBNull(1)) // 1 is the column index of FirstName
{
t.FirstName = reader["FirstName"].ToString();
}
if (!reader.IsDBNull(2))
{
t.LastName = reader["LastName"].ToString();
}
t.Password = reader["password"].ToString();
teachers.Add(t);
}
reader.Close();
}
catch (SqlCeException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(teachers);
}
public static FirmDetails GetFirmDetails(out string errorText)
{
SqlCeConnection connection = Global.getDatabaseConnection(out errorText);
if (connection == null)
{
return(null);
}
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "Select * From FirmDetails";
using (SqlCeDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
errorText = null;
return(null);
}
FirmDetails firmDetails = new FirmDetails();
firmDetails.FirmName = reader.GetString(0);
if (reader.IsDBNull(1))
{
firmDetails.FirmAddress = string.Empty;
}
else
{
firmDetails.FirmAddress = reader.GetString(1);
}
if (reader.IsDBNull(2))
{
firmDetails.PhoneNumbers = string.Empty;
}
else
{
firmDetails.PhoneNumbers = reader.GetString(2);
}
errorText = null;
return(firmDetails);
}
}
}
} // Load<T>
public static T LoadData <T>(SqlCeDataReader reader, int dataIndex, int dataAltIdex) where T : class
{
if (!reader.IsDBNull(0))
{
var data = reader.GetSqlBinary(dataIndex).Value;
return(XmlSerialization.Deserialize <T>(data));
}
else if (!reader.IsDBNull(1))
{
var data = reader.GetSqlBinary(dataAltIdex).Value;
return(XmlSerialization.Deserialize <T>(data));
}
else
{
return(null);
} // if-else
} // LoadData<T>
/// <summary>
/// Повертає список усіх товарів
/// </summary>
/// <returns>Список товарів</returns>
public IEnumerable <IProductsModel> GetAll()
{
List <ProductsModel> products = new List <ProductsModel>();
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
string query = "select Id, SupplierId, CategoryId, GroupId, UnitId, NameWebStore, NameSupplier, CodeWebStore, " +
"CodeSupplier, PriceWebStore, PriceSupplier, Available, LinkWebStore, LinkSupplier, Notes from Products";
using (SqlCeCommand command = new SqlCeCommand(query, db))
{
try
{
using (SqlCeDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ProductsModel product = new ProductsModel
{
Id = Convert.ToInt32(reader["Id"]),
SupplierId = Convert.ToInt32(reader["SupplierId"]),
CategoryId = Convert.ToInt32(reader["CategoryId"]),
GroupId = reader.IsDBNull(reader.GetOrdinal("GroupId")) ? 0 : Convert.ToInt32(reader["GroupId"]),
UnitId = Convert.ToInt32(reader["UnitId"]),
NameWebStore = reader["NameWebStore"].ToString(),
NameSupplier = reader["NameSupplier"].ToString(),
CodeWebStore = reader["CodeWebStore"].ToString(),
CodeSupplier = reader["CodeSupplier"].ToString(),
PriceWebStore = Convert.ToDecimal(reader["PriceWebStore"]),
PriceSupplier = Convert.ToDecimal(reader["PriceSupplier"]),
Available = reader["Available"].ToString(),
LinkWebStore = reader["LinkWebStore"].ToString(),
LinkSupplier = reader["LinkSupplier"].ToString(),
Notes = reader["Notes"].ToString()
};
products.Add(product);
}
}
}
catch (SqlCeException)
{
throw new Exception("Помилка отримання списку товарів з бази даних.");
}
}
}
return(products);
}
protected string getString(SqlCeDataReader reader, string columnName)
{
int index = reader.GetOrdinal(columnName);
if (!reader.IsDBNull(index))
{
return(reader.GetString(index));
}
return(null);
}
protected float?getFloat(SqlCeDataReader reader, string columnName)
{
int index = reader.GetOrdinal(columnName);
if (!reader.IsDBNull(index))
{
return((float)reader.GetFloat(index));
}
return(null);
}
public void FillListViewRows(ListView listView, string sql, List <SqlCeParameter> parameters)
{
try
{
using (SqlCeConnection conn = new SqlCeConnection(connString.ConnectionString))
{
conn.Open();
using (SqlCeCommand command = new SqlCeCommand(sql, conn))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters.ToArray());
}
using (SqlCeDataReader reader = command.ExecuteReader())
{
listView.Items.Clear();
while (reader.Read())
{
ListViewItem lvItem = new ListViewItem(reader[0].ToString());
for (int i = 1; i < reader.FieldCount; i++)
{
if (!reader.IsDBNull(i))
{
if (reader.GetFieldType(i).FullName == typeof(DateTime).FullName)
{
lvItem.SubItems.Add(reader.GetDateTime(i).ToString("d"));
}
else
{
lvItem.SubItems.Add(reader.GetString(i));
}
}
else
{
lvItem.SubItems.Add(string.Empty);
}
}
listView.Items.Add(lvItem);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Повертає товар по ідентифікатору
/// </summary>
/// <param name="id">Ідентифікатор товару</param>
/// <returns>Екземпляр товару</returns>
public IProductsModel GetById(int id)
{
ProductsModel product = new ProductsModel();
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
string query = "select Id, SupplierId, CategoryId, GroupId, UnitId, NameWebStore, NameSupplier, CodeWebStore, " +
"CodeSupplier, PriceWebStore, PriceSupplier, Available, LinkWebStore, LinkSupplier, Notes from Products where Id=@Id";
using (SqlCeCommand cmd = new SqlCeCommand(query, db))
{
cmd.Prepare();
cmd.Parameters.AddWithValue("@Id", id);
try
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
product.Id = Convert.ToInt32(reader["Id"]);
product.SupplierId = Convert.ToInt32(reader["SupplierId"]);
product.CategoryId = Convert.ToInt32(reader["CategoryId"]);
product.GroupId = reader.IsDBNull(reader.GetOrdinal("GroupId")) ? 0 : Convert.ToInt32(reader["GroupId"]);
product.UnitId = Convert.ToInt32(reader["UnitId"]);
product.NameWebStore = reader["NameWebStore"].ToString();
product.NameSupplier = reader["NameSupplier"].ToString();
product.CodeWebStore = reader["CodeWebStore"].ToString();
product.CodeSupplier = reader["CodeSupplier"].ToString();
product.PriceWebStore = Convert.ToDecimal(reader["PriceWebStore"]);
product.PriceSupplier = Convert.ToDecimal(reader["PriceSupplier"]);
product.Available = reader["Available"].ToString();
product.LinkWebStore = reader["LinkWebStore"].ToString();
product.LinkSupplier = reader["LinkSupplier"].ToString();
product.Notes = reader["Notes"].ToString();
}
}
}
catch (SqlCeException)
{
throw new Exception("Помилка отримання товару з бази даних.");
}
}
}
return(product);
}
private static Compania DataReaderAObjeto(SqlCeDataReader rdrLector)
{
Compania oCompania = new Compania();
oCompania.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID"));
oCompania.Nombre = rdrLector.GetString(rdrLector.GetOrdinal("Nombre"));
if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("Codigo")))
{
oCompania.Codigo = rdrLector.GetString(rdrLector.GetOrdinal("Codigo"));
}
return(oCompania);
}
Resource ReaderToResource(SqlCeDataReader reader)
{
string image = reader.IsDBNull(reader.GetOrdinal(COLUMN_IMAGE_PATH)) ? null :
reader.GetString(reader.GetOrdinal(COLUMN_IMAGE_PATH));
return(new Resource(reader.GetInt32(reader.GetOrdinal(COLUMN_ID)), reader.GetInt32(reader.GetOrdinal(COLUMN_PAR_ID)),
reader.GetString(reader.GetOrdinal(COLUMN_TITLE)), reader.GetString(reader.GetOrdinal(COLUMN_DESCRIPTION)),
reader.GetInt32(reader.GetOrdinal(COLUMN_INIT_SIZE)), reader.GetBoolean(reader.GetOrdinal(COLUMN_IS_PINNED)),
reader.GetInt64(reader.GetOrdinal(COLUMN_PINNED_DATE)), image,
reader.GetBoolean(reader.GetOrdinal(COLUMN_IS_GROUP)), reader.GetString(reader.GetOrdinal(COLUMN_DATE_CREATED)),
mainWindow));
}
public void WriteSQLdata()
{
try
{
using (SqlCeConnection con = new SqlCeConnection(conString))
{
con.Open();
int num = MainWindow.HFRarraymin;
int num2 = MainWindow.apexHFR;
int num4 = MainWindow.PosminHFR;
float up = (float)MainWindow.SlopeHFRup;
float down = (float)MainWindow.SlopeHFRdwn;
//row numbering adds 1 to max value, allows for deletion of rows without number dulpication
//can always modify or re-number in excel then import
using (SqlCeCommand com1 = new SqlCeCommand("SELECT MAX (Number) FROM table1", con))
{
SqlCeDataReader reader = com1.ExecuteReader();
while (reader.Read())
{
if (reader.IsDBNull(0))
{
_rows = 0;
}
else
{
_rows = reader.GetInt32(0);
}
}
}
using (SqlCeCommand com = new SqlCeCommand("INSERT INTO table1 (Date, PID, SlopeDWN, SlopeUP, Number, Equip, BestHFR, FocusPos) VALUES (@Date, @PID, @SlopeDWN, @SlopeUP, @Number, @equip, @BestHFR, @FocusPos)", con))
{
com.Parameters.AddWithValue("@Date", DateTime.Now);
com.Parameters.AddWithValue("@PID", MainWindow.PID);
com.Parameters.AddWithValue("@SlopeDWN", down);
com.Parameters.AddWithValue("@SlopeUP", up);
com.Parameters.AddWithValue("@Number", _rows + 1);
com.Parameters.AddWithValue("@equip", MainWindow.Eqiup);
com.Parameters.AddWithValue("@BestHFR", MainWindow.HFRarraymin);
com.Parameters.AddWithValue("@FocusPos", MainWindow.intersectPos);
com.ExecuteNonQuery();
_rows++;
}
con.Close();
}
}
catch (Exception ex)
{
MainWindow m = new MainWindow();
m.Log("WriteSQLData Error" + ex.ToString());
}
}
//public byte[] Image { get; set; }
public void LoadTrainer(SqlCeDataReader reader)
{
TrainerID = reader.GetInt32(0);
FName = reader["FirstName"].ToString();
LName = reader["LastName"].ToString();
Sex = reader["Sex"].ToString();
if (!reader.IsDBNull(3))
{
DateOfBirth = reader.GetDateTime(3);
}
Street = reader["Street"].ToString();
Suburb = reader["Suburb"].ToString();
City = reader["City"].ToString();
HomePhone = reader["HomePhone"].ToString();
CellPhone = reader["CellPhone"].ToString();
Email = reader["email"].ToString();
if (!reader.IsDBNull(11))
{
Salary = reader.GetDecimal(11);
}
Notes = reader["Notes"].ToString();
}
private static Transaction GetTransactionFromReader(SqlCeDataReader reader)
{
if(reader != null)
{
Transaction transaction = new Transaction();
transaction.TransactionID = (long)reader["TransactionID"];
transaction.CustomerID = (long)reader["CustomerID"];
if (!reader.IsDBNull(reader.GetOrdinal("ResponsiblePartyID")))
{
transaction.ResponsiblePartyID = (long)reader["ResponsiblePartyID"];
}
transaction.TransactionDate = DateTime.Parse(reader["TransactionDate"].ToString());
transaction.PaymentRecieved = DateTime.Parse(reader["PaymentRecieved"].ToString());
transaction.TotalPrice = (int)reader["TotalPrice"];
return transaction;
}
return null;
}
//
// GetUserFromReader
// A helper function that takes the current row from the SqlCeDataReader
// and hydrates a MembershipUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(SqlCeDataReader reader)
{
if (string.IsNullOrWhiteSpace(reader.GetString(1))) return null;
object providerUserKey = null;
string strGooid = Guid.NewGuid().ToString();
if (reader.GetValue(0).ToString().Length > 0)
providerUserKey = new Guid(reader.GetValue(0).ToString());
else
providerUserKey = new Guid(strGooid);
string username = reader.GetString(1);
string email = reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.UtcNow : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.UtcNow : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.UtcNow : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.UtcNow : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.UtcNow : reader.GetDateTime(11);
MembershipUser u = new MembershipUser(this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
// Loads member data
public void LoadMember(SqlCeDataReader reader)
{
//MemberID = Int32.Parse(reader["Id"].ToString());
MemberID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
CardNumber = reader.GetInt32(1);
}
LName = reader["LastName"].ToString();
FName = reader["FirstName"].ToString();
Sex = reader["Sex"].ToString();
if (!reader.IsDBNull(4))
{
DateOfBirth = reader.GetDateTime(4);
}
Street = reader["Street"].ToString();
Suburb = reader["Suburb"].ToString();
City = reader["City"].ToString();
if (!reader.IsDBNull(9))
{
PostalCode = reader.GetInt32(9);
}
HomePhone = reader["HomePhone"].ToString();
CellPhone = reader["CellPhone"].ToString();
Email = reader["email"].ToString();
Occupation = reader["Occupation"].ToString();
if (!reader.IsDBNull(16))
{
PersonalTrainer = reader.GetInt32(16);
}
//PersonalTrainer = reader["PersonalTrainer"].ToString();
Notes = reader["Notes"].ToString();
if (!reader.IsDBNull(15))
{
Image = (byte[])reader["Image"];
}
}
private DailyUsageBO ReadRecord(SqlCeDataReader reader)
{
DailyUsageBO result = new DailyUsageBO();
result.Day = reader.GetDateTime(0);
result.Month = reader.GetString(1);
result.Upload = reader.GetDouble(2);
result.Download = reader.GetDouble(3);
result.Total = reader.GetDouble(4);
if (!reader.IsDBNull(5))
result.Period = new Period(reader.GetString(5));
return result;
}
static string TryGetNullableString(SqlCeDataReader reader, int index)
{
if (!reader.IsDBNull(index))
return reader.GetString(index);
else
return "";
}
static FS2JournalItem ReadOneJournalItem(SqlCeDataReader reader)
{
var journalItem = new FS2JournalItem();
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
journalItem.No = reader.GetInt32(reader.GetOrdinal("Id"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemTime")))
journalItem.SystemTime = reader.GetDateTime(reader.GetOrdinal("SystemTime"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceTime")))
journalItem.DeviceTime = reader.GetDateTime(reader.GetOrdinal("DeviceTime"));
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalItem.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("Detalization")))
journalItem.Detalization = reader.GetString(reader.GetOrdinal("Detalization"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceName")))
journalItem.DeviceName = reader.GetString(reader.GetOrdinal("DeviceName"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelName")))
journalItem.PanelName = reader.GetString(reader.GetOrdinal("PanelName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceUID")))
journalItem.DeviceUID = reader.GetGuid(reader.GetOrdinal("DeviceUID"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelUID")))
journalItem.PanelUID = reader.GetGuid(reader.GetOrdinal("PanelUID"));
if (!reader.IsDBNull(reader.GetOrdinal("ZoneName")))
journalItem.ZoneName = reader.GetString(reader.GetOrdinal("ZoneName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceCategory")))
journalItem.DeviceCategory = reader.GetInt32(reader.GetOrdinal("DeviceCategory"));
if (!reader.IsDBNull(reader.GetOrdinal("StateType")))
journalItem.StateType = (StateType)reader.GetInt32(reader.GetOrdinal("StateType"));
if (!reader.IsDBNull(reader.GetOrdinal("SubsystemType")))
journalItem.SubsystemType = (SubsystemType)reader.GetInt32(reader.GetOrdinal("SubsystemType"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalItem.UserName = reader.GetString(reader.GetOrdinal("UserName"));
return journalItem;
}
static JournalItem ReadOneJournalItem(SqlCeDataReader reader)
{
var journalItem = new JournalItem();
if (!reader.IsDBNull(reader.GetOrdinal("JournalItemType")))
journalItem.JournalItemType = (JournalItemType)reader.GetByte(reader.GetOrdinal("JournalItemType"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemDateTime")))
journalItem.SystemDateTime = reader.GetDateTime(reader.GetOrdinal("SystemDateTime"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceDateTime")))
journalItem.DeviceDateTime = reader.GetDateTime(reader.GetOrdinal("DeviceDateTime"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectUID")))
journalItem.ObjectUID = reader.GetGuid(reader.GetOrdinal("ObjectUID"));
if (!reader.IsDBNull(reader.GetOrdinal("Name")))
journalItem.Name = reader.GetString(reader.GetOrdinal("Name"));
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalItem.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectState")))
journalItem.ObjectState = reader.GetInt32(reader.GetOrdinal("ObjectState"));
if (!reader.IsDBNull(reader.GetOrdinal("GKObjectNo")))
journalItem.GKObjectNo = (ushort)reader.GetInt16(reader.GetOrdinal("GKObjectNo"));
if (!reader.IsDBNull(reader.GetOrdinal("GKIpAddress")))
journalItem.GKIpAddress = reader.GetString(reader.GetOrdinal("GKIpAddress"));
if (!reader.IsDBNull(reader.GetOrdinal("GKJournalRecordNo")))
journalItem.GKJournalRecordNo = reader.GetInt32(reader.GetOrdinal("GKJournalRecordNo"));
if (!reader.IsDBNull(reader.GetOrdinal("KAUNo")))
journalItem.ControllerAddress = (ushort)reader.GetInt32(reader.GetOrdinal("KAUNo"));
if (!reader.IsDBNull(reader.GetOrdinal("StateClass")))
journalItem.StateClass = (XStateClass)reader.GetByte(reader.GetOrdinal("StateClass"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalItem.UserName = reader.GetString(reader.GetOrdinal("UserName"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectStateClass")))
journalItem.ObjectStateClass = (XStateClass)reader.GetByte(reader.GetOrdinal("ObjectStateClass"));
if (!reader.IsDBNull(reader.GetOrdinal("ObjectName")))
journalItem.ObjectName = reader.GetString(reader.GetOrdinal("ObjectName"));
if (!reader.IsDBNull(reader.GetOrdinal("AdditionalDescription")))
journalItem.AdditionalDescription = reader.GetString(reader.GetOrdinal("AdditionalDescription"));
if (!reader.IsDBNull(reader.GetOrdinal("Subsystem")))
journalItem.SubsystemType = (XSubsystemType)reader.GetByte(reader.GetOrdinal("Subsystem"));
return journalItem;
}
static JournalRecord ReadOneJournalRecord(SqlCeDataReader reader)
{
var journalRecord = new JournalRecord();
if (!reader.IsDBNull(reader.GetOrdinal("Description")))
journalRecord.Description = reader.GetString(reader.GetOrdinal("Description"));
if (!reader.IsDBNull(reader.GetOrdinal("Detalization")))
journalRecord.Detalization = reader.GetString(reader.GetOrdinal("Detalization"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceCategory")))
journalRecord.DeviceCategory = reader.GetInt32(reader.GetOrdinal("DeviceCategory"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceDatabaseId")))
journalRecord.DeviceDatabaseId = reader.GetString(reader.GetOrdinal("DeviceDatabaseId"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceName")))
journalRecord.DeviceName = reader.GetString(reader.GetOrdinal("DeviceName"));
if (!reader.IsDBNull(reader.GetOrdinal("DeviceTime")))
journalRecord.DeviceTime = reader.GetDateTime(reader.GetOrdinal("DeviceTime"));
if (!reader.IsDBNull(reader.GetOrdinal("OldId")))
journalRecord.OldId = reader.GetInt32(reader.GetOrdinal("OldId"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelDatabaseId")))
journalRecord.PanelDatabaseId = reader.GetString(reader.GetOrdinal("PanelDatabaseId"));
if (!reader.IsDBNull(reader.GetOrdinal("PanelName")))
journalRecord.PanelName = reader.GetString(reader.GetOrdinal("PanelName"));
if (!reader.IsDBNull(reader.GetOrdinal("StateType")))
journalRecord.StateType = (StateType)reader.GetInt32(reader.GetOrdinal("StateType"));
if (!reader.IsDBNull(reader.GetOrdinal("SubsystemType")))
journalRecord.SubsystemType = (SubsystemType)reader.GetInt32(reader.GetOrdinal("SubsystemType"));
if (!reader.IsDBNull(reader.GetOrdinal("SystemTime")))
journalRecord.SystemTime = reader.GetDateTime(reader.GetOrdinal("SystemTime"));
if (!reader.IsDBNull(reader.GetOrdinal("UserName")))
journalRecord.User = reader.GetString(reader.GetOrdinal("UserName"));
if (!reader.IsDBNull(reader.GetOrdinal("ZoneName")))
journalRecord.ZoneName = reader.GetString(reader.GetOrdinal("ZoneName"));
if (!reader.IsDBNull(reader.GetOrdinal("Id")))
journalRecord.No = reader.GetInt32(reader.GetOrdinal("Id"));
return journalRecord;
}