public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams)
{
var dt = new DataTable();
using (var conn = new SqlCeConnection(connStr))
using (var cmd = new SqlCeCommand(query, conn))
{
try
{
SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString);
engine.Upgrade(conn.ConnectionString);
}
catch
{
}
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(sqlParams);
conn.Open();
dt.Load(cmd.ExecuteReader());
}
return dt;
}
//Chamar sempre apos carregar os parametros
public void Carregar()
{
SqlCeDataReader reader=null;
try
{
string sql = "select * from funcionario where id=" + Id;
SqlCeCommand cmd = new SqlCeCommand(sql, D.Bd.Con);
reader = cmd.ExecuteReader();
reader.Read();
Nome = Convert.ToString(reader["nome"]);
DescontoMaximo = Convert.ToDouble(reader["desconto_maximo"]);
AcrescimoMaximo = Convert.ToDouble(reader["acrescimo_maximo"]);
}
catch (Exception ex)
{
throw new Exception("Não consegui obter os dados do funcionário, configure e sincronize antes de utilizar o dispositivo " + ex.Message);
}
finally
{
try
{
reader.Close();
}
catch { }
}
}
private void button2_Click(object sender, EventArgs e)
{
DataConnectionDialog dcd = new DataConnectionDialog();
DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
dcs.LoadConfiguration(dcd);
if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
textBox2.Text = dcd.ConnectionString;
connectionString = dcd.ConnectionString;
comboBox1.Enabled = true;
using (SqlCeConnection con = new SqlCeConnection(connectionString))
{
comboBox1.Items.Clear();
con.Open();
using (SqlCeCommand command = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.Tables", con))
{
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
comboBox1.Items.Add(reader.GetString(0));
}
}
}
//textBox1.Text = dcd.SelectedDataSource.DisplayName;
}
dcs.SaveConfiguration(dcd);
}
public int getNextOrderNo()
{
// Get the next order number from the local database
int orderNo = 0;
try
{
conn.Open();
SqlCeCommand sqlCmd = new SqlCeCommand("SELECT OrderNo FROM Orders", conn);
SqlCeDataReader rdr = sqlCmd.ExecuteReader();
while (rdr.Read())
{
orderNo = rdr.GetInt32(0);
}
}
catch (Exception ex)
{
dbError = ex.Message;
return -1;
}
finally
{
conn.Close();
}
return ++orderNo;
}
public static List<StudentModel> ReadAllStudents()
{
string cmdText =
@"SELECT Id, ExpGraduation, Name, CanCode, CreditsLeft, Advisor FROM Student";
List<StudentModel> list = new List<StudentModel>();
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
StudentModel item = new StudentModel()
{
Id = (int) reader["Id"],
ExpGraduation = (DateTime) reader["ExpGraduation"],
Name = (string) reader["Name"],
CanCode = (bool) reader["CanCode"],
CreditsLeft = (int) reader["CreditsLeft"],
Advisor = (string) reader["Advisor"]
};
list.Add(item);
}
}
}
}
return list;
}
protected void Login_Click(object sender, EventArgs e)
{
SqlCeConnection conn = new SqlCeConnection(connString); // SqlConnection
conn.Open();
//podlozno SQL injection napadu
//SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE ime = '" +
// txtKorisnickoIme.Text + "' AND lozinka = '" +
// txtLozinka.Text + "'", conn);
SqlCeCommand command = new SqlCeCommand("SELECT * FROM student WHERE ime = @ime AND lozinka = @lozinka", conn);
command.Parameters.AddWithValue("ime", txtKorisnickoIme.Text);
command.Parameters.AddWithValue("lozinka", txtLozinka.Text);
SqlCeDataReader dr = command.ExecuteReader();
if (dr.Read())
{
Session["korisnickoIme"] = dr["ime"].ToString();
dr.Close();
conn.Close();
Response.Redirect("Zasticena.aspx");
}
dr.Close();
conn.Close();
}
public void Tt()
{
string connectionString = @"DataSource=db.sdf";
var conn = new SqlCeConnection(connectionString);
if(!File.Exists(conn.Database))
{
new SqlCeEngine(connectionString).CreateDatabase();
}
conn.Open();
//Creating a table
var cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
cmdCreate.ExecuteNonQuery();
//Inserting some data...
var cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
cmdInsert.ExecuteNonQuery();
//Making sure that our data was inserted by selecting it
var cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
SqlCeDataReader reader = cmdSelect.ExecuteReader();
reader.Read();
Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
reader.Close();
conn.Close();
}
public static DateTime FindDate(int ordid, SqlCeConnection conn)
{
try
{
DateTime date = new DateTime(2000, 1, 1, 1, 1, 1);
string commandText = SQLQueryString.Selectdate;
SqlCeCommand cmd = new SqlCeCommand(commandText, conn);
cmd.Parameters.AddWithValue("@id", ordid);
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
date = (DateTime)dr[0];
return date;
}
return date;
}
catch (SqlCeException ex)
{
log.Error(ex.Message);
return DateTime.Now;
}
}
public static bool IsPersonOnBoard(string boardId, string personId)
{
string query = "SELECT * FROM [boards] WHERE board_id=@boardId AND person_id=@personId";
if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
{
return false;
}
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = CommonState.ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
{
cmd.CommandText = query;
cmd.Parameters.Add("@boardId", boardId);
cmd.Parameters.Add("@personId", personId);
cmd.Prepare();
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
long last = (long)reader["person_lastactivity"];
if (last > (CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT))
{
return true;
}
}
}
}
}
return false;
}
private void Code()
{
int code = comboBox1.SelectedIndex + 1; // had to add plus one, as it was retriving id as the previous number, so if picked bug_id2, it would give me 1's code
//selectes the code from the database, where = to what bug id selected
string sqlquery = "SELECT code FROM tbl_bug WHERE bug_id = " + code;
SqlCeCommand command = new SqlCeCommand(sqlquery, mySqlConnection);
try
{
//reads the command and exports code from database to the textbox - txtCode
SqlCeDataReader sdr = command.ExecuteReader();
while (sdr.Read())
{
txtCode.Text = (sdr["code"].ToString());
}
}
catch (SqlCeException ex)
{
MessageBox.Show("Failure with connection!");
}
}
public static List<BookStoreModel> ReadAllBooks()
{
string cmdText =
@"SELECT Id, Title, Author, PublishedDate, Cost, InStock, BindingType FROM Books";//Cover Add back after figure out how to data type this
List<BookStoreModel> list = new List<BookStoreModel>();
using (SqlCeConnection conn = CreateConnection())
{
using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
BookStoreModel book = new BookStoreModel();
book.Id = (int)reader["Id"];
book.Title = (string)reader["Title"];
book.Author = (string)reader["Author"];
book.PublishedDate = (DateTime)reader["PublishedDate"];
book.Cost = (double)reader["Cost"];
book.InStock = (bool)reader["InStock"];
book.BindingType = (string)reader["BindingType"];
//book.Cover = (???)reader["Cover"];
list.Add(book);
}
}
}
}
return list;
}
public void SelectUser(int x)
{
try
{
byte count = 0;
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Users", cKoneksi.Con);
SqlCeDataReader dr;
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read()) { count = 1; } else { count = 0; }
dr.Close(); cmd.Dispose(); if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
if (count != 0)
{
DataSet ds = new DataSet();
SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM Users", cKoneksi.Con);
da.Fill(ds, "Users");
textBoxUser.Text = ds.Tables["Users"].Rows[x][0].ToString();
textBoxPass.Text = ds.Tables["Users"].Rows[x][1].ToString();
checkBoxTP.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][2]);
checkBoxTPK.Checked = Convert.ToBoolean(ds.Tables["Users"].Rows[x][3]);
ds.Dispose();
da.Dispose();
}
else
{
MessageBox.Show("Data User Kosong", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
buttonClose.Focus();
}
}
catch (SqlCeException ex)
{
MessageBox.Show(cError.ComposeSqlErrorMessage(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
}
}
public static IEnumerable<string> GetFileDatabaseTables(string databaseFile, string password = null)
{
if (string.IsNullOrEmpty(databaseFile) || !File.Exists(databaseFile))
{
throw new Exception("Database not exists");
}
IList<string> tableNames = new List<string>();
SqlCeConnectionStringBuilder connectionString = new SqlCeConnectionStringBuilder();
connectionString.DataSource = databaseFile;
if (!string.IsNullOrEmpty(password))
connectionString.Password = password;
using (SqlCeConnection sqlCon = new SqlCeConnection(connectionString.ToString()))
{
string sqlStmt = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES";
using (SqlCeCommand cmd = new SqlCeCommand(commandText: sqlStmt, connection: sqlCon))
{
sqlCon.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
tableNames.Add((string)reader["table_name"]);
}
sqlCon.Close();
}
}
return tableNames;
}
//public void suaraOK()
//{
// SoundPlayer playOK = new SoundPlayer(@"Backup\doorbell.wav");
// playOK.Play();
//}
//public void suaraError()
//{
// SoundPlayer playError = new SoundPlayer(@"Backup\sirentone.WAV");
// playError.Play();
//}
public void Simpan()
{
//string Tanggal = DateTime.Now.ToString("dd/MM/yyyy");
//string JamTanggal = DateTime.Now.ToString();
try
{
SqlCeDataReader dr;
SqlCeCommand cmd = new SqlCeCommand("Select * from AngkutTP where ScanBarcode ='" + textBoxBarcode.Text + "'", cKoneksi.Con);
if (cKoneksi.Con.State == ConnectionState.Closed) { cKoneksi.Con.Open(); }
dr = cmd.ExecuteReader();
if (dr.Read())
{
//cSound.suaraError();
MessageBox.Show("Duplicate Scan!!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
dr.Close();
cmd.Dispose();
if (cKoneksi.Con.State == ConnectionState.Open) { cKoneksi.Con.Close(); }
textBoxBarcode.Text = "";
textBoxBarcode.Focus();
}
else
{
dr.Close();
cmd.Dispose();
string strSQL = "INSERT INTO AngkutTP VALUES('" + textBoxNoPolisi.Text + "','" + textBoxNomorator.Text + "','" + textBoxNamaTP.Text + "','" + textBoxBarcode.Text + "',GetDate())";
cQuery.Execute(strSQL);
//cSound.suaraOK();
}
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
}
}
static void Listen()
{
//[ClientID],[pNumber],[msg],[move],[start board]
while (true)
{
string sql;
string rcvd = Console.ReadLine();
if (rcvd.Split('¬')[2].StartsWith("cht:"))
{
sql = "insert into history(ClientID,name,message,status) values (" + System.Diagnostics.Process.GetCurrentProcess().Id + ",'" + name + "','" + msg.Substring(4) + "','N')";
SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
if (rcvd.Split('¬')[2].StartsWith("lst:"))
{
sql = "SELECT * FROM Session where clientID2 is null";
SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("¬" + reader["message"].ToString() + "¬");
}
}
}
}
public static int GetLastOldId()
{
try
{
using (var dataContext = new SqlCeConnection(ConnectionString))
{
var query = "SELECT MAX(OldId) FROM Journal";
var result = new SqlCeCommand(query, dataContext);
dataContext.Open();
var reader = result.ExecuteReader();
int? firstResult = null;
if (reader.Read())
firstResult = (int)reader[0];
dataContext.Close();
if (firstResult != null)
return (int)firstResult;
return -1;
}
}
catch (Exception e)
{
Logger.Error(e, "Исключение при вызове DatabaseHelper.GetLastOldId");
}
return -1;
}
public IEnumerable<Dbentry> GetFilterd(string name = "", string email = "", string country = "", string adress = "", string zip = "", string area = "", string company = "")
{
IList<Dbentry> ret = new List<Dbentry>();
if(string.IsNullOrEmpty(name) && string.IsNullOrEmpty(email) && string.IsNullOrEmpty(adress) && string.IsNullOrEmpty(zip) && string.IsNullOrEmpty(area) && string.IsNullOrEmpty(company))
return GetData();
if(_connection.State == ConnectionState.Closed)
_connection.Open();
var cmds = "SELECT Contacts.* FROM Contacts WHERE ";
if(!string.IsNullOrEmpty(name))
cmds += string.Format("Name LIKE '%{0}%',", name);
if(!string.IsNullOrEmpty(email))
cmds += string.Format("Email LIKE '%{0}%',", email);
if(!string.IsNullOrEmpty(adress))
cmds += string.Format("Adress LIKE '%{0}%',", adress);
if(!string.IsNullOrEmpty(zip))
cmds += string.Format("ZipCode LIKE '%{0}%',", zip);
if(!string.IsNullOrEmpty(country))
cmds += string.Format("Country LIKE '%{0}%',", country);
if(!string.IsNullOrEmpty(area))
cmds += string.Format("Area LIKE '%{0}%',", area);
if(!string.IsNullOrEmpty(company))
cmds += string.Format("Company LIKE '%{0}%',", company);
var cmd = new SqlCeCommand(cmds.Substring(0, cmds.Length - 1), _connection);
var sdr = cmd.ExecuteReader();
while(sdr.Read()) {
var tmp = new Dbentry { Id = sdr["ID"].ToString(), Name = sdr["Name"].ToString(), Email = sdr["Email"].ToString(), Adress = sdr["Adress"].ToString(), Zipcode = sdr["ZipCode"].ToString(), Country = sdr["Country"].ToString(), Area = sdr["Area"].ToString(), Office = sdr["Office"].ToString(), Mobile = sdr["Mobile"].ToString(), Home = sdr["Home"].ToString(), Company = sdr["Company"].ToString(), Other = sdr["Other"].ToString() };
ret.Add(tmp);
}
_connection.Close();
GC.Collect();
return ret;
}
private void postavi_labele()
{
//kreiraj novu praznu konekciju
SqlCeConnection conn = new SqlCeConnection();
//dohvati tekst za povezivanje na bazu iz web.config i postavi g ana konekciju
string connStr = WebConfigurationManager.ConnectionStrings["studenti"].ConnectionString;
conn.ConnectionString = connStr;
//kreiraj novu naredbu i postavi SQL kao i konekciju
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT COUNT(*) FROM student";
cmd.CommandType = System.Data.CommandType.Text; //tip je SQL naredba (a ne tablica ili stor.proc)
//otvori komunikaciju sa bazom
conn.Open();
int brojStud = (int)cmd.ExecuteScalar(); //izvrši vrati jednu vrijednost
Label1.Text = "U bazi imamo " + brojStud.ToString() + " studenata!";
cmd.CommandText = "SELECT * FROM student";
//sada ih vrati kao datareader
SqlCeDataReader dr = cmd.ExecuteReader();
Label2.Text = "ID - Ime - Prezime" + "<br>";
// na sql Expressu ima i dr.HasRows da vidimo je li prazan if(dr.HasRows))
while (dr.Read())
{
//čitaj red po red dok ne dođeš do kraja
Label2.Text += dr["stud_id"].ToString() + " - " + dr["ime"] + " - " + dr["prezime"] + " - " + dr["faks"] + "<br>";
}
conn.Close();
}
public List<DailyUsageBO> AllByMonth(string username)
{
List<DailyUsageBO> result = new List<DailyUsageBO>();
DailyUsageBO record;
CultureInfo provider = CultureInfo.InvariantCulture;
try
{
using (SqlCeCommand cmd = new SqlCeCommand(String.Format("SELECT month, SUM(upload), SUM(download), SUM(total) FROM {0} GROUP BY month ORDER BY month", username),
DataBaseFactory.Instance.GetConnection()))
{
using (SqlCeDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
record = new DailyUsageBO();
record.Month = dr.GetString(0);
record.Upload = dr.GetDouble(1);
record.Download = dr.GetDouble(2);
record.Total = dr.GetDouble(3);
record.Day = DateTime.ParseExact(record.Month, "yyyyMM", provider);
result.Add(record);
}
}
}
}
catch
{
CreateTable(username);
}
return result;
}
private void ecrireListeView(string query)
{
connexion();
cn.Open();
SqlCeCommand comm = new SqlCeCommand(query, cn);
SqlCeDataReader dr = comm.ExecuteReader();
clearList();
while (dr.Read())
{
ListViewItem item = new ListViewItem(dr[0].ToString());
item.SubItems.Add(dr[1].ToString());
item.SubItems.Add(dr[2].ToString());
item.SubItems.Add(dr[3].ToString());
item.SubItems.Add(dr[4].ToString());
item.SubItems.Add(dr[5].ToString());
item.SubItems.Add(dr[6].ToString());
item.SubItems.Add(dr[7].ToString());
item.SubItems.Add(dr[8].ToString());
item.SubItems.Add(dr[9].ToString());
listView1.Items.Add(item);
}
int listView1Count = listView1.Items.Count;
label2.Text = "Nombre de lignes : " + listView1Count.ToString();
cn.Close();
}
/// <summary>
/// Execution
/// </summary>
/// <param name="sqlRequest"></param>
/// <returns></returns>
public static void ExecuteSqlRequest(string sqlRequest, Action<SqlCeDataReader> readAction)
{
if (Filename != null)
{
string connectionString = "Data Source=" + Filename + ";Persist Security Info=false;";
using (SqlCeConnection con = new SqlCeConnection(connectionString))
{
con.Open();
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand(sqlRequest, con))
{
SqlCeDataReader reader = com.ExecuteReader();
try
{
if (readAction != null)
{
readAction(reader);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
}
}
}
public IList<EmployeeEntity> FindAll()
{
using (var cn = new SqlCeConnection(constr))
{
cn.Open();
var sql = "SELECT ID , Name , Age , Email FROM Employee";
using (var cmd = new SqlCeCommand(sql, cn))
{
using (var dr = cmd.ExecuteReader())
{
var result = new List<EmployeeEntity>();
while (dr.Read())
{
var rec = new EmployeeEntity()
{
ID = (int)dr["ID"],
Name = (string)dr["Name"],
Age = (int)dr["Age"],
Email = (string)dr["Email"],
};
result.Add(rec);
}
return result;
}
}
}
}
public void ExecuteQuery(SqlCeCommand cmd)
{
if (_isDatabaseAvailable)
try
{
using (var cn = new SqlCeConnection(_conn))
{
cn.Open();
cmd.Connection = cn;
XQuery(cmd.ExecuteReader());
//return cmd.ExecuteReader();
}
}
catch (SqlCeException cex)
{
System.Windows.Forms.MessageBox.Show(cex.ToString());
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
//return null;
}
/// <summary>
/// Gets the product detail by category.
/// </summary>
/// <param name="category">The category.</param>
/// <returns></returns>
public List<ProductDetail> GetProductDetailByCategory(string category)
{
// The results of the query
List<ProductDetail> products = new List<ProductDetail>();
// Build up the query string
const string query = "SELECT * FROM Product WHERE Category = @category";
using (var connection = new SqlCeConnection(_connectionString))
{
connection.Open();
// Build up the SQL command
SqlCeCommand sqlCommand = new SqlCeCommand(query, connection);
sqlCommand.Parameters.AddWithValue("@category", category);
using (SqlCeDataReader sqlCeReader = sqlCommand.ExecuteReader())
{
while (sqlCeReader.Read())
{
// Build up the object
ProductDetail productDetail = new ProductDetail();
productDetail.ProductId = Convert.ToInt32(sqlCeReader["ProductId"]);
productDetail.ProductDescription = sqlCeReader["ProductDescription"] != null ? sqlCeReader["ProductDescription"].ToString() : string.Empty;
productDetail.ImageUrl = sqlCeReader["ImageUrl"] != null ? sqlCeReader["ImageUrl"].ToString() : string.Empty;
productDetail.Category = sqlCeReader["Category"] != null ? sqlCeReader["Category"].ToString() : string.Empty;
// Add to the collection
products.Add(productDetail);
}
}
}
return products;
}
public static void Select()
{
IDbCommand command = new SqlCeCommand();
IDbConnection connection = new SqlCeConnection();
string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\MainDB.sdf";
connection.ConnectionString = "Data Source=" + dbfile;
command.Connection = connection;
command.Connection.Open();
command.Parameters.Clear();
command.CommandText = String.Format(@"
SELECT
[Id],
[Name],
[Score]
FROM [PlayersData]
ORDER BY [Id]
");
IDataReader reader = command.ExecuteReader();
Players.Clear();
while (reader.Read())
{
Players.Add(new Player {Id = (int) reader["Id"], Name = (string) reader["Name"], Score = (int) reader["Score"]});
}
command.Connection.Close();
}
public ETipoAssociado Consultar(int identificador)
{
#region declaração de variáveis
SqlCeConnection cnn = new SqlCeConnection();
SqlCeCommand cmd = new SqlCeCommand();
cnn.ConnectionString = Conexao.Caminho;
cmd.Connection = cnn;
#endregion declaração de variáveis
cmd.CommandText = "SELECT * FROM TipoAssociado WHERE identificador = @identificador";
cmd.Parameters.Add("@identificador", identificador);
cnn.Open();
SqlCeDataReader rdr = cmd.ExecuteReader();
ETipoAssociado _TipoAssociado = new ETipoAssociado();
if (rdr.Read())
{
_TipoAssociado.identificador = int.Parse(rdr["identificador"].ToString());
_TipoAssociado.descricao = rdr["Descricao"].ToString();
}
cnn.Close();
return _TipoAssociado;
}
public List<ETipoAssociado> Listar()
{
#region declaração de variáveis
SqlCeConnection cnn = new SqlCeConnection();
SqlCeCommand cmd = new SqlCeCommand();
cnn.ConnectionString = Conexao.Caminho;
cmd.Connection = cnn;
#endregion declaração de variáveis
cmd.CommandText = "SELECT * FROM TipoAssociado ORDER BY Descricao";
cnn.Open();
SqlCeDataReader rdr = cmd.ExecuteReader();
List<ETipoAssociado> lstRetorno = new List<ETipoAssociado>();
while (rdr.Read())
{
ETipoAssociado _TipoAssociado = new ETipoAssociado();
_TipoAssociado.identificador = int.Parse(rdr["identificador"].ToString());
_TipoAssociado.descricao = rdr["Descricao"].ToString();
lstRetorno.Add(_TipoAssociado);
}
cnn.Close();
return lstRetorno;
}
private void connectionBdd(string query)
{
connexion();
cn.Open();
SqlCeCommand comm = new SqlCeCommand(query, cn);
SqlCeDataReader dr = comm.ExecuteReader();
}
///////////////////
//VILLE
public bool createCity(string name)
{
//Connexion a la bdd
SqlCeConnection ConnectionBdd = new SqlCeConnection(ConnectionString);
try
{
//Ouverture de la connexion
ConnectionBdd.Open();
string query = "INSERT INTO [VILLE] (VILLE_NOM) "
+ "VALUES (" +
"'" + name +
"')";
Console.WriteLine(query);
SqlCeCommand mySqlCommand = new SqlCeCommand(query, ConnectionBdd);
mySqlCommand.ExecuteReader();
Console.WriteLine("--> Nouvelle ville créée : " + name);
ConnectionBdd.Close();
return true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
ConnectionBdd.Close();
return false;
}
}
public IQueryable<Blog> Fetch()
{
var blogs = new List<Blog>();
//const string connectionString = @"Data Source = 'App_Data\TBlogs.sdf'"; Not working even though copied to bin
const string connectionString = @"Data Source = 'C:\Projects\CSharp\Principles\DependecyInversionPrinciple\DependencyInversionPrinciple WithAnIocContainer\App_Data\TBlogs.sdf'";
var connection = new SqlCeConnection(connectionString);
var command = new SqlCeCommand("", connection);
connection.Open();
command.CommandText = "SELECT * FROM Blog;";
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var blog = new Blog
{
Author = dataReader["Author"].ToString(),
Title = dataReader["Title"].ToString(),
Url = dataReader["Url"].ToString(),
Email = dataReader["Email"].ToString()
};
blogs.Add(blog);
}
return blogs.AsQueryable();
}
public static int GetLocationCheckProductID(string locationCode, string createdBy)
{
int id = 0;
using (SqlCeConnection con = new SqlCeConnection(SqlHelper.SqlCeConnectionString))
{
con.Open();
using (SqlCeCommand com = new SqlCeCommand(SqlHelper.GetSql(27), con))
{
com.Parameters.AddWithValue("@LocationCode", locationCode);
com.Parameters.AddWithValue("@CreatedBy", createdBy);
using (var reader = com.ExecuteReader())
{
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
}
}
return(id);
}
public Dictionary <string, decimal> GetCurrentCreditCard()
{
Dictionary <string, decimal> currentCashAndBank = new Dictionary <string, decimal>();
using (var conn = new SqlCeConnection(Store.ConnectionString))
{
conn.Open();
string sql = "SELECT Name, Balance FROM Accounts"
+ " WHERE Type='CreditCard' ORDER BY Balance DESC";
var cmd = new SqlCeCommand(sql, conn);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
currentCashAndBank.Add((string)rdr["Name"], (decimal)rdr["Balance"]);
}
}
}
return(currentCashAndBank);
}
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 Detalles_credito_cliente GetDetallesCredito(string id_venta)
{
Detalles_credito_cliente detalle = 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 detalles_credito_cliente WHERE id_venta='" + id_venta + "';";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
detalle = new Detalles_credito_cliente(
int.Parse(reader["id_cliente"].ToString()),
reader["id_venta"].ToString()
);
}
return(detalle);
}
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);
}
private void LoggedUserCheck()
{
SqlCeConnection sqlConnection = new SqlCeConnection();
sqlConnection.ConnectionString = Auth.ConnectionDBString;
SqlCeCommand sqlCommand = new SqlCeCommand();
sqlCommand.CommandType = System.Data.CommandType.Text;
sqlCommand.CommandText = "select * from Users where username='******'";
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
SqlCeDataReader rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
tbUsername.Text = rd.GetValue(0).ToString();
tbNames.Text = rd.GetValue(1).ToString();
tbPassword.Text = rd.GetValue(2).ToString();
tbAdmin.Text = rd.GetValue(3).ToString();
}
sqlConnection.Close();
}
//End of year changes. made and sold YTD = 0. 1231 = current onhand.
private void button2_Click(object sender, EventArgs e)
{
var result = MessageBox.Show("Are you sure you wish to run end of year?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
conn.Open();
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Table2", conn);
SqlCeCommand cmd2 = new SqlCeCommand("UPDATE Table2 SET madeYTD = 0, soldYTD = 0, onHand1231 = @1 WHERE barcode = @2", conn);
SqlCeDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
cmd2.Parameters.AddWithValue("@1", rdr.GetInt32(1).ToString());
cmd2.Parameters.AddWithValue("@2", rdr.GetString(0));
cmd2.ExecuteNonQuery();
cmd2.Parameters.Clear();
}
reloadGrid();
conn.Close();
MessageBox.Show("End of year executed successfully.");
}
}
public static bool CheckUnique(string UserName)
{
bool flag = false;
try
{
SqlCeCommand command = new SqlCeCommand(@"SELECT UserName FROM Users
WHERE @usname = UserName", Connection);
command.Parameters.AddWithValue("@usname", UserName);
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read() && reader["UserName"].ToString() == UserName)
{
flag = true;
MessageBox.Show("Korisničko ime " + "'" + UserName + "'" + " već postoji !");
}
}
catch (Exception ex)
{
flag = false;
MessageBox.Show(ex.Message);
}
return(flag);
}
public IList <Station> FetchStations()
{
IList <Station> stanice = new List <Station>();
using (var com = new SqlCeCommand("SELECT * FROM Stanice", _connection))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
var id = reader.GetInt32(0);
var alt = (double)reader.GetDecimal(1);
var lon = (double)reader.GetDecimal(2);
var name = reader.GetString(3);
var direction = reader.GetString(4);
stanice.Add(new Station(alt, lon, name, direction)
{
Id = id
});
}
}
return(stanice);
}
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);
}
private void init_db()
{
try
{
dtp.Clear();
string SQL = "SELECT * FROM prod_resent WHERE [For Product] = '" + mainprod + "' AND [For Bulk] = '" + subprod + "'";
if (Main.Amatrix.mgt == "")
{
SqlCeConnection conn = new SqlCeConnection(Properties.Settings.Default.Amdtbse_4ConnectionString);
SqlCeCommand cmd = new SqlCeCommand(SQL, conn);
conn.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
dtp.Load(dr);
conn.Close();
}
else
{
dtp = basql.Execute(Main.Amatrix.mgt, SQL, "prod_resent", dtp);
}
dataGridView1.DataSource = dtp;
}
catch (Exception erty) { }
}
public IList <DepartureTimeContext> FetchDepartureTimeContextList()
{
IList <DepartureTimeContext> contextList = new List <DepartureTimeContext>();
using (var com = new SqlCeCommand("SELECT * FROM VrijemePolaska", _connection))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
var id = reader.GetInt32(0);
var stanicaId = reader.GetInt32(1);
var voziloId = reader.GetInt32(2);
var vrijeme = reader.GetString(3);
var dan = reader.GetString(4);
contextList.Add(new DepartureTimeContext(voziloId, stanicaId, vrijeme, dan)
{
Id = id
});
}
}
return(contextList);
}
public static TFabricante GetTFabricante(int id, SqlCeConnection conn)
{
TFabricante ta = null;
string sql = String.Format("SELECT * FROM Fabricante WHERE fabricante_id={0}", id);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TFabricante();
ta.FabricanteId = dr.GetInt32(0);
ta.Nombre = dr.GetString(2);
ta.Abm = dr.GetByte(3);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
public static TInstalacion GetTInstalacion(int id, SqlCeConnection conn)
{
TInstalacion ta = null;
string sql = String.Format("SELECT * FROM Instalacion WHERE instalacion_id={0}", id);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TInstalacion();
ta.InstalacionId = dr.GetInt32(0);
ta.Nombre = dr.GetString(1);
ta.Abm = dr.GetByte(2);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
public List <Correo> GetCorreos()
{
List <Correo> correo = new List <Correo>();
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;";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
correo.Add(new Correo(
int.Parse(reader["id_correo"].ToString()),
reader["correo"].ToString(),
int.Parse(reader["predeterminado"].ToString())
));
}
return(correo);
}
/// <summary>
/// Charge la liste des cibles de précision du tireur
/// </summary>
private void ChargerCiblePrecision()
{
m_colCiblePrecision.Clear();
using (SqlCeConnection con = new SqlCeConnection(BaseDeDonnee.InfoConnexion))
{
con.Open();
using (SqlCeCommand com = new SqlCeCommand("SELECT * FROM ciblePrecision WHERE idTireur = @idTireur ORDER BY dateTir DESC", con))
{
com.Parameters.AddWithValue("@idTireur", m_id);
SqlCeDataReader objReader = com.ExecuteReader();
while (objReader.Read())
{
int idCible = objReader.GetInt32(0);
int idTireur = objReader.GetInt32(1);
float score = (float)objReader.GetDouble(2);
DateTime dateTir = objReader.GetDateTime(3);
m_colCiblePrecision.Add(new CiblePrecision(idCible, idTireur, score, dateTir));
}
}
}
}
public List <Codigos_generados> GetCodigo()
{
List <Codigos_generados> codigo = new List <Codigos_generados>();
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 codigosGenerados;";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
codigo.Add(new Codigos_generados(
reader["codigo"].ToString(),
reader["descripcion"].ToString(),
reader["ruta"].ToString()));
}
return(codigo);
}
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);
}
public static bool CheckUnique(string SectionName)
{
bool flag = false;
try
{
SqlCeCommand command = new SqlCeCommand(@"SELECT Id FROM Sections
WHERE @description = Description", Connection);
command.Parameters.AddWithValue("@description", SectionName);
SqlCeDataReader reader = command.ExecuteReader();
if (reader.Read() && (int)reader["Id"] > 0)
{
flag = true;
MessageBox.Show("Smjer : " + "'" + SectionName + "'" + " već postoji !");
}
}
catch (Exception ex)
{
flag = false;
MessageBox.Show(ex.Message);
}
return(flag);
}
public Codigos_generados GetCodigo(Codigos_generados temp)
{
Codigos_generados codigo = new Codigos_generados();
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 codigosGenerados WHERE codigo='" + temp.codigo + "' AND descripcion='" + temp.descripcion + "';";
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
codigo = new Codigos_generados(
reader["codigo"].ToString(),
reader["descripcion"].ToString(),
reader["ruta"].ToString());
}
return(codigo);
}
//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 TModeloDispositivo GetTModeloDispositivo(int id, SqlCeConnection conn)
{
TModeloDispositivo ta = null;
string sql = String.Format("SELECT * FROM ModeloDispositivo WHERE modelo_id={0}", id);
using (SqlCeCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ta = new TModeloDispositivo();
ta.ModeloDispositivoId = dr.GetInt32(0);
ta.Nombre = dr.GetString(1);
ta.Abm = dr.GetByte(2);
}
if (!dr.IsClosed)
{
dr.Close();
}
}
return(ta);
}
//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 Tuple <LinkedList <int>, LinkedList <String>, LinkedList <String> > patientConditions(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));
}
private void FinalInvoice_Load(object sender, EventArgs e)
{
dboperation operation = new dboperation();
SqlCeConnection conn = operation.dbConnection(Settings.Default.DatabasePath);
string query = "SELECT prefix FROM regdetails WHERE Id=1";
SqlCeCommand cmd = new SqlCeCommand(query, conn);
SqlCeDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
invoiceprefix = reader.GetString(0);
}
}
catch (Exception ex)
{
MessageBox.Show("" + ex);
}
calculation();
}
//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));
}
public static TTipoAnomalia GetTTipoAnomalia(string nombre, SqlCeConnection conn)
{
TTipoAnomalia ta = null;
string sql = String.Format("SELECT * FROM TipoAnomalia WHERE nombre='{0}'", nombre);
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);
}
private void init()
{
if (radioButton2.Checked != true)
{
files_dtst.Clear();
SqlCeConnection conn = new SqlCeConnection(filesTableAdapter.Connection.ConnectionString);
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Files WHERE [For App] = '" + From_App + "' AND [For Serial] = '" + Serial + "'", conn);
conn.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
files_dtst.Files.Load(dr);
dataGridView1.DataSource = files_dtst.Files;
conn.Close();
}
else
{
try
{
dr_univ = Quer("SELECT * FROM Files WHERE [For App] = '" + From_App + "' AND [For Serial] = '" + Serial + "'");
files_dtst.Files.Load(dr_univ);
}
catch (Exception erty) { Am_err ner = new Am_err(); ner.tx("The Server Was Not Found"); radioButton1.Checked = true; radioButton2.Enabled = false; }
}
}
public Category GetByNameAndType(string name, string type)
{
Category category = null;
using (var conn = new SqlCeConnection(Store.ConnectionString))
{
conn.Open();
string sql = "SELECT * FROM " + tableName + " WHERE Name=@Name AND Type=@Type";
var cmd = new SqlCeCommand(sql, conn);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = name;
cmd.Parameters.Add("@Type", SqlDbType.NVarChar).Value = type;
using (var rdr = cmd.ExecuteReader())
{
category = Mapper.MapObject <Category>(rdr, new CategoryMapper());
}
}
return(category);
}
public static List <double> Load(string databaseFile, string tableName)
{
List <double> coefs = new List <double>();
using (SqlCeConnection conn = new SqlCeConnection("Data Source=" + databaseFile))
{
SqlCeCommand cmd = new SqlCeCommand("Select * from " + tableName + " order by SValue", conn);
conn.Open();
SqlCeDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
coefs.Add(double.Parse(dr[4].ToString()));
coefs.Add(double.Parse(dr[0].ToString()));
coefs.Add(double.Parse(dr[1].ToString()));
coefs.Add(double.Parse(dr[2].ToString()));
coefs.Add(double.Parse(dr[3].ToString()));
}
dr.Close();
conn.Close();
}
return(coefs);
}
