static void Main(string[] args)
{
var connectionString = $"Data Source = \"{SdfFilePath}\"";
SqlCeConnection connection = null;
SqlCeCommand command = null;
SqlCeDataReader reader = null;
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
var query = "SELECT * FROM Petitioners";
command = new SqlCeCommand(query, connection);
reader = command.ExecuteReader();
var json = new List <ExpandoObject>();
while (reader.Read())
{
var item = new ExpandoObject();
Enumerable.Range(0, reader.FieldCount)
.Skip(1)
.Select(i => new { Field = reader.GetName(i), Value = reader[i] })
.ToList()
.ForEach(data => ((IDictionary <string, Object>)item)[data.Field] = data.Value);
json.Add(item);
}
using (var jsonWriter = new StreamWriter($"{JsonFilePath}", false, Encoding.Default))
{
jsonWriter.WriteLine(JsonConvert.SerializeObject(json, Formatting.Indented));
};
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
finally
{
reader?.Close();
command?.Dispose();
connection?.Close();
}
Console.WriteLine($"{Environment.NewLine}Press any key to exit.");
Console.ReadKey();
}
static void Main(string[] args)
{
var connectionString = $"Data Source = \"{SdfFilePath}\"";
SqlCeConnection connection = null;
SqlCeCommand command = null;
SqlCeDataReader reader = null;
StreamWriter csvWriter = null;
try
{
connection = new SqlCeConnection(connectionString);
connection.Open();
var query = "SELECT * FROM Petitioners";
command = new SqlCeCommand(query, connection);
reader = command.ExecuteReader();
csvWriter = new StreamWriter($"{CsvFilePath}", false, Encoding.Default);
csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount)
.Select(i => $"\"{reader.GetName(i)}\"")
.Aggregate((current, next) => $"{current},{next}"));
while (reader.Read())
{
csvWriter.WriteLine(Enumerable.Range(0, reader.FieldCount)
.Select(i => String.Format("\"{0}\"",
reader.GetValue(i)
.ToString()
.Replace(',', '_')
.Replace('\"', '_')))
.Aggregate((current, next) => $"{current},{next}"));
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
finally
{
csvWriter?.Close();
reader?.Close();
command?.Dispose();
connection?.Close();
}
Console.WriteLine($"{Environment.NewLine}Press any key to exit.");
Console.ReadKey();
}
//selecting all patients
public Tuple <LinkedList <int>, LinkedList <String>, LinkedList <String> > AllPatients()
{
LinkedList <int> ids = new LinkedList <int>();
LinkedList <String> names = new LinkedList <String>();
LinkedList <String> nhsNos = new LinkedList <String>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT patientID, name, nhsNo FROM PatientInformation";
selectQuery.Parameters.Clear();
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
ids.AddLast(reader.GetInt32(0));
names.AddLast(reader.GetString(1));
nhsNos.AddLast(reader.GetString(2));
}
reader.Close();
return(Tuple.Create(ids, names, nhsNos));
}
public List <KonacanPlasman> findPreskokTak3(string ime, string prezime)
{
SqlCeCommand cmd = new SqlCeCommand(findPreskokTak3SQL);
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())
{
KonacanPlasman kp = new KonacanPlasman();
loadCommonData(kp, rdr);
kp.Preskok = Convert.IsDBNull(rdr["rank"]) ? null : (Nullable <short>)rdr["rank"];
kp.TipTakmicenja = TipTakmicenja.StandardnoTakmicenje;
result.Add(kp);
}
rdr.Close();
return(result);
}
public List <KonacanPlasman> findVisebojZbirViseKola(string ime, string prezime)
{
SqlCeCommand cmd = new SqlCeCommand(findVisebojZbirViseKolaSQL);
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())
{
KonacanPlasman kp = new KonacanPlasman();
loadCommonData(kp, rdr);
kp.Viseboj = Convert.IsDBNull(rdr["rank"]) ? null : (Nullable <short>)rdr["rank"];
kp.TipTakmicenja = TipTakmicenja.ZbirViseKola;
result.Add(kp);
}
rdr.Close(); // obavezno, da bi se zatvorila konekcija otvorena u executeReader
return(result);
}
public Historia BuscarPorID(Int64 _id)
{
Historia h = null;
try
{
String SQL = String.Format("SELECT * FROM Historia WHERE id = {0} ", _id);
SqlCeDataReader data = BD.ExecutarSelect(SQL);
if (data.Read())
{
h = new Historia();
h.id = data.GetInt32(0);
UsuarioDAO DaoUsuario = new UsuarioDAO();
h.Autor = DaoUsuario.BuscarPorID(data.GetInt32(1));
h.Terminada = data.GetBoolean(2);
h.Data = data.GetDateTime(3);
h.Titulo = data.GetString(4);
h.Sinopse = data.GetString(5);
}
data.Close();
BD.FecharConexao();
CapituloDAO dao = new CapituloDAO();
h.Capitulos = dao.BuscarCapitulosPorHistoria(h);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(h);
}
public List <string> GetTableNames()
{
const string sql1 = @"
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'TABLE'
ORDER BY TABLE_NAME" ;
List <string> tableNames = new List <string>();
//OleDbDataReader oleDbDataReader = null;
SqlCeDataReader sqlDataReader = null;
try
{
try
{
sqlDataReader = RunQuerySQL(sql1);
}
catch (Exception ex)
{
log.InfoFormat("Exception while running sql1: {0}", ex.Message);
throw;
}
while (sqlDataReader.Read())
{
tableNames.Add((string)sqlDataReader["TABLE_NAME"] + "|");
}
}
finally
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
return(tableNames);
}
public void routerSil(TextBox routerSilServerAdi)
{
//Sqlden Server silindiğinde mikrotik cihazdaki tüm userlar da silinecek.
try
{
SqlCeCommand komut = new SqlCeCommand();
MK mikrotik = new MK(svIp);
if (!mikrotik.Login(svKulAdi, svSifre))
{
mikrotik.Close();
}
else
{
if (baglanti.State == ConnectionState.Closed)
{
baglanti.Open();
}
komut.Connection = baglanti;
komut.CommandText = "select H.kullaniciAdi from HotspotTBL H, ServerTBL S where S.serverId=H.serverId and S.serverAdi='" + routerSilServerAdi.Text + "'";
komut.ExecuteNonQuery();
SqlCeDataReader dr = komut.ExecuteReader();
while (dr.Read())
{
silKulAdi = dr["kullaniciAdi"].ToString();
mikrotik.Send("/ip/hotspot/user/remove");
mikrotik.Send("=.id=" + silKulAdi + "", true);
}
dr.Close();
baglanti.Close();
}
}
catch (Exception)
{
//MessageBox.Show("Server Silindi");
}
}
/// <summary>
/// Gets the word.
/// </summary>
/// <param name="textid">The textid.</param>
/// <param name="cardid">The cardid.</param>
/// <returns></returns>
/// <remarks>Documented by Dev03, 2009-01-09</remarks>
private DbWord GetWord(int textid, int cardid)
{
DbWord wordCache = Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.Word, textid)] as DbWord;
if (wordCache != null)
{
return(wordCache);
}
SqlCeCommand cmd = MSSQLCEConn.CreateCommand(Parent.CurrentUser);
cmd.CommandText = "SELECT id, text, type, is_default FROM TextContent WHERE cards_id=@id;";
cmd.Parameters.Add("@id", cardid);
SqlCeDataReader reader = MSSQLCEConn.ExecuteReader(cmd);
IList <IWord> list = new List <IWord>();
while (reader.Read())
{
int tid = Convert.ToInt32(reader["id"]);
DbWord word = new DbWord(tid, reader["text"].ToString(),
(WordType)Enum.Parse(typeof(WordType), reader["type"].ToString(), true), Convert.ToBoolean(reader["is_default"]), Parent);
if (tid == textid)
{
wordCache = word;
}
if (Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.GetIdentifier(CacheObject.Word, tid)] == null)
{
Parent.CurrentUser.Cache[ObjectLifetimeIdentifier.Create(CacheObject.Word, tid)] = word;
}
}
reader.Close();
return(wordCache);
}
public Dictionary <Int64, Desenvolvedora> ListarTodos()
{
Dictionary <Int64, Desenvolvedora> mapaDesenvolvedoras = new Dictionary <Int64, Desenvolvedora>();
try
{
String SQL = "SELECT * FROM desenvolvedora;";
SqlCeDataReader data = BD.ExecutarSelect(SQL);
while (data.Read())
{
Desenvolvedora d = new Desenvolvedora();
d.Cnpj = data.GetInt64(0);
d.Nome = data.GetString(1);
d.Presidente = data.GetString(2);
d.Fundador = data.GetString(3);
d.Fundacao = data.GetString(4);
d.NumeroEmpregados = data.GetInt64(5);
d.Tipo = data.GetString(6);
JogoDAO daoJogo = new JogoDAO();
d.jogos = daoJogo.ListarJogosPorDesenvolvedora(d.Cnpj);
mapaDesenvolvedoras.Add(d.Cnpj, d);
}
data.Close();
BD.FecharConexao();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(mapaDesenvolvedoras);
}
public void OriginalValueIsReturnedWhenDBCmdTransactionError()
{
Database db = DatabaseFactory.CreateDatabase("SqlCeTest");
StringBuilder readerData = new StringBuilder();
DbTransaction transaction = null;
try
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
transaction = connection.BeginTransaction();
string SqlCeCommand = "update Items set QtyInHand = 45 where ItemId = 'Test'";
DbCommand dbCommandWrapper = db.GetSqlStringCommand(SqlCeCommand);
db.ExecuteNonQuery(dbCommandWrapper, transaction);
transaction.Commit();
}
}
catch { }
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = connStr;
conn.Open();
SqlCeCommand cmd = new SqlCeCommand("Select QtyInHand from Items where ItemId = 2", conn);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Assert.AreEqual(Convert.ToDouble(95), Convert.ToDouble(dr["QtyInHand"].ToString()));
}
dr.Close();
conn.Close();
}
public static IList <TIncidenciaEvolucion> GetTIncidenciaEvolucions(SqlCeConnection conn)
{
IList <TIncidenciaEvolucion> l = new List <TIncidenciaEvolucion>();
string sql = "SELECT * FROM IncidenciaEvolucion WHERE abm <> 2 ORDER BY incidencia_id DESC";
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
TIncidenciaEvolucion tIncidenciaEvolucion = new TIncidenciaEvolucion()
{
IncidenciaEvolucionId = dr.GetInt32(0),
Comentarios = dr.GetString(3),
Operativo = dr.GetBoolean(5)
};
if (dr[2] != DBNull.Value)
{
tIncidenciaEvolucion.FechaEvolucion = dr.GetDateTime(2);
}
if (dr[4] != DBNull.Value)
{
tIncidenciaEvolucion.TUsuario = GetTUsuario(dr.GetInt32(4), conn);
}
tIncidenciaEvolucion.Abm = dr.GetByte(6);
if (tIncidenciaEvolucion != null)
{
l.Add(tIncidenciaEvolucion);
}
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(l);
}
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);
}
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);
}
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);
}
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);
}
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 TAgenteExtintor GetTAgenteExtintor(string descripcion, SqlCeConnection conn)
{
TAgenteExtintor ta = null;
string sql = String.Format("SELECT * FROM AgenteExtintor WHERE descripcion='{0}'", descripcion);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TAgenteExtintor();
ta.AgenteExtintorId = dr.GetInt32(0);
ta.Descripcion = dr.GetString(2);
ta.Abm = dr.GetByte(3);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
public static TResponsable GetTResponsable(string nombre, SqlCeConnection conn)
{
TResponsable tr = null;
string sql = String.Format("SELECT * FROM Responsable WHERE nombre='{0}'", nombre);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tr = new TResponsable();
tr.ResponsableId = dr.GetInt32(0);
tr.Nombre = dr.GetString(1);
tr.Abm = dr.GetByte(2);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(tr);
}
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;
}
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();
}
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 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);
}
public static int GetIdByName(string name, string surname)
{
int result = -1;
try
{
SqlCeCommand command = new SqlCeCommand(@"SELECT Id FROM Teachers WHERE Name = @name AND Surname = @surname ", Connection);
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@surname", surname);
SqlCeDataReader reader = command.ExecuteReader();
reader.Read();
result = (int)reader["Id"];
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(result);
}
public Tuple <LinkedList <int>, LinkedList <String>, LinkedList <String> > scanTypesForPatient(int patientID)
{
LinkedList <int> conditionID = new LinkedList <int>();
LinkedList <String> condition = new LinkedList <String>();
LinkedList <String> description = new LinkedList <String>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "Select PatientCondition.ConditionID, condition, description from PatientCondition join Conditions on PatientCondition.conditionID = Conditions.conditionID where patientID = @PatientID";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@PatientID", patientID);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
conditionID.AddLast(reader.GetInt32(0));
condition.AddLast(reader.GetString(1));
description.AddLast(reader.GetString(2));
}
reader.Close();
return(Tuple.Create(conditionID, condition, description));
}
//table type 3 queries
//patientCondition, patientScans, pointRecognitionScans
public Tuple <LinkedList <int>, LinkedList <int> > selectType3(String tableName, String colName, String criterion)
{
LinkedList <int> id1 = new LinkedList <int>();
LinkedList <int> id2 = new LinkedList <int>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT * FROM @TableName WHERE @ColName LIKE @Criterion";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@TableName", tableName);
selectQuery.Parameters.Add("@ColName", colName);
selectQuery.Parameters.Add("@Criterion", criterion);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
id1.AddLast(reader.GetInt32(0));
id2.AddLast(reader.GetInt32(1));
}
reader.Close();
return(Tuple.Create(id1, id2));
}
//generalised method for getting one id
public LinkedList <int> selectID(String columnNeeded, String tableName, String column, String criterion)
{
LinkedList <int> id = new LinkedList <int>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT @ColumnNeeded FROM @TableName WHERE @Column LIKE @Criterion";
selectQuery.Parameters.Clear();
selectQuery.Parameters.Add("@ColumnNeeded", columnNeeded);
selectQuery.Parameters.Add("@TableName", tableName);
selectQuery.Parameters.Add("@Column", column);
selectQuery.Parameters.Add("@Criterion", criterion);
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
id.AddLast(reader.GetInt32(0));
}
reader.Close();
return(id);
}
public static int GetIdByTitle(int title, int ClassesId)
{
int result = -1;
try
{
SqlCeCommand command = new SqlCeCommand(@"SELECT Id FROM Departments WHERE Title = @title AND ClassesId = @classid", Connection);
command.Parameters.AddWithValue("@title", title);
command.Parameters.AddWithValue("@classid", ClassesId);
SqlCeDataReader reader = command.ExecuteReader();
reader.Read();
result = (int)reader["Id"];
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(result);
}
//selecting all conditions
public Tuple <LinkedList <int>, LinkedList <String>, LinkedList <String> > AllConditions()
{
LinkedList <int> ids = new LinkedList <int>();
LinkedList <String> conditions = new LinkedList <String>();
LinkedList <String> descriptions = new LinkedList <String>();
SqlCeCommand selectQuery = this.con.CreateCommand();
selectQuery.CommandText = "SELECT * FROM Condition";
selectQuery.Parameters.Clear();
SqlCeDataReader reader = selectQuery.ExecuteReader();
while (reader.Read())
{
//cSize = cSize + 1;
ids.AddLast(reader.GetInt32(0));
conditions.AddLast(reader.GetString(1));
descriptions.AddLast(reader.GetString(2));
}
reader.Close();
return(Tuple.Create(ids, conditions, descriptions));
}
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);
}
public DataTable getDocCheckProductAll()
{
DataTable table = getDataTableDocCheckProduct();
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("select DCode,'เลือกรายการ' as Blank from DocCheckProducts ");
sb.Append(" order by CreateDate desc");
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();
}
return table;
}
//Inserts an entry into the media table and returns true if insertion is successful, and false otherwise.
public Boolean insertMedia(MediaObject mObject)
{
string insertQuery;
string url, fileName, fileExt, fileType;
int typeID, width, height;
DateTime dateStart, dateEnd;
url = mObject.getUrl();
fileName = url.Split('\\').Last();
fileExt = fileName.Split('.').Last();
fileName = fileName.Split('.').First();
typeID = MediaFileUtilities.getFileTypeID(fileExt);
fileType = MediaFileUtilities.getFileType(fileExt);
MediaElement thisMediaFile = new MediaElement();
thisMediaFile.Source = new Uri(url);
width = (int)thisMediaFile.Width;
height = (int)thisMediaFile.Height;
dateStart = mObject.getStartDate();
dateEnd = mObject.getEndDate();
insertQuery = "INSERT INTO Media([url],[filename],[file_extension],[type_id],[width],[height],[date_start],[date_end])"
+ "VALUES ('" + url + "','" + fileName + "','" + fileExt + "','" + typeID + "','" + width + "','"
+ height + "','" + dateStart + "','" + dateEnd + "');";
try
{
sqlCmd = new SqlCeCommand(insertQuery, sc);
sqlRdr = sqlCmd.ExecuteReader();
sqlRdr.Close();
return true;
}
catch (SqlCeException sqlEx)
{
MessageBox.Show(sqlEx.Errors.ToString());
return false;
}
}
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;
}
// 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 IList<DocCheckProduct> getDocCheckProductByDCode(string _dCode)
{
IList<DocCheckProduct> docCheckProducts = new List<DocCheckProduct>();
DocCheckProduct docCheckProduct = null;
try
{
Conn = OpenConn();
sb = new StringBuilder();
if (_dCode.Equals(""))
{
sb.Append("select ID,DCode,Warehouse,Location,CreateDate from DocCheckProducts ");
}
else
{
sb.Append("select ID,DCode,Warehouse,Location,CreateDate from DocCheckProducts where DCode ='" + _dCode + "'");
}
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)
{
docCheckProduct = new DocCheckProduct();
docCheckProduct.ID = Convert.ToInt32(ds[0].ToString());
docCheckProduct.Code = Convert.ToString(ds[1].ToString());
docCheckProduct.Warehouse = Convert.ToString(ds[2].ToString());
docCheckProduct.Location = Convert.ToString(ds[3].ToString());
docCheckProduct.CreateDate = Convert.ToDateTime(ds[4].ToString());
docCheckProducts.Add(docCheckProduct);
}
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
return docCheckProducts;
}
// getById
public DocCheckProduct getDocCheckProductByCode(object _code)
{
DocCheckProduct docCheckProduct = null;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("select ID,DCode,Warehouse,Location,CreateDate from DocCheckProducts d where d.DCode ='" + Convert.ToString(_code) + "'");
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)
{
docCheckProduct = new DocCheckProduct();
docCheckProduct.ID = Convert.ToInt32(ds[0].ToString());
docCheckProduct.Code = Convert.ToString(ds[1].ToString());
docCheckProduct.Warehouse = Convert.ToString(ds[2].ToString());
docCheckProduct.Location = Convert.ToString(ds[3].ToString());
docCheckProduct.CreateDate = Convert.ToDateTime(ds[4].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally
{
Conn.Close();
}
return docCheckProduct;
}
// getById
public Product getById(object _id)
{
Product product = null;
try
{
Conn = OpenConn();
sb = new StringBuilder();
sb.Append("select ID,Name,Code,BarCode,Unit,CreateDate from Products p where p.ID=" + Convert.ToInt32(_id));
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)
{
product = new Product();
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());
product.CreateDate = Convert.ToDateTime(ds[5].ToString());
}
}
dr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Conn.Close();
}
finally {
Conn.Close();
}
return product;
}
//Removes an entry from the media table and returns true if deletion is successful, false otherwise.
public Boolean removeMedia(MediaObject mObject)
{
string removeQuery;
string url, fileName, fileExt;
url = mObject.getUrl();
fileName = url.Split('\\').Last();
fileExt = fileName.Split('.').Last();
fileName = fileName.Split('.').First();
removeQuery = "DELETE FROM Media WHERE url = '" + url + "' AND filename = '" + fileName + "' AND file_extension = '" + fileExt + "'";
try
{
sqlCmd = new SqlCeCommand(removeQuery, sc);
sqlRdr = sqlCmd.ExecuteReader();
sqlRdr.Close();
return true;
}
catch (SqlCeException sqlEx)
{
MessageBox.Show(sqlEx.Errors.ToString());
return false;
}
}
public Boolean removeFirstOccurance(string url)
{
string removeQuery;
removeQuery = "DELETE FROM Media WHERE media_id IN (SELECT TOP(1) media_id FROM Media WHERE url = '" + url + "' ORDER BY date_end ASC)";
try
{
sqlCmd = new SqlCeCommand(removeQuery, sc);
sqlRdr = sqlCmd.ExecuteReader();
sqlRdr.Close();
return true;
}
catch (SqlCeException sqlEx)
{
MessageBox.Show(sqlEx.Errors.ToString());
return false;
}
}
