public static void PopulateListView(ListView lst, String strQuery)
{
ListViewItem lstItem = null;
int x;
lst.Items.Clear();
conn.Open();
comm.Connection = conn;
comm.CommandText = strQuery;
reader = comm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
lstItem = lst.Items.Add(reader.GetValue(0).ToString());
for (x = 1; x < reader.FieldCount; x++)
{
lstItem.SubItems.Add(reader.GetValue(x).ToString());
}
}
}
conn.Close();
}
public Course(MySqlDataReader dReader)
{
lectures = new List<Section>();
labs = new List<Section>();
iterations = new List<List<Section>>();
dReader.Read();
dept = dReader.GetValue(0).ToString();
number = (int)dReader.GetValue(1);
creditHours = (int)dReader.GetValue(4);
do
{
if (dReader[6].ToString() == "LEC")
{
if (!CheckSections(lectures, dReader))
{
lectures.Add(new Section(dReader));
}
}
else if (dReader[6].ToString() != "DSO")
{
if (!CheckSections(labs, dReader))
{
labs.Add(new Section(dReader));
}
}
} while (dReader.Read());
}
public string Maxsuivant(string sTable, string sChamp, string scond)
{
//int iCount = GetRecordCount(QC, sTable, "");
DeltaSQLTmp.CommandText = "SELECT (max(" + sChamp + ") + 1) as tmp FROM " + sTable;
if (scond != "")
{
DeltaSQLTmp.CommandText = DeltaSQLTmp.CommandText + " WHERE " + scond;
}
MySql.Data.MySqlClient.MySqlDataReader Q = DeltaSQLTmp.ExecuteReader();
//Q.Read();
string Ret = "";
//if (Q.RecordCount != 0)
//if (iCount != 0)
if (Q.Read() && Q.GetValue(Q.GetOrdinal("tmp")).ToString() != "")
{
Ret = Q.GetValue(Q.GetOrdinal("tmp")).ToString();
}
else
{
Ret = "1";
}
Q.Close();
return(Ret);
}
/// <summary>
/// Mappt einen Datensatz aus der Datenbank auf ein Objekt vom Typ 'Arbeitsspeicher'
/// </summary>
/// <param name="reader">Der Datensatz, welcher gemappt wird</param>
/// <returns>RandomAccessMemory</returns>
protected override object MapToEntity(MySqlDataReader reader)
{
RandomAccessMemory ram = new RandomAccessMemory();
ProducerDataAccess producerDataAccess = new ProducerDataAccess();
ram.Id = Int32.Parse(reader.GetValue(0).ToString());
ram.Description = reader.GetValue(1).ToString();
ram.Memory = ulong.Parse(reader.GetValue(2).ToString());
ram.ClockRate = Double.Parse(reader.GetValue(3).ToString());
ram.Producer = producerDataAccess.GetEntityById<Producer>(Int32.Parse(reader.GetValue(4).ToString()));
return ram;
}
//Hérna er fundinn ákveðin einstaklingur og gögning hans koma til baka
public string[] FinnaAkvedinOgSkilaTilBaka(string kennitala)
{
string[] gogn = new string[8];
if (OpenConnection() == true)
{
fyrirspurn = "SELECT id, kennitala, password, name, age, position, salary, hours FROM workers where kennitala='" + kennitala + "'"; //til að fá upplýsingarnar um starfsmanninn
nySQLskipun = new MySqlCommand(fyrirspurn, sqltenging);
sqllesari = nySQLskipun.ExecuteReader();
while (sqllesari.Read())
{
gogn[0] = sqllesari.GetValue(0).ToString(); //id-ið hans
gogn[1] = sqllesari.GetValue(1).ToString(); //kennitalan
gogn[2] = sqllesari.GetValue(2).ToString(); //lykilorðið
gogn[3] = sqllesari.GetValue(3).ToString(); //Nafnið
gogn[4] = sqllesari.GetValue(4).ToString(); //aldur
gogn[5] = sqllesari.GetValue(5).ToString(); //starfsstaða
gogn[6] = sqllesari.GetValue(6).ToString(); //laun
gogn[7] = sqllesari.GetValue(7).ToString(); //vinnutímar
}
sqllesari.Close();
CloseConnection();
return gogn;
}
return gogn;
}
public static string getLine(MySqlDataReader mySqlDataReader)
{
int colum = mySqlDataReader.FieldCount;
string line = "";
for(int i =0;i<colum;i++){
if(mySqlDataReader.GetValue(i) is DBNull){
line += "null"+" ";
}else{
line += mySqlDataReader.GetValue(i)+" ";
}
}
return line;
}
public void connAndRead(string sql)
{
string ans = sql;
listBox1.Items.Clear();
try
{
conn.Open();
cmd.CommandText = ans;
dr = cmd.ExecuteReader();
while (dr.Read())
{
string thisrow = "";
for (int i = 0; i < dr.FieldCount; i++)
thisrow += dr.GetValue(i).ToString() + " ";
listBox1.Items.Add(thisrow);
}
conn.Close();
}
catch (MySqlException exp)
{
conn.Close();
MessageBox.Show(exp.Message);
}
}
//Þessi aðferð finnu ákveðinn einstakling
public bool FinnaEinstakling(string kt)
{
string lina = null;
if (OpenConnection() == true)
{
fyrirspurn = "SELECT kennitala FROM workers where kennitala='" + kt + "'"; //Fyrispurn hvort starsfmaður er í gagnagrunninum
nySQLskipun = new MySqlCommand(fyrirspurn, sqltenging);
sqllesari = nySQLskipun.ExecuteReader();
while (sqllesari.Read())
{
for (int i = 0; i < sqllesari.FieldCount; i++)
{
lina += (sqllesari.GetValue(i).ToString());
}
}
sqltenging.Close();
if (lina != null) //ef starsfmaður fannst
{
return true;
}
else //annars er skilað false
{
return false;
}
}
else //ef tengingin mistókst er líka skilað false
{
return false;
}
}
public bool Logar(string nickname, string senha)
{
Conexao();
bool logado = false;
string sql = "select id, nickname, senha from Usuario where nickname= @nickname ";
command = new MySqlCommand(sql, Conexao());
par = new MySqlParameter("@nickname", nickname);
par.MySqlDbType = MySqlDbType.VarChar;
command.Parameters.Add(par);
reader = command.ExecuteReader();
string senhaUsuario;
if (reader.Read())
{
senhaUsuario = reader.GetValue(reader.GetOrdinal("SENHA")).ToString();
if (senhaUsuario.Equals(senha))
{
logado = true;
}
}
FecharConexao();
return logado;
// command = new System.Data.SqlClient.SqlCommand(sql, conexão());
}
public static PyObject DBColumnToPyObject(int index, ref MySqlDataReader reader)
{
Type type = reader.GetFieldType(index);
switch (type.Name)
{
case "String":
return new PyString(reader.GetString(index));
case "UInt32":
case "Int32":
case "UInt16":
case "Int16":
case "SByte":
case "Byte":
return new PyInt(reader.GetInt32(index));
case "UInt64":
case "Int64":
return new PyLongLong(reader.GetInt64(index));
case "Byte[]":
return new PyBuffer((byte[])reader.GetValue(index));
case "Double":
return new PyFloat(reader.GetDouble(index));
case "Decimal":
return new PyFloat((double)reader.GetDecimal(index));
case "Boolean":
return new PyBool(reader.GetBoolean(index));
default:
Log.Error("Database", "Unhandled MySQL type " + type.Name);
break;
}
return null;
}
public bool refresh()
{
listBox1.Items.Clear();
try
{
conn.Open();
cmd.CommandText = "SELECT * FROM test";
dr = cmd.ExecuteReader();
while (dr.Read())
{
string thisrow = "";
for (int i = 0; i < dr.FieldCount; i++)
{
thisrow += dr.GetValue(i).ToString() + " ";
}
listBox1.Items.Add(thisrow);
}
conn.Close();
return true;
}
catch (MySqlException expsql2)
{
MessageBox.Show(String.Format("nie udalo sie pobrac danych, blad{0}", expsql2), "Pobieranie danych", MessageBoxButtons.OK);
return false;
}
}
public String checkNull(MySqlDataReader reader, Int16 index)
{
if (reader.IsDBNull(index))
{
return null;
}
return reader.GetValue(index).ToString();
}
/// <summary>
/// Mappt einen Datensatz aus der Datenbank auf ein Objekt vom Typ 'Hersteller'
/// </summary>
/// <param name="reader">Der Datensatz, welcher gemappt wird</param>
/// <returns>Producer</returns>
protected override object MapToEntity(MySqlDataReader reader)
{
Producer producer = new Producer();
producer.Id = Int32.Parse(reader.GetValue(0).ToString());
producer.CompanyName = reader.GetValue(1).ToString();
producer.PhoneNumber = reader.GetValue(2).ToString();
producer.Email = reader.GetValue(3).ToString();
producer.Website = reader.GetValue(4).ToString();
producer.PostalCode = uint.Parse(reader.GetValue(5).ToString());
producer.Place = reader.GetValue(6).ToString();
producer.Street = reader.GetValue(7).ToString();
producer.HouseNumber = uint.Parse(reader.GetValue(8).ToString());
return producer;
}
private static string getLine(MySqlDataReader mySqlDataReader)
{
string line = "";
for (int index = 0; index < mySqlDataReader.FieldCount; index++) {
object value = mySqlDataReader.GetValue (index);
if (value is DBNull)
value = "null";
line = line + value + " ";
}
return line;
}
private static string getline(MySqlDataReader mysqlDataReader)
{
int col=mysqlDataReader.FieldCount;
string linea=" ";
for(int i=0;i<col;i++){
if (mysqlDataReader.GetValue(i) is DBNull)
linea+="null ";
else
linea+=mysqlDataReader.GetValue(i)+" ";
}
return linea;
}
public string[] FinnaAkvedinOgSkila(string user_id)
{
string[] gogn = new string[5];
if (OpenConnection() == true)
{
fyrirspurn = "SELECT user_id,nafn,Simi,Lykilord,Netfang FROM user WHERE user_id='" + user_id + "'";
nySQLskipun = new MySqlCommand(fyrirspurn, sqltenging);
sqllesari = nySQLskipun.ExecuteReader();
while (sqllesari.Read())
{
gogn[0] = sqllesari.GetValue(0).ToString();
gogn[1] = sqllesari.GetValue(1).ToString();
gogn[2] = sqllesari.GetValue(2).ToString();
gogn[3] = sqllesari.GetValue(3).ToString();
gogn[3] = sqllesari.GetValue(4).ToString();
}
sqllesari.Close();
CloseConnection();
return gogn;
}
return gogn;
}
public string[] FinnaAkvedinnOgSkilaTilBaka(string id)
{
string[] gogn = new string[4];
if (OpenConnection() == true)
{
fyrirspurn = "SELECT id_medlimur, nafn, netfang, simanumer FROM medlimur WHERE id_medlimur = '" + id + "'";//leita inná sql töflur
nySQLskipun = new MySqlCommand(fyrirspurn, sqltenging);
sqllesari = nySQLskipun.ExecuteReader();
while (sqllesari.Read())
{
gogn[0] = sqllesari.GetValue(0).ToString();
gogn[1] = sqllesari.GetValue(1).ToString();
gogn[2] = sqllesari.GetValue(2).ToString();
gogn[3] = sqllesari.GetValue(3).ToString();
}
sqllesari.Close();
CloseConnection();
return gogn;
}
return gogn;
}
/// <summary>
/// Mappt einen Datensatz aus der Datenbank auf ein Objekt vom Typ 'Prozessor'
/// </summary>
/// <param name="reader">Der Datensatz, welcher gemappt wird</param>
/// <returns>Processor</returns>
protected override object MapToEntity(MySqlDataReader reader)
{
Processor processor = new Processor();
ProducerDataAccess producerDataAccess = new ProducerDataAccess();
processor.Id = Int32.Parse(reader.GetValue(0).ToString());
processor.Description = reader.GetValue(1).ToString();
processor.Model = reader.GetValue(2).ToString();
processor.Core = uint.Parse(reader.GetValue(3).ToString());
processor.CommandSet = reader.GetValue(4).ToString();
processor.Architecture = uint.Parse(reader.GetValue(5).ToString());
processor.ClockRate = Double.Parse(reader.GetValue(6).ToString());
processor.Producer = producerDataAccess.GetEntityById<Producer>(Int32.Parse(reader.GetValue(7).ToString()));
return processor;
}
public static void show(MySqlDataReader mySqlDataReader)
{
int numeroRegistro = mySqlDataReader.VisibleFieldCount;
Console.WriteLine ("");
int numeroColumnas = mySqlDataReader.FieldCount;
string fila = null;
for (int i=0; i <= numeroRegistro; i++) {
mySqlDataReader.Read ();
for (int j=0; j< numeroColumnas; j++) {
fila += mySqlDataReader.GetName (j)+": " +mySqlDataReader.GetValue (j).ToString () + " ";
}
Console.WriteLine (fila);
fila = null;
}
}
private void updateData()
{
try
{
db.sql = $"select `date_create`, `transaction_name`,`cash`,`comment` from `income_transaction` where `income_id` = '{month + year}'";
db.addCMD();
MySql.Data.MySqlClient.MySqlDataReader r = db.cmd.ExecuteReader();
bool flag = false;
while (r.Read())
{
flag = true;
int cash = r.GetInt32(2);
ListViewItem item = new ListViewItem(r.GetString(0));
for (int i = 1; i < r.FieldCount; i++)
{
item.SubItems.Add(r.GetValue(i).ToString());
}
cashDetailListView.Items.Add(item);
if (r.GetString(1) == "member payment")
{
memberIncome += cash;
}
else
{
otherIncome += cash;
}
allIncome += cash;
tt++;
}
if (!flag)
{
MessageBox.Show("NO ITEM HERE");
}
r.Dispose();
db.disposeCmd();
cashDetailTotalTbox.Text = allIncome.ToString("N");
cashDetailMemberIncomeTbox.Text = memberIncome.ToString("N");
cashDetailTransTbox.Text = tt.ToString();
cashDetailOtherTbox.Text = otherIncome.ToString("N");
monthTbox.Text = $"{month} {year}";
}
catch (MySqlException err)
{
MessageBox.Show(err.Message, err.Number.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public List<string> ReadData(string query)
{
List<string> list = new List<string>();
cmd.CommandText = query;
dataReader = cmd.ExecuteReader();
int count = dataReader.FieldCount;
while (dataReader.Read())
{
for (int i = 0; i < count; i++)
{
list.Add(dataReader.GetValue(i).ToString());
}
}
dataReader.Close();
return list;
}
public DatabaseTable(MySqlDataReader recordSet)
{
StringBuilder builder = new StringBuilder();
while (recordSet.Read())
{
columnCount = recordSet.FieldCount;
String thisrow = "";
for (int i = 0; i < recordSet.FieldCount; i++)
{
builder.Append(recordSet.GetValue(i).ToString());
builder.Append("\t");
}
builder.Append("\n");
//thisrow += "[" + recordSet.GetValue(i).ToString() + "]";
Logger.getInstance().log(thisrow, "DatabaseTable", Logger.Level.INFO);
}
builder.Append("TOTAL ROWS: ");
builder.Append(recordSet.RecordsAffected);
this.table = builder.ToString();
}
public List<string> AllEmailAddresses()
{
List<string> Results = new List<string>();
string Row = null;
if (OpenConnection() == true)
{
fyrirspurn = "SELECT email FROM user";
nySQLskipun = new MySqlCommand(fyrirspurn, sqltenging);
sqllesari = nySQLskipun.ExecuteReader();
while (sqllesari.Read())
{
for (int i = 0; i < sqllesari.FieldCount; i++)
{
Row += (sqllesari.GetValue(i).ToString()) + "";
}
Results.Add(Row);
Row = null;
}
CloseConnection();
return Results;
}
return Results;
}
protected DataTable CreateTable(MySqlDataReader reader)
{
DataTable table = new DataTable();
for (int i = 0; i < reader.FieldCount; i++)
{
table.Columns.Add(reader.GetName(i));
}
while (reader.Read())
{
DataRow row = table.NewRow();
table.Rows.Add(row);
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader.GetValue(i);
}
}
return table;
}
public string ReadEntries(string sql)
{
try{
cmd = new MySqlCommand(sql, con);
rdr = cmd.ExecuteReader();
if(rdr.HasRows){
while(rdr.Read()){
idFB = Int64.Parse(rdr.GetValue(0).ToString()); //GetValues Row index 0-i
}
}else{
idFB = -1;
}
rdr.Close();
return idFB.ToString();
}
catch(Exception e){
FBManager.errorMSG = e.Message;
return "[Read Fail]";
}
}
private void loadSidebar()
{
connection.Open();
HtmlGenericControl li = new HtmlGenericControl("li");
HtmlGenericControl a = new HtmlGenericControl("a");
a.ID = "home";
a.Attributes["onclick"] = "PageMethods.changeFeed(\"" + a.ID + "\", \"-1\");window.location='Home.aspx';";
a.Attributes["class"] = Session["location"].Equals(a.ID) == true ? "active" : "";
a.InnerHtml = "Home";
li.Controls.Add(a);
menuItems.Controls.Add(li);
li = new HtmlGenericControl("li");
a = new HtmlGenericControl("a");
a.ID = "unread";
a.Attributes["onclick"] = "PageMethods.changeFeed(\"" + a.ID + "\", \"-2\");window.location='Home.aspx';";
a.Attributes["class"] = Session["location"].Equals(a.ID) == true ? "active" : "";
a.InnerHtml = "Unread";
li.Controls.Add(a);
HtmlGenericControl unread = new HtmlGenericControl("span");
unread.Attributes["class"] = "badge";
command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(article_id) AS unread FROM Unread WHERE user_id = @userID";
command.Parameters.AddWithValue("@userID", Session["userID"]);
result = command.ExecuteReader();
result.Read();
unread.InnerText = " " + result.GetString("unread");
result.Close();
li.Controls.Add(unread);
menuItems.Controls.Add(li);
li = new HtmlGenericControl("li");
a = new HtmlGenericControl("a");
a.ID = "liked";
a.Attributes["onclick"] = "PageMethods.changeFeed(\"" + a.ID + "\", \"-3\");window.location='Home.aspx';";
a.Attributes["class"] = Session["location"].Equals(a.ID) == true ? "active" : "";
a.InnerHtml = "Liked";
li.Controls.Add(a);
menuItems.Controls.Add(li);
li = new HtmlGenericControl("li");
a = new HtmlGenericControl("a");
a.ID = "all";
a.Attributes["onclick"] = "PageMethods.changeFeed(\"" + a.ID + "\", \"-4\");window.location='Home.aspx';";
a.Attributes["class"] = Session["location"].Equals(a.ID) == true ? "active" : "";
a.InnerHtml = "All articles";
li.Controls.Add(a);
menuItems.Controls.Add(li);
command = connection.CreateCommand();
command.CommandText = "SELECT f.id, f.name, f.icon, u.unread FROM Subscriptions s JOIN Feeds f ON s.feed_id = f.id LEFT JOIN (SELECT a.feed_id, COUNT(a.feed_id) AS unread FROM Unread JOIN Articles a ON article_id = a.id WHERE user_id = @userID GROUP BY feed_id) AS u ON f.id = u.feed_id WHERE s.user_id = @userID AND s.folder IS NULL OR s.folder = '' ORDER BY f.name";
command.Parameters.AddWithValue("@userID", Session["userID"]);
result = command.ExecuteReader();
HtmlGenericControl noFolder = new HtmlGenericControl("ul");
noFolder.Attributes["class"] = "connected sortable";
while(result.Read() == true)
{
HtmlGenericControl feed = new HtmlGenericControl("li");
HtmlGenericControl link = new HtmlGenericControl("a");
link.ID = result.GetString("id");
if(link.ID.Equals(Session["feedID"].ToString()) == true)
{
link.Attributes["class"] = "active";
}
link.Attributes["style"] = "background-image: url(data:image/png;base64," + Convert.ToBase64String((byte[])result.GetValue(2)) + ");";
link.Attributes["onclick"] = "PageMethods.changeFeed(\"feed\", \"" + link.ID + "\");window.location='Home.aspx';";
link.InnerHtml = result.GetString("name");
feed.Controls.Add(link);
if(result.IsDBNull(3) == false)
{
unread = new HtmlGenericControl("span");
unread.Attributes["class"] = "badge";
unread.InnerHtml = " " + result.GetString("unread");
feed.Controls.Add(unread);
}
noFolder.Controls.Add(feed);
}
HtmlGenericControl empty = new HtmlGenericControl("li");
empty.Attributes["class"] = "empty-li";
noFolder.Controls.Add(empty);
subscriptions.ContentTemplateContainer.Controls.Add(noFolder);
result.Close();
command = connection.CreateCommand();
command.CommandText = "SELECT f.id, f.name, f.icon, u.unread, s.folder FROM Subscriptions s JOIN Feeds f ON s.feed_id = f.id LEFT JOIN (SELECT a.feed_id, COUNT(a.feed_id) AS unread FROM Unread JOIN Articles a ON article_id = a.id WHERE user_id = @userID GROUP BY feed_id) AS u ON f.id = u.feed_id WHERE s.user_id = @userID AND s.folder IS NOT NULL AND s.folder <> '' ORDER BY s.folder, f.name";
command.Parameters.AddWithValue("@userID", Session["userID"]);
result = command.ExecuteReader();
HtmlGenericControl folders = new HtmlGenericControl("ul");
HtmlGenericControl folder = null, input, label, feeds = null;
string previousFolder = null;
while(result.Read() == true)
{
string currentFolder = result.GetString("folder");
if(currentFolder != previousFolder)
{
folder = new HtmlGenericControl("li");
folder.Attributes["class"] = "folder";
input = new HtmlGenericControl("input");
input.ID = currentFolder;
input.Attributes["type"] = "checkbox";
folder.Controls.Add(input);
label = new HtmlGenericControl("label");
label.Attributes["for"] = currentFolder;
label.InnerText = currentFolder;
folder.Controls.Add(label);
feeds = new HtmlGenericControl("ul");
feeds.Attributes["class"] = "connected sortable";
folder.Controls.Add(feeds);
empty = new HtmlGenericControl("li");
empty.Attributes["class"] = "empty-li";
feeds.Controls.Add(empty);
previousFolder = currentFolder;
}
HtmlGenericControl feed = new HtmlGenericControl("li");
HtmlGenericControl link = new HtmlGenericControl("a");
link.ID = result.GetString("id");
if(link.ID.Equals(Session["feedID"].ToString()) == true)
{
link.Attributes["class"] = "active";
}
link.Attributes["style"] = "background-image: url(data:image/png;base64," + Convert.ToBase64String((byte[])result.GetValue(2)) + ");";
link.Attributes["onclick"] = "PageMethods.changeFeed(\"feed\", \"" + link.ID + "\");window.location='Home.aspx';";
link.InnerHtml = result.GetString("name");
feed.Controls.Add(link);
if(result.IsDBNull(3) == false)
{
unread = new HtmlGenericControl("span");
unread.Attributes["class"] = "badge";
unread.InnerHtml = " " + result.GetString("unread");
feed.Controls.Add(unread);
}
feeds.Controls.Add(feed);
folders.Controls.Add(folder);
}
subscriptions.ContentTemplateContainer.Controls.Add(folders);
result.Close();
connection.Close();
}
protected void cat_SelectedIndexChanged(object sender, EventArgs e)
{
id = cat.SelectedValue.ToString();
if (id.Equals("Beauty & Personal Care"))
{
i = 0;
while (i <= 32)
{
if (i >= 0 && i <= 4)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
if (id.Equals("Home & Kitchen"))
{
i = 0;
while (i <= 32)
{
if (i >= 5 && i <= 10)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
if (id.Equals("Clothing"))
{
i = 0;
while (i <= 32)
{
if (i >= 11 && i <= 15)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
if (id.Equals("Phones & Computers"))
{
i = 0;
while (i <= 32)
{
if (i >= 16 && i <= 20)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
if (id.Equals("TV,Audio,Video"))
{
i = 0;
while (i <= 32)
{
if (i >= 21 && i <= 24)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
if (id.Equals("Food & Beverages"))
{
i = 0;
while (i <= 32)
{
if (i >= 25 && i <= 32)
{
subcat1.Items[i].Enabled = true;
}
else
{
subcat1.Items[i].Enabled = false;
}
i++;
}
}
id = cat.SelectedValue.ToString();
// Response.Write(id);
if (id.Equals("Beauty & Personal Care"))
{
query1 = "select DISTINCT company from daily_personal_needs";
}
else if (id.Equals("Clothing"))
{
query1 = "select DISTINCT company from clothing";
}
else if (id.Equals("Home & Kitchen"))
{
query1 = "select DISTINCT company from home_kitchen";
}
else if (id.Equals("Phones & Computers"))
{
query1 = "select DISTINCT company from phone_computer";
}
else if (id.Equals("TV,Audio,Video"))
{
query1 = "select DISTINCT company from tv_audio_video";
}
else if (id.Equals("Food & Beverages"))
{
query1 = "select DISTINCT company from food_beverages";
}
//Response.Write(query1);
try
{
conn = new MySqlConnection(GetConnectionString());
conn.Open();
comm = new MySqlCommand(query1,conn);
rd = comm.ExecuteReader();
//Response.Write("rd");
j = 0;
DropDownList5.Items.Clear();
while (rd.Read())
{
//Response.Write("ABC" + rd.GetValue(0).ToString());
DropDownList5.Items.Add(rd.GetValue(0).ToString());
j++;
}
}
catch (Exception ed)
{
Response.Write(ed.StackTrace);
}
finally
{
conn.Close();
//Response.Redirect("~/WebForm1.aspx");
}
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
conn1 = new MySqlConnection(GetConnectionString());
conn1.Open();
comm1 = new MySqlCommand("select DISTINCT company from daily_personal_needs", conn1);
rd1 = comm1.ExecuteReader();
// Response.Write("rd");
j = 0;
while (rd1.Read())
{
//Response.Write("ABC" + rd.GetValue(0).ToString());
DropDownList5.Items.Add(rd1.GetValue(0).ToString());
j++;
}
}
catch (Exception ed)
{
Response.Write(ed.Message);
}
finally
{
conn1.Close();
//Response.Redirect("~/WebForm1.aspx");
}
}
private static Tests.Models.ChangeTrackingEmployee Load(MySql.Data.MySqlClient.MySqlDataReader reader)
{
var result = new Tests.Models.ChangeTrackingEmployee();
if (reader.IsDBNull(0))
{
result.CreatedDate = null;
}
else
{
result.CreatedDate = reader.GetDateTime(0);
}
if (reader.IsDBNull(1))
{
result.EmployeeKey = null;
}
else
{
result.EmployeeKey = (System.Nullable <System.Int64>)reader.GetValue(1);
}
if (reader.IsDBNull(2))
{
result.FirstName = null;
}
else
{
result.FirstName = reader.GetString(2);
}
if (reader.IsDBNull(3))
{
result.LastName = null;
}
else
{
result.LastName = reader.GetString(3);
}
if (reader.IsDBNull(4))
{
result.ManagerKey = null;
}
else
{
result.ManagerKey = reader.GetInt32(4);
}
if (reader.IsDBNull(5))
{
result.MiddleName = null;
}
else
{
result.MiddleName = reader.GetString(5);
}
if (reader.IsDBNull(6))
{
result.Title = null;
}
else
{
result.Title = reader.GetString(6);
}
if (reader.IsDBNull(7))
{
result.UpdatedDate = null;
}
else
{
result.UpdatedDate = reader.GetDateTime(7);
}
((System.ComponentModel.IChangeTracking)result).AcceptChanges();
return(result);
}
internal void ProcessOutputParameters(MySqlDataReader reader)
{
// We apparently need to always adjust our output types since the server
// provided data types are not always right
AdjustOutputTypes(reader);
// now read the output parameters data row
CommandBehavior behavior = reader.CommandBehavior;
if ((behavior & CommandBehavior.SchemaOnly) != 0) return;
if (!reader.Read()) return;
//reader.ResultSet.NextRow(behavior);
string prefix = "@" + StoredProcedure.ParameterPrefix;
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.StartsWith(prefix))
fieldName = fieldName.Remove(0, prefix.Length);
MySqlParameter parameter = command.Parameters.GetParameterFlexible(fieldName, true);
parameter.Value = reader.GetValue(i);
}
}
/// <summary>
/// Alter field for que given table.
/// </summary>
/// <param name="tableName">Table</param>
/// <param name="fieldName">Field</param>
public void alterField(string tableName, string fieldName)
{
//show selected field info
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SHOW FULL FIELDS FROM " + tableName + " WHERE Field = '" + fieldName + "';", mySqlConnection);
mySqlDataReader = mySqlCommand.ExecuteReader();
ShowLog("SHOW FIELDS FROM " + tableName + " WHERE Field = '" + fieldName + "';");
//start form for edit field
frmAlterField frm = new frmAlterField();
//DialogResult dialogResult = frm.ShowDialog();
//list to store data
List <string> data = new List <string>();
while (mySqlDataReader.Read())
{
for (int i = 0; i < mySqlDataReader.FieldCount; i++)
{
data.Add(mySqlDataReader.GetValue(i).ToString());
}
}
mySqlDataReader.Close();
//name
frm.txtFieldName.Text = data[0];
//type
frm.txtFieldType.Text = data[1];
//collation
frm.txtFieldCollation.Text = data[2];
//nullable
if (data[3] == "YES")
{
frm.rbNullableYes.Checked = true;
}
else if (data[3] == "NO")
{
frm.rbNullableNo.Checked = true;
}
//key / index
if (data[4] == "PRI")
{
frm.rbKeyPrimary.Checked = true;
}
else if (data[4] == "UNI")
{
frm.rbKeyUnique.Checked = true;
}
else if (data[4] == "MUL")
{
frm.rbKeyMultiple.Checked = true;
}
else if (data[4] == "")
{
frm.rbKeyNull.Checked = true;
}
//default
frm.txtFieldDefault.Text = data[5];
//extra
frm.txtFieldExtra.Text = data[6];
//privileges
frm.txtFieldPrivileges.Text = data[7];
//comment
frm.txtFieldComment.Text = data[8];
//boolean to continue or not with connection attempts
Boolean b = true;
while (b)
{
//show frmAlterField and save dialog
DialogResult dialogResult = frm.ShowDialog();
if (dialogResult == DialogResult.OK)
{
string query = "ALTER TABLE " + tableName + " CHANGE " + data[0] + " " + frm.txtFieldName.Text + " " + frm.txtFieldType.Text + ";";
//confirm query
if (ConfirmQuery(query) == true)
{
try
{
//try execute query
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(query, mySqlConnection);
mySqlCommand.ExecuteNonQuery();
ShowLog("Query: " + query);
mySqlDataReader.Close();
//query executed successfully
b = false;
}
catch (Exception e)
{
//error
DialogResult dialogException = MessageBox.Show("ERROR to execute query: " + e.ToString(), "ERROR", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
if (dialogException == DialogResult.Retry)
{
//new attemp
b = true;
}
else if (dialogException == DialogResult.Cancel)
{
//stop attemps
b = false;
}
}
}
}
else if (dialogResult == DialogResult.Cancel)
{
//stop attemps
b = false;
}
}
}
private MembershipUser GetUserFromReader(MySqlDataReader reader)
{
object providerUserKey = reader.GetInt32("userId");
string username = reader.GetString("name");
string email = null;
if (!reader.IsDBNull(reader.GetOrdinal("Email")))
email = reader.GetString("Email");
string passwordQuestion = "";
if (!(reader.GetValue(reader.GetOrdinal("PasswordQuestion")) == DBNull.Value))
passwordQuestion = reader.GetString("PasswordQuestion");
string comment = "";
if (!(reader.GetValue(reader.GetOrdinal("Comment")) == DBNull.Value))
comment = reader.GetString("Comment");
bool isApproved = reader.GetBoolean("IsApproved");
bool isLockedOut = reader.GetBoolean("IsLockedOut");
DateTime creationDate = reader.GetDateTime("CreationDate");
DateTime lastLoginDate = new DateTime();
if (!(reader.GetValue(reader.GetOrdinal("LastLoginDate")) == DBNull.Value))
lastLoginDate = reader.GetDateTime("LastLoginDate");
DateTime lastActivityDate = reader.GetDateTime("LastActivityDate");
DateTime lastPasswordChangedDate = reader.GetDateTime("LastPasswordChangedDate");
DateTime lastLockedOutDate = new DateTime();
if (!(reader.GetValue(reader.GetOrdinal("LastLockedoutDate")) == DBNull.Value))
lastLockedOutDate = reader.GetDateTime("LastLockedoutDate");
MembershipUser u =
new MembershipUser(Name, username, providerUserKey, email, passwordQuestion, comment, isApproved,
isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate,
lastLockedOutDate);
return u;
}
private void FindTables(MySqlSchemaCollection schema, string[] restrictions)
{
StringBuilder sql = new StringBuilder();
StringBuilder where = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
"SHOW TABLE STATUS FROM `{0}`", restrictions[1]);
if (restrictions != null && restrictions.Length >= 3 &&
restrictions[2] != null)
{
where.AppendFormat(CultureInfo.InvariantCulture,
" LIKE '{0}'", restrictions[2]);
}
sql.Append(where.ToString());
string table_type = restrictions[1].ToLower() == "information_schema"
?
"SYSTEM VIEW"
: "BASE TABLE";
MySqlCommand cmd = new MySqlCommand(sql.ToString(), connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MySqlSchemaRow row = schema.AddRow();
row["TABLE_CATALOG"] = null;
row["TABLE_SCHEMA"] = restrictions[1];
row["TABLE_NAME"] = reader.GetString(0);
row["TABLE_TYPE"] = table_type;
row["ENGINE"] = GetString(reader, 1);
row["VERSION"] = reader.GetValue(2);
row["ROW_FORMAT"] = GetString(reader, 3);
row["TABLE_ROWS"] = reader.GetValue(4);
row["AVG_ROW_LENGTH"] = reader.GetValue(5);
row["DATA_LENGTH"] = reader.GetValue(6);
row["MAX_DATA_LENGTH"] = reader.GetValue(7);
row["INDEX_LENGTH"] = reader.GetValue(8);
row["DATA_FREE"] = reader.GetValue(9);
row["AUTO_INCREMENT"] = reader.GetValue(10);
row["CREATE_TIME"] = reader.GetValue(11);
row["UPDATE_TIME"] = reader.GetValue(12);
row["CHECK_TIME"] = reader.GetValue(13);
row["TABLE_COLLATION"] = GetString(reader, 14);
row["CHECKSUM"] = reader.GetValue(15);
row["CREATE_OPTIONS"] = GetString(reader, 16);
row["TABLE_COMMENT"] = GetString(reader, 17);
}
}
}
//
// GetUserFromReader
// A helper function that takes the current row from the MySqlDataReader
// and hydrates a MembershiUser from the values. Called by the
// MembershipUser.GetUser implementation.
//
private MembershipUser GetUserFromReader(MySqlDataReader reader)
{
object providerUserKey = new Guid(reader.GetValue(0).ToString());
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string email = reader.IsDBNull(2) ? "" : reader.GetString(2);
string passwordQuestion = reader.IsDBNull(3) ? "" : reader.GetString(3);
string comment = reader.IsDBNull(4) ? "" : reader.GetString(4);
bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6);
DateTime creationDate = reader.IsDBNull(7) ? DateTime.Now : reader.GetDateTime(7);
DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.Now : reader.GetDateTime(8);
DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.Now : reader.GetDateTime(9);
DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.Now : reader.GetDateTime(10);
DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.Now : reader.GetDateTime(11);
return new MembershipUser(
this.Name,
username,
providerUserKey,
email,
passwordQuestion,
comment,
isApproved,
isLockedOut,
creationDate,
lastLoginDate,
lastActivityDate,
lastPasswordChangedDate,
lastLockedOutDate
);
}
public virtual MySqlSchemaCollection GetIndexColumns(string[] restrictions)
{
MySqlSchemaCollection dt = new MySqlSchemaCollection("IndexColumns");
dt.AddColumn("INDEX_CATALOG", typeof(string));
dt.AddColumn("INDEX_SCHEMA", typeof(string));
dt.AddColumn("INDEX_NAME", typeof(string));
dt.AddColumn("TABLE_NAME", typeof(string));
dt.AddColumn("COLUMN_NAME", typeof(string));
dt.AddColumn("ORDINAL_POSITION", typeof(int));
dt.AddColumn("SORT_ORDER", typeof(string));
int max = restrictions == null ? 4 : restrictions.Length;
string[] tableRestrictions = new string[Math.Max(max, 4)];
if (restrictions != null)
{
restrictions.CopyTo(tableRestrictions, 0);
}
tableRestrictions[3] = "BASE TABLE";
MySqlSchemaCollection tables = GetTables(tableRestrictions);
foreach (MySqlSchemaRow table in tables.Rows)
{
string sql = String.Format("SHOW INDEX FROM `{0}`.`{1}`",
table["TABLE_SCHEMA"], table["TABLE_NAME"]);
MySqlCommand cmd = new MySqlCommand(sql, connection);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string key_name = GetString(reader, reader.GetOrdinal("KEY_NAME"));
string col_name = GetString(reader, reader.GetOrdinal("COLUMN_NAME"));
if (restrictions != null)
{
if (restrictions.Length >= 4 && restrictions[3] != null &&
key_name != restrictions[3])
{
continue;
}
if (restrictions.Length >= 5 && restrictions[4] != null &&
col_name != restrictions[4])
{
continue;
}
}
MySqlSchemaRow row = dt.AddRow();
row["INDEX_CATALOG"] = null;
row["INDEX_SCHEMA"] = table["TABLE_SCHEMA"];
row["INDEX_NAME"] = key_name;
row["TABLE_NAME"] = GetString(reader, reader.GetOrdinal("TABLE"));
row["COLUMN_NAME"] = col_name;
row["ORDINAL_POSITION"] = reader.GetValue(reader.GetOrdinal("SEQ_IN_INDEX"));
row["SORT_ORDER"] = reader.GetString("COLLATION");
}
}
}
return(dt);
}
/// <summary>
/// Mappt einen Datensatz aus der Datenbank auf ein Objekt vom Typ 'Festplatte'
/// </summary>
/// <param name="reader">Der Datensatz, welcher gemappt wird</param>
/// <returns>Disk</returns>
protected override object MapToEntity(MySqlDataReader reader)
{
Disk disk = new Disk();
ProducerDataAccess producerDataAccess = new ProducerDataAccess();
disk.Id = Int32.Parse(reader.GetValue(0).ToString());
disk.Description = reader.GetValue(1).ToString();
disk.Capacity = ulong.Parse(reader.GetValue(2).ToString());
disk.Ssd = Boolean.Parse(reader.GetValue(3).ToString());
disk.Inch = Double.Parse(reader.GetValue(4).ToString());
disk.Producer = producerDataAccess.GetEntityById<Producer>(Int32.Parse(reader.GetValue(5).ToString()));
disk.PhysicalInterfaces = this.GetPhysicalInterfaces(disk);
return disk;
}
/// <summary>
/// Print node's child.
/// </summary>
private void PutDataInTreeView()
{
try
{
//if it's connected...
if (CheckConnectMySQL())
{
if (currentElementType == "server")
{
treeView.Nodes.Clear();
//Get all databases
List <string> databases = new List <string>();
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SHOW DATABASES;", mySqlConnection);
mySqlDataReader = mySqlCommand.ExecuteReader();
ShowLog("Query: SHOW DATABASES;");
while (mySqlDataReader.Read())
{
for (int i = 0; i < mySqlDataReader.FieldCount; i++)
{
treeView.Nodes.Add(mySqlDataReader.GetValue(i).ToString());
}
}
mySqlDataReader.Close();
currentSelectedElementName = "";
currentSelectedElementType = "";
PutCurrentElementTreeView();
PutCurrentElementSelectedTreeView();
EnableDisableButtons();
}
else if (currentElementType == "database")
{
treeView.Nodes.Clear();
//Use database
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("USE " + wayToElement["database"] + ";", mySqlConnection);
mySqlDataReader = mySqlCommand.ExecuteReader();
ShowLog("Query: USE " + wayToElement["database"] + ";");
mySqlDataReader.Close();
//Get all tables
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SHOW TABLES;", mySqlConnection);
mySqlDataReader = mySqlCommand.ExecuteReader();
ShowLog("Query: SHOW TABLES;");
while (mySqlDataReader.Read())
{
for (int i = 0; i < mySqlDataReader.FieldCount; i++)
{
treeView.Nodes.Add(mySqlDataReader.GetValue(i).ToString());
}
}
mySqlDataReader.Close();
currentSelectedElementName = "";
currentSelectedElementType = "";
PutCurrentElementTreeView();
PutCurrentElementSelectedTreeView();
EnableDisableButtons();
}
else if (currentElementType == "table")
{
treeView.Nodes.Clear();
//Get all fields
mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand("SELECT column_name FROM information_schema.columns WHERE table_schema = database() AND table_name = '" + wayToElement["table"] + "';", mySqlConnection);
mySqlDataReader = mySqlCommand.ExecuteReader();
ShowLog("Query: SELECT column_name FROM information_schema.columns WHERE table_schema = database() AND table_name = '" + wayToElement["table"] + "';");
while (mySqlDataReader.Read())
{
for (int i = 0; i < mySqlDataReader.FieldCount; i++)
{
treeView.Nodes.Add(mySqlDataReader.GetValue(i).ToString());
}
}
mySqlDataReader.Close();
currentSelectedElementName = "";
currentSelectedElementType = "";
PutCurrentElementTreeView();
PutCurrentElementSelectedTreeView();
EnableDisableButtons();
}
else if (currentElementType == "field")
{
MessageBox.Show("You can´t go more deep.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception ex)
{
//disconnect mysql
//DisconnectMysql();
//exception thrown, show message
DialogResult dialog = MessageBox.Show("ERROR to extract data: " + ex.ToString(), "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
