CHAPTER 7 -:Coding

Data Context Class

using System;

using System.Collections.Generic;

using System.Web;

using System.Data;

using System.Data.SqlClient;

public class DataContext

{

SqlConnection con = new SqlConnection();

SqlCommand cmd = new SqlCommand();

SqlDataAdapter da = new SqlDataAdapter();

//SqlDataReader dr = new SqlDataReader();

public void Openconnection()

{

if (con.State == ConnectionState.Closed)

{

try

{

string constring = "Data Source=.; Initial Catalog=onlineauction; Integrated Security=True";

con.ConnectionString = constring;

con.Open();

cmd.Connection = con;

}

catch (Exception ex)

{

ex.Message.ToString();

}

}

}

public void CloseConnection()

{

if (con.State == ConnectionState.Open)

{

con.Close();

}

}

public DataTable getdataTable(string Query)

{

DataTable dt = new DataTable();

Openconnection();

cmd.CommandType = CommandType.Text;

cmd.CommandText = Query;

da.SelectCommand = cmd;

da.Fill(dt);

CloseConnection();

return dt;

}

public int executeNonquery(string Query)

{

Openconnection();

cmd.CommandType = CommandType.Text;

cmd.CommandText = Query;

int i = cmd.ExecuteNonQuery();

CloseConnection()

Business Logic Class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Data;

using System.Web;

/// <summary>

/// Summary description for BusinessLogic

/// </summary>

public class BusinessLogic

{

DataContext obj_datacontext = new DataContext();

public bool submitdetails(string username, string password, string name, string Email, long mobile, string accountno, string bankname, string usertype,string address,string ques,string ans)

{

string query="insert into tbl_usermaster (Username,Password,question,ans) values('"+username+"','"+password+"','"+ques+"','"+ans+"')";

int i = obj_datacontext.executeNonquery(query);

string query1 = "select max(i_autoid) as i_autoid from tbl_usermaster ";

DataTable dt = obj_datacontext.getdataTable(query1);

int refid = Convert.ToInt32(dt.Rows[0]["i_autoid"].ToString());

string query2 = "INSERT INTO tbl_userdetails (ref_id, user_type, address, name, mobile_no, Email_id, Account_no, bankname) VALUES ('"+refid+"', '"+usertype+"', '"+address+"', '"+name+"', '"+mobile+"', '"+Email+"', '"+accountno+"', '"+bankname+"')";

int roweffected = obj_datacontext.executeNonquery(query2);

if (i > 0 && roweffected > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getuserdetails(int userid)

{

string query = "SELECT ref_id, user_type, address, name, mobile_no, Email_id, Account_no, bankname FROM tbl_userdetails where ref_id = '" + userid + "'";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

public bool updateuserdetails(int uid, string accountno, string name, string Email, long mobile, string bankname, string address)

{

string query = "UPDATE tbl_userdetails SET address ='"+address+"', name ='"+name+"', mobile_no ='"+mobile+"', Email_id ='"+Email+"', Account_no ='"+accountno+"', bankname ='"+bankname+"' WHERE (ref_id = '"+uid+"')";

int i = obj_datacontext.executeNonquery(query);

if (i > 0)

{

return true;

}

else

{

return false;

}

}

public bool inserttypedata(string typeimagepath,string typename)

{

string query = "insert into tbl_typemaster (typeimage,typename) values('"+typeimagepath+"','"+typename+"')";

int roweffected = obj_datacontext.executeNonquery(query);

if (roweffected > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getitemtypes()

{

string query = "select * from tbl_typemaster";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

public bool insertitemsdetails(int i_refid,string itemimagepath, string itemname)

{

string query = "insert into tbl_itemsdetailsmaster(i_refid,itemname,itemimage) values('"+i_refid+"','"+itemname+"','"+itemimagepath+"')";

int i = obj_datacontext.executeNonquery(query);

if (i > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getitemsdetails(int typeid)

{

string query = "SELECT tbl_itemsdetailsmaster.itemname, tbl_itemsdetailsmaster.itemimage, tbl_typemaster.i_autoid FROM tbl_itemsdetailsmaster INNER JOIN tbl_typemaster ON tbl_itemsdetailsmaster.i_refid = tbl_typemaster.i_autoid WHERE (tbl_itemsdetailsmaster.i_refid = '" + typeid + "')";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

public bool addauctiondetails(DateTime effectedfrom, DateTime effectedto, string description, int userid, int itemid, int starting_amount)

{

string query = "INSERT INTO tbl_auctionmaster (userid, itemid, startingdate, endingdate, Description,starting_amount) VALUES ('" + userid + "','" + itemid + "', '" + effectedfrom + "','" + effectedto + "', '" + description + "','" + starting_amount + "')";

int i = obj_datacontext.executeNonquery(query);

if (i > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getauctiondetails(int userid)

{

string query = "SELECT tbl_auctionmaster.itemid, tbl_itemsdetailsmaster.itemname, tbl_itemsdetailsmaster.itemimage, tbl_auctionmaster.starting_amount, tbl_auctionmaster.startingdate, tbl_biddetails.i_bidprice FROM tbl_bidmaster INNER JOIN tbl_biddetails ON tbl_bidmaster.i_autoid = tbl_biddetails.i_refid INNER JOIN tbl_auctionmaster ON tbl_bidmaster.i_auctionid = tbl_auctionmaster.i_autoid INNER JOIN tbl_itemsdetailsmaster ON tbl_auctionmaster.itemid = tbl_itemsdetailsmaster.itemname WHERE (tbl_biddetails.i_bidprice = MAX(tbl_biddetails.i_bidprice) AND (tbl_auctionmaster.userid = '"+userid+"'))";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

public bool submitContactForm(string name, string email, string description, string mobileno)

{

string query = "INSERT INTO tbl_contactDetails (Name, Mobileno, Msg, Email) VALUES ('" + name + "', '" + mobileno + "', '" + description + "', '" + email + "')";

int roweffected = obj_datacontext.executeNonquery(query);

if (roweffected > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getContactdetails()

{

string query = "select * from tbl_contactDetails";

DataTable dt = obj_datacontext.getdataTable(query);

if (dt.Rows.Count > 0)

{

return dt;

}

else

{

dt = null;

return dt;

}

}

public DataTable getAllauctionDetails()

{

string query = "SELECT tbl_auctionmaster.itemid, tbl_itemsdetailsmaster.itemname, tbl_itemsdetailsmaster.itemimage, tbl_auctionmaster.startingdate, tbl_auctionmaster.endingdate, tbl_auctionmaster.Description, tbl_auctionmaster.starting_amount, tbl_auctionmaster.userid FROM tbl_auctionmaster INNER JOIN tbl_itemsdetailsmaster ON tbl_auctionmaster.itemid = tbl_itemsdetailsmaster.i_autoid";

DataTable dt = obj_datacontext.getdataTable(query);

if (dt.Rows.Count > 0)

{

return dt;

}

else

{

dt = null;

return dt;

}

}

public bool sendfeedback(string name, string emailid, string subject, string feedback)

{

string query = "INSERT INTO tbl_feedbackdetails (name, emailid, subject, feedback) VALUES ('"+name+"', '"+emailid+"', '"+subject+"', '"+feedback+"')";

int i = obj_datacontext.executeNonquery(query);

if (i > 0)

{

return true;

}

else

{

return false;

}

}

public DataTable getFeedbackdetails()

{

string query = "select * from tbl_feedbackdetails";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

public DataTable recoverpassword(string username, string securityques, string ans)

{

string query = "select * from tbl_usermaster where username = '"+username+"'and Question='"+securityques+"'";

DataTable dt = obj_datacontext.getdataTable(query);

return dt;

}

}