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(); } } } } } }