public Image GetLogo(int codEmp)
{
string sql = "";
Image LogoEmp = null;
try
{
sql = "SELECT logoEmp FROM EMPRESA where EmpresaId = " + codEmp;
SqlCeConnection conexao = new SqlCeConnection(Funcoes.Busca_Conexao());
SqlCeCommand cmd = new SqlCeCommand(sql, conexao);
conexao.Open();
SqlCeDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (!string.IsNullOrEmpty(rd.GetValue(0).ToString()))
{
LogoEmp = Funcoes.GetImage((byte[])rd.GetValue(0));
}
}
conexao.Close();
return(LogoEmp);
}
catch (Exception ex)
{
throw ex;
}
}
private void populateControlsWithData(SqlCeDataReader reader)
{
int customerID = reader.GetInt32(reader.GetOrdinal("CustomerID"));
customerNameCombo.SelectedValue = customerID;
paymentDatePicker.Value = reader.GetDateTime(reader.GetOrdinal("PaymentDate"));
decimal amount = reader.GetDecimal(reader.GetOrdinal("Amount"));
amountField.Text = amount.ToString("N2");
string paymentMode = reader.GetString(reader.GetOrdinal("PaymentMode"));
switch (paymentMode)
{
case "C":
cashButton.Checked = true;
break;
case "Q":
chequeButton.Checked = true;
break;
case "D":
demandDraftButton.Checked = true;
break;
}
object value = reader.GetValue(reader.GetOrdinal("InstrumentNumber"));
if (value != DBNull.Value)
{
instrumentNumberField.Text = (string)value;
}
value = reader.GetValue(reader.GetOrdinal("Notes"));
if (value != DBNull.Value)
{
notesField.Text = (string)value;
}
string errorText;
decimal?balanceAmount = GlobalMethods.GetCustomerBalance(customerID,
out errorText);
if (!balanceAmount.HasValue)
{
string message = "An error occurred in fetching the customer's balance from the database."
+ "\nThe error text is as follows:\n" + errorText;
Cursor.Current = Cursors.Default;
SystemSounds.Exclamation.Play();
MessageBox.Show(message, "Error in Fetching Account Balance", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation);
return;
}
balanceAmount = balanceAmount.Value - amount;
balanceField.Text = GlobalMethods.GetBalanceAsString(balanceAmount.Value);
}
public Image GetImagePro(int codPro)
{
string sql = "";
Image Imagem = null;
try
{
sql = "SELECT ImagProd FROM PRODUTO where ProdutoId = " + codPro;
SqlCeConnection conexao = new SqlCeConnection(Funcoes.Busca_Conexao());
SqlCeCommand cmd = new SqlCeCommand(sql, conexao);
conexao.Open();
SqlCeDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
if (!string.IsNullOrEmpty(rd.GetValue(0).ToString()))
{
Imagem = Funcoes.GetImage((byte[])rd.GetValue(0));
}
}
conexao.Close();
return(Imagem);
}
catch (Exception ex)
{
throw ex;
}
}
public ReportSpecific(int studNo)
{
InitializeComponent();
this.studNo = studNo;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName + ', ' + FirstName + ' ' + COALESCE(MiddleName, '') AS [Full Name], ResidenceStatus, CounterProbi FROM StudentInfo WHERE StudentNo = @studentNo", conn))
{
cmd.Parameters.AddWithValue("@studentNo", studNo);
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.Read())
{
int fullNameIndex = reader.GetOrdinal("Full Name");
fullName = Convert.ToString(reader.GetValue(fullNameIndex));
int residenceIndex = reader.GetOrdinal("ResidenceStatus");
residence = Convert.ToString(reader.GetValue(residenceIndex));
int probiCountIndex = reader.GetOrdinal("CounterProbi");
probiCount = Convert.ToInt32(reader.GetValue(probiCountIndex));
}
}
}
txtStudNo.Text = studNo.ToString();
txtResidence.Text = residence;
txtFullName.Text = fullName;
txtProb.Text = AddOrdinal(probiCount);
updateListView();
}
private void txtName_TextChanged(object sender, TextChangedEventArgs e)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName, firstName from StudentInfo WHERE (LastName LIKE @lastName ) or (firstName LIKE @firstName)", conn))
{
cmd.Parameters.AddWithValue("@lastName", txtLastName.Text + "%");
cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text + "%");
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
lvListStudent.Items.Clear();
i = 1;
if (reader.HasRows)
{
while (reader.Read())
{
int lastNameIndex = reader.GetOrdinal("LastName");
string lastName = Convert.ToString(reader.GetValue(lastNameIndex));
int firstNameIndex = reader.GetOrdinal("firstName");
string firstName = Convert.ToString(reader.GetValue(firstNameIndex));
lvListStudent.Items.Add(new ListViewSearchStudent
{
i = this.i,
LastName = lastName,
FirstName = firstName
});
i++;
}
}
}
}
conn.Close();
}
public void BackgroundChanger()
{
LinearGradientBrush gradient = new LinearGradientBrush();
gradient.StartPoint = new System.Windows.Point(0.5, 0);
gradient.EndPoint = new System.Windows.Point(0.5, 1);
var primaire = "";
try
{
SqlCeCommand cmd = new SqlCeCommand("SELECT PrimaireColor FROM Apps WHERE Name='" + NameExe.Content.ToString() + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetValue(0).ToString() != "")
{
primaire = reader.GetValue(0).ToString();
}
}
}
finally
{
con.Close();
}
var PictoColor = "#FF85C1F5";
if (primaire.ToString() != "")
{
PictoColor = "#" + primaire;
}
System.Windows.Media.Color color = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString(PictoColor.ToString());
System.Windows.Media.Color colorEnd = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString("#003D3D3D");
GradientStop Start = new GradientStop();
Start.Color = color;
Start.Offset = 0;
GradientStop End = new GradientStop();
End.Color = colorEnd;
End.Offset = 0.75;
gradient.GradientStops.Add(Start);
gradient.GradientStops.Add(End);
Main.Background = gradient;
Storyboard sb = (Storyboard)FindResource("NewGradientColor");
sb.Begin(this);
}
private Applicant GetFromReader(SqlCeDataReader reader)
{
var a = new Applicant
{
ap_id_10023 = Convert.ToInt32(reader.GetValue(0)),
ap_name_10023 = reader.GetValue(1).ToString(),
ap_score_10023 = Convert.ToInt32(reader.GetValue(2)),
ap_tests_taken_10023 = reader.GetValue(3).ToString()
};
return(a);
}
private void updateRecords()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
borrowers.Clear();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT bl.prodCode, ai.name, ai.manuf, bl.qty, bl.breakage, ai.size from BorrowerList bl INNER JOIN ApparatusInventory ai on bl.prodCode = ai.prodCode where bl.studentNo = @studentNo and bl.groupID = @grpID and bl.lockNo = @lockNo and bl.subject = @subj and bl.expName = @experiment and bl.dateReq = @dateReq and bl.dateExp = @dateExp", conn))
{
cmd.Parameters.AddWithValue("@studentNo", studentNo);
cmd.Parameters.AddWithValue("@subj", txtSubj.Text);
cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text);
cmd.Parameters.AddWithValue("@experiment", txtExpName.Text);
cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text);
cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text);
cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text);
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
while (reader.Read())
{
int nameIndex = reader.GetOrdinal("name");
string name = Convert.ToString(reader.GetValue(nameIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
int breakageIndex = reader.GetOrdinal("breakage");
bool breakage = Convert.ToBoolean(reader.GetValue(breakageIndex));
borrowers.Add(new LVBorrower
{
breakage = breakage,
prodName = name,
manuf = manuf,
qty = qty,
size = size
});
}
}
else
{
this.NavigationService.Navigate(new BorrowersList());
}
}
}
}
//Get Reader from
private Applicant GetFromReader(SqlCeDataReader reader)
{
var a = new Applicant
{
Id = Convert.ToInt32(reader.GetValue(0)),
Name = reader.GetValue(1).ToString(),
Score = Convert.ToInt32(reader.GetValue(2)),
Test = reader.GetValue(3).ToString()
};
return(a);
}
// private void LoadOneProduct( int intProductID)
// {
// // Append the desired ProductID to the SELECT statement.
// string strSQL = strGetOneProduct + intProductID;
//
// // A connection, a command, and a reader.
// SqlCeConnection connDB = new SqlCeConnection(strConn);
// SqlCeCommand cmndDB = new SqlCeCommand();
// SqlCeDataReader drdrDB;
//
// // Open the connection.
// connDB.Open();
//
// // Submit the SQL statement and receive
// // the SqlCeReader for the one-row
// // results set.
// drdrDB = cmndDB.ExecuteReader();
//
// // Read the first (only) row.
// // Display it. Close the reader.
// if ( drdrDB.Read() )
// {
// LoadControlsFromRow(drdrDB);
// }
// drdrDB.Close();
//
// // Close the connection.
// connDB.Close();
// }
private void LoadControlsFromRow(SqlCeDataReader drdrDB)
{
// Transfer the colum titles and the field
// contents of the current row from the
// reader to the form//s controls.
lblProductID.Text = drdrDB.GetName(0);
textProductID.Text = drdrDB.GetValue(0).ToString();
lblProductName.Text = drdrDB.GetName(1);
textProductName.Text = drdrDB.GetValue(1).ToString();
lblCategoryName.Text = drdrDB.GetName(2);
textCategoryName.Text = drdrDB.GetValue(2).ToString();
}
private bool loadBillData(SqlCeConnection connection)
{
try
{
using (SqlCeCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM BILLMASTER WHERE ID = " + billToEdit.Value;
using (SqlCeDataReader reader = command.ExecuteReader())
{
reader.Read();
billDateField.Value = reader.GetDateTime(reader.GetOrdinal("BillDate"));
object val = reader.GetValue(reader.GetOrdinal("CustomerID"));
if (val == DBNull.Value)
{
cashBillButton.Checked = true;
}
else
{
creditBillButton.Checked = true;
customerNameField.SelectedValue = (int)val;
}
decimal amount = reader.GetDecimal(reader.GetOrdinal("DiscountAmount"));
discountField.Text = amount.ToString("N2");
amount = reader.GetDecimal(reader.GetOrdinal("ExpenseAmount"));
expenseAmountField.Text = amount.ToString("N2");
val = reader.GetValue(reader.GetOrdinal("ExpenseText"));
if (val != DBNull.Value)
{
expenseTextField.Text = (string)val;
}
}
}
loadBillDetails(connection);
}
catch (Exception ex)
{
string message = "An error occurred in loading the bill data. \nThe error text is as follows:\n"
+ Global.getExceptionText(ex);
SystemSounds.Hand.Play();
Cursor.Current = Cursors.Default;
MessageBox.Show(message, "Error in Loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
ErrorLogger.LogError(ex);
return(false);
}
return(true);
}
private Sprint GetFromReader(SqlCeDataReader reader)
{
var s = new Sprint
{
ID = Convert.ToInt32(reader.GetValue(0)),
Name = reader.GetValue(1).ToString(),
Date = Convert.ToDateTime(reader.GetValue(2)),
NumberOfDevelopers = Convert.ToInt32(reader.GetValue(3)),
Status = reader.GetValue(4).ToString()
};
return(s);
}
private void searchUser_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)
{
if (string.IsNullOrEmpty(txtUser.Text))
{
MessageBox.Show("Username field is empty!");
txtUser.Focus();
}
else
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("Select COUNT(1) from Accounts where username = @username", conn))
{
cmd.Parameters.AddWithValue("@username", txtUser.Text);
int userCount;
userCount = (int)cmd.ExecuteScalar();
if (userCount > 0)
{
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * from Accounts where username = @username", conn))
{
cmd1.Parameters.AddWithValue("@username", txtUser.Text);
using (SqlCeDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
if (reader.HasRows)
{
reader.Read();
int firstNameIndex = reader.GetOrdinal("firstName");
string firstName = Convert.ToString(reader.GetValue(firstNameIndex));
int lastNameIndex = reader.GetOrdinal("lastName");
string lastName = Convert.ToString(reader.GetValue(lastNameIndex));
int securityQuestionIndex = reader.GetOrdinal("securityQuestion");
string securityQuestion = Convert.ToString(reader.GetValue(securityQuestionIndex));
txtFirstName.Text = firstName;
txtLastName.Text = lastName;
cmbQuestion.Text = securityQuestion;
}
}
}
}
else
{
MessageBox.Show("User does not exist!");
}
}
}
}
private void PopulateList()
{
String query = "SELECT * FROM " + db.TableName + " ORDER BY date DESC";
using (SqlCeConnection c = new SqlCeConnection(DataSource))
{
c.Open();
using (SqlCeCommand cmd = new SqlCeCommand(query, c))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
String id = "" + reader.GetInt32(0);
String rupee = "" + reader.GetValue(1);
String dateTime = "" + reader.GetDateTime(2);
ListAdd(id, rupee, dateTime);
}
reader.Close();
}
}
c.Close();
}
}
private void btn_write_to_file_Click(object sender, EventArgs e)
{
StringBuilder b = new StringBuilder();
String query = "SELECT * FROM " + db.TableName;
using (SqlCeConnection c = new SqlCeConnection(DataSource))
{
c.Open();
using (SqlCeCommand cmd = new SqlCeCommand(query, c))
{
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
b.AppendLine("" + reader.GetValue(1));
}
reader.Close();
}
}
c.Close();
}
api.FileWrite("currencyList", b.ToString());
}
private void WriteTable(string directoryPath, string tableName, Action <string> updateStatus)
{
int count = 0;
int total = ExecuteScalar <int>($"SELECT COUNT(*) FROM {tableName}");
using (StreamWriter writter = new StreamWriter($"{directoryPath}/{tableName}.CSV")) {
using (SqlCeCommand command = CreateCommand($"SELECT * FROM {tableName}")) {
using (SqlCeDataReader dataReader = command.ExecuteReader()) {
IEnumerable <int> fieldRange = Enumerable.Range(0, dataReader.FieldCount);
CsvWriter.WriteRow(writter, fieldRange.Select(i => dataReader.GetName(i).ToLower()).ToArray());
while (dataReader.Read())
{
updateStatus($"Exportando tabela \"{tableName}\" - linha {++count} de {total}.");
CsvWriter.WriteRow(
writter,
fieldRange.Select(i => dataReader.GetValue(i))
.Select(i => {
if (i is bool)
{
return(((bool)i) ? "1" : "0");
}
else
{
return(i.ToString());
}
})
.ToArray()
);
}
}
};
}
}
public static bool LoggedUserCheckPermission()
{
SqlCeConnection sqlConnection = new SqlCeConnection();
sqlConnection.ConnectionString = 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();
rd.Read();
if (rd.GetValue(3).ToString() == "Admin")
{
return(true);
}
else
{
return(false);
}
rd.Close();
sqlConnection.Close();
}
/// <summary>
/// vérifier le donnée a déjà exist dans la base de données
/// </summary>
/// <param name="Table">La table</param>
/// <param name="Colonne">Le colonne de la table</param>
/// <param name="Value">La valeur du colonne</param>
/// <returns>bool</returns>
private bool DonneeIsExist(string Table, string Colonne, string Value)
{
CmdRecherche = ConnSql.CreateCommand();
//Rechercher ID familles en relation dans table "Familles"
CmdRecherche.CommandText = "SELECT COUNT( " + Colonne + " ) FROM " + Table + " WHERE " + Colonne + "=@Value;";
CmdRecherche.Parameters.AddWithValue("@Value", Value);
CmdRecherche.ExecuteNonQuery();
RdrReche = CmdRecherche.ExecuteReader();
try
{
int Count = -1;
if (RdrReche.Read())
{
Count = (int)RdrReche.GetValue(0);
}
if (Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Console.Out.WriteLine(ex.Message);
return(false);
}
}
public List <int> amountPerson(string table)
{
string source = "Data Source=\"PersonsDB.sdf\"; password=\"qwerty\"";
con = new SqlCeConnection(source);
con.Open();
var f = @"Select COUNT(*) From " + table + " Where ДатаПриема <= @Data and ДатаУвольнения is NULL";
cmd = new SqlCeCommand(f, con);
cmd.Parameters.AddWithValue("@Data", SqlDbType.DateTime).Value = DateTime.Today.ToString("d");
SqlCeDataReader reader = cmd.ExecuteReader();
var numbers = new List <int>();
bool hasRow = reader.Read();
if (hasRow)
{
numbers.Add(Convert.ToInt32(reader.GetValue(0)));
}
reader.Close();
return(numbers);
}
private void SettingsItem_Click(object sender, RoutedEventArgs e)
{
if (SettingsItem.Opacity != 0 && NameExe.Content.ToString() != "")
{
try
{
SqlCeCommand cmd = new SqlCeCommand("SELECT Id FROM Apps WHERE Name='" + NameExe.Content.ToString() + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetValue(0).ToString() != "")
{
Properties.Settings.Default.ExId = reader.GetInt32(0);
}
}
}
finally
{
con.Close();
}
try
{
Properties.Settings.Default.ExNa = NameExe.Content.ToString();
Properties.Settings.Default.Save();
Settings settings = new Settings();
settings.Owner = this;
settings.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (Properties.Settings.Default.Saved == true)
{
NameExe.Content = "";
IconExe.Source = null;
list();
Button button = new Button();
button.Name = "A" + Properties.Settings.Default.ExId.ToString();
button.Click += Button_Click;
button.RaiseEvent(new RoutedEventArgs(Button.ClickEvent));
}
Properties.Settings.Default.Saved = false;
Properties.Settings.Default.ExId = 0;
Properties.Settings.Default.ExNa = "";
Properties.Settings.Default.Save();
}
}
}
private void selectedUserCheck()
{
sqlConnection.ConnectionString = Auth.ConnectionDBString;
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();
}
private void RefreshUsers()
{
listboxUsers.Items.Clear();
listboxInfo.Items.Clear();
sqlConnection.ConnectionString = Auth.ConnectionDBString;
sqlCommand.CommandType = System.Data.CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM Users";
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
SqlCeDataReader rd = sqlCommand.ExecuteReader();
while (rd.Read())
{
listboxUsers.Items.Add(rd.GetValue(0).ToString());
listboxInfo.Items.Add("Names: " + rd.GetValue(1).ToString() + " ,Password: "******" ,Permission: " + rd.GetValue(3).ToString());
}
sqlConnection.Close();
}
private void btnRefresh_Click(object sender, EventArgs e)
{
string SqlCeQuery = "SELECT * FROM tblUsers";
SqlCeCommand cm = new SqlCeCommand(SqlCeQuery, cn);
SqlCeDataReader dr = cm.ExecuteReader();
lbId.Items.Clear();
lbuserName.Items.Clear();
lbPass.Items.Clear();
lbEmail.Items.Clear();
while (dr.Read())
{
lbuserName.Items.Add(dr.GetValue(0).ToString());
lbPass.Items.Add(dr.GetValue(2).ToString());
lbEmail.Items.Add(dr.GetValue(3).ToString());
lbId.Items.Add(dr.GetValue(4).ToString());
}
}
private void ClAd_Click(object sender, RoutedEventArgs e)
{
try
{
SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT Class FROM Apps WHERE Class = '" + ClNa.Text + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetValue(0).ToString() != "")
{
exists = true;
}
}
}
finally
{
con.Close();
}
if ((bool)exists == false)
{
try
{
SqlCeCommand cmd = new SqlCeCommand("UPDATE Apps SET Class = '" + ClNa.Text + "' WHERE Id = '" + ExId + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
con.Close();
Start();
ClNa.Text = "";
}
Properties.Settings.Default.Saved = true;
Properties.Settings.Default.Save();
}
else
{
MessageBox.Show("This class already exists. Please type a different name.");
exists = false;
}
}
public string DoQuery(string query, bool ret)
{
if (!ret)
{
try
{
using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\Андрей свали с компа\Desktop\myDB.sdf"))
{
conn.Open();
using (SqlCeCommand c = new SqlCeCommand(query))
{
c.Connection = conn;
c.ExecuteNonQuery();
}
}
return("Execution completed sucsessful");
}
catch (Exception exp)
{
return("Excecution error: " + exp.Message);
}
}
else
{
try
{
string s = "";
using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\Андрей свали с компа\Desktop\myDB.sdf"))
{
conn.Open();
using (SqlCeCommand c = new SqlCeCommand(query))
{
c.Connection = conn;
SqlCeDataReader sql = c.ExecuteReader();
while (sql.Read())
{
for (int i = 0; i < sql.FieldCount; i++)
{
s += sql.GetValue(i).ToString() + " ";
}
s += Environment.NewLine;
}
sql.Close();
}
}
return(s);
}
catch (Exception exp)
{
return("Excecution error: " + exp.Message);
}
}
}
private void save_Click(object sender, RoutedEventArgs e)
{
try
{
SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Apps WHERE Name = '" + NewName.Text + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCeDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader.GetValue(2).ToString() != "")
{
exists = true;
}
}
}
finally
{
con.Close();
}
if ((bool)exists == false || NewName.Text == ExNa)
{
try
{
SqlCeCommand cmd = new SqlCeCommand("UPDATE Apps SET Name = '" + NewName.Text.ToString() + "', Class = '" + Class.SelectedItem.ToString() + "' WHERE Id = '" + ExId + "';");
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
con.Close();
}
Properties.Settings.Default.Saved = true;
Properties.Settings.Default.Save();
this.Close();
}
else
{
MessageBox.Show("This name is already used. Please choose a different.");
}
exists = false;
}
private void updateListView()
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory", conn))
{
summary.Clear();
using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
{
while (reader.Read())
{
int prodCodeIndex = reader.GetOrdinal("prodCode");
string prodCode = Convert.ToString(reader.GetValue(prodCodeIndex));
int inventNameIndex = reader.GetOrdinal("name");
string inventName = Convert.ToString(reader.GetValue(inventNameIndex));
int manufIndex = reader.GetOrdinal("manuf");
string manuf = Convert.ToString(reader.GetValue(manufIndex));
int qtyIndex = reader.GetOrdinal("qty");
int qty = Convert.ToInt32(reader.GetValue(qtyIndex));
int sizeIndex = reader.GetOrdinal("size");
string size = Convert.ToString(reader.GetValue(sizeIndex));
summary.Add(new LVOutstanding
{
i = i,
prodCode = prodCode,
inventName = inventName,
manuf = manuf,
qty = qty.ToString(),
size = size,
});
i++;
}
}
}
}
/* private bool executaComando()
* {
* string localBD = PegaCaminhoBD();
* using (SqlCeConnection conexao = new SqlCeConnection("Data Source="+localBD))
* {
* SqlCeCommand comando;
* conexao.Open();
* comando.Connection = conexao;
* if (comando.ExecuteNonQuery() > 0)
* {
* return true;
* }
* else
* {
* return false;
* }
* }
* }*/
public Usuarios VerificaLogin(string usuario, string senha)
{
string localBD = PegaCaminhoBD();
Usuarios ListaDeUsuarios = null;
using (SqlCeConnection conexao = new SqlCeConnection("Data Source=" + localBD))
{
SqlCeCommand comando = new SqlCeCommand("SELECT * FROM Usuarios WHERE Login = @login AND Senha = @senha");
comando.Parameters.AddWithValue("login", usuario);
comando.Parameters.AddWithValue("senha", senha);
conexao.Open();
comando.Connection = conexao;
SqlCeDataReader leitor = comando.ExecuteReader();
while (leitor.Read())
{
ListaDeUsuarios = new Usuarios {
Id = (int)leitor.GetValue(0),
Nome = leitor.GetValue(1).ToString(),
Cpf = leitor.GetValue(2).ToString(),
Login = leitor.GetValue(3).ToString(),
Senha = leitor.GetValue(4).ToString(),
Acesso = leitor.GetValue(5).ToString()
};
}
leitor.Close();
}
return(ListaDeUsuarios);
}
public void refreshContacts()
{
listboxUsers.Items.Clear();
listboxPass.Items.Clear();
listboxNames.Items.Clear();
listboxEmail.Items.Clear();
listboxAdmin.Items.Clear();
conn.ConnectionString = ConnectionDBString;
cmd = new SqlCeCommand("SELECT * FROM UsersCEDB", conn);
conn.Open();
SqlCeDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
listboxUsers.Items.Add(rd.GetValue(0).ToString());
listboxNames.Items.Add(rd.GetValue(1).ToString());
listboxPass.Items.Add(rd.GetValue(2).ToString());
listboxEmail.Items.Add(rd.GetValue(3).ToString());
if (rd.GetValue(4).ToString() == "2")
{
listboxAdmin.Items.Add("Admin");
}
else if (rd.GetValue(4).ToString() == "1")
{
listboxAdmin.Items.Add("Member");
}
else
{
listboxAdmin.Items.Add("Public");
}
}
conn.Close();
}
private void btnSpeGenReport_Click(object sender, RoutedEventArgs e)
{
SqlCeConnection conn = DBUtils.GetDBConnection();
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(1) from StudentInfo WHERE (lastName = @LastName) and (firstName = @firstName)", conn))
{
cmd.Parameters.AddWithValue("@lastName", txtLastName.Text);
cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text);
if (string.IsNullOrEmpty(txtLastName.Text) && string.IsNullOrEmpty(txtFirstName.Text))
{
MessageBox.Show("No user input!");
}
else if (string.IsNullOrEmpty(txtLastName.Text))
{
MessageBox.Show("Please fill up the missing fields!");
txtLastName.Focus();
}
else if (string.IsNullOrEmpty(txtFirstName.Text))
{
MessageBox.Show("Please fill up the missing fields!");
txtFirstName.Focus();
}
else
{
int studCount;
studCount = (int)cmd.ExecuteScalar();
if (studCount > 0)
{
using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT StudentNo from StudentInfo WHERE (lastName = @LastName) and (firstName = @firstName)", conn))
{
cmd1.Parameters.AddWithValue("@lastName", txtLastName.Text);
cmd1.Parameters.AddWithValue("@firstName", txtFirstName.Text);
using (SqlCeDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable))
{
reader.Read();
int studNo = Convert.ToInt32(reader.GetValue(0));
ReportSpecific rs = new ReportSpecific(studNo);
rs.studNo = studNo;
rs.ShowDialog();
}
}
}
else
{
MessageBox.Show("Student does not exist!");
}
}
}
conn.Close();
}
void ActivateNewDeal(int newline, int level, bool loadFromDB, SqlCeDataReader sqlReader)
{
// ----------- 1. Создание сдачи в БД / загрузка сдачи из БД + REFLECT --------------
if (!loadFromDB)
{
// !!! Выполнить инициализацию данных, помеченных в REFLECT_Depends со значением {-1}
// !!! Обычно это зона и порядковые номера
// !!! При sub-deal для 1 стола -1, для 2 стола -2 и т.д.
REFLECT_DEPENDENCES(newline, (this.isSplit ? -(level + 1) : -1));
}
else
{
ArrayOfInt poss = new ArrayOfInt(); //колонки, в которые будут загружены данные; список всегда начинается с -1 (-2)
poss.Add((this.isSplit ? -(level + 1) : -1));
for (int i = 0; i < DB_Deals_ColumnsNames.Keys.Count; i++)
{
int colPos = DB_Deals_ColumnsNames.Keys.ElementAt(i); //VALUES[newline, colPos]
if (this.isSplit)
{
if (SUBDEALS_WhatLevel(colPos) == level || SUBDEALS_WhatLevel(colPos) == -1 && level == 0)
{
//ok
}
else
continue;
}
// *** Загрузить из БД ***
poss.Add(colPos);
object val = sqlReader.GetValue(sqlReader.GetOrdinal(DB_Deals_ColumnsNames[colPos]));
(VALUES[newline, colPos] as ISQLSerialize)._FromDataBase(val);
}
// Одним махом REFLECT для poss[] - т.е. для начала (-1, -2 для subdeal) и для загруженных данных
REFLECT_DEPENDENCES(newline, poss);
}
// -------------- 2. Активация изменений данных: Changed -> SAVE, REFLECT ----------------
for (int i = 0; i < columnsDataClasses.Count; i++)
{
if (this.isSplit)
{
if (SUBDEALS_WhatLevel(i) == level || SUBDEALS_WhatLevel(i) == -1 && level == 0)
{
//ok
}
else
continue;
}
// >>>>>>>>> Сначала SAVE, потом REFLECT <<<<<<<<<<<<<<
// delme!!!
if (DB_Deals_ColumnsNames.Keys.Contains(i))
(VALUES[newline, i] as BaseChangedData).Changed += OnInputChanged_SaveToDataBase;
(VALUES[newline, i] as BaseChangedData).Changed += OnInputChanged;
}
// ----------------- 3. Присоединение данных к контролам -----------------
int startControlIndex;
int endControlIndex;
if (!this.isSplit || this.isSplit && level == 0)
{
startControlIndex = 0;
endControlIndex = columnsControlsClasses.Count - 1;
}
else
{
startControlIndex = columnsControlsClasses.Count + (level - 1) * SUBDEALS_CONTROLS_GetSpliColumnsCount();
endControlIndex = startControlIndex + SUBDEALS_CONTROLS_GetSpliColumnsCount() - 1;
}
for (int i = startControlIndex; i <= endControlIndex; i++)
{
int columnIndex = (!this.isSplit ? i : SUBDEALS_CONTROLS_Get_Split_Coordinates(i).column);
MethodInfo method;
Type[] types;
object[] parameters;
int params_count = 0; //для sub-deal
if (!this.isSplit)
{
types = new Type[CoVa_Dependences[columnIndex].Count];
for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++)
types[j] = VALUES[newline, CoVa_Dependences[columnIndex, j]].GetType();
}
else
{
params_count = CoVa_Dependences[columnIndex].Count + (CoVa_Dependences_SPLIT[columnIndex].Count / SUBDEALS.Count);
types = new Type[params_count];
int ind = 0;
for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++)
{
types[ind++] = VALUES[newline, CoVa_Dependences[columnIndex, j]].GetType();
}
for (int j = 0; j < CoVa_Dependences_SPLIT[columnIndex].Count; j++)
{
int l = SUBDEALS_WhatLevel(CoVa_Dependences_SPLIT[columnIndex, j]);
if (l == level)
types[ind++] = VALUES[newline, CoVa_Dependences_SPLIT[columnIndex, j]].GetType();
}
}
method = CONTROLS[newline, i].GetType().GetMethod("AttachData", types);
if (method != null)
{
if (!this.isSplit)
{
parameters = new object[CoVa_Dependences[columnIndex].Count];
for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++)
parameters[j] = VALUES[newline, CoVa_Dependences[columnIndex, j]];
}
else
{
parameters = new object[params_count];
int ind = 0;
for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++)
{
parameters[ind++] = VALUES[newline, CoVa_Dependences[columnIndex, j]];
}
for (int j = 0; j < CoVa_Dependences_SPLIT[columnIndex].Count; j++)
{
int l = SUBDEALS_WhatLevel(CoVa_Dependences_SPLIT[columnIndex, j]);
if (l == level)
parameters[ind++] = VALUES[newline, CoVa_Dependences_SPLIT[columnIndex, j]];
}
}
method.Invoke(CONTROLS[newline, i], parameters);
}
}
}
private object DataReaderToObject(SqlCeDataReader reader, EntityType entity)
{
object entityObject = null;
switch (entity)
{
case EntityType.Processor:
entityObject = new EntProcessor();
break;
case EntityType.OS:
entityObject = new EntOS();
break;
case EntityType.Bios:
entityObject = new EntBios();
break;
case EntityType.MotherBoard:
entityObject = new EntMotherBoard();
break;
case EntityType.Disk:
entityObject = new EntDisk();
break;
case EntityType.Memory:
entityObject = new EntMemory();
break;
case EntityType.LogicalDrive:
entityObject = new EntLogicalDrive();
break;
case EntityType.CDRom:
entityObject = new EntCDRom();
break;
case EntityType.Video:
entityObject = new EntVideo();
break;
case EntityType.Multimedia:
entityObject = new EntMultimedia();
break;
case EntityType.Monitor:
entityObject = new EntMonitor();
break;
case EntityType.Share:
entityObject = new EntShare();
break;
case EntityType.StartUp:
entityObject = new EntStartUp();
break;
case EntityType.Hotfix:
entityObject = new EntHotfixes();
break;
case EntityType.Processes:
entityObject = new EntProcesses();
break;
case EntityType.Softwares:
entityObject = new EntSoftwares();
break;
case EntityType.Services:
entityObject = new EntServices();
break;
case EntityType.IPRoutes:
entityObject = new EntIPRoutes();
break;
case EntityType.EnvironmentVar:
entityObject = new EntEnvironmentVars();
break;
case EntityType.Computer:
entityObject = new EntComputer();
break;
case EntityType.Printer:
entityObject = new EntPrinter();
break;
case EntityType.UserGroup:
entityObject = new EntUserGroups();
break;
case EntityType.NetworkAdapter:
entityObject = new EntNetworkAdapter();
break;
}
Type t = entityObject.GetType();
PropertyInfo[] pi = t.GetProperties();
foreach (PropertyInfo prop in pi)
{
if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
continue;
switch (prop.PropertyType.Name)
{
case "String":
string strValue = reader.GetString(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, strValue, null);
break;
case "Int32":
int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, intValue, null);
break;
case "Double":
double dValue = reader.GetDouble(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, dValue, null);
break;
case "DateTime":
DateTime dtValue = DateTime.MinValue;
if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value)
{
dtValue = DateTime.MinValue;
}
else
{
dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name));
}
prop.SetValue(entityObject, dtValue, null);
break;
case "Boolean":
bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name));
prop.SetValue(entityObject, bValue, null);
break;
}
}
return entityObject;
}
//
// 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;
}
/// <summary>
/// Converts a SqlCeDataReader to a DataSet
/// <param name='reader'>
/// SqlDataReader to convert.</param>
/// <returns>
/// DataSet filled with the contents of the reader.</returns>
/// </summary>
public static DataSet ConvertDataReaderToDataSet(SqlCeDataReader reader, string tabela)
{
DataSet dataSet = new DataSet();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable(tabela);
if (schemaTable != null)
{
// A query returning records was executed
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
// Create a column name that is unique in the data table
string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
// Add the column definition to the data table
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
dataTable.Columns.Add(column);
}
dataSet.Tables.Add(dataTable);
// Fill the data table we just created
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
public static IBaseObject Reader(SqlCeDataReader reader, string entity)
{
Assembly entityImpl = Assembly.GetAssembly(typeof (BaseObject));
string entityName = entityImpl.FullName.Substring(0, entityImpl.FullName.IndexOf(",")).Trim() + "." + entity;
Type type = entityImpl.GetType(entityName);
object obj = entityImpl.CreateInstance(entityName);
foreach (PropertyInfo p in type.GetProperties())
{
try
{
int index;
object value;
if (p.PropertyType == typeof(ITeacher))
{
index = reader.GetOrdinal("Teacher_Id");
using (Command cmd = new Command("init_Teacher"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = UserGateway.Get(cmd, id, Role.Teacher) as ITeacher;
}
catch
{
value = null;
}
}
}
else if (p.PropertyType == typeof(ICourse))
{
index = reader.GetOrdinal("Course_Id");
using (Command cmd = new Command("init_Course"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = CourseGateway.Get(cmd, id);
}
catch
{
value = null;
}
}
}
else if (p.PropertyType == typeof(IStudent))
{
index = reader.GetOrdinal("Student_Id");
using (Command cmd = new Command("init_Student"))
{
try
{
string id = Convert.ToString(reader.GetValue(index));
value = UserGateway.Get(cmd, id, Role.Student) as IStudent;
}
catch
{
value = null;
}
}
}
else
{
index = reader.GetOrdinal(p.Name);
value = reader.GetValue(index);
value = value is DBNull ? null : value;
}
p.SetValue(obj, value, null);
}
catch
{
continue;
}
}
return obj as IBaseObject;
}
private void PopulateEntity(Entity entity, SqlCeDataReader reader)
{
entity.Id = reader.GetValue(reader.GetOrdinal("Id")).ToString();
entity.Code = (string)reader.GetValue(reader.GetOrdinal("Code"));
entity.Label = (string)reader.GetValue(reader.GetOrdinal("Label"));
entity.Data1 = (string)reader.GetValue(reader.GetOrdinal("Data1"));
entity.Data2 = (string)reader.GetValue(reader.GetOrdinal("Data2"));
entity.Data3 = (string)reader.GetValue(reader.GetOrdinal("Data3"));
}