class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;


namespace Database_Lecture
{ 
class clsDatabaseManager
{ 
private OleDbConnection conn;
private OleDbCommand cmd;
private OleDbDataAdapter da;
private OleDbDataReader dr;


#region initializeComponent
public clsDatabaseManager() 
{ 
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=C:\Users\alvin\Documents\UE Files\PROG3 (C#)\db_student.mdb";
try
{
conn = new OleDbConnection(connectionString);
cmd = new OleDbCommand();
da = new OleDbDataAdapter(cmd); 
}
catch (Exception ex)
{
MessageBox.Show(ex.Message); 
}
}
#endregion


#region fillComboBox
public void fillComboBox(ComboBox cBox, string query, ComboBoxStyle DropDownStyle)
{
cBox.Items.Clear();
cBox.DropDownStyle = DropDownStyle;
cBox.Text = "";




try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}


while (dr.Read())
{
cBox.Items.Add(dr[0]);
}


conn.Close();


try
{
cBox.Text = cBox.Items[0].ToString();
}
catch (Exception e)
{


}
}
#endregion


#region fillListView
public void fillListView(ListView lstView, string query, Boolean gridLines, View view, int fullRowSelect)
{
lstView.View = view;
lstView.GridLines = gridLines;
if (fullRowSelect <= 0)
{
lstView.FullRowSelect = false;
}
else
{
lstView.FullRowSelect = true;
}


lstView.Items.Clear();
lstView.Columns.Clear();


DataSet ds = new DataSet();


try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
da.Fill(ds, "Aw");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}


DataTable dt = new DataTable();
dt = ds.Tables["Aw"];


ListViewItem obj;


for (int x = 0; x < dt.Columns.Count; x++)
{
lstView.Columns.Add(dt.Columns[x].ColumnName);
}


for (int x = 0; x < dt.Rows.Count; x++)
{
obj = lstView.Items.Add(dt.Rows[x][0].ToString());
for (int y = 1; y < dt.Columns.Count; y++)
obj.SubItems.Add(dt.Rows[x][y].ToString());
}


for (int x = 0; x < dt.Columns.Count; x++)
lstView.AutoResizeColumn(x, ColumnHeaderAutoResizeStyle.HeaderSize);


conn.Close();
}
#endregion




#region execute
public void execute(string query)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}
conn.Close();
}
#endregion


#region getSingleValue
public string getSingleValue(string query)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return "";
}


string value = "";
if (dr.Read())
{
value = dr[0].ToString();
}
else
{
value = "";
}


conn.Close();
return value;
}
#endregion




#region retrieveData
public void retrieveData(string query, TextBox txtFName, TextBox txtMName, TextBox txtLName, TextBox txtAge, TextBox txtAddress, ComboBox cboCourse)
{
try
{
cmd.Connection = conn;
conn.Open();
cmd.CommandText = query;
dr = cmd.ExecuteReader();


}
catch (Exception e)
{
MessageBox.Show(e.Message);
conn.Close();
return;
}
while (dr.Read())
{
txtFName.Text = dr["First Name"].ToString();
txtMName.Text = dr["Middle Name"].ToString();
txtLName.Text = dr["Last Name"].ToString();
txtAge.Text = dr["Age"].ToString();
cboCourse.Text = dr["Course"].ToString();
txtAddress.Text = dr["Address"].ToString();
}
dr.Close();
conn.Close();
}
#endregion
}
}
val126

Posts: 21
Joined: Wed Sep 28, 2011 3:32 pm