public bool insertLoginCredentials(LOGIN_CREDENTIALS obj)
{
int val = 0;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("INSERT INTO [dbo].[LOGIN_CREDENTIALS] ([PASSWORD],[TYPE],[STATUS])" + "VALUES (@PASSWORD, @TYPE, @STATUS)");
//SqlParameter p = new SqlParameter("@ID", SqlDbType.Int);
//p.Value = obj.ID;
SqlParameter p1 = new SqlParameter("@PASSWORD", SqlDbType.VarChar, 50);
p1.Value = obj.PASSWORD;
SqlParameter p2 = new SqlParameter("@TYPE", SqlDbType.VarChar, 10);
p2.Value = obj.TYPE;
SqlParameter p3 = new SqlParameter("@STATUS", SqlDbType.VarChar, 10);
p3.Value = obj.STATUS;
//cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Connection.Open();
val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (Exception ex)
{
}
return(val > 0);
}
private void metroButton2_Click(object sender, EventArgs e)
{
try
{
if (metroTextBox1.Text == "" || metroGrid1.Rows[0].Cells["Tag Name"].Value.ToString() == "" || metroGrid1.Rows[0].Cells["Tag Value"].Value.ToString() == "")
{
CheckEmpty();
}
else
{
DialogResult f = MetroMessageBox.Show(this, "Are you sure to save?", "WSH notify", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (f == DialogResult.OK)
{
access_data d = new access_data();
site s = new site();
s.url = metroTextBox1.Text;
s.description = textBox1.Text;
int site_id = d.InsertNweUrlData(s);
int i = metroGrid1.Rows.Count;
SqlDbDataAccess dr = new SqlDbDataAccess();
for (int j = 0; j < i; j++)
{
using (SqlCommand cmd = dr.GetCommand("insert into dbo.site_details (site_id,tag_name,tag_value) values(@site_id,@tag_name,@tag_value);"))
{
SqlParameter p = new SqlParameter("@site_id", SqlDbType.Int);
p.Value = site_id;
SqlParameter p1 = new SqlParameter("@tag_name", SqlDbType.VarChar, 200);
p1.Value = metroGrid1.Rows[j].Cells["Tag Name"].Value.ToString();
SqlParameter p2 = new SqlParameter("@tag_value", SqlDbType.VarChar, 500);
p2.Value = metroGrid1.Rows[j].Cells["Tag Value"].Value.ToString();
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
MetroMessageBox.Show(this, "Data successfully Saved", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
metroTextBox1.Text = "";
metroTextBox2.Text = "";
textBox1.Text = "";
textBox2.Text = "";
metroGrid1.DataSource = null;
}
}
}
catch (System.ArgumentOutOfRangeException)
{
CheckEmpty();
}
}
public void get_info_by_url(string url)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("select dbo.site.url,dbo.site_details.tag_name,dbo.site_details.tag_value,dbo.site.description from dbo.site,dbo.site_details where dbo.site.id=dbo.site_details.site_id and dbo.site.url=@url;");
SqlParameter p = new SqlParameter("@url", SqlDbType.VarChar, 50);
p.Value = url;
cmd.Parameters.Add(p);
cmd.Connection.Open();
SqlDataReader d = cmd.ExecuteReader();
d.Read();
try
{
a = d.GetString(0);
b = d.GetString(1);
c = d.GetString(2);
dd = d.GetString(3);
}
catch (System.Data.SqlTypes.SqlNullValueException)
{
a = "";
b = "";
c = "";
dd = "";
}
cmd.Connection.Close();
}
public void setValuesToTextbox()
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM USERS WHERE ID = " + idVal.id);
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
using (reader)
{
while (reader.Read())
{
idLabel.Text = reader.GetInt32(0).ToString();
label6.Text = reader.GetString(1);
IMAGE = (byte[])reader[2];
MemoryStream ms = new MemoryStream(IMAGE);
pictureBox2.Image = System.Drawing.Image.FromStream(ms);
label8.Text = reader.GetDateTime(3).ToString();
label9.Text = reader.GetString(4);
label11.Text = reader.GetString(5);
label13.Text = reader.GetString(6);
label14.Text = reader.GetString(7);
label18.Text = reader.GetString(8);
}
}
reader.Close();
cmd.Connection.Close();
}
public bool Update_site_details(int id, string tag_name, string tag_value)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("update dbo.site_details set tag_name=@tag_name,tag_value=@tag_value where dbo.site_details.id=@id;");
SqlParameter p = new SqlParameter("@tag_name", SqlDbType.VarChar, 200);
p.Value = tag_name;
SqlParameter p1 = new SqlParameter("@tag_value", SqlDbType.VarChar, 500);
p1.Value = tag_value;
SqlParameter p2 = new SqlParameter("@id", SqlDbType.Int);
p2.Value = id;
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
public bool Edit_web_info(site e, int id)
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("update dbo.site set url=@url,description=@description where dbo.site.id=@id;");
SqlParameter p = new SqlParameter("@url", SqlDbType.VarChar, 50);
p.Value = e.url;
SqlParameter p1 = new SqlParameter("@description", SqlDbType.VarChar, 500);
p1.Value = e.description;
SqlParameter p2 = new SqlParameter("@id", SqlDbType.Int);
p2.Value = id;
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
public bool InsertNew_site_details(int site_id, string tag_name, string tag_value)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("insert into dbo.site_details (site_id,tag_name,tag_value) values(@site_id,@tag_name,@tag_value);");
SqlParameter p = new SqlParameter("@site_id", SqlDbType.Int);
p.Value = site_id;
SqlParameter p1 = new SqlParameter("@tag_name", SqlDbType.VarChar, 200);
p1.Value = tag_name;
SqlParameter p2 = new SqlParameter("@tag_value", SqlDbType.VarChar, 500);
p2.Value = tag_value;
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
public void GetEmailConfigInfo(user_details u)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("select dbo.user_details.e_from,dbo.user_details.e_password,dbo.user_details.port,dbo.user_details.enablessi,dbo.user_details.usedefaultcredentials from dbo.user_details where dbo.user_details.username='******';");
try
{
cmd.Connection.Open();
SqlDataReader dx = cmd.ExecuteReader();
dx.Read();
u.e_from = dx.GetString(0);
u.e_password = dx.GetString(1);
u.port = dx.GetInt32(2);
u.enablessi = dx.GetString(3);
u.usedefaultcredentials = dx.GetString(4);
dx.Close();
cmd.Connection.Close();
}
catch (System.Data.SqlTypes.SqlNullValueException)
{
u.e_from = "";
u.e_password = "";
u.port = 0;
u.enablessi = "";
u.usedefaultcredentials = "";
}
}
public string GetAdminPassword()
{
string i = "";
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("select dbo.user_details.password from dbo.user_details where dbo.user_details.username='******';");
try
{
cmd.Connection.Open();
SqlDataReader d = cmd.ExecuteReader();
d.Read();
i = d.GetString(0);
d.Close();
cmd.Connection.Close();
}
catch (System.InvalidOperationException)
{
MessageBox.Show("Please set your proper database connection link.Go TO Tools->Connection option.", "WSH notift", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Data.SqlTypes.SqlNullValueException)
{
return("empty");
}
catch (System.Data.SqlClient.SqlException)
{
return("empty");
}
return(i);
}
public string GetUrlByUsingId(int id)
{
string i = string.Empty;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("select dbo.site.url from dbo.site where dbo.site.id=@id;");
SqlParameter p = new SqlParameter("@id", SqlDbType.Int);
p.Value = id;
cmd.Parameters.Add(p);
cmd.Connection.Open();
SqlDataReader d = cmd.ExecuteReader();
d.Read();
i = d.GetString(0);
}
catch (System.InvalidOperationException)
{
i = "empty";
}
return(i);
}
public bool Insert(UserInfo obj)
{
var da = new SqlDbDataAccess();
SqlCommand cmd =
da.GetCommand("INSERT INTO [dbo].[Userinfo] ([uname],[pass]) VALUES (@uname, @pass)");
var p1 = new SqlParameter("@uname", SqlDbType.VarChar, 50)
{
Value = obj.Uname
};
var p2 = new SqlParameter("@pass", SqlDbType.VarChar, 50)
{
Value = obj.Pass
};
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Connection.Open();
int val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(val > 0);
}
public bool insertDonationHistory(DONATION_HISTORY obj)
{
int val = 0;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("INSERT INTO [dbo].[DONATION_HISTORY] ([DONOR_ID],[RECIEVER_ID],[DONATION_DATE])" + "VALUES (@DONOR_ID, @RECIEVER_ID, @DONATION_DATE)");
SqlParameter p1 = new SqlParameter("@DONOR_ID", SqlDbType.Int);
p1.Value = obj.DONOR_ID;
SqlParameter p2 = new SqlParameter("@RECIEVER_ID", SqlDbType.Int);
p2.Value = obj.RECIEVER_ID;
SqlParameter p3 = new SqlParameter("@DONATION_DATE", SqlDbType.Date);
p3.Value = obj.DONATION_DATE;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Connection.Open();
val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (Exception ex)
{
}
return(val > 0);
}
public bool UpdateQuan(string price, int id, int quan, String unit)
{
var da = new SqlDbDataAccess();
SqlCommand cmd =
da.GetCommand("update [dbo].[Product] SET quan = @quan,price=@price,unit=@unit WHERE id = @id");
var p1 = new SqlParameter("@price", SqlDbType.VarChar, 50)
{
Value = price
};
var p2 = new SqlParameter("@quan", SqlDbType.Int)
{
Value = quan
};
var p3 = new SqlParameter("@id", SqlDbType.Int)
{
Value = id
};
var p4 = new SqlParameter("@unit", SqlDbType.VarChar, 50)
{
Value = unit
};
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Connection.Open();
int val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(val > 0);
}
public int getNextID()
{
int i = 0;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT MAX(ID) FROM LOGIN_CREDENTIALS");
//List <LOGIN_CREDENTIALS> userLoginInfo = GetDataLogInCredentials(cmd);
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
LOGIN_CREDENTIALS obj = new LOGIN_CREDENTIALS();
using (reader)
{
while (reader.Read())
{
obj.ID = reader.GetInt32(0);
}
}
reader.Close();
cmd.Connection.Close();
i = obj.ID;
i++;
}
catch (Exception ex)
{
}
return(i);
}
//public List<DONATION_HISTORY> GetDonationHistoryList()
//{
// //returns all donation history info
// SqlDbDataAccess da = new SqlDbDataAccess();
// SqlCommand cmd = da.GetCommand("SELECT * FROM DONATION_HISTORY");
// List<DONATION_HISTORY> donationHistoryList = GetDataLogInCredentials(cmd);
// return logInCredentialsList;
//}
public List <LOGIN_CREDENTIALS> getUserLoginInfoById(int id)
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM LOGIN_CREDENTIALS WHERE ID = " + id);
List <LOGIN_CREDENTIALS> userLoginInfo = GetDataLogInCredentials(cmd);
return(userLoginInfo);
}
private void button7_Click(object sender, EventArgs e)
{
//DECLINE
bool flag;
button5.Visible = true;
button7.Visible = true;
button5.Enabled = true;
button7.Enabled = true;
button8.Visible = false;
button8.Enabled = false;
if (u_id > 0)
{
try
{
BloodBankData bbd = new BloodBankData();
flag = bbd.deleteUsers(u_id);
if (flag == true)
{
MessageBox.Show("User Has Been Deleted from USERS Table!", "Delete Successfull", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Error While Deleting User from USERS Table", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
flag = bbd.deleteLoginCredentials(u_id);
if (flag == true)
{
MessageBox.Show("User Has Been Deleted from LOGIN_CREDENTIALS Table!", "Delete Successfull", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Error While Deleting User from LOGIN_CREDENTIALS Table", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
DataSet ds = new DataSet();
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM USERS, LOGIN_CREDENTIALS WHERE LOGIN_CREDENTIALS.STATUS = 'PENDING' AND LOGIN_CREDENTIALS.ID = USERS.ID;");
using (SqlDataAdapter dt = new SqlDataAdapter(cmd))
{
cmd.Connection.Open();
DataTable tbl = new DataTable();
dt.Fill(tbl);
cmd.Connection.Close();
dataGridView1.DataSource = tbl;
}
}
catch (Exception ex)
{
}
}
else
{
MessageBox.Show("Select a valid column first", "Inavlid Selection", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
}
}
public List <TransInfo> GetTransInfo()
{
var da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * from Trans");
List <TransInfo> transList = GetData(cmd);
return(transList);
}
public List <LOGIN_CREDENTIALS> GetLogInCredentialsList()
{
//returns all user login info
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM LOGIN_CRESENTIALS");
List <LOGIN_CREDENTIALS> logInCredentialsList = GetDataLogInCredentials(cmd);
return(logInCredentialsList);
}
public List <ProductInfo> GetProductInfo()
{
var da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * from Product");
List <ProductInfo> productList = GetData(cmd);
return(productList);
}
public DataTable GetProductInfoTable()
{
var da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * from Product");
var dt = new SqlDataAdapter(cmd);
cmd.Connection.Open();
var tbl = new DataTable();
dt.Fill(tbl);
cmd.Connection.Close();
return(tbl);
}
public DataTable ReturnEmailToCc()
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("select dbo.email_cc.e_to As 'To' from dbo.email_cc where dbo.email_cc.username='******';");
DataTable tbl = new DataTable();
using (SqlDataAdapter dt = new SqlDataAdapter(cmd))
{
cmd.Connection.Open();
dt.Fill(tbl);
cmd.Connection.Close();
}
return(tbl);
}
public List <TransInfo> GetTransInfoByName(String pname)
{
var da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM Trans WHERE Pname lik '%@Pname%'");
var p1 = new SqlParameter("@Pname", SqlDbType.VarChar, 50)
{
Value = pname
};
cmd.Parameters.Add(p1);
List <TransInfo> transList = GetData(cmd);
return(transList);
}
public void InsertNoficationInterval(int time)
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("update dbo.user_details set regularintervaltogetupdate=@regularintervaltogetupdate where username='******';");
SqlParameter p = new SqlParameter("@regularintervaltogetupdate", SqlDbType.Int);
p.Value = time;
cmd.Parameters.Add(p);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public void UpdateNotificationThroughEmail(string value)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("update dbo.user_details set notifythroughemail=@notifythroughemail where dbo.user_details.username='******';");
SqlParameter p = new SqlParameter("@notifythroughemail", SqlDbType.VarChar, 4);
p.Value = value;
cmd.Parameters.Add(p);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public DataTable ReturnWebSiteUrl()
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("select dbo.site.url as URL from dbo.site;");
DataTable tbl = new DataTable();
using (SqlDataAdapter dt = new SqlDataAdapter(cmd))
{
cmd.Connection.Open();
dt.Fill(tbl);
cmd.Connection.Close();
}
return(tbl);
}
public List <ProductInfo> GetProductInfoByName(String Pname)
{
var da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("SELECT * FROM Product WHERE Pname =@Pname ");
var p1 = new SqlParameter("@Pname", SqlDbType.VarChar, 50)
{
Value = Pname
};
cmd.Parameters.Add(p1);
List <ProductInfo> productList = GetData(cmd);
return(productList);
}
public bool insertUsersReceiver(USERS obj)
{
int val = 0;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("INSERT INTO [dbo].[USERS] ([ID],[NAME],[IMAGE], [DOB], [GENDER], [BLOOD_GROUP], [ADDRESS], [CELLPHONE], [EMAIL]) VALUES (@ID, @NAME, @IMAGE, @DOB, @GENDER, @BLOOD_GROUP, @ADDRESS, @CELLPHONE, @EMAIL)");
SqlParameter p = new SqlParameter("@ID", SqlDbType.Int);
p.Value = obj.ID;
SqlParameter p1 = new SqlParameter("@NAME", SqlDbType.VarChar, 50);
p1.Value = obj.NAME;
SqlParameter p2 = new SqlParameter("@IMAGE", SqlDbType.Image);
p2.Value = obj.IMAGE;
SqlParameter p3 = new SqlParameter("@DOB", SqlDbType.Date);
p3.Value = obj.DOB;
SqlParameter p4 = new SqlParameter("@GENDER", SqlDbType.VarChar, 6);
p4.Value = obj.GENDER;
SqlParameter p5 = new SqlParameter("@BLOOD_GROUP", SqlDbType.VarChar, 3);
p5.Value = obj.BLOOD_GROUP;
SqlParameter p6 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 50);
p6.Value = obj.ADDRESS;
SqlParameter p7 = new SqlParameter("@CELLPHONE", SqlDbType.VarChar, 11);
p7.Value = obj.CELLPHONE;
SqlParameter p8 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 50);
p8.Value = obj.EMAIL;
cmd.Parameters.Add(p);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
cmd.Parameters.Add(p7);
cmd.Parameters.Add(p8);
cmd.Connection.Open();
val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (Exception ex)
{
}
return(val > 0);
}
public bool DeleteRow(int id)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("delete from dbo.site_details where dbo.site_details.id=@id;");
SqlParameter p = new SqlParameter("@id", SqlDbType.Int);
p.Value = id;
cmd.Parameters.Add(p);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
public bool DeleteTo(string email)
{
SqlDbDataAccess dr = new SqlDbDataAccess();
SqlCommand cmd = dr.GetCommand("delete from dbo.email_cc where dbo.email_cc.e_to=@e_to");
SqlParameter p = new SqlParameter("@e_to", SqlDbType.VarChar, 50);
p.Value = email;
cmd.Parameters.Add(p);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(true);
}
public bool deleteLoginCredentials(int id)
{
int val = 0;
try
{
SqlDbDataAccess da = new SqlDbDataAccess();
SqlCommand cmd = da.GetCommand("DELETE FROM LOGIN_CREDENTIALS WHERE ID = " + id);
cmd.Connection.Open();
val = cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch (Exception ex)
{
}
return(val > 0);
}
