public Employee getEmployeeByPisPasep(String pisPasep)
{
Employee employee = new Employee();
String formattedPsiPAsep = Formatter.getInstance.formatPisPasep(pisPasep, null);
OleDbCommand cmd = DBConnection.getInstance.getDbCommand();
cmd.CommandText = "SELECT * FROM EMPLOYEE WHERE PIS_PASEP=?";
cmd.Parameters.Add("PIS_PASEP", OleDbType.VarChar).Value = formattedPsiPAsep;
OleDbDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
if (result.Read())
{
employee.idEmployee = result.GetInt32(0);
employee.employeeName = result.GetString(1);
String fileName = Convert.ToString(result[2]);
employee.pisPasep = Formatter.getInstance.formatPisPasep(result.GetString(3), "");
employee.company = companyControl.getCompany(result.GetInt32(4));
employee.schedule = scheduleControl.getSchedule(result.GetInt32(5));
employee.leefNumber = Convert.ToString(result[6]);
employee.ctps = result.GetString(7);
employee.department = departmentControl.getDepartment(result.GetInt32(8));
employee.office = officeControl.getOffice(result.GetInt32(9));
employee.admission = result.GetDateTime(10);
employee.resignation = result.GetDateTime(11);
employee.resignationReason = resignationReasonControl.getResignationReason(result.GetInt32(12));
employee.phone = result.GetString(13);
employee.email = result.GetString(14);
employee.generalRegistry = result.GetString(15);
employee.registryEntity = result.GetString(16);
employee.father = result.GetString(17);
employee.mother = result.GetString(18);
employee.birth = result.GetDateTime(19);
employee.gender = result.GetString(20);
employee.civilStatus = result.GetString(21);
employee.nationality = result.GetString(22);
employee.naturalness = result.GetString(23);
employee.address = result.GetString(24);
employee.neighborhood = result.GetString(25);
employee.city = result.GetString(26);
employee.state = result.GetString(27);
employee.zipCode = result.GetString(28);
String imageFile = ConfigControl.Instance.getImageDirectory() + "/" + fileName;
if (File.Exists(imageFile))
{
employee.employeeImage = new BitmapImage(new Uri(imageFile));
}
else
{
employee.employeeImage = new BitmapImage(new Uri(ConfigControl.Instance.getNoImageFile()));
}
}
}
result.Close();
return(employee);
}
public Kurswahlen(string aktSj, Klasses klasses, Fachs fachs, Unterrichts unterrichts, int periode)
{
using (OleDbConnection oleDbConnection = new OleDbConnection(Global.ConnectionStringUntis))
{
try
{
Console.Write("Kurswahlen ".PadRight(75, '.') + " ");
string queryString = @"SELECT
StudentChoice.STUDENT_ID,
Student.Longname,
Student.FirstName,
Student.BirthDate,
StudentChoice.Number,
StudentChoice.AlternativeCourses,
Student.Name,
Student.StudNumber,
StudentChoice.Deleted
FROM Student LEFT JOIN StudentChoice ON Student.STUDENT_ID = StudentChoice.STUDENT_ID
WHERE (((StudentChoice.SCHOOLYEAR_ID)= " + aktSj + ") AND ((StudentChoice.TERM_ID)=" + periode + ")) ORDER BY StudentChoice.STUDENT_ID;";
OleDbCommand oleDbCommand = new OleDbCommand(queryString, oleDbConnection);
oleDbConnection.Open();
OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();
while (oleDbDataReader.Read())
{
Kurswahl kurswahl = new Kurswahl(aktSj, periode);
kurswahl.StudentId = oleDbDataReader.GetInt32(0);
try
{
kurswahl.Nachname = Global.SafeGetString(oleDbDataReader, 1);
if (kurswahl.Nachname == "Dillhage")
{
string a = "";
}
kurswahl.Vorname = Global.SafeGetString(oleDbDataReader, 2);
kurswahl.Geburtsdatum = DateTime.ParseExact((oleDbDataReader.GetInt32(3)).ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
kurswahl.Number = Convert.ToInt32(oleDbDataReader.GetValue(4));
kurswahl.AlternativeCourses.Add(Global.SafeGetString(oleDbDataReader, 5));
kurswahl.StudentKurzname = Global.SafeGetString(oleDbDataReader, 6);
kurswahl.Fach = (from f in fachs where f.IdUntis.ToString() == kurswahl.AlternativeCourses[0].Split('/')[1] select f.KürzelUntis).FirstOrDefault();
kurswahl.Klasse = (from u in unterrichts
where u.IdUntis.ToString() == kurswahl.AlternativeCourses[0].Split('/')[0]
select u.Klasse.NameUntis).FirstOrDefault();
kurswahl.AtlantisId = Global.SafeGetString(oleDbDataReader, 7);
kurswahl.Deleted = oleDbDataReader.GetBoolean(8);
this.Add(kurswahl);
}
catch (Exception)
{
Console.WriteLine("Schüler " + kurswahl.Nachname + " " + kurswahl.Vorname + " hat keine Klasse");
}
}
;
oleDbDataReader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
oleDbConnection.Close();
Console.WriteLine(this.Count);
}
}
}
void chargerListeAlbum()
{
listAlbums.Items.Clear();
List <Emprunter> emprunts = new List <Emprunter>();
string sql = "Select * " +
"From Emprunter " +
"Where Code_Abonné = '" + abn.Code_Abonné + "'";
OleDbCommand cmd = new OleDbCommand(sql, dbCon);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int codeAbo = reader.GetInt32(0);
int codeAlb = reader.GetInt32(1);
DateTime dateEmprunt = reader.GetDateTime(2);
if (!reader.IsDBNull(3))
{
DateTime dateRet = reader.GetDateTime(3);
emprunts.Add(new Emprunter()
{
Code_Abonné = codeAbo,
Code_Album = codeAlb,
Date_Emprunt = dateEmprunt,
Date_Retour = dateRet
});
}
else
{
emprunts.Add(new Emprunter()
{
Code_Abonné = codeAbo,
Code_Album = codeAlb,
Date_Emprunt = dateEmprunt
});
}
}
foreach (Emprunter emp in emprunts)
{
Album album = new Album();
sql = "Select * " +
"From Album " +
"Where Code_Album = " + emp.Code_Album.ToString();
cmd = new OleDbCommand(sql, dbCon);
reader = cmd.ExecuteReader();
while (reader.Read())
{
int codeAlb = reader.GetInt32(0);
string titre = reader.GetString(1);
int annee = reader.GetInt32(2);
int codeGenre = reader.GetInt32(3);
int codeEditeur = reader.GetInt32(4);
album = new Album()
{
Code_Album = codeAlb,
Titre_Album = titre,
Année_Album = annee,
Code_Genre = codeGenre,
Code_Editeur = codeEditeur
};
}
if (checkBoxEmprunt.Checked && emp.Date_Retour == null)
{
listAlbums.Items.Add(album);
}
else if (!checkBoxEmprunt.Checked)
{
listAlbums.Items.Add(album);
}
}
}
// Return enrollment ID from an OleDbDataReader
public int getId(OleDbDataReader rs)
{
return(rs.GetInt32(0));
}
private void btnAggiungi_Click(object sender, EventArgs e)
{
string s = "";
int j = 0;
if (rdbImgNo.Checked)
{
path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Utilities\\img/NoImage.jpg";
filePicked = true;
}
if (filePicked)
{
bool usato;
bool kmZero;
if (rdbUsatoSì.Checked)
{
usato = true;
}
else
{
usato = false;
}
if (rdbKmZeroSì.Checked)
{
kmZero = true;
}
else
{
kmZero = false;
}
if (txtMarca.Text == "")
{
s = "marca";
}
if (txtModello.Text == "")
{
s += "\nmodello";
}
if (txtPotenzakW.Text == "")
{
s += "\npotenzakW";
}
try
{
int cilind = Convert.ToInt32(nudCilindrata.Value);
}
catch (Exception)
{
s = "\ncilindrata";
}
try
{
int kmp = Convert.ToInt32(nudKmPercorsi.Value);
}
catch (Exception)
{
s += "\nnumero Km percorsi";
}
if (cmbTipoVeicolo.SelectedIndex == 0)
{
//AUTO
try
{
int nAirbag = Convert.ToInt32(nudNumeroAirbag.Value);
}
catch (Exception)
{
s += "\nnumero di airbag";
}
}
else
{
//MOTO
if (txtSella.Text == "")
{
s += "\nsella";
}
}
if (s != "")
{
MessageBox.Show("Inserire i dati correttamente." +
"\nI dati da correggere sono:\n" + s);
}
else if (cmbTipoVeicolo.SelectedIndex == 0) //AUTO
{
OleDbConnection con = new OleDbConnection(connStr);
con.Open();
OleDbCommand command2 = new OleDbCommand();
command2.Connection = con;
command2.CommandText = "SELECT CodVeicolo FROM Veicoli";
OleDbDataReader r2 = command2.ExecuteReader();
if (r2.HasRows)
{
while (r2.Read())
{
j = r2.GetInt32(0);
}
j++;
}
if (rdbImgSì.Checked)
{
path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Utilities\\img/" + j + ".jpg";
File.Copy(f, Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Utilities\\img/" + j + ".jpg");
}
bindingListVeicoli.Add(new Auto(j, txtMarca.Text, txtModello.Text, cmbColore.Text, Convert.ToInt32(nudCilindrata.Value), Convert.ToDouble(txtPotenzakW.Text), Convert.ToDateTime(dtpImmatricolazione.Value.ToShortDateString()), usato, kmZero, Convert.ToInt32(nudKmPercorsi.Value), Convert.ToInt32(nudNumeroAirbag.Value), path));
using (con)
{
OleDbCommand command = new OleDbCommand();
command.Connection = con;
command.CommandText = "INSERT INTO Veicoli (CodVeicolo, Tipologia, Marca, Modello, Colore, Cilindrata, PotenzaKw, Immatricolazione, IsUsato, IsKmZero, KmPercorsi, Informazioni, Immagine) VALUES" +
"(@codveicolo, @tipologia, @marca, @modello, @colore, @cilindrata, @potenzakw, @immatricolazione, @isusato, @iskmzero, @kmpercorsi, @informazioni, @immagine)";
command.Parameters.Add("@codveicolo", OleDbType.Integer).Value = j;
command.Parameters.Add(new OleDbParameter("@tipologia", OleDbType.VarChar, 255)).Value = cmbTipoVeicolo.Text;
command.Parameters.Add("@marca", OleDbType.VarChar, 255).Value = txtMarca.Text;
command.Parameters.Add("@modello", OleDbType.VarChar, 255).Value = txtModello.Text;
command.Parameters.Add("@colore", OleDbType.VarChar, 255).Value = cmbColore.Text;
command.Parameters.Add("@cilindrata", OleDbType.Integer).Value = Convert.ToInt32(nudCilindrata.Value);
command.Parameters.Add("@potenzakw", OleDbType.Integer).Value = Convert.ToInt32(txtPotenzakW.Text);
command.Parameters.Add("@immatricolazione", OleDbType.Date).Value = Convert.ToDateTime(dtpImmatricolazione.Value.ToShortDateString());
command.Parameters.Add("@isusato", OleDbType.Boolean).Value = usato;
command.Parameters.Add("@iskmzero", OleDbType.Boolean).Value = kmZero;
command.Parameters.Add("@kmpercorsi", OleDbType.Integer).Value = Convert.ToInt32(nudKmPercorsi.Value);
command.Parameters.Add("@informazioni", OleDbType.VarChar, 255).Value = nudNumeroAirbag.Value.ToString();
command.Parameters.Add("@immagine", OleDbType.VarChar, 255).Value = path;
command.Prepare();
command.ExecuteNonQuery();
con.Close();
}
clsMetodi.caricaDgv(bindingListVeicoli, ((DataGridView)formMain.Controls["dgvVeicoli"]));
clsMetodi.checkMarca(txtMarca.Text);
clsMetodi.checkColore(cmbColore.Text);
formMain.salva();
con.Close();
this.Close();
}
else //MOTO
{
OleDbConnection con = new OleDbConnection(connStr);
con.Open();
OleDbCommand command2 = new OleDbCommand();
command2.Connection = con;
command2.CommandText = "SELECT CodVeicolo FROM Veicoli";
OleDbDataReader r2 = command2.ExecuteReader();
if (r2.HasRows)
{
while (r2.Read())
{
j = r2.GetInt32(0);
}
j++;
}
if (rdbImgSì.Checked)
{
path = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Utilities\\img/" + j + ".jpg";
File.Copy(f, Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Utilities\\img/" + j + ".jpg");
}
bindingListVeicoli.Add(new Moto(j, txtMarca.Text, txtModello.Text, cmbColore.Text, Convert.ToInt32(nudCilindrata.Value), Convert.ToDouble(txtPotenzakW.Text), Convert.ToDateTime(dtpImmatricolazione.Value.ToShortDateString()), usato, kmZero, Convert.ToInt32(nudKmPercorsi.Value), txtSella.Text, path));
using (con)
{
OleDbCommand command = new OleDbCommand();
command.Connection = con;
command.CommandText = "INSERT INTO Veicoli (CodVeicolo, Tipologia, Marca, Modello, Colore, Cilindrata, PotenzaKw, Immatricolazione, IsUsato, IsKmZero, KmPercorsi, Informazioni, Immagine) VALUES" +
"(@codveicolo, @tipologia, @marca, @modello, @colore, @cilindrata, @potenzakw, @immatricolazione, @isusato, @iskmzero, @kmpercorsi, @informazioni, @immagine)";
command.Parameters.Add("@codveicolo", OleDbType.Integer).Value = j;
command.Parameters.Add(new OleDbParameter("@tipologia", OleDbType.VarChar, 255)).Value = cmbTipoVeicolo.Text;
command.Parameters.Add("@marca", OleDbType.VarChar, 255).Value = txtMarca.Text;
command.Parameters.Add("@modello", OleDbType.VarChar, 255).Value = txtModello.Text;
command.Parameters.Add("@colore", OleDbType.VarChar, 255).Value = cmbColore.Text;
command.Parameters.Add("@cilindrata", OleDbType.Integer).Value = Convert.ToInt32(nudCilindrata.Value);
command.Parameters.Add("@potenzakw", OleDbType.Integer).Value = Convert.ToInt32(txtPotenzakW.Text);
command.Parameters.Add("@immatricolazione", OleDbType.Date).Value = Convert.ToDateTime(dtpImmatricolazione.Value.ToShortDateString());
command.Parameters.Add("@isusato", OleDbType.Boolean).Value = usato;
command.Parameters.Add("@iskmzero", OleDbType.Boolean).Value = kmZero;
command.Parameters.Add("@kmpercorsi", OleDbType.Integer).Value = Convert.ToInt32(nudKmPercorsi.Value);
command.Parameters.Add("@informazioni", OleDbType.VarChar, 255).Value = nudNumeroAirbag.Value.ToString();
command.Parameters.Add("@immagine", OleDbType.VarChar, 255).Value = path;
command.Prepare();
command.ExecuteNonQuery();
con.Close();
}
clsMetodi.caricaDgv(bindingListVeicoli, ((DataGridView)formMain.Controls["dgvVeicoli"]));
clsMetodi.checkMarca(txtMarca.Text);
clsMetodi.checkColore(cmbColore.Text);
formMain.salva();
con.Close();
this.Close();
}
}
else
{
MessageBox.Show("Non è stata selezionata l'immagine da associare al veicolo");
}
}
/// <summary>
/// Importa los datos de las tablas clientes, prestamo y cuotas a la base de datos DB_INVEREG
/// </summary>
private void Importar()
{
int contfalse = 0;
bool control = false;
string newcodigo = "";
string oldcodigo = "";
try
{
conex.Open();
OleDbDataReader dr = null;
if (comboBox1.Text == "T_Prestamos")
{
string sqlselect = "SELECT codigo, valor, saldo, fecha_ini, fecha_fin, valor_cuota, valor_total, numero_cuotas, T_Prestamos.cedula";
sqlselect += " FROM T_Prestamos, T_Clientes";
sqlselect += " WHERE T_Clientes.cedula = T_Prestamos.cedula";
dr = new OleDbCommand(sqlselect, conex).ExecuteReader();
}
//else if (comboBox1.Text == "T_Cuotas")
//{
// dr = new OleDbCommand("SELECT codigo_prestamo, abono, fecha FROM T_Cuotas ORDER BY T_Cuotas.Id", conex).ExecuteReader();
//}
else if (comboBox1.Text == "T_Clientes")
{
dr = new OleDbCommand("SELECT *FROM " + comboBox1.Text, conex).ExecuteReader();
}
if (dr.HasRows)
{
if (dataGridView1.RowCount > 0)
{
progressBar1.Maximum = dataGridView1.RowCount - 1;
}
else
{
progressBar1.Maximum = 1;
}
for (int i = 0; i < dataGridView1.RowCount; i++)
{
string sql = null;
dr.Read();
//ARMAR INSERT SQL DE CLIENTES
if (comboBox1.Text == "T_Clientes")
{
sql = "INSERT INTO tcliente(nombre, cedula, direccion, telefono, barrio)";
sql += "VALUES('" + dr.GetString(1) + "', '" + dr.GetString(2) + "', '" + dr.GetString(4) + "', '" + dr.GetString(5) + "', '" + dr.GetString(3) + "');";
}
//ARMAR INSERT SQL DE PRESTAMOS
else if (comboBox1.Text == "T_Prestamos")
{
oldcodigo = dr.GetString(0);
string cedula = dr.GetString(8);
int indice = getIndiceOleDb(dr.GetString(0));
string auxcod = new Prestamo().GenerarCodigo();
newcodigo = auxcod;
double valor = Convert.ToDouble(dr.GetValue(1));
double valorcuota = Convert.ToDouble(dr.GetValue(5));
double saldo = Convert.ToDouble(dr.GetValue(2));
double total = Convert.ToDouble(dr.GetValue(6));
string fechaini = dr.GetDateTime(3).ToShortDateString();
string fechafin = dr.GetDateTime(4).ToShortDateString();
int numcuotas = dr.GetInt32(7);
sql = "INSERT INTO tprestamo(codigo, valor, porcentaje, total, fecha_inicio, fecha_final, cuotas, valor_cuota, cedula_cliente, saldo)";
sql += " VALUES ('" + auxcod + "', " + valor + ", 0," + total + ",'" + fechaini + "','" + fechafin + "',";
sql += "" + numcuotas + ", " + Convert.ToString(valorcuota).Replace(",", ".") + ", '" + cedula + "', " + saldo + "); ";
sql += "INSERT INTO tindice(num_indice, codigo_prestamo) ";
sql += "VALUES (" + indice + ", '" + auxcod + "');";
control = true;
}
ConexionDB conexOdbc = new ConexionDB();
if (conexOdbc.Ejecutar(sql) == false)
{
contfalse++;
}
else
{
if (control)
{
this.InsertarCuotasPrestamo(oldcodigo, newcodigo);
control = false;
}
}
progressBar1.Value = i;
}
if (contfalse >= 1)
{
MessageBox.Show(contfalse + " Registros devolvieron false", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
MessageBox.Show("Datos importados con exito", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dr.Close();
dr = null;
}
else
{
MessageBox.Show("No hay datos disponibles en el DataReader", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conex.Close();
progressBar1.Value = 0;
}
}
public static void test_4()
{
OleDbConnection con = AccessOp.getCon(null);
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from admin";
cmd.Connection = con;
OleDbDataAdapter db = new OleDbDataAdapter();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0} {1} {2}", reader.GetString(1), reader.GetString(2), reader.GetInt32(3));
}
AccessOp.closeAll(con, cmd, reader);
Console.Read();
}
public bool UpdateStockprice(bool IsBatch)
{
string sSName = "";
string sUpdates = "";
string [] strOnlinevalue;
OleDbCommand cmdStockName = new OleDbCommand();
OleDbCommand cmdStockRate = new OleDbCommand();
OleDbDataReader drStock = null;
cmdStockRate.Connection = _oConnDM;
cmdStockRate.CommandType = CommandType.Text;
cmdStockName.Connection = _oConnDM;
cmdStockName.CommandType = CommandType.StoredProcedure;
cmdStockName.CommandText = "QryActiveStocks";
cmdStockName.Parameters.Clear();
cmdStockName.Parameters.Add("@Active", OleDbType.Boolean).Value = false;
if (!IsBatch)
{
_pBarDoubleM.Visible = true;
}
try
{
if (_oConnDM.State != ConnectionState.Open)
{
_oConnDM.Open();
}
drStock = cmdStockName.ExecuteReader();
if (drStock.HasRows)
{
if (!IsBatch)
{
_pBarDoubleM.Value = 0;
}
while (drStock.Read()) //Reading Stock Names
{
if (!IsBatch)
{
_pBarDoubleM.Maximum = drStock.GetInt32(10) + 1;
}
sUpdates = "";
sSName = drStock.GetString(4); //4 - YFCode
if (CommonDoubleM.YahooStockLatestUpdates(sSName, ref sUpdates))
{
//Add latest Rates
try
{
string sqlUpdateRate = "INSERT into [TRates] (StockID, PRICE, Ondate) VALUES (@StockID, @price, #@Ondate#)";
strOnlinevalue = CommonDoubleM.FilterOnlineValues(sUpdates);
if (!IsBatch)
{
_lblMsgDoubleM.Text = "Latest value: " + sSName + " [" + strOnlinevalue[1] + "]";
}
System.Windows.Forms.Application.DoEvents();
if (Convert.ToDouble(strOnlinevalue[1]) > 0)
{
sqlUpdateRate = sqlUpdateRate.Replace("@StockID", drStock.GetInt32(0).ToString());
sqlUpdateRate = sqlUpdateRate.Replace("@price", strOnlinevalue[1]);
//sqlUpdateRate = sqlUpdateRate.Replace("@Ondate", strOnlinevalue[2].Trim('"', '/') + " " + strOnlinevalue[3].Trim('"', '/'));
sqlUpdateRate = sqlUpdateRate.Replace("@Ondate", DateTime.Now.ToString("dd/MMMM/yyyy HH:mm:ss"));
cmdStockRate.CommandText = sqlUpdateRate;
cmdStockRate.ExecuteNonQuery();
}
}
catch (Exception ex1)
{
if (!IsBatch)
{
_lblMsgDoubleM.Text = ex1.Message + " Unable to update latest Rate of :" + sSName;
}
CommonDoubleM.LogDM("Unable to update latest Rate of :" + sSName);
}
if (!IsBatch)
{
_pBarDoubleM.Value = _pBarDoubleM.Value + 1;
}
}
}
}
return(true);
}
catch (Exception ex)
{
if (!IsBatch)
{
_lblMsgDoubleM.Text = ex.Message;
}
CommonDoubleM.LogDM(ex.Message);
return(false);
}
finally
{
cmdStockName.Dispose();
cmdStockRate.Dispose();
drStock.Close();
drStock.Dispose();
if (!IsBatch)
{
_pBarDoubleM.Visible = false;
}
if (_oConnDM.State != ConnectionState.Closed)
{
_oConnDM.Close();
}
}
}
static void Main(string[] args)
{
string connectionString = ConfigurationManager.ConnectionStrings["DictionaryAccess"].ConnectionString;
Dictionary <int, int> dictionaryLenght = new Dictionary <int, int>();
OleDbConnection connection = null;
OleDbCommand command = null;
OleDbDataReader dataReader = null;
try
{
connection = new OleDbConnection(connectionString);
command = connection.CreateCommand();
command.CommandText = "select (int(abs(x2-x1)+0.5)) as len, Count(*) as num from Coordinates group by (int(abs(x2-x1)+0.5)) ORDER BY 1";
//открываем соединение и читаем из таблицы Coordinates в Dictionary
connection.Open();
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
dictionaryLenght.Add((int)dataReader.GetDouble(0), dataReader.GetInt32(1));
}
dataReader.Close();
//удаляем записи в таблице Frequencies
command.CommandText = "delete from Frequencies";
command.ExecuteNonQuery();
//запись коллекции в таблицу Frequencies используя параметры, чтобы избежать внедрения
command.CommandText = "insert into Frequencies (len, num) values (@len, @num)";
command.Parameters.Add("@len", OleDbType.Integer);
command.Parameters.Add("@num", OleDbType.Integer);
foreach (int len in dictionaryLenght.Keys)
{
command.Parameters["@len"].Value = len;
command.Parameters["@num"].Value = dictionaryLenght[len];
command.ExecuteNonQuery();
}
// поиск записей в Frequencies в которых len>num
command.CommandText = "select * from Frequencies where len>num";
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine("{0};{1}\n", dataReader.GetInt32(1), dataReader.GetInt32(2));
}
dataReader.Close();
connection.Close();
}
catch (ConfigurationErrorsException)
{
Console.WriteLine("Application configuration error has occurred.");
}
catch (InvalidCastException)
{
Console.WriteLine("Database contains invalid data type. Conversion of an instance of one type to another type is not supported.");
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.Message);
}
catch (OleDbException ex)
{
string errorMessages = "";
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages += $"Number #{i+1}\n" +
$"Message: {ex.Errors[i].Message}\n" +
$"NativeError: {ex.Errors[i].NativeError}\n" +
$"Source: {ex.Errors[i].Source}\n" +
$"SQLState: {ex.Errors[i].SQLState}\n";
}
Console.WriteLine("Error!!!\n" + errorMessages + "\nPlease contact your system administrator.");
}
finally
{
if (dataReader != null)
{
dataReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (connection != null)
{
connection.Dispose();
}
}
Console.Write("\nFor exit press any key...");
Console.ReadKey();
}
internal static void FillReferences(GreyFoxKeyword _GreyFoxKeyword)
{
StringBuilder s = new StringBuilder("SELECT GreyFoxKeywordChildID FROM kitCms_KeywordsChildren_References ");
s.Append("WHERE GreyFoxKeywordID=");
s.Append(_GreyFoxKeyword.iD);
s.Append(";");
OleDbConnection dbConnection = new OleDbConnection(_GreyFoxKeyword.connectionString);
OleDbCommand dbCommand = new OleDbCommand(s.ToString(), dbConnection);
dbConnection.Open();
OleDbDataReader r = dbCommand.ExecuteReader();
GreyFoxKeywordCollection references;
if (_GreyFoxKeyword.references != null)
{
references = _GreyFoxKeyword.references;
references.Clear();
}
else
{
references = new GreyFoxKeywordCollection();
_GreyFoxKeyword.references = references;
}
while (r.Read())
{
references.Add(GreyFoxKeyword.NewPlaceHolder(_GreyFoxKeyword.connectionString, r.GetInt32(0)));
}
dbConnection.Close();
_GreyFoxKeyword.references = references;
}
public void Card_update(OleDbConnection sourceConnection, SqlConnection destinationConnection, string tbl)
{
MessageBox.Show("Card ulaz");
// m1 je maximalni CardholderID a n1 je broj redova u Access tabeli Cardholder
var commandSourceData1 = new OleDbCommand("Select MAX(CardID) from " + tbl, sourceConnection);
var commandSourceData2 = new OleDbCommand("Select COUNT(CardID) from " + tbl, sourceConnection);
int m1 = (int)commandSourceData1.ExecuteScalar();
int n1 = (int)commandSourceData2.ExecuteScalar();
MessageBox.Show(" maksimalni " + m1 + " ,broj redova " + n1);
// m1 je maximalni CardholderID a n1 je broj redova u Access tabeli Cardholder
var commandDestinationData1 = new SqlCommand("Select MAX(CardID) from " + tbl, destinationConnection);
var commandDestinationData2 = new SqlCommand("Select COUNT(CardID) from " + tbl, destinationConnection);
int n2 = (int)commandDestinationData2.ExecuteScalar();
//kopiranje
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{
//ako je tabela u SQL-u NIJE prazna
//problem -----> ako je prazna ne moze da se nadje maximalni CardholderID
if (n2 != 0)
{
//ako je prazna ne moze da se nadje maximalni CardholderID , pa se tek sada racuna
int m2 = (int)commandDestinationData1.ExecuteScalar();
MessageBox.Show(" maksimalni " + m2 + " ,broj redova " + n2);
// ako broj redova NIJE isti i ako je m1 u accessu veci od m2u SQL kopiraju se oni koji su novi
if (n1 != n2 && m1 > m2)
{
MessageBox.Show("ima novih kartica, kopira se");
// iz access tablel izvlace se podaci(korisnici) koji su novi
var commandSourceData = new OleDbCommand("Select CardID, CardholderID, CardNo, CardNoInt from" + tbl + " where CardID <=" + @m1 + " and CardID >" + @m2, sourceConnection);
// ispisuje se CardholderID koji se dodaje u tabelu
var commandSourceData3 = new OleDbCommand("Select CardID from " + tbl + " where CardID <=" + @m1 + "and CardID >" + @m2, sourceConnection);
int c = (int)commandSourceData3.ExecuteScalar();
MessageBox.Show(" Ubacje se CardID " + c);
//cita se iz izvorne Access tabele
var reader = commandSourceData3.ExecuteReader();
//ime tabele u SQL bazi
bulkCopy.DestinationTableName = "dbo." + tbl;
//pisanje u tabelu
try { bulkCopy.WriteToServer(reader); }
catch (Exception ex) { MessageBox.Show(ex.Message); }
finally { reader.Close(); }
}
//brisanje
else
{
}
}
else
{
//ako je tabela SQL prazna sve kopiramo
var commandSourceData = new OleDbCommand("Select CardID, CardNo, CardNoIntCode, CardholderID from " + tbl, sourceConnection);
var reader = commandSourceData.ExecuteReader();
MessageBox.Show("tabela prazna ----> ima novih kartica,kopira se");
bulkCopy.DestinationTableName = "dbo." + tbl;
try { bulkCopy.WriteToServer(reader); }
catch (Exception ex) { MessageBox.Show(ex.Message); }
finally { reader.Close(); }
}
}
if (n2 != 0)
{
int m2 = (int)commandDestinationData1.ExecuteScalar();
MessageBox.Show(" maksimalni " + m2 + " ,broj redova " + n2);
if (n1 < n2)
{
// lista je lista cardholderID-a iz SQL baze
SqlDataReader rdr = null;
List <int> lista = new List <int>();
var commandDestinationData3 = new SqlCommand("Select CardID from " + tbl, destinationConnection);
rdr = commandDestinationData3.ExecuteReader();
while (rdr.Read())
{
// MessageBox.Show(" "+rdr[0]);
lista.Add(rdr.GetInt32(0));
}
// sourceLista je lista CardholderId iz Access baze
OleDbDataReader Sourcerdr = null;
List <int> sourceLista = new List <int>();
var commandSourceData3 = new OleDbCommand("Select Card from " + tbl, sourceConnection);
Sourcerdr = commandSourceData3.ExecuteReader();
while (Sourcerdr.Read())
{
// MessageBox.Show(" "+rdr[0]);
sourceLista.Add(Sourcerdr.GetInt32(0));
}
//for (int i = 0; i < sourceLista.Count; i++)
//MessageBox.Show(" " + sourceLista[i]);
//svi oni koji su izbrisani iz access baze, tj oni kojih ima u sql ali ne u access,oni se posle brisu, izuzetak je CardholderID=-1 jer on je za neregistrovane korisnike kartica
var firstNotSecond = lista.Except(sourceLista).ToList();
MessageBox.Show(" proslo");
//var commandDestinationData4= new SqlCommand("Select * from "+tbl, destinationConnection);
Sourcerdr.Close();
MessageBox.Show(" proslo");
SqlDataReader rdr1 = null;
for (int i = 0; i < firstNotSecond.Count; i++)
{
if (firstNotSecond[i] != -1)
{
MessageBox.Show(" " + firstNotSecond[i]);
int n = (int)firstNotSecond[i];
var command = new SqlCommand("Delete from " + tbl + " where CardID=" + @firstNotSecond[i], destinationConnection);
try
{
rdr1 = command.ExecuteReader();
MessageBox.Show("successfully data Deleted", "user information");
while (rdr1.Read())
{
}
}
catch (Exception ec)
{
MessageBox.Show(ec.Message);
}
}
else
{
}
}
}
else
{
}
}
else
{
}
MessageBox.Show("Card izlaz");
return;
}
public uint AVPrjProgress(string strUsername, string strTicket, int nSimulationId)
{
const uint NOT_FOUND = 0x80000000;
const uint ERR_AUTH = 0x80000001;
const uint ERR_UNKN = 0x80000002;
const uint QUEUED = 0xC0000000;
const uint STORING = 0x00000000;
const uint READY = 0x40000000;
if (AVValidateTicket(strUsername, strTicket) == 0)
{
return(ERR_AUTH);
}
// Read SavedAll, TimeSaved from AVProjects
OleDbConnection conn = new OleDbConnection(GetVisConnStr());
OleDbCommand cmdSelect = new OleDbCommand("SELECT SavedAll, TimeSaved FROM AVProjects WHERE SimulationId=?", conn);
cmdSelect.Parameters.AddWithValue("SimulationId", nSimulationId);
conn.Open();
OleDbDataReader reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
int AllSaved = reader.GetInt32(0);
int TimeSaved = reader.GetInt32(1);
reader.Close();
if (AllSaved == 1)
{
// Status: All Saved
return(READY + (uint)TimeSaved);
}
else if (TimeSaved >= 0)
{
// Status: Processing
return(STORING + (uint)TimeSaved);
}
else
{
// Status: Processing
return(QUEUED + (uint)0);
}
}
// No such project in AVProjects - look at SimQueue
conn = new OleDbConnection(GetSimQueueConnStr());
cmdSelect = new OleDbCommand(@"SELECT COUNT(ItemID) AS QueueNum FROM [SimQueue].[dbo].[Queue] WHERE SimulationId = ?", conn);
cmdSelect.Parameters.AddWithValue("SimulationId", nSimulationId);
conn.Open();
reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
int count = reader.GetInt32(0);
reader.Close();
if (count == 0)
{
// Status: Project not found
return(NOT_FOUND);
}
// find out the position in the queue
conn = new OleDbConnection(GetSimQueueConnStr());
cmdSelect = new OleDbCommand(@"SELECT COUNT(ItemID) AS QueueNum
FROM [SimQueue].[dbo].[Queue] q, (SELECT Priority, SubmittedDate FROM [SimQueue].[dbo].[Queue] WHERE SimulationId = ?) p
WHERE q.Priority > p.Priority OR (q.Priority = p.Priority AND q.SubmittedDate < p.SubmittedDate)", conn);
cmdSelect.Parameters.AddWithValue("SimulationId", nSimulationId);
conn.Open();
reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
int queue = reader.GetInt32(0);
reader.Close();
// Status: Queued
return(QUEUED + (uint)queue);
}
}
// Something went wrong
return(ERR_UNKN);
}
public List <Employee> getAllEmployeesFromDepartment(int idDepartment, int idOffice)
{
List <Employee> employees = new List <Employee>();
OleDbCommand cmd = DBConnection.getInstance.getDbCommand();
String commandText = "SELECT * FROM EMPLOYEE";
if (idDepartment != 0)
{
commandText += " WHERE ID_DEPARTMENT=?";
cmd.Parameters.Add("ID_DEPARTMENT", OleDbType.Integer).Value = idDepartment;
}
if (idOffice != 0)
{
if (idDepartment != 0)
{
commandText += " AND";
}
else
{
commandText += " WHERE";
}
commandText += " ID_OFFICE=?";
cmd.Parameters.Add("ID_OFFICE", OleDbType.Integer).Value = idOffice;
}
cmd.CommandText = commandText;
OleDbDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
Employee employee = new Employee();
employee.idEmployee = result.GetInt32(0);
employee.employeeName = result.GetString(1);
String fileName = Convert.ToString(result[2]);
employee.pisPasep = Formatter.getInstance.formatPisPasep(result.GetString(3), "");
employee.company = companyControl.getCompany(result.GetInt32(4));
employee.schedule = scheduleControl.getSchedule(result.GetInt32(5));
employee.leefNumber = Convert.ToString(result[6]);
employee.ctps = Convert.ToString(result[7]);
employee.department = departmentControl.getDepartment(result.GetInt32(8));
employee.office = officeControl.getOffice(result.GetInt32(9));
employee.admission = result.GetDateTime(10);
employee.resignation = result.GetDateTime(11);
employee.resignationReason = resignationReasonControl.getResignationReason(result.GetInt32(12));
employee.phone = result.GetString(13);
employee.email = result.GetString(14);
employee.generalRegistry = result.GetString(15);
employee.registryEntity = result.GetString(16);
employee.father = result.GetString(17);
employee.mother = result.GetString(18);
employee.birth = result.GetDateTime(19);
employee.gender = result.GetString(20);
employee.civilStatus = result.GetString(21);
employee.nationality = result.GetString(22);
employee.naturalness = result.GetString(23);
employee.address = result.GetString(24);
employee.neighborhood = result.GetString(25);
employee.city = result.GetString(26);
employee.state = result.GetString(27);
employee.zipCode = result.GetString(28);
String imageFile = ConfigControl.Instance.getImageDirectory() + "/" + fileName;
if (File.Exists(imageFile))
{
employee.employeeImage = new BitmapImage(new Uri(imageFile));
}
else
{
employee.employeeImage = new BitmapImage(new Uri(ConfigControl.Instance.getNoImageFile()));
}
employees.Add(employee);
}
}
result.Close();
return(employees);
}
public List <Employee> getAllEmployees()
{
List <Employee> employees = new List <Employee>();
OleDbCommand cmd = DBConnection.getInstance.getDbCommand();
cmd.CommandText = "SELECT * FROM EMPLOYEE";
OleDbDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
Employee employee = new Employee();
employee.idEmployee = result.GetInt32(0);
employee.employeeName = result.GetString(1);
String fileName = Convert.ToString(result[2]);
employee.pisPasep = Formatter.getInstance.formatPisPasep(result.GetString(3), "");
employee.company = companyControl.getCompany(result.GetInt32(4));
employee.schedule = scheduleControl.getSchedule(result.GetInt32(5));
employee.leefNumber = Convert.ToString(result[6]);
employee.ctps = result.GetString(7);
employee.department = departmentControl.getDepartment(result.GetInt32(8));
employee.office = officeControl.getOffice(result.GetInt32(9));
employee.admission = result.GetDateTime(10);
employee.resignation = result.GetDateTime(11);
employee.resignationReason = resignationReasonControl.getResignationReason(result.GetInt32(12));
employee.phone = result.GetString(13);
employee.email = result.GetString(14);
employee.generalRegistry = result.GetString(15);
employee.registryEntity = result.GetString(16);
employee.father = result.GetString(17);
employee.mother = result.GetString(18);
employee.birth = result.GetDateTime(19);
employee.gender = result.GetString(20);
employee.civilStatus = result.GetString(21);
employee.nationality = result.GetString(22);
employee.naturalness = result.GetString(23);
employee.address = result.GetString(24);
employee.neighborhood = result.GetString(25);
employee.city = result.GetString(26);
employee.state = result.GetString(27);
employee.zipCode = result.GetString(28);
String imageFile = ConfigControl.Instance.getImageDirectory() + "/" + fileName;
if (File.Exists(imageFile))
{
BitmapImage img = new BitmapImage();
img.BeginInit();
img.CacheOption = BitmapCacheOption.OnLoad;
img.UriSource = new Uri(imageFile);
img.EndInit();
employee.employeeImage = img;
}
else
{
BitmapImage img = new BitmapImage();
img.BeginInit();
img.CacheOption = BitmapCacheOption.OnLoad;
img.UriSource = new Uri(ConfigControl.Instance.getNoImageFile());
img.EndInit();
employee.employeeImage = img;
}
employees.Add(employee);
}
}
result.Close();
return(employees);
}
private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, OleDbConnection conn)
{
List <ProfileColumnData> columnData = new List <ProfileColumnData>(properties.Count);
StringBuilder commandText = new StringBuilder("SELECT u.MEMBER_ID");
OleDbCommand cmd = new OleDbCommand(String.Empty, conn);
int columnCount = 0;
foreach (SettingsProperty prop in properties)
{
SettingsPropertyValue value = new SettingsPropertyValue(prop);
if (prop.PropertyType == typeof(List <SnitzLink>))
{
prop.ThrowOnErrorDeserializing = true;
prop.SerializeAs = SettingsSerializeAs.Xml;
value.Deserialized = false;
}
svc.Add(value);
string persistenceData = prop.Attributes["CustomProviderData"] as string;
// If we can't find the table/column info we will ignore this data
if (String.IsNullOrEmpty(persistenceData))
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string[] chunk = persistenceData.Split(new char[] { ';' });
if (chunk.Length != 2)
{
// REVIEW: Perhaps we should throw instead?
continue;
}
if (chunk[1].ToLower() == "int")
{
chunk[1] = "integer";
}
string columnName = chunk[0];
// REVIEW: Should we ignore case?
OleDbType datatype = (OleDbType)Enum.Parse(typeof(OleDbType), chunk[1], true);
columnData.Add(new ProfileColumnData(columnName, prop, null /* not needed for get */, datatype));
commandText.Append(", ");
commandText.Append("t." + columnName);
++columnCount;
}
commandText.AppendFormat(" FROM {0} t, {1}MEMBERS u WHERE ", TableName, Config.MemberTablePrefix).AppendLine();
commandText.Append("u.M_NAME = @Username AND t.UserID = u.MEMBER_ID");
cmd.CommandText = commandText.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Username", username);
OleDbDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
//If no row exists in the database, then the default Profile values
//from configuration are used.
if (reader.Read())
{
svc.Clear();
int userId = reader.GetInt32(0);
for (int i = 0; i < columnData.Count; ++i)
{
object val = reader.GetValue(i + 1);
ProfileColumnData colData = columnData[i];
SettingsPropertyValue propValue = new SettingsPropertyValue(colData.PropertyValue);
//Only initialize a SettingsPropertyValue for non-null values
//if (!(val is DBNull || val == null))
//{
propValue.IsDirty = false;
if (propValue.Property.SerializeAs == SettingsSerializeAs.Xml)
{
propValue.Deserialized = false;
object test = "";
if (!val.Equals(test))
{
propValue.SerializedValue = val;
}
}
else
{
propValue.PropertyValue = val;
propValue.Deserialized = true;
}
svc.Add(propValue);
//}
}
// need to close reader before we try to update the user
if (reader != null)
{
reader.Close();
reader = null;
}
//UpdateLastActivityDate(conn, userId);
}
else
{
object val = GetBookMarkModValues(username);
ProfileColumnData colData = columnData.Find(c => c.ColumnName == "BookMarks");
SettingsPropertyValue propValue = new SettingsPropertyValue(colData.PropertyValue);
propValue.IsDirty = false;
if (propValue.Property.SerializeAs == SettingsSerializeAs.Xml)
{
if (propValue.Name == "BookMarks")
{
svc.Remove("BookMarks");
propValue.Deserialized = false;
object test = "";
if (!val.Equals(test))
{
propValue.SerializedValue = val;
}
svc.Add(propValue);
}
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public void BuildDataSet(string filePath)
{
OleDbConnection con = new OleDbConnection();
con.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\STC\Office365\Access\2010\Books\Books_be.mdb";
try
{
con.Open();
DataSet dataSet = new DataSet("BCM");
DataTable dataTableCategory = new DataTable("Category");
DataColumn dataColumnCategoryCategoryId = new DataColumn {
ColumnName = "ID", DataType = Type.GetType("System.Int32")
};
dataTableCategory.Columns.Add(dataColumnCategoryCategoryId);
dataTableCategory.Columns.Add(new DataColumn {
ColumnName = "Name", DataType = Type.GetType("System.String")
});
dataTableCategory.Columns.Add(new DataColumn {
ColumnName = "Description", DataType = Type.GetType("System.String")
});
dataTableCategory.Columns.Add(new DataColumn {
ColumnName = "ParentCategoryID", DataType = Type.GetType("System.Int32")
});
dataTableCategory.Rows.Add(1, "Books", String.Empty, -1);
dataTableCategory.Rows.Add(2, "Antiques & Collectibles", String.Empty, 1);
dataTableCategory.Rows.Add(3, "Architecture", String.Empty, 1);
dataTableCategory.Rows.Add(4, "Art", String.Empty, 1);
dataTableCategory.Rows.Add(5, "Biography & Autobiography", String.Empty, 1);
dataTableCategory.Rows.Add(6, "Computers & Internet", String.Empty, 1);
dataTableCategory.Rows.Add(7, "Education", String.Empty, 1);
dataTableCategory.Rows.Add(8, "Fiction", String.Empty, 1);
dataTableCategory.Rows.Add(9, "Children's Fiction", String.Empty, 1);
dataTableCategory.Rows.Add(10, "Humor", String.Empty, 1);
dataTableCategory.Rows.Add(11, "History", String.Empty, 1);
dataTableCategory.Rows.Add(12, "Philosophy", String.Empty, 1);
dataTableCategory.Rows.Add(13, "Music", String.Empty, 1);
dataTableCategory.Rows.Add(14, "Science", String.Empty, 1);
dataTableCategory.Rows.Add(15, "Travel", String.Empty, 1);
dataTableCategory.Rows.Add(16, "Business & Economics", String.Empty, 1);
dataTableCategory.Rows.Add(17, "Reference", String.Empty, 1);
dataTableCategory.Rows.Add(18, "Non-Classifiable", String.Empty, 1);
dataSet.Tables.Add(dataTableCategory);
DataTable dataTableBookCategory = new DataTable("BookCategory");
DataColumn dataColumnBookCategoryBookId = new DataColumn {
ColumnName = "BookId", DataType = Type.GetType("System.Int32")
};
DataColumn dataColumnBookCategoryCategoryId = new DataColumn {
ColumnName = "CategoryId", DataType = Type.GetType("System.Int32")
};
dataTableBookCategory.Columns.Add(dataColumnBookCategoryBookId);
dataTableBookCategory.Columns.Add(dataColumnBookCategoryCategoryId);
OleDbCommand cmdBookCategory = new OleDbCommand();
cmdBookCategory.CommandText = "SELECT BookID FROM Book";
cmdBookCategory.Connection = con;
OleDbDataReader readerBookCategory = cmdBookCategory.ExecuteReader();
while (readerBookCategory.Read())
{
dataTableBookCategory.Rows.Add(
readerBookCategory["BookID"]
, 18
);
}
dataSet.Tables.Add(dataTableBookCategory);
DataTable dataTableBookAuthor = new DataTable("BookAuthor");
DataColumn dataColumnBookAuthorBookId = new DataColumn {
ColumnName = "BookId", DataType = Type.GetType("System.Int32")
};
DataColumn dataColumnBookAuthorAuthorId = new DataColumn {
ColumnName = "AuthorId", DataType = Type.GetType("System.Int32")
};
dataTableBookAuthor.Columns.Add(dataColumnBookAuthorBookId);
dataTableBookAuthor.Columns.Add(dataColumnBookAuthorAuthorId);
OleDbCommand cmdBookAuthor = new OleDbCommand();
cmdBookAuthor.CommandText = "SELECT BookId, AuthorId FROM BookAuthor";
cmdBookAuthor.Connection = con;
OleDbDataReader readerBookAuthor = cmdBookAuthor.ExecuteReader();
while (readerBookAuthor.Read())
{
dataTableBookAuthor.Rows.Add(
readerBookAuthor["BookId"]
, readerBookAuthor["AuthorId"]
);
}
dataSet.Tables.Add(dataTableBookAuthor);
DataTable dataTableAuthor = new DataTable("Author");
DataColumn dataColumnAuthorId = new DataColumn {
ColumnName = "ID", DataType = Type.GetType("System.Int32")
};
dataTableAuthor.Columns.Add(dataColumnAuthorId);
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "BirthDate", DataType = Type.GetType("System.DateTime")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "BirthName", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "BirthPlace", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "DateOfDeath", DataType = Type.GetType("System.DateTime")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "FirstName", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "LastName", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "Nationality", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "Notes", DataType = Type.GetType("System.String")
});
dataTableAuthor.Columns.Add(new DataColumn {
ColumnName = "Photograph", DataType = Type.GetType("System.String")
});
OleDbCommand cmdAuthor = new OleDbCommand();
cmdAuthor.CommandText = "SELECT * FROM Author";
cmdAuthor.Connection = con;
OleDbDataReader readerAuthor = cmdAuthor.ExecuteReader();
while (readerAuthor.Read())
{
dataTableAuthor.Rows.Add(
readerAuthor.GetInt32(0)
, readerAuthor["BirthDate"]
, String.Empty
, readerAuthor["BirthPlace"]
, readerAuthor["DateOfDeath"]
, readerAuthor["FirstName"]
, readerAuthor["LastName"]
, readerAuthor["Nationality"]
, readerAuthor["Notes"]
, String.Empty
);
}
dataSet.Tables.Add(dataTableAuthor);
DataTable dataTableBook = new DataTable("Book");
DataColumn dataColumnBookID = new DataColumn {
ColumnName = "ID", DataType = Type.GetType("System.Int32")
};
dataTableBook.Columns.Add(dataColumnBookID);
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "CopyrightYear", DataType = Type.GetType("System.Int32")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "CoverType", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "DatePurchased", DataType = Type.GetType("System.DateTime")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "EditionNumber", DataType = Type.GetType("System.Int32")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ImageUrl", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ImageUrl2", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ImageUrl3", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ImageUrl4", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ISBNNumber", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "ListPrice", DataType = Type.GetType("System.Decimal")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "Location", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "Notes", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "Pages", DataType = Type.GetType("System.Int32")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "PlaceofPublication", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "PublisherName", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "PublishingCompany", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "PublishingYear", DataType = Type.GetType("System.Int32")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "PurchasePrice", DataType = Type.GetType("System.Decimal")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "Title", DataType = Type.GetType("System.String")
});
dataTableBook.Columns.Add(new DataColumn {
ColumnName = "VolumeNumber", DataType = Type.GetType("System.Int32")
});
OleDbCommand cmdBooks = new OleDbCommand();
cmdBooks.CommandText = "SELECT * FROM Book";
cmdBooks.Connection = con;
OleDbDataReader readerBooks = cmdBooks.ExecuteReader();
while (readerBooks.Read())
{
dataTableBook.Rows.Add(
readerBooks.GetInt32(0)
, readerBooks["CopyrightYear"]
, readerBooks["CoverType"]
, readerBooks["DatePurchased"]
, readerBooks["EditionNumber"]
, readerBooks["ImageUrl"]
, readerBooks["ImageUrl2"]
, readerBooks["ImageUrl3"]
, readerBooks["ImageUrl4"]
, readerBooks["ISBNNumber"]
, readerBooks["ListPrice"]
, readerBooks["ShelfNumber"]
, readerBooks["Notes"]
, readerBooks["Pages"]
, readerBooks["PlaceofPublication"]
, readerBooks["PublisherName"]
, readerBooks["PublishingCompany"]
, readerBooks["PublishingYear"]
, readerBooks["PurchasePrice"]
, readerBooks["Title"]
, readerBooks["VolumeNumber"]
);
}
readerBooks.Close();
dataSet.Tables.Add(dataTableBook);
dataSet.Relations.Add(new DataRelation("relationBookBookAuthor", dataColumnBookID, dataColumnBookAuthorBookId));
dataSet.Relations.Add(new DataRelation("relationAuthorBookAuthor", dataColumnAuthorId, dataColumnBookAuthorAuthorId));
dataSet.Relations.Add(new DataRelation("relationBookBookCategory", dataColumnBookID, dataColumnBookCategoryBookId));
dataSet.Relations.Add(new DataRelation("relationCategoryBookCategory", dataColumnCategoryCategoryId, dataColumnBookCategoryCategoryId));
dataSet.AcceptChanges();
WriteXml(dataSet, filePath);
}
catch (Exception e)
{
Console.WriteLine("Failed!\n{0}\n", e.Message);
}
finally
{
con.Close();
}
}
private void ucAddStudent_Load(object sender, EventArgs e)
{
MessageBox.Show("hi");
try
{
connection.Open();
OleDbCommand command1 = new OleDbCommand();
command1.Connection = connection;
string clas = "SELECT ClassID , ClassName from Class";
command1.CommandText = clas;
OleDbDataReader reader = command1.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader.GetString(1));
myDictionary.Add(reader.GetInt32(0), reader.GetString(1));
com_class.DataSource = new BindingSource(myDictionary, null);
com_class.DisplayMember = "Value";
com_class.ValueMember = "Key";
}
OleDbCommand command2 = new OleDbCommand();
command2.Connection = connection;
string clas1 = "Select [ClassAndSectionID] , [Section] from Classandsection";
command2.CommandText = clas1;
OleDbDataReader reader1 = command2.ExecuteReader();
Dictionary <int, string> myDictionary1 = new Dictionary <int, string>();
while (reader1.Read())
{
myDictionary1.Add(reader1.GetInt32(0), reader1.GetString(1));
com_Section.DataSource = new BindingSource(myDictionary1, null);
com_Section.DisplayMember = "Value";
com_Section.ValueMember = "Key";
}
OleDbCommand command3 = new OleDbCommand();
command3.Connection = connection;
string clas3 = "Select [TransportID] , [TransportRoute] from Transport";
command3.CommandText = clas3;
OleDbDataReader reader3 = command3.ExecuteReader();
while (reader3.Read())
{
myDictionary3.Add(reader3.GetInt32(0), reader3.GetString(1));
com_Transport.DataSource = new BindingSource(myDictionary3, null);
com_Transport.DisplayMember = "Value";
com_Transport.ValueMember = "Key";
}
OleDbCommand command4 = new OleDbCommand();
command4.Connection = connection;
string clas4 = "Select [ReligionID] , [ReligionName] from Religion";
command4.CommandText = clas4;
OleDbDataReader reader4 = command4.ExecuteReader();
Dictionary <int, string> myDictionary4 = new Dictionary <int, string>();
while (reader4.Read())
{
myDictionary4.Add(reader4.GetInt32(0), reader4.GetString(1));
com_Religion.DataSource = new BindingSource(myDictionary4, null);
com_Religion.DisplayMember = "Value";
com_Religion.ValueMember = "Key";
}
OleDbCommand command5 = new OleDbCommand();
command5.Connection = connection;
string clas5 = "Select [CastID] , [CastName] from [Cast]";
command5.CommandText = clas5;
OleDbDataReader reader5 = command5.ExecuteReader();
Dictionary <int, string> myDictionary5 = new Dictionary <int, string>();
while (reader5.Read())
{
myDictionary5.Add(reader5.GetInt32(0), reader5.GetString(1));
com_Cast.DataSource = new BindingSource(myDictionary5, null);
com_Cast.DisplayMember = "Value";
com_Cast.ValueMember = "Key";
}
connection.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Execute method
/// </summary>
/// <param name="connectionString">Microsoft Access connection string</param>
public void Execute(string fullPath)
{
if (!File.Exists(fullPath))
{
throw new FileNotFoundException("File not found!", fullPath);
}
OleDbConnection con = new OleDbConnection();
con.ConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fullPath);
try
{
con.Open();
OleDbCommand cmdCategories = new OleDbCommand();
OleDbCommand cmdBooks = new OleDbCommand();
cmdBooks.CommandText = "SELECT * FROM Book";
cmdBooks.Connection = con;
Dictionary <int, Book> books = new Dictionary <int, Book>();
OleDbDataReader readerBooks = cmdBooks.ExecuteReader();
while (readerBooks.Read())
{
Book book = new Book();
int id = readerBooks.GetInt32(0);
books.Add(id, book);
if (!String.IsNullOrEmpty(readerBooks["CopyrightYear"].ToString()))
{
book.CopyrightYear = int.Parse(readerBooks["CopyrightYear"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["CoverType"].ToString()))
{
book.CoverType = readerBooks["CoverType"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["DatePurchased"].ToString()))
{
book.DatePurchased = DateTime.Parse(readerBooks["DatePurchased"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["EditionNumber"].ToString()))
{
book.EditionNumber = int.Parse(readerBooks["EditionNumber"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["ISBNNumber"].ToString()))
{
book.ISBNNumber = readerBooks["ISBNNumber"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["ListPrice"].ToString()))
{
book.ListPrice = decimal.Parse(readerBooks["ListPrice"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["ShelfNumber"].ToString()))
{
book.Location = readerBooks["ShelfNumber"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["Notes"].ToString()))
{
book.Notes = readerBooks["Notes"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["Pages"].ToString()))
{
book.Pages = int.Parse(readerBooks["Pages"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["PlaceOfPublication"].ToString()))
{
book.PlaceOfPublication = readerBooks["PlaceOfPublication"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["PublisherName"].ToString()))
{
book.PublisherName = readerBooks["PublisherName"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["PublishingCompany"].ToString()))
{
book.PublishingCompany = readerBooks["PublishingCompany"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["PublishingYear"].ToString()))
{
book.PublishingYear = int.Parse(readerBooks["PublishingYear"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["PurchasePrice"].ToString()))
{
book.PurchasePrice = decimal.Parse(readerBooks["PurchasePrice"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["Title"].ToString()))
{
book.Title = readerBooks["Title"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["VolumeNumber"].ToString()))
{
book.VolumeNumber = int.Parse(readerBooks["VolumeNumber"].ToString());
}
if (!String.IsNullOrEmpty(readerBooks["ImageUrl"].ToString()))
{
book.ImageUrl = readerBooks["ImageUrl"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["ImageUrl2"].ToString()))
{
book.ImageUrl2 = readerBooks["ImageUrl2"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["ImageUrl3"].ToString()))
{
book.ImageUrl3 = readerBooks["ImageUrl3"].ToString();
}
if (!String.IsNullOrEmpty(readerBooks["ImageUrl4"].ToString()))
{
book.ImageUrl4 = readerBooks["ImageUrl4"].ToString();
}
}
readerBooks.Close();
OleDbCommand cmdBookAuthor = new OleDbCommand();
cmdBookAuthor.CommandText = "SELECT Author.*"
+ " FROM Author INNER JOIN BookAuthor ON Author.AuthorID = BookAuthor.AuthorID"
+ " WHERE (((BookAuthor.BookID)=@p1));";
cmdBookAuthor.Parameters.Add("@p1", OleDbType.Integer);
cmdBookAuthor.Connection = con;
foreach (KeyValuePair <int, Book> kvp in books)
{
Book book = (kvp.Value) as Book;
cmdBookAuthor.Parameters["@p1"].Value = kvp.Key.ToString();
OleDbDataReader readerBookAuthor = cmdBookAuthor.ExecuteReader();
if (readerBookAuthor.HasRows)
{
while (readerBookAuthor.Read())
{
Author author = new Author();
if (!String.IsNullOrEmpty(readerBookAuthor["BirthDate"].ToString()))
{
author.BirthDate = DateTime.Parse(readerBookAuthor["BirthDate"].ToString());
}
author.BirthName = String.Empty;
if (!String.IsNullOrEmpty(readerBookAuthor["BirthPlace"].ToString()))
{
author.BirthPlace = readerBookAuthor["BirthPlace"].ToString();
}
if (!String.IsNullOrEmpty(readerBookAuthor["DateOfDeath"].ToString()))
{
author.DateOfDeath = DateTime.Parse(readerBookAuthor["DateOfDeath"].ToString());
}
if (!String.IsNullOrEmpty(readerBookAuthor["FirstName"].ToString()))
{
author.FirstName = readerBookAuthor["FirstName"].ToString();
}
if (!String.IsNullOrEmpty(readerBookAuthor["LastName"].ToString()))
{
author.LastName = readerBookAuthor["LastName"].ToString();
}
if (!String.IsNullOrEmpty(readerBookAuthor["Nationality"].ToString()))
{
author.Nationality = readerBookAuthor["Nationality"].ToString();
}
if (!String.IsNullOrEmpty(readerBookAuthor["Notes"].ToString()))
{
author.Notes = readerBookAuthor["Notes"].ToString();
}
author.Photograph = String.Empty;
book.Authors.Add(author);
}
}
readerBookAuthor.Close();
}
BCM.DAL.ApplicationDbContext context = new BCM.DAL.ApplicationDbContext();
List <Category> categories = new List <Category>
{
new Category {
Name = "Books", Description = String.Empty, ParentCategoryID = -1
},
new Category {
Name = "Antiques & Collectibles", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Architecture", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Art", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Biography & Autobiography", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Computers & Internet", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Education", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Fiction", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Children's Fiction", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Humor", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "History", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Philosophy", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Music", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Science", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Travel", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Business & Economics", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Reference", Description = String.Empty, ParentCategoryID = 1
},
new Category {
Name = "Non-Classifiable", Description = String.Empty, ParentCategoryID = 1
}
};
categories.ForEach(c => context.Categories.Add(c));
context.SaveChanges();
Category parentCategory = categories.Find(c => c.Name.Equals("Antiques & Collectibles"));
Category catMilitary = new Category {
Name = "Military", Description = String.Empty, ParentCategoryID = parentCategory.ID
};
context.Categories.Add(catMilitary);
context.SaveChanges();
Category category = categories.Find(c => c.Name.Equals("Non-Classifiable"));
foreach (KeyValuePair <int, Book> kvp in books)
{
Book book = ((Book)kvp.Value);
if ((book.Title.StartsWith("Biennial report of THE CHIEF OF STAFF")) ||
(book.Title.StartsWith("Die Kämpfe der deutschen Truppen")))
{
book.Categories.Add(catMilitary);
}
else
{
book.Categories.Add(category);
}
context.Books.Add(book);
Console.WriteLine("{0}\t{1}", kvp.Key, book.Title);
foreach (Author author in book.Authors)
{
Console.WriteLine("\t\t{0}\t{1}", author.LastName, author.FirstName);
}
}
context.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Failed!\n{0}\n", e.Message);
}
finally
{
con.Close();
}
}
public static void createWordDoc()
{
string path = $"{Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName}\\.data\\Veicoli.accdb";
string connStr = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" + path;
if (connStr != null)
{
OleDbConnection con = new OleDbConnection(connStr);
using (con)
{
OleDbCommand com = new OleDbCommand("SELECT * FROM VEICOLI", con);
con.Open();
OleDbDataReader reader = com.ExecuteReader();
if (reader.HasRows)
{
string filepath = $"{Directory.GetParent(Directory.GetCurrentDirectory()).Parent.Parent.FullName}\\.data\\Veicoli.docx";
WordprocessingDocument doc = WordprocessingDocument.Create(filepath, WordprocessingDocumentType.Document);
MainDocumentPart mainPart = doc.AddMainDocumentPart();
mainPart.Document = new Document();
Body body = mainPart.Document.AppendChild(new Body());
ClsWord.AddStyle(mainPart, "Intestazione", "style1", "Consolas", 28, "#FF8800", true, false, false);
Paragraph headingPar = ClsWord.CreateParagraphWithStyle("Intestazione", JustificationValues.Center);
ClsWord.AddTextToParagraph(headingPar, "Vendita veicoli");
body.AppendChild(headingPar);
while (reader.Read())
{
string usato = String.Empty;
string kmzero = String.Empty;
if (reader.GetBoolean(8))
{
usato = "Sì";
}
else
{
usato = "No";
}
if (reader.GetBoolean(9))
{
kmzero = "Sì";
}
else
{
kmzero = "No";
}
if (reader.GetString(1) == "MOTO")
{
Paragraph p = body.AppendChild(new Paragraph());
Run r = p.AppendChild(new Run());
r.AppendChild(new Text("- " + reader.GetString(1)));
r.AppendChild(new Break());
r.AppendChild(new Text("Marca: " + reader.GetString(2)));
r.AppendChild(new Break());
r.AppendChild(new Text("Modello: " + reader.GetString(3)));
r.AppendChild(new Break());
r.AppendChild(new Text("Colore: " + reader.GetString(4)));
r.AppendChild(new Break());
r.AppendChild(new Text("Cilindrata: " + reader.GetInt32(5).ToString()));
r.AppendChild(new Break());
r.AppendChild(new Text("PotenzaKw: " + reader.GetInt32(6).ToString()));
r.AppendChild(new Break());
r.AppendChild(new Text("Data di immatricolazione: " + reader.GetDateTime(7).ToShortDateString()));
r.AppendChild(new Break());
r.AppendChild(new Text("Usato: " + usato));
r.AppendChild(new Break());
r.AppendChild(new Text("Kmzero: " + kmzero));
r.AppendChild(new Break());
r.AppendChild(new Text("Km percorsi: " + reader.GetInt32(10).ToString() + " km"));
r.AppendChild(new Break());
r.AppendChild(new Text("Sella: " + reader.GetString(11)));
r.AppendChild(new Break());
}
else
{
Paragraph p = body.AppendChild(new Paragraph());
Run r = p.AppendChild(new Run());
r.AppendChild(new Text("- " + reader.GetString(1)));
r.AppendChild(new Break());
r.AppendChild(new Text("Marca: " + reader.GetString(2)));
r.AppendChild(new Break());
r.AppendChild(new Text("Modello: " + reader.GetString(3)));
r.AppendChild(new Break());
r.AppendChild(new Text("Colore: " + reader.GetString(4)));
r.AppendChild(new Break());
r.AppendChild(new Text("Cilindrata: " + reader.GetInt32(5).ToString()));
r.AppendChild(new Break());
r.AppendChild(new Text("PotenzaKw: " + reader.GetInt32(6).ToString()));
r.AppendChild(new Break());
r.AppendChild(new Text("Data di immatricolazione: " + reader.GetDateTime(7).ToShortDateString()));
r.AppendChild(new Break());
r.AppendChild(new Text("Usato: " + usato));
r.AppendChild(new Break());
r.AppendChild(new Text("Kmzero: " + kmzero));
r.AppendChild(new Break());
r.AppendChild(new Text("Km percorsi: " + reader.GetInt32(10).ToString() + " km"));
r.AppendChild(new Break());
r.AppendChild(new Text("Numero di airbag: " + reader.GetString(11)));
r.AppendChild(new Break());
}
}
doc.Close();
Console.Write("\nDocumento creato. Vuoi aprirlo?[S/N]: ");
if (Console.ReadLine() == "S")
{
Process.Start(filepath);
}
else
{
Thread.Sleep(2000);
}
}
else
{
Console.WriteLine("La tabella non contiene nessuna riga!");
}
}
}
}
/// <summary> Creates source code for a single data type in the HL7 normative
/// database.
/// </summary>
/// <param name="targetDirectory">the directory into which the file will be written
/// </param>
/// <param name="datatype">the name (e.g. ST, ID, etc.) of the data type to be created
/// </param>
/// <param name="version">the HL7 version of the intended data type
/// </param>
public static void make(FileInfo targetDirectory, String dataType, String version)
{
Console.WriteLine(" Writing " + targetDirectory.FullName + dataType);
//make sure that targetDirectory is a directory ...
if (!Directory.Exists(targetDirectory.FullName))
{
throw new IOException("Can't create file in " + targetDirectory.ToString() + " - it is not a directory.");
}
//get any components for this data type
OleDbConnection conn = NormativeDatabase.Instance.Connection;
OleDbCommand stmt = SupportClass.TransactionManager.manager.CreateStatement(conn);
StringBuilder sql = new StringBuilder();
//this query is adapted from the XML SIG informative document
sql.Append(
"SELECT HL7DataStructures.data_structure, HL7DataStructureComponents.seq_no, HL7DataStructures.description, HL7DataStructureComponents.table_id, ");
sql.Append(
"HL7Components.description, HL7Components.table_id, HL7Components.data_type_code, HL7Components.data_structure ");
sql.Append(
"FROM HL7Versions LEFT JOIN (HL7DataStructures LEFT JOIN (HL7DataStructureComponents LEFT JOIN HL7Components ");
sql.Append("ON HL7DataStructureComponents.comp_no = HL7Components.comp_no AND ");
sql.Append("HL7DataStructureComponents.version_id = HL7Components.version_id) ");
sql.Append("ON HL7DataStructures.version_id = HL7DataStructureComponents.version_id ");
sql.Append("AND HL7DataStructures.data_structure = HL7DataStructureComponents.data_structure) ");
sql.Append("ON HL7DataStructures.version_id = HL7Versions.version_id ");
sql.Append("WHERE HL7DataStructures.data_structure = '");
sql.Append(dataType);
sql.Append("' AND HL7Versions.hl7_version = '");
sql.Append(version);
sql.Append("' ORDER BY HL7DataStructureComponents.seq_no");
OleDbCommand temp_OleDbCommand;
temp_OleDbCommand = stmt;
temp_OleDbCommand.CommandText = sql.ToString();
OleDbDataReader rs = temp_OleDbCommand.ExecuteReader();
ArrayList dataTypes = new ArrayList(20);
ArrayList descriptions = new ArrayList(20);
ArrayList tables = new ArrayList(20);
String description = null;
while (rs.Read())
{
if (description == null)
{
description = Convert.ToString(rs[3 - 1]);
}
String de = Convert.ToString(rs[5 - 1]);
String dt = Convert.ToString(rs[8 - 1]);
int ta = -1;
if (!rs.IsDBNull(4 - 1))
{
ta = rs.GetInt32(4 - 1);
}
//trim all CE_x to CE
if (dt != null)
{
if (dt.StartsWith("CE"))
{
dt = "CE";
}
}
//System.out.println("Component: " + de + " Data Type: " + dt); //for debugging
dataTypes.Add(dt);
descriptions.Add(de);
tables.Add((Int32)ta);
}
if (dataType.ToUpper().Equals("TS"))
{
dataTypes[0] = "TSComponentOne";
}
rs.Close();
stmt.Dispose();
NormativeDatabase.Instance.returnConnection(conn);
//if there is only one component make a Primitive, otherwise make a Composite
String source = null;
if (dataTypes.Count == 1)
{
if (dataType.Equals("FT") || dataType.Equals("ST") || dataType.Equals("TX") || dataType.Equals("NM") ||
dataType.Equals("SI") || dataType.Equals("TN") || dataType.Equals("GTS"))
{
source = makePrimitive(dataType, description, version);
}
else
{
source = null; //note: IS, ID, DT, DTM, and TM are coded manually
}
}
else if (dataTypes.Count > 1)
{
int numComponents = dataTypes.Count;
//copy data into arrays ...
String[] type = new String[numComponents];
String[] desc = new String[numComponents];
int[] table = new int[numComponents];
for (int i = 0; i < numComponents; i++)
{
type[i] = ((String)dataTypes[i]);
desc[i] = ((String)descriptions[i]);
table[i] = ((Int32)tables[i]);
}
source = makeComposite(dataType, description, type, desc, table, version);
}
else
{
//no components?
//throw new DataTypeException("The data type " + dataType + " could not be found");
Console.WriteLine("No components for " + dataType);
}
//System.out.println(source);
//write to file ...
if (source != null)
{
String targetFile = targetDirectory.ToString() + "/" + dataType + ".cs";
using (StreamWriter writer = new StreamWriter(targetFile))
{
writer.Write(source);
writer.Write("}"); //End namespace
}
}
else
{
Console.WriteLine("No Source for " + dataType);
}
}
public List <Person> ReadTreeViewData(DateTime start, DateTime end)
{
Debug.Assert(start != null, "ReadTreeViewData: start time was null");
Debug.Assert(end != null, "ReadTreeViewData: end time was null");
var cursor = databaseConnection.CreateCommand();
Debug.Print("ReadTreeViewData: start");
var data = new List <Person>();
cursor.CommandText = TREE_QUERY;
cursor.Parameters.Add(new OleDbParameter("@start", OleDbType.Date));
cursor.Parameters[0].Value = start;
cursor.Parameters.Add(new OleDbParameter("@end", OleDbType.Date));
cursor.Parameters[1].Value = end;
CardLogEntry entry = null;
OleDbDataReader reader = null;
try
{
cursor.Prepare();
reader = cursor.ExecuteReader();
}
catch (InvalidOperationException ex)
{
Debug.Print(ex.ToString());
return(data);
}
catch (OleDbException ex)
{
if ((uint)ex.HResult == 0x80040E37)
{
MessageBox.Show("Logide või personali tabelit '" + TABLE_NAME + "' ei eksisteeri.\nVeateade:\n" + ex.Message, "Viga Accessi andmebaasis");
}
Debug.Print(ex.ToString());
return(data);
}
if (reader == null)
{
Debug.Print("Something went wrong while reading.");
return(data);
}
if (reader.HasRows)
{
Debug.Print("ReadTreeViewData: has rows");
while (reader.Read())
{
string firstName = reader.GetString(reader.GetOrdinal("Eesnimi"));
string lastName = reader.GetString(reader.GetOrdinal("Perekonnanimi"));
string company = reader.GetString(reader.GetOrdinal("Kompanii"));
string platoon = reader.GetString(reader.GetOrdinal("Ryhm"));
string position = reader.GetString(reader.GetOrdinal("Ametikoht"));
string attends = reader.GetInt32(reader.GetOrdinal("Kohal")).ToString();
var p = new Person();
p.data.Add("Eesnimi", firstName);
p.data.Add("Perekonnanimi", lastName);
p.data.Add("Kompanii", company);
p.data.Add("Ryhm", platoon);
p.data.Add("Kohal", attends);
p.data.Add("Ametikoht", position);
data.Add(p);
}
}
else
{
Debug.Print("ReadTreeViewData: no rows");
return(data);
}
return(data);
}
/// <summary>
/// Test SQL statement with parameters
/// </summary>
private static void Test_Parameters()
{
string val32 = "/asp/sns/member/dotcom-20000.asp";
string val40 = val32 + new String(' ', 8);
using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = TestCasesOld.connString;
conn.Open();
(new OleDbCommand("DROP TABLE IF EXISTS dat_client_ip_log", conn)).ExecuteNonQuery();
(new OleDbCommand("CREATE TABLE dat_client_ip_log (request_url character varying(256) DEFAULT '' NOT NULL)", conn)).ExecuteNonQuery();
//insert 40-chars string
(new OleDbCommand("INSERT INTO dat_client_ip_log(request_url) VALUES('" + val40 + "')", conn)).ExecuteNonQuery();
using (OleDbCommand cmd = new OleDbCommand("SELECT request_url, LENGTH(request_url) FROM dat_client_ip_log", conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetString(0) == val32);
Debug.Assert(reader.GetInt32(1) == val40.Length);
Debug.Assert(reader.GetDataTypeName(0) == "DBTYPE_VARCHAR");
Debug.Assert(reader.GetDataTypeName(1) == "DBTYPE_I4");
Debug.Assert(reader.GetFieldType(0).UnderlyingSystemType.Name == "String");
Debug.Assert(reader.GetFieldType(1).UnderlyingSystemType.Name == "Int32");
}
}
//Try the same as above, but with parameters
(new OleDbCommand("DROP TABLE IF EXISTS dat_client_ip_log", conn)).ExecuteNonQuery();
(new OleDbCommand("CREATE TABLE dat_client_ip_log (request_url character varying(256) DEFAULT '' NOT NULL)", conn)).ExecuteNonQuery();
//insert 40-chars string
using (OleDbCommand cmd = new OleDbCommand("INSERT INTO dat_client_ip_log(request_url) VALUES(?)", conn))
{
OleDbParameter parameter = new OleDbParameter("@p1", OleDbType.VarChar, 256);
parameter.Value = val40;
cmd.Parameters.Add(parameter);
cmd.ExecuteNonQuery();
}
using (OleDbCommand cmd = new OleDbCommand("SELECT request_url, LENGTH(request_url) FROM dat_client_ip_log", conn))
{
using (OleDbDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Debug.Assert(reader.GetString(0) == val32);
Debug.Assert(reader.GetInt32(1) == val40.Length);
Debug.Assert(reader.GetDataTypeName(0) == "DBTYPE_VARCHAR");
Debug.Assert(reader.GetDataTypeName(1) == "DBTYPE_I4");
Debug.Assert(reader.GetFieldType(0).UnderlyingSystemType.Name == "String");
Debug.Assert(reader.GetFieldType(1).UnderlyingSystemType.Name == "Int32");
}
}
(new OleDbCommand("DROP TABLE IF EXISTS dat_client_ip_log", conn)).ExecuteNonQuery();
}
}
private void bindData()
{
string strqry = "SELECT ID,sfzh, yourname,xingbie, " +
"iif(DateDiff('YYYY', CDate(iif(isnull(birth),now,iif(birth='',now(),birth))),Format(Now(),'yyyy-mm-dd'))=0,'',DateDiff('YYYY', CDate(iif(isnull(birth),now,iif(birth='',now(),birth))),Format(Now(),'yyyy-mm-dd'))) AS nianling," +
"xrzw, sbzw, iif(isnull(tj_flag),'未审核',tj_flag) as tuijian,edit_flag,fenlei FROM cpry where gzdw = '" + Session["admin_id"].ToString() + "' ";
if (RadioButtonList1.SelectedValue != "all")
{
strqry = strqry + " and iif(isnull(tj_flag),'未审核',tj_flag) = '" + RadioButtonList1.SelectedValue + "'";
}
strqry = strqry + " ORDER BY ID;";
Session["dv_detail"] = DBFun.GetDataView(strqry);
DataView dv = (DataView)Session["dv_detail"];
GridView1.DataSource = dv;
GridView1.DataBind();
string str_sfzh;
RadioButtonList rbtnList_1;
for (int i = 0; i < this.GridView1.Rows.Count; i++)
{
str_sfzh = dv.Table.Rows[i + GridView1.PageIndex * GridView1.PageSize]["sfzh"].ToString();
rbtnList_1 = (RadioButtonList)this.GridView1.Rows[i].FindControl("rbtnList_xg");
rbtnList_1.SelectedValue = dv.Table.Rows[i + GridView1.PageIndex * GridView1.PageSize]["edit_flag"].ToString();
}
strqry = " SELECT iif(isnull(csgz),'未填写',csgz), count(csgz) AS num " +
" FROM cpry " +
" WHERE gzdw = '" + Session["admin_id"].ToString() + "'";
if (RadioButtonList1.SelectedValue != "all")
{
strqry += " and iif(isnull(tj_flag),'未审核',tj_flag) = '" + RadioButtonList1.SelectedValue + "'";
}
strqry += " GROUP BY csgz;";
OleDbDataReader reader = DBFun.dataReader(strqry);
lbl_tongji.Text = "";
if (reader != null)
{
try
{
while (reader.Read())
{
lbl_tongji.Text = lbl_tongji.Text + reader.GetString(0).ToString() + ":" + reader.GetInt32(1).ToString() + "<br/>";
}
}
catch { }
}
DBFun.closeDataReader(ref reader);
}
/// <summary>
/// Test CUBRID data types Get...()
/// </summary>
private static void Test_Various_DataTypes()
{
using (OleDbConnection conn = new OleDbConnection())
{
conn.ConnectionString = TestCasesOld.connString;
conn.Open();
TestCasesOld.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, ";
sql += "c_smallint smallint, ";
sql += "c_integer integer, ";
sql += "c_bigint bigint, ";
sql += "c_numeric numeric(15,1), ";
sql += "c_float float, ";
sql += "c_decimal decimal(15,3), ";
sql += "c_double double, ";
sql += "c_char char, ";
sql += "c_varchar varchar(4096), ";
sql += "c_time time, ";
sql += "c_date date, ";
sql += "c_timestamp timestamp, ";
sql += "c_datetime datetime, ";
sql += "c_bit bit(1), ";
sql += "c_varbit bit varying(4096), ";
sql += "c_monetary monetary, ";
sql += "c_string string";
sql += ")";
TestCasesOld.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, ";
sql += "11, ";
sql += "111, ";
sql += "1111, ";
sql += "1.1, ";
sql += "1.11, ";
sql += "1.111, ";
sql += "1.1111, ";
sql += "'a', ";
sql += "'abcdfghijk', ";
sql += "TIME '13:15:45 pm', ";
sql += "DATE '00-10-31', ";
sql += "TIMESTAMP '13:15:45 10/31/2008', ";
sql += "DATETIME '13:15:45 10/31/2008', ";
sql += "B'0', ";
sql += "B'0', ";
sql += "123456789, ";
sql += "'qwerty'";
sql += ")";
TestCasesOld.ExecuteSQL(sql, conn);
sql = "select * from t";
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
try
{
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Debug.Assert(reader.GetInt32(0) == 1);
Debug.Assert(reader.GetInt16(1) == 11);
Debug.Assert(reader.GetInt32(2) == 111);
Debug.Assert(reader.GetInt64(3) == 1111);
Debug.Assert(reader.GetDecimal(4) == (decimal)1.1);
Debug.Assert(reader.GetFloat(5) == (float)1.11); //"Single"
Debug.Assert(reader.GetDecimal(6) == (decimal)1.111);
Debug.Assert(reader.GetDouble(7) == (double)1.1111);
//We use GetString() because GetChar() is not supported or System.Data.OleDb.
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getchar
Debug.Assert(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Debug.Assert(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
//GetGateTime cannot cast just the time value in a DateTime object, so we use TimeSpan
Debug.Assert(reader.GetTimeSpan(10) == new TimeSpan(13, 15, 45)); //"TimeSpan"
Debug.Assert(reader.GetDateTime(11) == new DateTime(2000, 10, 31)); //"DateTime"
Debug.Assert(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
Console.WriteLine(reader.GetValue(13));
Debug.Assert(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
//The GetByte() method does not perform any conversions and the driver does not give tha data as Byte
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getbyte
//Use GetValue() or GetBytes() methods to retrieve BIT coulumn value
// Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit(1)" in CUBRID)
//Or
Byte[] value = new Byte[1];
reader.GetBytes(14, 0, value, 0, 1);
// Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit(1)" in CUBRID)
//Debug.Assert((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit varying(4096)" in CUBRID)
//Or
// reader.GetBytes(15, 0, value, 0, 1);
// Debug.Assert(value[0] == (byte)0);//"Byte[]" ("bit varying(4096)" in CUBRID)
Debug.Assert(reader.GetDouble(16) == 123456789.0); //"Double" ("Monetary" in CUBRID)
Debug.Assert(reader.GetString(17) == "qwerty"); //"String"
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
TestCasesOld.ExecuteSQL("drop table if exists t", conn);
}
}
public Form2()
{
InitializeComponent();
Func <ChartPoint, string> labelPoint = chartPoint =>
string.Format("{0} ({1:P})", chartPoint.Y, chartPoint.Participation);
// prepare variabel
int mati = 0;
int selamat = 0;
int dirawat = 0;
int total = 0;
// load data dari database
using (OleDbConnection connection = new OleDbConnection(connParam))
{
// buat query dan koneksikan ke db
OleDbCommand command = new OleDbCommand("select * from covid", connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
// fetch data
while (reader.Read())
{
selamat = reader.GetInt32(2);
mati = reader.GetInt32(3);
dirawat = reader.GetInt32(4);
total = reader.GetInt32(5);
}
// always call Close when done reading.
reader.Close();
}
// inject data ke chart
pieChart2.Series = new SeriesCollection
{
new PieSeries
{
Title = "Sembuh",
Values = new ChartValues <double> {
selamat
},
PushOut = 15,
DataLabels = true,
LabelPoint = labelPoint
},
new PieSeries
{
Title = "Dalam masa Perawatan",
Values = new ChartValues <double> {
dirawat
},
DataLabels = true,
LabelPoint = labelPoint
},
new PieSeries
{
Title = "Meninggal",
Values = new ChartValues <double> {
mati
},
DataLabels = true,
LabelPoint = labelPoint
}
};
pieChart2.LegendLocation = LegendLocation.Bottom;
cartesianChart1.Series = new SeriesCollection
{
new ColumnSeries
{
Title = "Sembuh",
Values = new ChartValues <double> {
231, 285, 108, 218
}
}
};
//adding series will update and animate the chart automatically
cartesianChart1.Series.Add(new ColumnSeries
{
Title = "Positif",
Values = new ChartValues <double> {
568, 490, 529, 489
}
});
cartesianChart1.Series.Add(new ColumnSeries
{
Title = "Meningal ",
Values = new ChartValues <double> {
15, 33, 13, 59
}
});
cartesianChart1.AxisX.Add(new Axis
{
Title = "Tanggal",
Labels = new[] { "14 Mei", "15 Mei", "16 Mei", "17 Mei" }
});
cartesianChart1.AxisY.Add(new Axis
{
Title = "Jumlah",
LabelFormatter = value => value.ToString("N")
});
}
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
// Private methods
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
private ProfileInfoCollection GetProfilesForQuery(string sqlQuery, OleDbParameter[] args, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("Page index must be non-negative", "pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("Page size must be positive", "pageSize");
}
long lBound = (long)pageIndex * pageSize;
long uBound = lBound + pageSize - 1;
if (uBound > System.Int32.MaxValue)
{
throw new ArgumentException("pageIndex*pageSize too large");
}
try
{
AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
ProfileInfoCollection profiles = new ProfileInfoCollection();
OleDbDataReader reader = null;
try
{
OleDbCommand cmd = new OleDbCommand(sqlQuery, holder.Connection);
cmd.Parameters.Add(new OleDbParameter("@AppId", GetApplicationId(holder)));
int len = args.Length;
for (int iter = 0; iter < len; iter++)
{
cmd.Parameters.Add(args[iter]);
}
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
totalRecords = 0;
while (reader.Read())
{
totalRecords++;
if (totalRecords - 1 < lBound || totalRecords - 1 > uBound)
{
continue;
}
string username;
DateTime dtLastActivity, dtLastUpdated;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = reader.GetDateTime(2);
dtLastUpdated = reader.GetDateTime(3);
int size = reader.GetInt32(4);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
}
return(profiles);
}
catch (Exception e)
{
throw AccessConnectionHelper.GetBetterException(e, holder);
}
finally
{
if (reader != null)
{
reader.Close();
}
holder.Close();
}
}
catch
{
throw;
}
}
public static UserModel getUserModel(LoginModel model)
{
UserModel user = null;
OleDbConnection connection = new OleDbConnection(
ConfigurationManager.ConnectionStrings["mainDB"].ConnectionString);
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM Users WHERE Login='******' " +
"AND Password='******'", connection);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
user = new UserModel(reader.GetInt32(0), reader.GetString(1),
reader.GetString(2), reader.GetString(3));
break;
}
reader.Close();
connection.Close();
if (user == null)
{
return(null);
}
else
{
connection.Open();
PeopleModel people = null;
if (user.Status == "Student")
{
command = new OleDbCommand("SELECT * FROM Students WHERE " +
"Id_User="******"Teacher")
{
command = new OleDbCommand("SELECT * FROM Teachers WHERE " +
"Id_User=" + user.Id, connection);
reader = command.ExecuteReader();
while (reader.Read())
{
people = new TeacherModel(reader.GetInt32(0),
reader.GetString(2), reader.GetString(3), reader.GetString(4));
break;
}
reader.Close();
}
connection.Close();
user.People = people;
if (people == null)
{
return(null);
}
else
{
return(user);
}
}
}
public void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
{
string username = (string)context["UserName"];
bool userIsAuthenticated = (bool)context["IsAuthenticated"];
if (String.IsNullOrEmpty(username) || collection.Count < 1)
{
return;
}
OleDbConnection conn = null;
OleDbDataReader reader = null;
OleDbCommand cmd = null;
try
{
bool anyItemsToSave = false;
// First make sure we have at least one item to save
foreach (SettingsPropertyValue pp in collection)
{
if (pp.IsDirty)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
anyItemsToSave = true;
break;
}
}
if (!anyItemsToSave)
{
return;
}
conn = new OleDbConnection(SqlHelper.ConnString);
conn.Open();
List <ProfileColumnData> columnData = new List <ProfileColumnData>(collection.Count);
foreach (SettingsPropertyValue pp in collection)
{
if (!userIsAuthenticated)
{
bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
//Normal logic for original SQL provider
//if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
//Can eliminate unnecessary updates since we are using a table though
if (!pp.IsDirty)
{
continue;
}
string persistenceData = pp.Property.Attributes["CustomProviderData"] as string;
// If we can't find the table/column info we will ignore this data
if (String.IsNullOrEmpty(persistenceData))
{
// REVIEW: Perhaps we should throw instead?
continue;
}
string[] chunk = persistenceData.Split(new char[] { ';' });
if (chunk.Length != 2)
{
// REVIEW: Perhaps we should throw instead?
continue;
}
if (chunk[1] == "int")
{
chunk[1] = "integer";
}
string columnName = chunk[0];
// REVIEW: Should we ignore case?
OleDbType datatype = (OleDbType)Enum.Parse(typeof(OleDbType), chunk[1], true);
object value = null;
// REVIEW: Is this handling null case correctly?
if (pp.Deserialized && pp.PropertyValue == null)
{ // is value null?
value = DBNull.Value;
}
else
{
if (pp.Deserialized && (pp.Property.PropertyType != typeof(List <SnitzLink>)))
{
value = pp.PropertyValue;
}
else
{
value = pp.SerializedValue ?? pp.PropertyValue;
}
}
// REVIEW: Might be able to ditch datatype
columnData.Add(new ProfileColumnData(columnName, null, value, datatype));
}
// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table
int userId = 0;
cmd = new OleDbCommand("SELECT MEMBER_ID FROM " + Config.MemberTablePrefix + "MEMBERS WHERE M_NAME = @Username", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Username", username);
try
{
reader = cmd.ExecuteReader();
if (reader.Read())
{
userId = reader.GetInt32(0);
}
else
{
reader.Close();
cmd.Dispose();
reader = null;
}
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
cmd.Dispose();
}
// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
OleDbParameter user = new OleDbParameter("@UserId", OleDbType.Integer)
{
Value = userId
};
var exists = SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, String.Format("SELECT COUNT(UserId) FROM {0} WHERE UserId = @UserId", TableName), user);
List <OleDbParameter> parms = new List <OleDbParameter>();
parms.Add(user);
// Build up strings used in the query
StringBuilder columnStr = new StringBuilder();
StringBuilder valueStr = new StringBuilder();
StringBuilder setStr = new StringBuilder();
int count = 0;
foreach (ProfileColumnData data in columnData)
{
columnStr.Append(", ");
valueStr.Append(", ");
columnStr.Append(data.ColumnName);
string valueParam = "@Value" + count;
valueStr.Append(valueParam);
OleDbParameter p = new OleDbParameter(valueParam, data.DataType)
{
Value = data.Value ?? 0
};
parms.Add(p);
// REVIEW: Can't update Timestamps?
if (data.DataType != OleDbType.DBTimeStamp)
{
if (count > 0)
{
setStr.Append(",");
}
setStr.Append(data.ColumnName);
setStr.Append("=");
setStr.Append(valueParam);
}
++count;
}
StringBuilder sqlCommand = new StringBuilder();
if (exists == (object)0)
{
sqlCommand.Append("UPDATE ").Append(TableName).Append(" SET ").Append(setStr.ToString());
sqlCommand.Append(" WHERE UserId = @UserId");
}
else
{
sqlCommand.Append("INSERT INTO ").Append(TableName).Append(" (UserId").Append(columnStr.ToString());
sqlCommand.Append(") VALUES (@UserId").Append(valueStr.ToString()).Append(")");
}
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, sqlCommand.ToString(), parms.ToArray());
}
finally
{
if (reader != null)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
conn.Close();
}
}
}
static void Main()
{
// Connection string and SQL query
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\POS Database\POS Construction_be.mdb";
string strSQL = "SELECT ID, si_SaturdayDeliveryOption, p_TrackingNumber, si_ServiceType, UPSTNTDay, si_ReturnServiceOption, si_PickUpDate, st_City, st_State, st_PostalZipCode, st_Country, RecipientCode FROM TRACKING WHERE((UPSTNTDay) = '' or (isNull(UPSTNTDay) AND ((si_ReturnServiceOption) = 'N') AND ((si_VoidIndicator) = 'N')))";
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
// Create a command and set its connection
OleDbCommand command = new OleDbCommand(strSQL, connection);
try
{
connection.Open();
using (OleDbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
string strTrackID = reader.GetString(reader.GetOrdinal("p_TrackingNumber"));
string strSatDelOpt = reader.GetString(reader.GetOrdinal("si_SaturdayDeliveryOption"));
string strCity = reader.GetString(reader.GetOrdinal("st_City"));
string strState = reader.GetString(reader.GetOrdinal("st_State"));
string strZip = reader.GetString(reader.GetOrdinal("st_PostalZipCode"));
string strShipdate = reader.GetString(reader.GetOrdinal("si_PickUpDate"));
string strCountry = reader.GetString(reader.GetOrdinal("st_Country"));
string strServiceType = reader.GetString(reader.GetOrdinal("si_ServiceType"));
Int32 strID = reader.GetInt32(reader.GetOrdinal("ID"));
try
{
TimeInTransitService tntService = new TimeInTransitService();
TimeInTransitRequest tntRequest = new TimeInTransitRequest();
RequestType request = new RequestType();
String[] requestOption = { "TNT" };
request.RequestOption = requestOption;
tntRequest.Request = request;
tntRequest.SaturdayDeliveryInfoRequestIndicator = "N";
RequestShipFromType shipFrom = new RequestShipFromType();
RequestShipFromAddressType addressFrom = new RequestShipFromAddressType();
addressFrom.City = "Edina";
addressFrom.CountryCode = "US";
addressFrom.PostalCode = "55435";
addressFrom.StateProvinceCode = "MN";
shipFrom.Address = addressFrom;
tntRequest.ShipFrom = shipFrom;
RequestShipToType shipTo = new RequestShipToType();
RequestShipToAddressType addressTo = new RequestShipToAddressType();
addressTo.City = strCity;
addressTo.CountryCode = strCountry;
addressTo.PostalCode = strZip;
addressTo.StateProvinceCode = strState;
shipTo.Address = addressTo;
tntRequest.ShipTo = shipTo;
PickupType pickup = new PickupType();
string left = strShipdate.Substring(0, 8);
pickup.Date = left;
pickup.Time = "120000";
tntRequest.Pickup = pickup;
if (strCountry == "CA")
{
ShipmentWeightType shipmentWeight = new ShipmentWeightType();
shipmentWeight.Weight = "10";
CodeDescriptionType unitOfMeasurement = new CodeDescriptionType();
unitOfMeasurement.Code = "KGS";
unitOfMeasurement.Description = "Kilograms";
shipmentWeight.UnitOfMeasurement = unitOfMeasurement;
tntRequest.ShipmentWeight = shipmentWeight;
tntRequest.TotalPackagesInShipment = "1";
InvoiceLineTotalType invoiceLineTotal = new InvoiceLineTotalType();
invoiceLineTotal.CurrencyCode = "CAD";
invoiceLineTotal.MonetaryValue = "10";
tntRequest.InvoiceLineTotal = invoiceLineTotal;
tntRequest.MaximumListSize = "1";
}
else
{
ShipmentWeightType shipmentWeight = new ShipmentWeightType();
shipmentWeight.Weight = "10";
CodeDescriptionType unitOfMeasurement = new CodeDescriptionType();
unitOfMeasurement.Code = "LBS";
unitOfMeasurement.Description = "pounds";
shipmentWeight.UnitOfMeasurement = unitOfMeasurement;
tntRequest.ShipmentWeight = shipmentWeight;
tntRequest.TotalPackagesInShipment = "1";
InvoiceLineTotalType invoiceLineTotal = new InvoiceLineTotalType();
invoiceLineTotal.CurrencyCode = "USD";
invoiceLineTotal.MonetaryValue = "10";
tntRequest.InvoiceLineTotal = invoiceLineTotal;
tntRequest.MaximumListSize = "1";
}
UPSSecurity upss = new UPSSecurity();
UPSSecurityServiceAccessToken upsSvcToken = new UPSSecurityServiceAccessToken();
upsSvcToken.AccessLicenseNumber = "1D5E2960D39CB1B5 ";
upss.ServiceAccessToken = upsSvcToken;
UPSSecurityUsernameToken upsSecUsrnameToken = new UPSSecurityUsernameToken();
upsSecUsrnameToken.Username = "******";
upsSecUsrnameToken.Password = "******";
upss.UsernameToken = upsSecUsrnameToken;
tntService.UPSSecurityValue = upss;
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12 | System.Net.SecurityProtocolType.Tls | System.Net.SecurityProtocolType.Tls11; //This line will ensure the latest security protocol for consuming the web service call.
TimeInTransitResponse tntResponse = tntService.ProcessTimeInTransit(tntRequest);
if (strServiceType == "WORLDWIDE SAVER")
{
strServiceType = "Worldwide Saver";
}
strServiceType = "UPS " + strServiceType;
if (tntResponse.Item != null)
{
var timeInTransitResponse = (TransitResponseType)tntResponse.Item;
foreach (var serviceSummaryType in timeInTransitResponse.ServiceSummary)
{
string strUPScode = serviceSummaryType.Service.Code;
if (serviceSummaryType.Service.Description == strServiceType && strSatDelOpt == "Y" && strUPScode.Substring(strUPScode.Length - 1, 1) == "S" || serviceSummaryType.Service.Description == strServiceType && strSatDelOpt != "Y" && strUPScode.Substring(strUPScode.Length - 1, 1) != "S")
{
Console.WriteLine(addressTo.City + strState + ", " + strCity + " - " + serviceSummaryType.EstimatedArrival.BusinessDaysInTransit);
string intUPSTNT = serviceSummaryType.EstimatedArrival.BusinessDaysInTransit;
string strTNTDay = serviceSummaryType.EstimatedArrival.Arrival.Date;
DateTime d = DateTime.ParseExact(strTNTDay, "yyyyMMdd", CultureInfo.InvariantCulture);
string strTNTtime = serviceSummaryType.EstimatedArrival.Arrival.Time;
if (Convert.ToInt32(strTNTtime) >= 230000)
{
strTNTtime = "End of Day";
}
else
{
IFormatProvider format = System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat;
DateTime time_24 = DateTime.ParseExact(strTNTtime, "HHmmss", format);
strTNTtime = time_24.ToString("h:mm tt");
}
strSQL = "UPDATE TRACKING SET UPSTNTDay = '" + d.ToString("MM/dd/yyyy") + "', UPSTNTTime = '" + strTNTtime + "' WHERE p_TrackingNumber ='" + strTrackID + "'";
command = new OleDbCommand(strSQL, connection);
command.ExecuteReader();
Console.Write(strTrackID);
Console.Write("Business Days in Transit: ");
Console.Write(serviceSummaryType.EstimatedArrival.BusinessDaysInTransit);
Console.Write(", Arrival Date: ");
Console.Write(serviceSummaryType.EstimatedArrival.Arrival.Date);
Console.Write(", Service: (");
Console.Write(serviceSummaryType.Service.Code);
Console.Write(") ");
Console.Write(serviceSummaryType.Service.Description);
Console.WriteLine(serviceSummaryType.EstimatedArrival.Arrival.Time);
}
}
}
}
catch (System.Web.Services.Protocols.SoapException ex)
{
Console.WriteLine("");
Console.WriteLine("---------Time In Transit Web Service returns error----------------");
Console.WriteLine("---------\"Hard\" is user error \"Transient\" is system error----------------");
Console.WriteLine("SoapException Message= " + ex.Message);
Console.WriteLine("");
Console.WriteLine("SoapException Category:Code:Message= " + ex.Detail.LastChild.InnerText);
Console.WriteLine("");
Console.WriteLine("SoapException XML String for all= " + ex.Detail.LastChild.OuterXml);
Console.WriteLine("");
Console.WriteLine("SoapException StackTrace= " + ex.StackTrace);
Console.WriteLine("-------------------------");
Console.WriteLine("");
}
catch (System.ServiceModel.CommunicationException ex)
{
Console.WriteLine("");
Console.WriteLine("--------------------");
Console.WriteLine("CommunicationException= " + ex.Message);
Console.WriteLine("CommunicationException-StackTrace= " + ex.StackTrace);
Console.WriteLine("-------------------------");
Console.WriteLine("");
}
catch (Exception ex)
{
Console.WriteLine("");
Console.WriteLine("-------------------------");
Console.WriteLine(" Generaal Exception= " + ex.Message);
Console.WriteLine(" Generaal Exception-StackTrace= " + ex.StackTrace);
Console.WriteLine("-------------------------");
}
finally
{
}
}
}
}
}
catch (System.ServiceModel.CommunicationException ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(" General Exception-StackTrace= " + ex.StackTrace);
}
}
Console.WriteLine("done");
//Console.ReadKey();
}
private static void PoliceData()
{
string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Jose\Dropbox\dev\net\2010\Truenorth\Compass\Solution Files\Data\SampleMDB.mdb;Persist Security Info=False";
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.CompassDbConnString))
using (OleDbConnection oleConn = new OleDbConnection(connStr))
{
conn.Open();
oleConn.Open();
using (SqlCommand cmd = new SqlCommand())
using (OleDbCommand oleCmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText =
"INSERT INTO [dbo].[Location] ([LocationTypeEnumCode]," + "\r\n" +
" [LocationGroupEnumCode]," + "\r\n" +
" [SourceIntUniqueKey]," + "\r\n" +
" [Name]," + "\r\n" +
" [DescriptionEn]," + "\r\n" +
" [DescriptionEs]," + "\r\n" +
" [Address1]," + "\r\n" +
" [Address2]," + "\r\n" +
" [City]," + "\r\n" +
" [State]," + "\r\n" +
" [ZipCode]," + "\r\n" +
" [Latitude]," + "\r\n" +
" [Longitude]," + "\r\n" +
" [IncidentDate])" + "\r\n" +
"VALUES (1," + "\r\n" +
" 6," + "\r\n" +
" @SourceIntUniqueKey," + "\r\n" +
" @Name," + "\r\n" +
" @DescriptionEn," + "\r\n" +
" @DescriptionEs," + "\r\n" +
" @Address1," + "\r\n" +
" @Address2," + "\r\n" +
" @City," + "\r\n" +
" @State," + "\r\n" +
" @ZipCode," + "\r\n" +
" @Latitude," + "\r\n" +
" @Longitude," + "\r\n" +
" @IncidentDate);";
SqlParameter sourceIntUniqueKeyParam = cmd.Parameters.Add("@SourceIntUniqueKey", System.Data.SqlDbType.Int);
SqlParameter nameParam = cmd.Parameters.Add("@Name", System.Data.SqlDbType.VarChar, 30);
SqlParameter descriptionEnParam = cmd.Parameters.Add("@DescriptionEn", System.Data.SqlDbType.VarChar, 50);
SqlParameter descriptionEsParam = cmd.Parameters.Add("@DescriptionEs", System.Data.SqlDbType.VarChar, 50);
SqlParameter address1Param = cmd.Parameters.Add("@Address1", System.Data.SqlDbType.VarChar, 50);
SqlParameter address2Param = cmd.Parameters.Add("@Address2", System.Data.SqlDbType.VarChar, 50);
SqlParameter cityParam = cmd.Parameters.Add("@City", System.Data.SqlDbType.VarChar, 30);
SqlParameter stateParam = cmd.Parameters.Add("@State", System.Data.SqlDbType.VarChar, 30);
SqlParameter zipCodeParam = cmd.Parameters.Add("@ZipCode", System.Data.SqlDbType.VarChar, 10);
SqlParameter latitudeParam = cmd.Parameters.Add("@Latitude", System.Data.SqlDbType.Float);
SqlParameter longitudeParam = cmd.Parameters.Add("@Longitude", System.Data.SqlDbType.Float);
SqlParameter incidentDateParam = cmd.Parameters.Add("@IncidentDate", System.Data.SqlDbType.DateTime);
stateParam.Value = "PR";
address1Param.Value = string.Empty;
address2Param.Value = string.Empty;
cityParam.Value = string.Empty;
zipCodeParam.Value = string.Empty;
oleCmd.Connection = oleConn;
oleCmd.CommandText =
"SELECT incidencia2013sample.[OBJECTID]," + "\r\n" +
" incidencia2013sample.[fecha_ocurrencia]," + "\r\n" +
" incidencia2013sample.[hora_ocurrencia]," + "\r\n" +
" incidencia2013sample.[FK_delito_cometido_Tipo_I]," + "\r\n" +
" incidencia2013sample.[POINT_X]," + "\r\n" +
" incidencia2013sample.[POINT_Y]" + "\r\n" +
" FROM incidencia2013sample;";
CoordSys A = CoordSys.GetCS("US_SPC83", "PRVI83", "PUERTO RICO", "METERS");
CoordSys B = CoordSys.GetCS("LAT_LONG", "LAT-LONG", "PUERTO RICO", "METERS");
double[] LonX = new double[1];
double[] LatY = new double[1];
double[] Z = new double[1];
Z[0] = 0.0;
using (OleDbDataReader oleReader = oleCmd.ExecuteReader())
{
while (oleReader.Read())
{
if (!oleReader.IsDBNull(0) && !oleReader.IsDBNull(1) && !oleReader.IsDBNull(2) && !oleReader.IsDBNull(3) && !oleReader.IsDBNull(4) && !oleReader.IsDBNull(5))
{
int objId = oleReader.GetInt32(0);
DateTime fecha_ocurrencia = oleReader.GetDateTime(1);
string hora_ocurrencia = oleReader.GetString(2).EnsureNotNull(true);
int delito_cometido_Tipo_I = oleReader.GetInt32(3);
double x = oleReader.GetDouble(4);
double y = oleReader.GetDouble(5);
DateTime hour;
if (DateTime.TryParse(hora_ocurrencia, out hour))
{
fecha_ocurrencia = new DateTime(fecha_ocurrencia.Year, fecha_ocurrencia.Month, fecha_ocurrencia.Day, hour.Hour, hour.Minute, hour.Second);
}
int proveedorId = objId;
string nombre = "Delito Tipo I - " + delito_cometido_Tipo_I.ToString();
string estAuthNumber = delito_cometido_Tipo_I.ToString();
LonX[0] = DMS.GetLon(x.ToString());
LatY[0] = DMS.GetLat(y.ToString());
CoordSys.Transform(A, B, LonX, LatY, Z, 1);
double longitud = Math.Round(LonX[0], 9);
double latitud = Math.Round(LatY[0], 9);
sourceIntUniqueKeyParam.Value = proveedorId;
nameParam.Value = nombre;
descriptionEnParam.Value = estAuthNumber;
descriptionEsParam.Value = estAuthNumber;
latitudeParam.Value = latitud;
longitudeParam.Value = longitud;
incidentDateParam.Value = fecha_ocurrencia;
cmd.ExecuteNonQuery();
}
}
}
}
}
}
