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