private void cmdLer_Click(object sender, EventArgs e) { ListViewItem item; int cont = 0; string ano = null; DateTime? data = null; string convertData = null; string fone = null; try { _oleCmd.CommandText = "SELECT Nome, Titulo, Situacao, Pagamento, Telefone, obs, obs2 FROM [Socios$]"; OleDbDataReader reader = _oleCmd.ExecuteReader(); while (reader.Read()) { if (reader.GetValue(3).ToString().Length == 4) { ano = reader.GetValue(3).ToString(); } else if (reader.GetValue(3).ToString().Length > 4) { convertData = reader.GetValue(3).ToString().Substring(0, 10); data = DateTime.Parse(convertData.ToString()); } else { ano = null; } if (reader.GetValue(4).ToString().Length == 8) { fone = "(00) 9" + reader.GetValue(4).ToString().Substring(0, 4) + "-" + reader.GetValue(4).ToString().Substring(4, 4); } else if (reader.GetValue(4).ToString().Length == 9) { fone = "(00) " + reader.GetValue(4).ToString().Substring(0, 5) + "-" + reader.GetValue(4).ToString().Substring(5, 4); } else { fone = ""; } item = new ListViewItem(); item.Text = reader.GetValue(0).ToString(); item.SubItems.Add(reader.GetValue(1).ToString()); item.SubItems.Add(reader.GetValue(2).ToString()); if (!string.IsNullOrEmpty(ano)) { item.SubItems.Add(ano.ToString()); } else if (data != null) { item.SubItems.Add(data.ToString().Substring(0, 10)); } else { item.SubItems.Add(""); } item.SubItems.Add(fone.ToString()); item.SubItems.Add(reader.GetValue(5).ToString() + " - " + reader.GetValue(6).ToString()); lstImportacao.Items.Add(item); cont += 1; ano = null; data = null; } reader.Close(); lblCont.Text = cont.ToString(); } catch (SystemException ex) { MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void ImportTable_Export() { string strSQL = "SELECT Tabelle,Reihenfolge,Export,ExportData,DropOnly " + "FROM _Export ORDER BY Reihenfolge"; OleDbCommand cmd = mcon.CreateCommand(); cmd.CommandText = strSQL; OleDbDataReader dr = null; try { dr = cmd.ExecuteReader(); if (mProjectSettings.entityexport == null) { mProjectSettings.entityexport = new projectsettingsEntityexport(); } ArrayList alEntityExport = new ArrayList(); while (dr.Read()) { string strTable = dr.GetString(dr.GetOrdinal("Tabelle")); //ignore System-Tables if (strTable.ToLower().Equals("_export") || strTable.ToLower().Equals("_pventitygenerator")) { continue; } projectsettingsEntityexportExportentity e = new projectsettingsEntityexportExportentity(); e.entity = strTable; object val = dr.GetValue(dr.GetOrdinal("Reihenfolge")); if (!val.Equals(DBNull.Value)) { e.sortno = val.ToString(); } e.exportstructure = false; val = dr.GetValue(dr.GetOrdinal("Export")); if (!val.Equals(DBNull.Value)) { e.exportstructure = (bool)val; } e.exportdata = false; val = dr.GetValue(dr.GetOrdinal("ExportData")); if (!val.Equals(DBNull.Value)) { e.exportdata = (bool)val; } e.exportdrop = false; val = dr.GetValue(dr.GetOrdinal("DropOnly")); if (!val.Equals(DBNull.Value)) { e.exportdrop = (bool)val; } alEntityExport.Add(e); } dr.Close(); if (alEntityExport.Count > 0) { mProjectSettings.entityexport.exportentities = (projectsettingsEntityexportExportentity[]) alEntityExport.ToArray(typeof(projectsettingsEntityexportExportentity)); } } finally { if (dr != null) { dr.Close(); } } }
private void DefaultMethod() { AppState appState = AppState.FromJson(Request.Form["state"]); Configuration config = AppContext.GetConfiguration(); Configuration.ApplicationRow applicationRow = config.Application.First(o => o.ApplicationID == appState.Application); Configuration.SearchRow searchRow = config.Search.First(o => o.SearchID == appState.Search); Dictionary <String, Object> criteria = appState.SearchCriteria; List <String> levels = new List <String>(); if (!applicationRow.IsZoneLevelIDNull()) { levels = applicationRow.ZoneLevelRow.GetLevelRows().Select(o => o.LevelID).ToList(); } List <String> where = new List <String>(); List <Object> parameters = new List <Object>(); foreach (string criteriaID in criteria.Keys) { Configuration.SearchInputFieldRow searchInputFieldRow = config.SearchInputField.First(o => o.FieldID == criteriaID); switch (searchInputFieldRow.FieldType) { case "autocomplete": case "date": case "list": case "number": case "text": where.Add(searchInputFieldRow.ColumnName + " = ?"); parameters.Add(criteria[criteriaID]); break; case "textcontains": where.Add(searchInputFieldRow.ColumnName + " like ?"); parameters.Add("%" + criteria[criteriaID].ToString() + "%"); break; case "textstarts": where.Add(searchInputFieldRow.ColumnName + " like ?"); parameters.Add(criteria[criteriaID].ToString() + "%"); break; case "daterange": case "numberrange": ArrayList values = (ArrayList)criteria[criteriaID]; if (values[0] != null) { where.Add(searchInputFieldRow.ColumnName + " >= ?"); parameters.Add(values[0]); } if (values[1] != null) { where.Add(searchInputFieldRow.ColumnName + " <= ?"); parameters.Add(values[1]); } break; } } Dictionary <String, Object> result = new Dictionary <String, Object>(); using (OleDbCommand command = searchRow.GetSelectCommand()) { command.CommandText = String.Format(command.CommandText, String.Join(" and ", where.ToArray())); for (int i = 0; i < parameters.Count; ++i) { command.Parameters.AddWithValue(i.ToString(), parameters[i]); } using (OleDbDataReader reader = command.ExecuteReader()) { // get the indexes of the ID columns int mapIdColumn = reader.GetColumnIndex("MapID"); int dataIdColumn = reader.GetColumnIndex("DataID"); int levelIdColumn = levels.Count > 0 ? reader.GetColumnIndex("LevelID") : -1; // write the column headers List <String> headers = new List <String>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn) { headers.Add(reader.GetName(i)); } } result.Add("headers", headers); // write the data List <Dictionary <String, Object> > rows = new List <Dictionary <String, Object> >(); while (reader.Read()) { if (!reader.IsDBNull(mapIdColumn) && !reader.IsDBNull(dataIdColumn)) { Dictionary <String, String> id = new Dictionary <String, String>(); id.Add("m", reader.GetValue(mapIdColumn).ToString()); if (dataIdColumn > -1 && !reader.IsDBNull(dataIdColumn)) { id.Add("d", reader.GetValue(dataIdColumn).ToString()); } if (levelIdColumn > -1 && !reader.IsDBNull(levelIdColumn)) { string levelId = reader.GetValue(levelIdColumn).ToString(); if (levels.Contains(levelId)) { id.Add("l", levelId); } } List <Object> values = new List <Object>(); for (int i = 0; i < reader.FieldCount; ++i) { if (i != mapIdColumn && i != dataIdColumn && i != levelIdColumn) { values.Add(reader.IsDBNull(i) ? null : reader.GetValue(i)); } } Dictionary <String, Object> row = new Dictionary <String, Object>(); row.Add("id", id); row.Add("v", values); rows.Add(row); } } result.Add("rows", rows); } } ReturnJson(result); }
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 load(bool type) { int i = 0; dataGridView1.Rows.Clear(); conn.Open(); connLocal.Open(); OleDbCommand cmd = new OleDbCommand(getCommand(type), conn); //создаем команду OleDbCommand cmd2; OleDbDataReader dr2; comboBox2.Items.Clear(); comboBox1.Items.Clear(); comboBox3.Items.Clear(); using (OleDbDataReader dr = cmd.ExecuteReader()) { //выполняем while (dr.Read()) { //читаем результат dataGridView1.Rows.Add(); comboBox1.Items.Add(dr.GetValue(1).ToString()); comboBox2.Items.Add(dr.GetValue(1).ToString()); comboBox3.Items.Add(dr.GetValue(1).ToString()); dataGridView1[0, i].Value = dr.GetValue(1).ToString(); dataGridView1[1, i].Value = dr.GetValue(2).ToString(); dataGridView1[2, i].Value = dr.GetValue(3).ToString(); dataGridView1[3, i].Value = dr.GetValue(4).ToString(); dataGridView1[4, i].Value = dr.GetValue(5).ToString(); dataGridView1[5, i].Value = dr.GetValue(6).ToString(); dataGridView1[6, i].Value = dr.GetValue(7).ToString(); dataGridView1[7, i].Value = dr.GetValue(0).ToString(); cmd2 = new OleDbCommand(getCommandById(dr.GetValue(0).ToString()), connLocal); dr2 = cmd2.ExecuteReader(); dr2.Read(); dataGridView1[8, i].Value = dr2.GetValue(0).ToString(); dataGridView1[9, i].Value = dr2.GetValue(1).ToString(); dataGridView1.Rows[i].ReadOnly = true; i++; } } conn.Close(); connLocal.Close(); }
public static Field fromSnapshot(Table table, OleDbDataReader reader) { string name = reader.GetValue(reader.GetOrdinal("name")).ToString(); int dataType = int.Parse(reader.GetValue(reader.GetOrdinal("dataType")).ToString()); Field field = null; if (table.fields.ContainsKey(name)) { field = table.fields[name]; } switch (dataType) { case (int)OleDbType.BigInt: case (int)OleDbType.Decimal: case (int)OleDbType.Currency: case (int)OleDbType.Double: case (int)OleDbType.Integer: case (int)OleDbType.Numeric: case (int)OleDbType.Single: case (int)OleDbType.SmallInt: case (int)OleDbType.UnsignedBigInt: case (int)OleDbType.UnsignedInt: case (int)OleDbType.UnsignedSmallInt: case (int)OleDbType.UnsignedTinyInt: { if (field == null) { field = new FieldNumeric(table, reader); } else { field.read(reader); } break; } case (int)OleDbType.Date: case (int)OleDbType.DBDate: case (int)OleDbType.DBTime: case (int)OleDbType.DBTimeStamp: case (int)OleDbType.Filetime: { if (field == null) { field = new FieldDateTime(table, reader); } else { field.read(reader); } break; } case (int)OleDbType.BSTR: case (int)OleDbType.Char: case (int)OleDbType.LongVarChar: case (int)OleDbType.LongVarWChar: case (int)OleDbType.VarChar: case (int)OleDbType.VarWChar: case (int)OleDbType.WChar: { if (field == null) { field = new FieldString(table, reader); } else { field.read(reader); } break; } case (int)OleDbType.Boolean: { if (field == null) { field = new FieldBoolean(table, reader); } else { field.read(reader); } break; } default: { Console.WriteLine($"Error : Unknown OleDb Data Type {dataType} | Table : {table.name} | Field : {name}"); break; } } // don't add multiple if (field != null && !table.fields.ContainsKey(field.name)) { table.fields.Add(field.name, field); } return(field); }
private void PrepareIds(out StringCollection targetIds, out StringCollection filteredIds, out StringCollection selectionIds) { targetIds = _appState.TargetIds.Clone(); selectionIds = _appState.SelectionIds.Clone(); // segregate the target IDs that pass through the query from // those that do not if (String.IsNullOrEmpty(_appState.Query)) { filteredIds = new StringCollection(); } else { Configuration config = AppContext.GetConfiguration(); Configuration.QueryRow query = config.Query.FindByQueryID(_appState.Query); using (OleDbCommand command = query.GetDatabaseCommand()) { command.Parameters[0].Value = _appState.TargetIds.Join(","); if (command.Parameters.Count > 1) { command.Parameters[1].Value = AppUser.GetRole(); } using (OleDbDataReader reader = command.ExecuteReader()) { int mapIdColumn = reader.GetOrdinal("MapID"); filteredIds = targetIds; targetIds = new StringCollection(); while (reader.Read()) { if (!reader.IsDBNull(mapIdColumn)) { string mapId = reader.GetValue(mapIdColumn).ToString(); filteredIds.Remove(mapId); if (!targetIds.Contains(mapId)) { targetIds.Add(mapId); } } } } command.Connection.Dispose(); } } if (targetIds.Count > 0) { // remove the active ID from the targets if (_appState.ActiveMapId.Length > 0) { targetIds.Remove(_appState.ActiveMapId); } // remove the selection IDs from the targets if necessary if (_appState.TargetLayer == _appState.SelectionLayer && _appState.SelectionIds.Count > 0) { if (_appState.ActiveMapId.Length > 0) { selectionIds.Remove(_appState.ActiveMapId); } foreach (string selectionId in _appState.SelectionIds) { targetIds.Remove(selectionId); } } } }
private void DefaultMethod() { string v = Request.Form["visiblelayers"]; string[] visibleLayers = v == null ? new string[0] : v.Split('\u0001'); string level = Request.Form["level"]; double x = Convert.ToDouble(Request.Form["x"]); double y = Convert.ToDouble(Request.Form["y"]); double distance = Convert.ToDouble(Request.Form["distance"]); double scale = Convert.ToDouble(Request.Form["scale"]); Configuration config = AppContext.GetConfiguration(); Configuration.MapTabRow mapTab = config.MapTab.First(o => o.MapTabID == Request.Form["maptab"]); CommonDataFrame dataFrame = AppContext.GetDataFrame(mapTab); Dictionary <String, Configuration.LayerRow> layers = new Dictionary <String, Configuration.LayerRow>(); Dictionary <String, Configuration.LayerFunctionRow> layerFunctions = new Dictionary <String, Configuration.LayerFunctionRow>(); foreach (Configuration.MapTabLayerRow mapTabLayer in mapTab.GetMapTabLayerRows()) { bool isCandidateLayer = mapTab.IsInteractiveLegendNull() || mapTab.InteractiveLegend == 0; if (!isCandidateLayer) { bool shownInLegend = !mapTabLayer.IsShowInLegendNull() && mapTabLayer.ShowInLegend == 1; bool checkedInLegend = mapTabLayer.IsCheckInLegendNull() || mapTabLayer.CheckInLegend < 0 || visibleLayers.Any(o => o == mapTabLayer.LayerID); isCandidateLayer = !shownInLegend || checkedInLegend; } if (isCandidateLayer) { Configuration.LayerRow layer = mapTabLayer.LayerRow; Configuration.LayerFunctionRow layerFunction = layer.GetLayerFunctionRows().FirstOrDefault(o => o.FunctionName.ToLower() == "maptip"); if (layerFunction != null) { layers.Add(layer.LayerName, layer); layerFunctions.Add(layer.LayerName, layerFunction); } } } string tipText = null; for (int i = 0; i < dataFrame.Layers.Count - 1 && tipText == null; ++i) { CommonLayer commonLayer = dataFrame.Layers[i]; string id = null; if (layers.ContainsKey(commonLayer.Name) && commonLayer.IsWithinScaleThresholds(scale)) { if (commonLayer.Type == CommonLayerType.Feature) { Configuration.LayerRow layer = layers[commonLayer.Name]; string levelQuery = layer.GetLevelQuery(commonLayer, level); CommonField keyField = commonLayer.FindField(layer.KeyField); DataTable table = commonLayer.GetFeatureTable(keyField.Name, levelQuery, x, y, commonLayer.FeatureType == OgcGeometryType.MultiPolygon ? 0 : distance * scale); if (table != null && table.Rows.Count > 0) { id = table.Rows[table.Rows.Count - 1][0].ToString(); } } if (commonLayer.Type == CommonLayerType.Image) { id = ((AgsLayer)commonLayer).GetRasterValue(x, y); } } if (!String.IsNullOrEmpty(id)) { Configuration.LayerFunctionRow layerFunction = layerFunctions[commonLayer.Name]; using (OleDbCommand command = layerFunction.GetDatabaseCommand()) { command.Parameters[0].Value = id; if (command.Parameters.Count > 1) { command.Parameters[1].Value = AppUser.GetRole(); } using (OleDbDataReader reader = command.ExecuteReader()) { if (reader.Read()) { StringCollection text = new StringCollection(); for (int j = 0; j < reader.FieldCount; ++j) { if (!reader.IsDBNull(j)) { text.Add(reader.GetValue(j).ToString()); } } if (text.Count > 0) { tipText = text.Join("\n"); } } } command.Connection.Close(); } } } if (tipText == null) { ReturnJson(null); } else { Dictionary <String, Object> result = new Dictionary <String, Object>(); result.Add("tipText", tipText); ReturnJson(result); } }
public static List <PeriodicHydroElement> GetPeriodicHydroElements() { List <PeriodicHydroElement> periodicHydroElements = new List <PeriodicHydroElement>(); string query = String.Format("SELECT Nombre, Periodo, TurMinimo, TurMaximo, Filtracion, FactorRecuperacion " + "FROM {0} " + "ORDER BY Nombre, Periodo ASC", table); OleDbDataReader reader = DataBaseManager.ReadData(query); while (reader.Read()) { periodicHydroElements.Add(new PeriodicHydroElement(reader.GetString(0), Convert.ToInt32(reader.GetValue(1)), Convert.ToDouble(reader.GetValue(2)), Convert.ToDouble(reader.GetValue(3)), Convert.ToDouble(reader.GetValue(4)), Convert.ToDouble(reader.GetValue(5)))); } DataBaseManager.DbConnection.Close(); return(periodicHydroElements); }
private void ReloadBtn_Click(object sender, EventArgs e) { string prop_name; string prp_val; try { string Qry = "select * from WorkMat;"; OleDbConnection con = new OleDbConnection(ConStr); OleDbCommand cmd = new OleDbCommand(Qry, con); con.Open(); OleDbDataReader Dr = null; Dr = cmd.ExecuteReader(); if (Dr.HasRows) { while (Dr.Read()) { prop_name = Dr.GetString(0); prp_val = Dr.GetValue(1).ToString(); if (prop_name == "Brick_Brd") { txtBrBrd.Text = prp_val.ToString(); } else if (prop_name == "Brick_Hi") { txtBrThick.Text = prp_val.ToString(); } else if (prop_name == "Brick_Len") { txtBrLen.Text = prp_val.ToString(); } else if (prop_name == "Cement") { txtCement.Text = prp_val.ToString(); } else if (prop_name == "Clm_Rod_Len") { txtClmRdLen.Text = prp_val.ToString(); } else if (prop_name == "Clm_Rod_Thick") { txtClmRdThick.Text = prp_val.ToString(); } else if (prop_name == "Ring_Rod_Len") { txtRingRdLen.Text = prp_val.ToString(); } else if (prop_name == "Ring_Rod_Thick") { txtRingRdThick.Text = prp_val.ToString(); } else if (prop_name == "Sand") { txtSand.Text = prp_val.ToString(); } else if (prop_name == "Stone") { txtStone.Text = prp_val.ToString(); } else { MessageBox.Show("No proper field to save...", "Information", MessageBoxButtons.OK); } } } if (Dr != null) { Dr.Close(); } if (con != null) { con.Close(); } } catch (Exception ex) { MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK); } }
public static void SecondSearch() { int[] ev = new int[6]; OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source = farbeliasav.mdb"); baglanti.Open(); string sql = "Select * from sav Where [slot] = '" + Game1.geym + "'"; OleDbCommand komut = new OleDbCommand(sql, baglanti); OleDbDataReader reader = komut.ExecuteReader(); reader.Read(); Game1.sahne = Convert.ToInt32(reader.GetValue(2)); Game1.koordinat.X = (float)Convert.ToInt32(reader.GetValue(3)); Game1.koordinat.Y = (float)Convert.ToInt32(reader.GetValue(4)); ev[0] = Convert.ToInt32(reader.GetValue(5)); ev[1] = Convert.ToInt32(reader.GetValue(6)); ev[2] = Convert.ToInt32(reader.GetValue(7)); ev[3] = Convert.ToInt32(reader.GetValue(8)); ev[4] = Convert.ToInt32(reader.GetValue(9)); Game1.saat = (float)Convert.ToInt32(reader.GetValue(10)); Game1.gun = Convert.ToInt32(reader.GetValue(11)); Game1.background.R = (byte)Convert.ToInt32(reader.GetValue(12)); Game1.background.G = (byte)Convert.ToInt32(reader.GetValue(13)); Game1.background.B = (byte)Convert.ToInt32(reader.GetValue(14)); Game1.karartma.R = (byte)Convert.ToInt32(reader.GetValue(15)); Game1.karartma.G = (byte)Convert.ToInt32(reader.GetValue(16)); Game1.karartma.B = (byte)Convert.ToInt32(reader.GetValue(17)); Game1.item[0] = Convert.ToInt32(reader.GetValue(18)); Game1.item[1] = Convert.ToInt32(reader.GetValue(19)); Game1.item[2] = Convert.ToInt32(reader.GetValue(20)); Game1.item[3] = Convert.ToInt32(reader.GetValue(21)); Game1.item[4] = Convert.ToInt32(reader.GetValue(22)); Game1.item[5] = Convert.ToInt32(reader.GetValue(23)); Game1.item[6] = Convert.ToInt32(reader.GetValue(24)); Game1.item[7] = Convert.ToInt32(reader.GetValue(25)); Game1.item[8] = Convert.ToInt32(reader.GetValue(26)); Game1.item[9] = Convert.ToInt32(reader.GetValue(27)); Game1.item[10] = Convert.ToInt32(reader.GetValue(28)); Game1.item[11] = Convert.ToInt32(reader.GetValue(29)); Game1.item[12] = Convert.ToInt32(reader.GetValue(30)); Game1.item[13] = Convert.ToInt32(reader.GetValue(31)); Game1.item[14] = Convert.ToInt32(reader.GetValue(32)); Game1.item[15] = Convert.ToInt32(reader.GetValue(33)); Game1.item[16] = Convert.ToInt32(reader.GetValue(34)); Game1.item[17] = Convert.ToInt32(reader.GetValue(35)); Game1.item[18] = Convert.ToInt32(reader.GetValue(36)); Game1.item[19] = Convert.ToInt32(reader.GetValue(37)); Game1.item[20] = Convert.ToInt32(reader.GetValue(38)); Game1.item[21] = Convert.ToInt32(reader.GetValue(39)); Game1.item[22] = Convert.ToInt32(reader.GetValue(40)); Game1.item[23] = Convert.ToInt32(reader.GetValue(41)); Game1.item[24] = Convert.ToInt32(reader.GetValue(42)); Game1.item[25] = Convert.ToInt32(reader.GetValue(43)); Game1.item[26] = Convert.ToInt32(reader.GetValue(44)); Game1.item[27] = Convert.ToInt32(reader.GetValue(45)); Game1.item[28] = Convert.ToInt32(reader.GetValue(46)); Game1.item[29] = Convert.ToInt32(reader.GetValue(47)); Game1.lanet = Convert.ToInt32(reader.GetValue(48)); Game1.iksirmission = Convert.ToInt32(reader.GetValue(49)); Game1.map = Convert.ToInt32(reader.GetValue(50)); Game1.kalkancan = Convert.ToInt32(reader.GetValue(51)); ev[5] = Convert.ToInt32(reader.GetValue(52)); for (int i = 0; i < 5; i++) { if (ev[i] == 1) { Game1.benbuevesahibim[i] = true; } else { Game1.benbuevesahibim[i] = false; } } if (ev[5] == 1) { Game1.konustumulan = true; } else { Game1.konustumulan = false; } baglanti.Close(); }
private void buttonStatistics_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count == 0) { return; } int totalCount = dataGridView1.Rows.Count; int totalGongli = 0; int startMile = 0; int endMile = 0; if (CommonClass.listDIC[0].bIndex) { startMile = (int)(float.Parse(CommonClass.listDIC[0].listIC[0].lStartMeter) * 1000); endMile = (int)(float.Parse(CommonClass.listDIC[0].listIC[CommonClass.listDIC[0].listIC.Count - 1].LEndMeter) * 1000); } else { //后去没有索引过后 string sLength = CommonClass.cdp.QueryDataMileageRange(CommonClass.listDIC[0].sFilePath, false, CommonClass.listDIC[0].bEncrypt); string[] sSE = sLength.Split(','); string[] sSE1 = sSE[1].Split('-'); startMile = (int)(float.Parse(sSE1[0].Trim()) * 1000); endMile = (int)(float.Parse(sSE1[1].Trim()) * 1000); } totalGongli = Math.Abs(startMile - endMile); List <StatisticsDataClass> dataList = new List <StatisticsDataClass>(); try { int typeNum = 0; //无效区段类型的个数 Dictionary <int, String> dicInvalidType = new Dictionary <int, String>(); //无效区段类型 using (OleDbConnection sqlconn = new OleDbConnection(CommonClass.sDBConnectionString)) { OleDbCommand sqlcom = new OleDbCommand("", sqlconn); sqlconn.Open(); sqlcom.CommandText = "select i_no,i_name from 无效区段类型"; OleDbDataReader oledbReader = sqlcom.ExecuteReader(); while (oledbReader.Read()) { dicInvalidType.Add(int.Parse(oledbReader.GetValue(0).ToString()), oledbReader.GetValue(1).ToString()); } oledbReader.Close(); sqlconn.Close(); } typeNum = dicInvalidType.Count; using (OleDbConnection sqlconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + CommonClass.listDIC[0].sAddFile + ";Persist Security Info=True")) { OleDbCommand sqlcom = new OleDbCommand("", sqlconn); sqlconn.Open(); for (int i = 0; i < typeNum; i++) { sqlcom.CommandText = "select InvalidType,StartMile,EndMile from InvalidData where InvalidType=" + i.ToString(); OleDbDataReader oledbReader = sqlcom.ExecuteReader(); StatisticsDataClass statisticsDataCls = null; while (oledbReader.Read()) { if (statisticsDataCls == null) { statisticsDataCls = new StatisticsDataClass(totalCount, totalGongli); } statisticsDataCls.reasonType = dicInvalidType[int.Parse(oledbReader.GetValue(0).ToString())];//类型 statisticsDataCls.sumcount++; startMile = (int)(float.Parse(oledbReader.GetValue(1).ToString()) * 1000); endMile = (int)(float.Parse(oledbReader.GetValue(2).ToString()) * 1000); statisticsDataCls.sumGongli = statisticsDataCls.sumGongli + Math.Abs(endMile - startMile); } oledbReader.Close(); if (statisticsDataCls != null) { dataList.Add(statisticsDataCls); } } sqlconn.Close(); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } statisticsForm = new InvalidDataStatisticsForm(dataList); statisticsForm.Owner = this; statisticsForm.TopLevel = true; statisticsForm.Show(); }
public List <TestCaseData> LerPlanilhaExcel(string caminho, string aba) { try { string connectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + caminho + ";Extended Properties=Excel 8.0;"; List <TestCaseData> testDataList = new List <TestCaseData>(); using (OleDbConnection connection = new OleDbConnection(connectionStr)) { connection.Open(); OleDbCommand command = new OleDbCommand("SELECT * FROM [" + aba + "$]", connection); OleDbDataReader reader = command.ExecuteReader(); int contagemDeColunas = reader.FieldCount; contagemDeColunas = contagemDeColunas - 2; while (reader.Read()) { string[] args = new string[contagemDeColunas]; for (int i = 0; i < contagemDeColunas; i++) { if (i == contagemDeColunas) { args[i] = reader.GetValue(contagemDeColunas).ToString(); } else { args[i] = reader.GetValue(i + 2).ToString(); } } TestCaseData testData = new TestCaseData(args); if (reader.GetName(0) != "Caso de teste") { throw new FormatException("O campo Caso de teste é obrigatório como primeira coluna.\nAba: " + aba); } if (reader.GetName(1) != "Descricao") { throw new FormatException("O campo Descricao é obrigatório como segunda coluna.\nAba: " + aba); } testData.SetName(reader.GetString(0)); testData.SetDescription(reader.GetString(1)); testDataList.Add(testData); } } return(testDataList); } catch (FormatException e) { throw new Exception(e.Message); } catch (Exception e) { throw new Exception("Uma ou mais colunas obrigatórias podem estar ausentes na base de dados. Mensagem original: " + e.Message); } }
public void run() { Exception exp = null; cmd.CommandText = "Select EmployeeID, LastName, FirstName, Title, BirthDate From Employees where EmployeeID in (100,200) order by EmployeeID asc"; OleDbDataReader rdr = cmd.ExecuteReader(); try { BeginCase("first row"); bool read = rdr.Read(); Compare(read, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("first row - value"); object obj = rdr.GetValue(0); Compare(obj.ToString(), "100"); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Second row"); bool read = rdr.Read(); Compare(read, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Second row - value"); object obj = rdr.GetValue(0); Compare(obj.ToString(), "200"); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("End of data"); bool read = rdr.Read(); Compare(read, false); rdr.Close(); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Read return false"); cmd.CommandText= "select * from Orders where OrderID=-909"; rdr = cmd.ExecuteReader(); Compare(rdr.Read(),false); rdr.Close(); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} }
protected void Button1_Click(object sender, EventArgs e) { int flag = 0, check = 1; con.ConnectionString = "provider=sqloledb.1;data source=.\\SQLEXPRESS;initial catalog=road;integrated security=sspi"; con.Open(); if (flag == 0) { cmd.CommandText = "select * from personal_details"; cmd.Connection = con; dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { if (dr.GetValue(0).Equals(regno.Text)) { Label2.Visible = true; con.Close(); check = 0; break; } } if (check == 0) { flag = 1; } } } if (flag == 1) { con.ConnectionString = "provider=sqloledb.1;data source=.\\SQLEXPRESS;initial catalog=road;integrated security=sspi"; con.Open(); nmd.CommandText = "delete from vehicle_details where v_id =" + regno.Text + ""; nmd.Connection = con; nmd.ExecuteNonQuery(); nmd.CommandText = "delete from personal_details where reg_id =" + regno.Text + ""; nmd.Connection = con; nmd.ExecuteNonQuery(); nmd.CommandText = "delete from dealer_details where d_id =" + regno.Text + ""; nmd.Connection = con; nmd.ExecuteNonQuery(); Label2.Text = "Registration deleted"; Label2.Visible = true; } if (flag == 0) { Label2.Text = "Registration Must Match"; Label2.Visible = true; } }
private void simpleButton1_Click(object sender, EventArgs e) { bool kontrol = false; try { if (String.IsNullOrEmpty(textEdit1.Text) || String.IsNullOrEmpty(textEdit2.Text) || String.IsNullOrEmpty(textEdit3.Text) || String.IsNullOrEmpty(textEdit4.Text)) { MessageBox.Show("Lütfen bilgileri eksiksiz doldurun...", "EKSİK ALAN UYARISI", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { cevap = MessageBox.Show("Ekleme işlemini yapmak istediğinizden emin misiniz?", "SATIŞ SORUMLUSU EKLEME İŞLEMİ", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (cevap == DialogResult.Yes) { baglanti.Open(); OleDbCommand satis_oku = new OleDbCommand("select * from satis_sorumlusu", baglanti); OleDbDataReader oku = satis_oku.ExecuteReader(); while (oku.Read()) { if (oku.GetValue(3).ToString() == textEdit3.Text) { kontrol = true; } } if (kontrol == false) { OleDbCommand sorumlu_ekle = new OleDbCommand("insert into satis_sorumlusu(adi,soyadi,kullanici_adi,sifre) values ('" + textEdit1.Text + "','" + textEdit2.Text + "','" + textEdit3.Text + "','" + textEdit4.Text + "')", baglanti); sorumlu_ekle.ExecuteNonQuery(); OleDbCommand giris_ekle = new OleDbCommand("insert into giris_bilgiler(kullanici_adi,sifre,yetki) values ('" + textEdit3.Text + "','" + textEdit4.Text + "','Satış Danışmanı')", baglanti); giris_ekle.ExecuteNonQuery(); MessageBox.Show("Satış sorumlusu başarıyla eklenilmiştir.", "SORUMLU EKLENİLDİ", MessageBoxButtons.OK, MessageBoxIcon.Information); OleDbCommand verileri_ekle = new OleDbCommand("SELECT adi AS[ADI],soyadi AS[SOYADI],kullanici_adi AS[KULLANICI ADI],sifre AS[ŞİFRE] from satis_sorumlusu", baglanti); OleDbDataAdapter da = new OleDbDataAdapter(verileri_ekle); DataTable dt = new DataTable(); da.Fill(dt); gridControl1.DataSource = dt; comboBoxEdit1.Properties.Items.Clear(); comboBoxEdit2.Properties.Items.Clear(); OleDbCommand satis_oku_tekrar = new OleDbCommand("select * from satis_sorumlusu", baglanti); OleDbDataReader oku_tekrar = satis_oku_tekrar.ExecuteReader(); while (oku_tekrar.Read()) { comboBoxEdit1.Properties.Items.Add(oku_tekrar.GetValue(3).ToString()); comboBoxEdit2.Properties.Items.Add(oku_tekrar.GetValue(3).ToString()); } textEdit1.Text = ""; textEdit2.Text = ""; textEdit3.Text = ""; textEdit4.Text = ""; } else { MessageBox.Show("Bu kullanıcı adı zaten kayıtlıdır.Lütfen farklı bir kullanıcı adı deneyiniz.", "SORUMLU KULLANICI ADI KAYITLI !!!", MessageBoxButtons.OK, MessageBoxIcon.Error); kontrol = false; } baglanti.Close(); } } } catch (Exception) { MessageBox.Show("Ekleme işlemi yapılamadı.Lütfen tekrar deneyin.", "BAŞARISIZ EKLEME İŞLEMİ", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void read(OleDbDataReader reader) { /* * jaysus - these all fail on NULLs * need a better way of reading records!! Maybe pass an array in of type * bind name of field to property of same name */ id = int.Parse(reader.GetValue(reader.GetOrdinal("id")).ToString()); tableId = int.Parse(reader.GetValue(reader.GetOrdinal("tableId")).ToString()); tableName = reader.GetValue(reader.GetOrdinal("tableName")).ToString(); name = reader.GetValue(reader.GetOrdinal("name")).ToString(); dataType = int.Parse(reader.GetValue(reader.GetOrdinal("dataType")).ToString()); ordinal = int.Parse(reader.GetValue(reader.GetOrdinal("ordinal")).ToString()); flags = int.Parse(reader.GetValue(reader.GetOrdinal("flags")).ToString()); hasDefault = bool.Parse(reader.GetValue(reader.GetOrdinal("hasDefault")).ToString()); defaultValueString = reader.GetValue(reader.GetOrdinal("defaultValue")).ToString(); isNullable = bool.Parse(reader.GetValue(reader.GetOrdinal("isNullable")).ToString()); description = reader.GetValue(reader.GetOrdinal("description")).ToString(); fieldList = int.Parse(reader.GetValue(reader.GetOrdinal("fieldList")).ToString()); }
private void button8_Click(object sender, EventArgs e) { dataGridView2.Rows.Clear(); conn1 = new OleDbConnection(connectionString); try { conn1.Open(); //데이터베이스 연결 OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "SELECT 성적확인 FROM" + " " + "신청확인" + " " + "WHERE 성적확인=1"; cmd.CommandType = CommandType.Text; //검색명령을 쿼리 형태로 cmd.Connection = conn1; cmd.ExecuteNonQuery(); //쿼리문을 실행하고 영향받는 행의 수를 반환. OleDbDataReader read = cmd.ExecuteReader(); //select * from emp 결과 if (!read.Read()) { MessageBox.Show("성적입력기간이 아닙니다."); return; } read.Close(); } catch (Exception ex) { MessageBox.Show("Error" + ex.Message); //에러 메세지 } finally { if (conn1 != null) { conn1.Close(); //데이터베이스 연결 해제 } } try { conn1.Open(); //데이터베이스 연결 OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = "SELECT * FROM" + " " + "수강" + " " + "WHERE 교과목번호=" + textBox5.Text; cmd.CommandType = CommandType.Text; //검색명령을 쿼리 형태로 cmd.Connection = conn1; cmd.ExecuteNonQuery(); //쿼리문을 실행하고 영향받는 행의 수를 반환. OleDbDataReader read = cmd.ExecuteReader(); //select * from emp 결과 dataGridView2.ColumnCount = 4; //필드명 받아오는 반복문 for (int i = 0; i < 4; i++) { dataGridView2.Columns[i].Name = read.GetName(i); } while (read.Read()) { object[] obj = new object[4]; // 필드수만큼 오브젝트 배열 for (int i = 0; i < 4; i++) // 필드 수만큼 반복 { obj[i] = new object(); obj[i] = read.GetValue(i); // 오브젝트배열에 데이터 저장 } dataGridView2.Rows.Add(obj); //데이터그리드뷰에 오브젝트 배열 추가 } read.Close(); } catch (Exception ex) { MessageBox.Show("Error" + ex.Message); //에러 메세지 } finally { if (conn1 != null) { conn1.Close(); //데이터베이스 연결 해제 } } }
private void DrawMarkup(Graphics graphics) { if (_appState.MarkupGroups.Count == 0) { return; } SolidBrush pointBrush = new SolidBrush(Color.Red); SolidBrush polygonBrush = new SolidBrush(Color.FromArgb(128, 255, 192, 192)); Pen pen = new Pen(Color.Red, Convert.ToSingle(2 * _resolution)); pen.EndCap = System.Drawing.Drawing2D.LineCap.Square; pen.LineJoin = System.Drawing.Drawing2D.LineJoin.Round; float dotSize = Convert.ToSingle(10 * _resolution); System.Drawing.Font font = AppSettings.MarkupFont; font = new System.Drawing.Font(font.FontFamily, Convert.ToSingle(font.Size * _resolution), font.Style, font.Unit); System.Drawing.Font coordinatesFont = AppSettings.CoordinatesFont; coordinatesFont = new System.Drawing.Font(coordinatesFont.FontFamily, Convert.ToSingle(coordinatesFont.Size * _resolution), coordinatesFont.Style, coordinatesFont.Unit); SolidBrush textBrush = new SolidBrush(Color.FromArgb(192, 0, 0)); SolidBrush glowBrush = new SolidBrush(Color.Black); StringFormat format = new StringFormat(); format.LineAlignment = StringAlignment.Far; Configuration config = AppContext.GetConfiguration(); Configuration.ApplicationRow application = config.Application.FindByApplicationID(_appState.Application); string[] modes = application.IsCoordinateModesNull() ? new string[] { "ne" } : application.CoordinateModes.ToLower().Split(','); using (OleDbConnection connection = AppContext.GetDatabaseConnection()) { foreach (string groupId in _appState.MarkupGroups) { string sql = String.Format("update {0}MarkupGroup set DateLastAccessed = ? where GroupID = {1}", AppSettings.ConfigurationTablePrefix, groupId); using (OleDbCommand command = new OleDbCommand(sql, connection)) { command.Parameters.Add("@1", OleDbType.Date).Value = DateTime.Now; command.ExecuteNonQuery(); } sql = String.Format("select Shape, Color, Glow, Text, Measured from {0}Markup where GroupID = {1} and Deleted = 0", AppSettings.ConfigurationTablePrefix, groupId); using (OleDbCommand command = new OleDbCommand(sql, connection)) { using (OleDbDataReader reader = command.ExecuteReader()) { WKTReader wktReader = new WKTReader(); while (reader.Read()) { IGeometry geometry = wktReader.Read(reader.GetString(0)); Color color = ColorTranslator.FromHtml(reader.GetString(1)); bool isMeasured = !reader.IsDBNull(4) && Convert.ToInt32(reader.GetValue(4)) == 1; bool isGlow = !reader.IsDBNull(2); if (isGlow) { glowBrush.Color = ColorTranslator.FromHtml(reader.GetString(2)); } switch (geometry.OgcGeometryType) { case OgcGeometryType.Point: bool isText = !reader.IsDBNull(3); if (isText) { textBrush.Color = color; DrawText(graphics, (IPoint)geometry, reader.GetString(3), font, textBrush, isGlow ? glowBrush : null, 0, 0, format); } else { if (isMeasured) { pen.Color = color; pen.EndCap = System.Drawing.Drawing2D.LineCap.Flat; textBrush.Color = color; DrawCoordinate(graphics, (IPoint)geometry, modes, pen, coordinatesFont, textBrush, isGlow ? glowBrush : null, format); } else { pointBrush.Color = color; DrawPoint(graphics, (IPoint)geometry, pointBrush, dotSize); } } break; case OgcGeometryType.LineString: pen.Color = color; pen.EndCap = System.Drawing.Drawing2D.LineCap.Square; DrawLineString(graphics, (ILineString)geometry, pen); break; case OgcGeometryType.Polygon: pen.Color = color; pen.EndCap = System.Drawing.Drawing2D.LineCap.Square; polygonBrush.Color = Color.FromArgb(128, color); DrawPolygon(graphics, (IPolygon)geometry, polygonBrush, null, pen); break; } if (isMeasured && geometry.OgcGeometryType != OgcGeometryType.Point) { DrawMeasure(graphics, geometry); } } } } } } }
//Поиск записи и замена найденных кодов на значения public static string FindRecord(string table, string cond, Dictionary <string, Dictionary <string, string> > vals) { Conn.Open(); DbCommand.Connection = Conn; DbCommand.CommandText = $"select * from {table} where {cond}"; List <string> name = new List <string>(); List <string> val = new List <string>(); try { using (OleDbDataReader reader = DbCommand.ExecuteReader()) { while (reader != null && reader.Read()) { for (int i = 1; i < reader.FieldCount; i++) { name.Add(FieldsDisplay[reader.GetName(i)]); val.Add(reader.GetValue(i).GetType().IsEquivalentTo(typeof(DateTime)) ? reader.GetDateTime(i).ToShortDateString() : (reader.GetValue(i).ToString() == "False") ? "Не выполнен" : (reader.GetValue(i).ToString() == "True") ? "Выполнен" : reader.GetValue(i).ToString()); } } } } catch (Exception e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } List <string> conds = new List <string>(val.Where(r => int.TryParse(r, out int _))); if (conds.Count != vals.Count) { throw new Exception("Не совпадает длина параметров"); } string ret = ""; try { Conn.Open(); DbCommand.Connection = Conn; int i; for (i = 0; i < conds.Count; i++) { DbCommand.CommandText = $"select {vals.ElementAt(i).Value.ElementAt(0).Key} from {vals.ElementAt(i).Key} where {vals.ElementAt(i).Value.ElementAt(0).Value} = {conds[i]}"; using (OleDbDataReader reader = DbCommand.ExecuteReader()) { while (reader != null && reader.Read()) { for (int j = 0; j < vals.ElementAt(i).Value.ElementAt(0).Key.Split(',').Length; j++) { ret = ret + FieldsDisplay[reader.GetName(j)] + ": " + (reader.GetValue(j).GetType().IsEquivalentTo(typeof(DateTime)) ? reader.GetDateTime(j).ToShortDateString() : (reader.GetValue(j).ToString() == "False") ? "Не выполнен" : (reader.GetValue(j).ToString() == "True") ? "Выполнен" : reader.GetValue(j).ToString()) + '\n'; } val.Remove(val.First()); name.Remove(name.First()); } } } } catch (Exception e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } for (int i = 0; i < val.Count; i++) { ret = ret + name[i] + ": " + val[i] + '\n'; } return(ret); }
private bool WriteControlFile(string iDbfName, string iOracleName) { try { mRunWindowViewModel.TableProgressBarValue = 0; setupDbfConnection(); //Find total number of item in table and set table progress bar maximum using (OleDbCommand wCommand = new OleDbCommand("SELECT COUNT(*) FROM " + iDbfName, mDbfConnection)) { using (OleDbDataReader wReader = wCommand.ExecuteReader()) { wReader.Read(); mRunWindowViewModel.TableProgressBarMax = double.Parse(wReader.GetValue(0).ToString()); mNumberOfRecordsDbf = (int)(mRunWindowViewModel.TableProgressBarMax); } } //Show information on Console mBackgroundWorker.ReportProgress(0, new ConsoleMessage("Writing Control File for " + iDbfName + " (" + mNumberOfRecordsDbf + " records) ...", ConsoleMessage.MessageTypeEnum.eInfo)); //loop through table records using (OleDbCommand wCommand = new OleDbCommand("SELECT * FROM " + iDbfName, mDbfConnection)) { using (OleDbDataReader wReader = wCommand.ExecuteReader()) { int wColumnCount = wReader.FieldCount; //special case for COMPANY and COMMENTS table if (iDbfName == "COMPANY") { wColumnCount = 6; } else if (iDbfName == "COMMENTS") { wColumnCount = 2; } //convert column types to be compatible with Oracle List <string> wColumnNames = new List <string>(); for (int i = 0; i < wColumnCount; i++) { switch (wReader.GetFieldType(i).Name.ToUpper()) { case "DATETIME": wColumnNames.Add(wReader.GetName(i).ToUpper() + " \"to_date(:" + wReader.GetName(i) + ",'YYYY/MM/DD')\""); break; default: if (wReader.GetName(i).ToUpper() == "NUMBER") { wColumnNames.Add("SEG_NUMBER"); } else { wColumnNames.Add(wReader.GetName(i).ToUpper()); } break; } } // //Write Control File using (TextWriter wTextWriter = new StreamWriter(mControlFilePath, false)) { //Write Header wTextWriter.WriteLine("load data"); wTextWriter.WriteLine("infile *"); wTextWriter.WriteLine("into table " + iOracleName); wTextWriter.WriteLine("fields terminated by \"|\" optionally enclosed by '\"'"); wTextWriter.WriteLine("TRAILING NULLCOLS"); wTextWriter.WriteLine("(" + string.Join(",", wColumnNames) + ")"); wTextWriter.WriteLine("begindata"); //Loop through records and write them to Control File #region WriteControlFileContent int wRecordCounter = 1; while (wReader.Read()) { try { wRecordCounter++; List <string> wFieldValues = new List <string>(); for (int i = 0; i < wColumnCount; i++) { //Some column types require a special conversion switch (wReader.GetFieldType(i).Name) { case "DateTime": string wDateValue = wReader.GetValue(i).ToString(); if (wDateValue != "") { DateTime wDateTime = DateTime.Parse(wDateValue); wFieldValues.Add(string.Join("/", new string[] { wDateTime.Year.ToString(), wDateTime.Month.ToString("00"), wDateTime.Day.ToString("00") })); } else { //add an empty value wFieldValues.Add(""); } break; case "Decimal": //TODO deal with culture wFieldValues.Add(wReader.GetValue(i).ToString().Replace('.', ',')); break; case "String": #region StringType //Make the string uppercase and remove unwanted accents or symbols string wString = wReader.GetValue(i).ToString().ToUpper().Replace(Environment.NewLine, ""); //loop through string char and remove symbols string wFilteredString = ""; List <string> wUnknownChars = new List <string>(); foreach (char c in wString) { if (c > 0x005F) { string wConvertedChar = ""; if (CharConverter.Instance.getConvertedChar(c, out wConvertedChar) == false) { //Symbol not found in char lookup if (Settings.Instance.AutoRunIsOn) { //log error, the symbol is unknown wUnknownChars.Add(c.ToString()); } else { //ask user on UI thread Dispatcher.Invoke(new Action(() => { CharConverterWindow wCharConverterWindow = new CharConverterWindow(wString, c); wCharConverterWindow.Owner = this; wCharConverterWindow.ShowDialog(); wFilteredString += wCharConverterWindow.ConvertedSymbol; })); } } else { wFilteredString += wConvertedChar; } } else { wFilteredString += c; } } if (wUnknownChars.Count != 0) { //log an error mBackgroundWorker.ReportProgress(0, new ConsoleMessage( "Unknown character(s) found at record #" + wRecordCounter + " in string '" + wString + "' : " + string.Join(",", wUnknownChars), ConsoleMessage.MessageTypeEnum.eError)); } wFieldValues.Add(wFilteredString); #endregion break; default: wFieldValues.Add(wReader.GetValue(i).ToString()); break; } } wTextWriter.WriteLine(string.Join("|", wFieldValues)); mRunWindowViewModel.TableProgressBarValue = wRecordCounter; } catch (Exception exception) { mBackgroundWorker.ReportProgress(0, new ConsoleMessage("Error when writing record #" + wRecordCounter, ConsoleMessage.MessageTypeEnum.eError)); mBackgroundWorker.ReportProgress(0, new ConsoleMessage(exception.Message, ConsoleMessage.MessageTypeEnum.eError)); } } #endregion } } } mBackgroundWorker.ReportProgress(0, new ConsoleMessage("Finished writing Control File for " + iDbfName, ConsoleMessage.MessageTypeEnum.eInfo)); return(true); } catch (Exception ex) { mBackgroundWorker.ReportProgress(0, new ConsoleMessage("Error when writing Control File for " + iDbfName, ConsoleMessage.MessageTypeEnum.eError)); mBackgroundWorker.ReportProgress(0, new ConsoleMessage(ex.Message, ConsoleMessage.MessageTypeEnum.eError)); return(false); } }
//Список записей с заменой public static List <List <string> > GetList(string table, Dictionary <string, Dictionary <string, string> > vals) { Conn.Open(); DbCommand.Connection = Conn; DbCommand.CommandText = $"select * from {table}"; List <List <string> > list = new List <List <string> >(); try { using (OleDbDataReader reader = DbCommand.ExecuteReader()) { var headers = false; while (reader != null && reader.Read()) { List <string> tempList = new List <string>(); List <string> headList = new List <string>(); for (int i = 0; i < reader.FieldCount; i++) { if (!headers) { headList.Add(FieldsDisplay[reader.GetName(i)]); } tempList.Add(reader.GetValue(i).GetType().IsEquivalentTo(typeof(DateTime)) ? reader.GetDateTime(i).ToShortDateString() : (reader.GetValue(i).ToString() == "False") ? "Не выполнен" : (reader.GetValue(i).ToString() == "True") ? "Выполнен" : reader.GetValue(i).ToString()); } if (!headers) { list.Add(headList); } list.Add(tempList); headers = true; } } } catch (Exception e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } try { Conn.Open(); DbCommand.Connection = Conn; int i; var headers = false; for (i = 1; i < list.Count; i++) { var conds = new List <string>(list[i].Where(r => int.TryParse(r, out int _))); conds.RemoveAt(0); for (int j = 0; j < conds.Count; j++) { DbCommand.CommandText = $"select {vals.ElementAt(j).Value.ElementAt(0).Key} from {vals.ElementAt(j).Key} where {vals.ElementAt(j).Value.ElementAt(0).Value} = {conds[j]}"; using (OleDbDataReader reader = DbCommand.ExecuteReader()) { while (reader != null && reader.Read()) { for (int l = 0; l < vals.ElementAt(j).Value.ElementAt(0).Key.Split(',').Length; l++) { var temp = reader.GetValue(l).GetType().IsEquivalentTo(typeof(DateTime)) ? reader.GetDateTime(l).ToShortDateString() : (reader.GetValue(l).ToString() == "False") ? "Не выполнен" : (reader.GetValue(l).ToString() == "True") ? "Выполнен" : reader.GetValue(l).ToString(); list[i][j + 1] = temp; if (!headers) { list[0][j + 1] = FieldsDisplay[reader.GetName(l)]; } } } } } headers = true; } } catch (IOException e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } return(list); }
private void button1_Click(object sender, EventArgs e) { String email = textBox1.Text; String password = textBox2.Text; bool emailOk = true; bool passwordOk = true; Regex emailValidator = new Regex(@"^([\w\.]+)@(\w+)((\.(\w){2,3})+)$"); if (!emailValidator.IsMatch(email)) { emailOk = false; } Regex passwordValidator = new Regex("^[A-Za-z0-9.@!#$%]"); //if (!passwordValidator.IsMatch(password)) //{ // passwordOk = false; //} if (emailOk == false && passwordOk == false) { MessageBox.Show("Both email and password fields contains invalid characters!", "Invalid email / password", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (emailOk == false) { MessageBox.Show("Email field contain invalid characters!", "Invalid email", MessageBoxButtons.OK, MessageBoxIcon.Error); } else if (passwordOk == false) { MessageBox.Show("Password field contains invalid characters!", "Invalid password", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { connectionString = "Provider=OraOLEDB.Oracle;DATA SOURCE=localhost:1521/XE;PERSIST SECURITY INFO=True;USER ID=STUDENT;Password=STUDENT;"; connection = new OleDbConnection(connectionString); try { connection.Open(); MessageBox.Show("Connection with the database has been made!", "Succesfull connection!", MessageBoxButtons.OK, MessageBoxIcon.Information); command = new OleDbCommand("SELECT COUNT(*) FROM users_ WHERE email='" + email + "' AND password='******'", connection); dataReader = command.ExecuteReader(); dataReader.Read(); int value = int.Parse(dataReader.GetValue(0).ToString()); connection.Close(); if (value != 0) { MessageBox.Show("Succesfull login!", "GyF Login", MessageBoxButtons.OK, MessageBoxIcon.Information); Main formMain = new Main(email); /* Show main form and hide login form */ this.Hide(); formMain.ShowDialog(); this.Close(); } else { MessageBox.Show("Invalid login!", "GyF Login", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show("Invalid connection to the database! Error:\n" + ex.ToString(), "Database error!", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }
//Сводка за месяц public static string GetSummary() { Conn.Open(); DbCommand.Connection = Conn; DbCommand.CommandText = "SELECT ORDERS.ORD_Code, PHOTOGRAPHERS.PHO_Salary, SERVICES.SER_Price, ORDERS.ORD_Date FROM SERVICES INNER JOIN (PHOTOGRAPHERS INNER JOIN ORDERS ON PHOTOGRAPHERS.PHO_Code = ORDERS.ORD_PhoCode) ON SERVICES.SER_Code = ORDERS.ORD_SerCode;"; int income = 0, consumption = 0, numOrd = 0; var today = DateTime.Today; var month = new DateTime(today.Year, today.Month, 1); var first = month.AddMonths(-1); var last = month.AddDays(-1); try { using (OleDbDataReader reader = DbCommand.ExecuteReader()) { while (reader != null && reader.Read()) { if (Convert.ToDateTime(reader.GetValue(3)) < first || Convert.ToDateTime(reader.GetValue(3)) > last) { continue; } numOrd++; income += Convert.ToInt32(reader.GetValue(2)); consumption += Convert.ToInt32(reader.GetValue(1)); } } } catch (Exception e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } Conn.Open(); DbCommand.Connection = Conn; DbCommand.CommandText = "SELECT ORDERS.ORD_Date, ASSISTANTS.ASS_Salary FROM ASSISTANTS INNER JOIN (ORDERS INNER JOIN ASSISTANCE ON ORDERS.ORD_Code = ASSISTANCE.ASCE_OrdCode) ON ASSISTANTS.ASS_Code = ASSISTANCE.ASCE_AssCode;"; try { using (OleDbDataReader reader = DbCommand.ExecuteReader()) { while (reader != null && reader.Read()) { if (Convert.ToDateTime(reader.GetValue(0)) < first || Convert.ToDateTime(reader.GetValue(0)) > last) { continue; } consumption += Convert.ToInt32(reader.GetValue(1)); } } } catch (Exception e) { MessageBox.Show(@"Ошибка при поиске!" + Environment.NewLine + e.Message, @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); //throw; } finally { Conn.Close(); } return($"Количество заказов: {numOrd}\n" + $"Доход: {income} руб.\n" + $"Расход: {consumption} руб.\n" + $"Чистый заработок: {income - consumption} руб.");; }
public static void ReadMyData(string connectionString, string queryString) { OleDbConnection oleconnection = new OleDbConnection(connectionString); OleDbCommand olecommand = new OleDbCommand(queryString, oleconnection); oleconnection.Open(); OleDbDataReader olereader = olecommand.ExecuteReader(); int i = 1; NpgsqlConnection postgreconn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=Database_Password;Database=DatabaseName"); postgreconn.Open(); NpgsqlCommand cmddelete = new NpgsqlCommand("DELETE FROM repair *", postgreconn); cmddelete.ExecuteNonQuery(); while (olereader.Read()) { Console.WriteLine(olereader.GetValue(0) + " " + olereader.GetValue(1) + " " + olereader.GetValue(2) + " " + olereader.GetValue(3) + " " + olereader.GetValue(4) + " " + olereader.GetValue(5) + " " + olereader.GetValue(6)); i++; NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO repair (id, kv, username, device, failure, acceptdate, readydate, repairstatus) VALUES (@id, @kv, @username, @device, @failure, @acceptdate, @readydate, @repairstatus)", postgreconn); // Console.WriteLine(i); cmd.Parameters.AddWithValue("id", i); //Basa.Kod, Client.Persona, Phone.Name, Bas.PolomkaDesc, Basa.DataPrihod, Basa.DataGotov, Basa.KodRepair var val2 = olereader.GetValue(0); if (val2 == null) { val2 = 1234554321; } cmd.Parameters.AddWithValue("kv", val2); // Console.WriteLine(val2); var val = olereader.GetValue(1); if (val == null || val.ToString().Equals("")) { val = "Информация отсутствует"; } cmd.Parameters.AddWithValue("username", val); // Console.WriteLine(val); val = olereader.GetValue(2); if (val == null || val.ToString().Equals("")) { val = "Информация отсутствует"; } cmd.Parameters.AddWithValue("device", val); // Console.WriteLine(val); val = olereader.GetValue(3); if (val == null || val.ToString().Equals("")) { val = "Информация отсутствует"; } cmd.Parameters.AddWithValue("failure", val); val = olereader.GetValue(4); if (val == null || val.ToString().Equals("")) { val = "Информация отсутствует"; } cmd.Parameters.AddWithValue("acceptdate", val); val = olereader.GetValue(5); if (val == null || val.ToString().Equals("")) { val = "Информация отсутствует"; } cmd.Parameters.AddWithValue("readydate", val); val2 = olereader.GetValue(6); switch (val2) { case 1: val = "В ремонте"; break; case 2: val = "Отремонтирован"; break; case 0: val = "Без ремонта"; break; case null: val = "Информация отсутствует"; break; default: val = "Информация отсутствует"; break; } cmd.Parameters.AddWithValue("repairstatus", val); cmd.ExecuteNonQuery(); } postgreconn.Close(); oleconnection.Close(); Console.ReadKey(); }
protected void ActualizareSalar_Click(object sender, EventArgs e) { string serverpath = "-"; if (FileUpload1.HasFile) { try { string filename = Path.GetFileName(FileUpload1.FileName); serverpath = Server.MapPath("~/") + "aaa.xlsx"; FileUpload1.SaveAs(serverpath); Literal1.Text = "Upload status: File uploaded!"; // XlsFile.Text = serverpath; } catch (Exception ex) { Literal1.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message; } } SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["CrisTimeConnection"].ConnectionString; string connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = '" + serverpath + "'; Extended Properties = 'Excel 8.0;HDR=Yes;IMEX=1'"; try { OleDbConnection connection = new OleDbConnection(connectionString); connection.Open(); OleDbCommand Slct = new OleDbCommand("SELECT marca,salar FROM [Sheet1$]", connection); OleDbDataReader readslct = Slct.ExecuteReader(); while (readslct.Read()) { //int Marca, Salar; string Marca = Convert.ToString(readslct.GetValue(0)); string Salar = Convert.ToString(readslct.GetValue(1)); SqlCommand sqlCommand = new SqlCommand("SetSalar", sqlConnection); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.Add(new SqlParameter("@marca", Marca)); sqlCommand.Parameters.Add(new SqlParameter("@salar", Salar)); sqlCommand.Connection.Open(); try { sqlCommand.ExecuteNonQuery(); this.GridView1.DataBind(); } catch (SqlException ex) { this.Literal1.Text = ex.Message + "<br /> eroare executie sql command <br /> marca:" + Marca.ToString() + " salar:" + Salar.ToString(); } sqlCommand.Connection.Close(); } connection.Close(); } catch (OleDbException ex) { this.Literal1.Text = ex.Message + "eroeare excel" + ex.ToString(); } }
private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords) { const string findSharedState = "SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" + " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" + " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId"; const string orderBy = " ORDER BY Paths.Path ASC"; const string findUserState = "SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" + " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Paths Paths" + " WHERE PerUser.PathId = Paths.PathId" + " AND Paths.ApplicationId = @ApplicationId" + " AND Paths.Path LIKE @Path"; AccessConnectionHolder connectionHolder = null; OleDbConnection connection = null; OleDbDataReader reader = null; totalRecords = 0; try { try { connectionHolder = GetConnectionHolder(); connection = connectionHolder.Connection; OleDbCommand command = new OleDbCommand(findSharedState, connection); OleDbParameterCollection parameters = command.Parameters; OleDbParameter parameter; int appId = GetApplicationID(connectionHolder); parameters.AddWithValue("ApplicationId", appId); if (path != null) { command.CommandText += " AND Paths.Path LIKE @Path"; parameter = parameters.Add("Path", OleDbType.WChar); parameter.Value = path; } command.CommandText += orderBy; reader = command.ExecuteReader(CommandBehavior.SequentialAccess); PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection(); long recordCount = 0; long lBound = pageIndex * pageSize; long uBound = lBound + pageSize; while (reader.Read()) { recordCount++; if (recordCount <= lBound || recordCount > uBound) { continue; } string returnedPath = reader.GetString(0); DateTime lastUpdatedDate = reader.GetDateTime(1); int size = reader.GetInt32(2); // Create temp info since we need to retrieve the corresponding personalization size and count later stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1)); } totalRecords = (int)recordCount; // We need to close the reader in order to make other queries reader.Close(); command = new OleDbCommand(findUserState, connection); parameters = command.Parameters; parameters.AddWithValue("ApplicationId", appId); parameter = parameters.Add("Path", OleDbType.WChar); PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection(); foreach (PersonalizationStateInfo stateInfo in stateInfoCollection) { parameter.Value = stateInfo.Path; reader = command.ExecuteReader(CommandBehavior.SequentialAccess); reader.Read(); int sizeOfPersonalizations = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture); int countOfPersonalizations = reader.GetInt32(1); reader.Close(); sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo( stateInfo.Path, stateInfo.LastUpdatedDate, stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations)); } return(sharedStateInfoCollection); } finally { if (connectionHolder != null) { connectionHolder.Close(); connectionHolder = null; } if (reader != null) { reader.Close(); } } } catch { throw; } }
public void TestMultipleResultSetsWithSP() { #if !JAVA if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle) { this.Log("Not testing Stored procedures with multiple ref-cursors on Oracle with .NET due to bug in .NET (only the first ref-cursor is retrived)."); return; } if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL) { // fail to work on .NET OLEDB this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF"); return; } #endif bool NextResultExists = false; // transaction use was add for PostgreSQL OleDbTransaction tr = con.BeginTransaction(); OleDbCommand cmd = new OleDbCommand("GH_MULTIRECORDSETS", con, tr); cmd.CommandType = CommandType.StoredProcedure; OleDbDataReader rdr = cmd.ExecuteReader(); try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 exists"); Compare(rdr != null, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Data"); Compare(rdr.GetValue(1).ToString(), "Yavine"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "EMPLOYEEID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } // -------------- ResultSet 2 ------------ try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Data"); Compare(rdr.GetValue(1).ToString(), "Morgenstern Gesundkost"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } // -------------- ResultSet 3 ------------ try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 exists"); NextResultExists = rdr.NextResult(); Compare(NextResultExists, true); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 contains data"); NextResultExists = rdr.Read(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 3 Schema"); Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "ORDERID"); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not exist."); NextResultExists = rdr.NextResult(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } try { exp = null; BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not contain data."); NextResultExists = rdr.Read(); Compare(NextResultExists, false); } catch (Exception ex) { exp = ex; } finally { EndCase(exp); } //Cleanup: if (!rdr.IsClosed) { rdr.Close(); } // transaction use was add for PostgreSQL tr.Commit(); }
/// <summary> /// Load the modified records for the given cache and set these flags to false /// </summary> /// <returns></returns> private Hashtable LoadTableData(string syncTable, string cacheName, OleDbConnection connection) { object[] tableInfo = new object[] { syncTable, cacheName }; Hashtable tableData = new Hashtable(); OleDbDataReader reader = null; OleDbCommand command = null; string cacheKey = ""; bool modified = false; bool hasRows = false; lock (connection) { var transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead); try { if (connection.State != ConnectionState.Open) { connection.Open(); } command = connection.CreateCommand(); command.CommandText = string.Format(CultureInfo.InvariantCulture, "UPDATE {0} SET WORK_IN_PROGRESS = 1 WHERE CACHE_ID = '{1}' AND MODIFIED = 1", tableInfo); command.CommandType = CommandType.Text; command.Transaction = transaction; reader = command.ExecuteReader(); } catch (Exception ex) { NCacheLog.Error(cacheName, ex.ToString()); transaction.Rollback(); return(null); } finally { if (reader != null) { reader.Close(); reader.Dispose(); reader = null; } if (command != null) { command.Dispose(); command = null; } } try { if (connection.State != ConnectionState.Open) { connection.Open(); } command = connection.CreateCommand(); command.CommandText = string.Format(CultureInfo.InvariantCulture, "SELECT CACHE_KEY, MODIFIED FROM {0} WHERE CACHE_ID = '{1}' AND WORK_IN_PROGRESS = 1", tableInfo); command.CommandType = CommandType.Text; command.Transaction = transaction; reader = command.ExecuteReader(); hasRows = reader.HasRows; while (reader.Read()) { cacheKey = Convert.ToString(reader.GetValue(0)); modified = Convert.ToBoolean(reader.GetValue(1)); tableData.Add(cacheKey, modified); } } catch (Exception ex) { NCacheLog.Error(cacheName, ex.ToString()); transaction.Rollback(); return(null); } finally { if (reader != null) { reader.Close(); reader.Dispose(); reader = null; } if (command != null) { command.Dispose(); command = null; } } transaction.Commit(); } return(tableData); }
private void btnVisualize_Click(object sender, EventArgs e) { chartPredict.Series["Total Sales per Month"].Points.Clear(); chartPredict.Titles.Clear(); DateTime dt = DateTime.Now; int CurrYear = dt.Year; int NextMonth = dt.AddMonths(+1).Month; chartPredict.ChartAreas[0].AxisX.Minimum = 1; chartPredict.ChartAreas[0].AxisX.Maximum = 12; OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = SRePS.Properties.Settings.Default.SRePS_DatabaseConnectionString; conn.Open(); if (cmbGroup.SelectedItem == null || cmbSubGroup.SelectedItem == null) { MessageBox.Show("Please select a category or sub-category.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { if (rdSales.Checked) { chartPredict.Titles.Add("Monthly Total Sales of " + cmbSubGroup.SelectedItem.ToString()); chartPredict.ChartAreas[0].AxisX.Title = "Month"; chartPredict.ChartAreas[0].AxisY.Title = "Sales (RM)"; chartPredict.Series["Total Sales per Month"].ToolTip = "Month: #VALX\nSales: #VALY"; string query = "SELECT MONTH(Sales.S_Date),SUM(Product.P_Price*[Order].S_Quantity) As Total FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No)" + "INNER JOIN Product ON [Order].P_ID = Product.P_ID)" + "WHERE (YEAR(Sales.S_Date) = @curYear) AND (Product.P_SubGroup = @pGroup)" + "GROUP BY MONTH(Sales.S_Date),Product.P_SubGroup"; OleDbCommand cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@curYear", CurrYear); cmd.Parameters.AddWithValue("@pGroup", cmbSubGroup.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); for (int x = 1; x < 13; x++) { chartPredict.Series["Total Sales per Month"].Points.AddXY(x, 0); } if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { while (reader.HasRows) { while (reader.Read()) { chartPredict.Visible = true; int y = Convert.ToInt32(reader.GetValue(0).ToString()) - 1; chartPredict.Series["Total Sales per Month"].Points.ElementAt(y).SetValueY(reader.GetValue(1)); chartPredict.Refresh(); chartPredict.ChartAreas[0].RecalculateAxesScale(); } reader.NextResult(); } } conn.Close(); } else { chartPredict.Titles.Add("Monthly Total Quantity Sold for " + cmbSubGroup.SelectedItem.ToString()); chartPredict.ChartAreas[0].AxisX.Title = "Month"; chartPredict.ChartAreas[0].AxisY.Title = "Quantity"; chartPredict.Series["Total Sales per Month"].ToolTip = "Month: #VALX\nQuantity: #VALY"; string query = "SELECT MONTH(Sales.S_Date),SUM([Order].S_Quantity) As Total FROM ((Sales INNER JOIN [Order] ON Sales.Inv_No = [Order].Inv_No)" + "INNER JOIN Product ON [Order].P_ID = Product.P_ID)" + "WHERE (YEAR(Sales.S_Date) = @curYear) AND (Product.P_SubGroup = @pGroup)" + "GROUP BY MONTH(Sales.S_Date),Product.P_SubGroup"; OleDbCommand cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("@curYear", CurrYear); cmd.Parameters.AddWithValue("@pGroup", cmbSubGroup.SelectedItem.ToString()); cmd.ExecuteNonQuery(); OleDbDataReader reader = cmd.ExecuteReader(); for (int x = 1; x < 13; x++) { chartPredict.Series["Total Sales per Month"].Points.AddXY(x, 0); } if (!reader.HasRows) { MessageBox.Show("There is no sales for this product group in this month.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { while (reader.HasRows) { while (reader.Read()) { chartPredict.Visible = true; int y = Convert.ToInt32(reader.GetValue(0).ToString()) - 1; chartPredict.Series["Total Sales per Month"].Points.ElementAt(y).SetValueY(reader.GetValue(1)); chartPredict.Refresh(); chartPredict.ChartAreas[0].RecalculateAxesScale(); } reader.NextResult(); } } conn.Close(); } } }