public static Int32 chknewentry1(string path, string type) { OleDbConnection conn = new OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0; Data Source=" + path); conn.Open(); OleDbCommand cmd = new OleDbCommand("select realstat from location ", conn); OleDbDataReader rdr = null; rdr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(rdr); DataSet ds = new DataSet(); DbCommand cmd1 = BaseDb.CreateStrCommand(); cmd1.CommandText = "select realstat from location "; //"select ralstat from location where type='" + type + "'" DataTable dt1 = new DataTable(); dt1 = BaseDb.ExecuteSelect(cmd1); ds.Tables.Add(dt1); ds.Tables.Add(dt); DataView dtview = new DataView(dt1); int i = (dtview.FindRows(dt)).Count(); return(i); //dtview.RowFilter="realstat='"+ }
public static Int32 chknewentry(string path, string type) { OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Persist Security Info=True"); conn.Open(); OleDbCommand cmd = new OleDbCommand("select realstat from location ", conn); OleDbDataReader rdr = null; rdr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(rdr); DataSet ds = new DataSet(); DbCommand cmd1 = BaseDb.CreateStrCommand(); cmd1.CommandText = "select realstat from Location "; DataTable dt1 = new DataTable(); dt1 = BaseDb.ExecuteSelect(cmd1); var query1 = dt.AsEnumerable().Select(a => new { ID = a["realstat"].ToString() }); var query2 = dt1.AsEnumerable().Select(b => new { ID = b["realstat"].ToString() }); var exceptresult = query1.Except(query2); int i = exceptresult.Count(); return(i); }
public static int insertusermaster(usermasterentity objm) { DbCommand dbcom = BaseDb.CreateStrCommand(); try { string abc = objm.Getfrom_dt.ToString(); string def = objm.Getto_dt.ToString(); if (objm.Getto_dt.ToString() != "Null" && objm.Getfrom_dt.ToString() != "Null") { dbcom.CommandText = "insert into usermaster(userid,username,password,start_dt,close_dt,status,created_by,created_dt) values('" + objm.Getuserid + "','" + objm.Getusername + "','" + objm.Getpassword + "','" + Convert.ToDateTime(objm.Getfrom_dt.ToString()).Date + "','" + dateconvert.Convertdt(objm.Getto_dt.ToString()) + "','" + objm.Getstatus + "','" + objm.Getcreated_by + "','" + dateconvert.Convertdt(objm.Getcreated_dt.ToString()) + "')"; } else if (objm.Getto_dt.ToString() == "Null") { dbcom.CommandText = "insert into usermaster(userid,username,password,start_dt,status,created_by,created_dt) values('" + objm.Getuserid + "','" + objm.Getusername + "','" + objm.Getpassword + "','" + dateconvert.Convertdt(objm.Getfrom_dt.ToString()) + "','" + objm.Getstatus + "','" + objm.Getcreated_by + "','" + dateconvert.Convertdt(objm.Getcreated_dt.ToString()) + "')"; } BaseDb.ExecuteNonQuery(dbcom); return(1); } catch (Exception ex) { throw (ex); } }
public static Int32 chkduplicatedata(MetaDataEntity objm, string type) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from avlbldatadet where realstat='" + objm.Getstation + "' and parameterid='" + objm.getparameter + "' and type='" + type + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd)); return(chk); }
public static DataTable GetPassword(string userid) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select password from usermaster where userid='" + userid + "'"; DataTable dt = BaseDb.ExecuteSelect(cmd); return(dt); }
public static Int32 chkduplicatelocation(MetaDataEntity objm) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from Location where realstat='" + objm.Getrealstationname + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString()); return(chk); }
public static Int32 chkduplicateparameter(MetaDataEntity objm) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select count(*) from DATAPARAMETER where parameterid='" + objm.getparameter + "'"; int chk = Convert.ToInt32(BaseDb.ExecuteScalar(cmd)); return(chk); }
public static int setvisibility() { DataAcessLayer dal = new DataAcessLayer(); DbCommand dbcom = BaseDb.CreateStrCommand(); dbcom.CommandText = "select count(*) from Location"; int i = Convert.ToInt32(BaseDb.ExecuteScalar(dbcom)); return(i); }
public static void updatepassword(usermasterentity objm) { try { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "update usermaster set password='******'where userid='" + objm.Getuserid + "'"; BaseDb.ExecuteNonQuery(cmd); } catch (Exception ex) { throw (ex); } }
public static DataTable showdata(string srno) { try { DbCommand dbcom = BaseDb.CreateStrCommand(); dbcom.CommandText = "select userid,username,convert(varchar,start_dt,103) as start_dt,convert(varchar,close_dt,103) as close_dt,case when status = 'A' then 'Active' else 'Inactive' end as status from usermaster where srno=" + Convert.ToInt16(srno); DataTable dt = new DataTable(); dt = BaseDb.ExecuteSelect(dbcom); return(dt); } catch (Exception ex) { throw(ex); } }
public static DataTable GetMemInfo(string name) { DbCommand dbComm = BaseDb.CreateStrCommand(); // dbComm.CommandText = "select * from Member_Details where Name='" + name + "'"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static System.Data.DataTable ShowUserDetailsbycond(string cond) { DbCommand cmd = BaseDb.CreateStrCommand(); cmd.CommandText = "select srno,userid,username,case when status = 'A' then 'Active' else 'Inactive' end as status from usermaster where " + cond + " order by created_dt desc"; System.Data.DataTable dt; try { dt = BaseDb.ExecuteSelect(cmd); return(dt); } catch (Exception ex) { throw ex; } }
public static DataTable GetHduInfoByReg(string RegNo) { DbCommand dbComm = BaseDb.CreateStrCommand(); // dbComm.CommandText = "select * from Hdu_Details where RegNo='" + RegNo + "'"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static DataTable GetPassword(string userid) { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "select password from hdumaster where userid= '" + userid + "'"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static DataTable GetHduDetails() { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "select * from Hdu_Details"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static DataTable GetDamName(string RegionName) { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "SELECT DAM_NAME FROM DAMMASTER WHERE (REGIONID = (SELECT REGIONID FROM REGIONMASTER WHERE (REGION_NAME = '" + RegionName + "')))"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static DataTable GetUserDetails(string userid) { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "select * from usermaster where userid= '" + userid + "'"; DataTable fieldData; //try //{ fieldData = BaseDb.ExecuteSelect(dbComm); //} //catch (Exception exc) //{ //throw exc; //} return(fieldData); }
public static DataTable GetRoleId(string userid) { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "select PASSWORD as password from USERMASTER where USERID='" + userid + "'"; DataTable fieldData; try { fieldData = BaseDb.ExecuteSelect(dbComm); } catch (Exception exc) { throw exc; } return(fieldData); }
public static DataTable GetAccessByDate(DateTime tdate, string uname) { DbCommand dbComm = BaseDb.CreateStrCommand(); dbComm.CommandText = "SELECT * from usermaster where userid='" + uname + "' AND status='A' AND ((close_dt IS NULL) OR (close_dt >= '" + tdate + "'))"; DataTable fieldData; //try //{ fieldData = BaseDb.ExecuteSelect(dbComm); //} //catch (Exception exc) //{ //throw exc; //} return(fieldData); }
public static void updateusermaster(usermasterentity objm) { try { DbCommand dbcom = BaseDb.CreateStrCommand(); if (objm.Getto_dt.ToString() != "Null") { dbcom.CommandText = "update usermaster set close_dt='" + objm.Getto_dt + "',status='" + objm.Getstatus + "',updated_by='" + objm.Getupdated_by + "',updated_dt='" + objm.Getupdated_dt + "' where srno=" + objm.Getsrno; } else if (objm.Getto_dt.ToString() == "Null") { dbcom.CommandText = "update usermaster set status='" + objm.Getstatus + "',updated_by='" + objm.Getupdated_by + "',updated_dt='" + objm.Getupdated_dt + "' where srno=" + objm.Getsrno; } BaseDb.ExecuteNonQuery(dbcom); } catch (Exception ex) { throw (ex); } }
public static DataTable getavailability() { DbCommand cmd = BaseDb.CreatetmpCommand(); cmd.CommandText = "select realstat,datatypeid,parameterid,year from MISNGDATADET order by REALSTAT "; DataTable dt = BaseDb.ExecuteSelect(cmd); dt.Columns.Add("availability"); DataTable dtmsngyr = new DataTable(); DbCommand tmpcmd = BaseDb.CreatetmpCommand(); DbCommand localcmd = BaseDb.CreateStrCommand(); tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid from misngdatadet where cancel=0 "; DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd); dt1.Columns.Add("availability"); if (dt1.Rows.Count > 0) { foreach (DataRow dr in dt1.Rows) { string avlbl = ""; tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (cnt == 0) { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString()); if (cnt1 > 0) { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; DataTable dt2 = BaseDb.ExecuteSelect(localcmd); if (dt2.Rows.Count > 0) { avlbl = dt2.Rows[0]["from_date"] + " - " + dt2.Rows[0]["to_date"]; } } } else { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; DataTable dt3 = BaseDb.ExecuteSelect(tmpcmd); if (dt3.Rows.Count > 0) { avlbl = dt3.Rows[0]["from_date"] + " - " + dt3.Rows[0]["to_date"]; } } dr["availability"] = avlbl.ToString(); //tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; //DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd); //string avlblyear=""; //if (dtmsngavlbl.Rows.Count > 0) //{ // foreach (DataRow msngrow in dtmsngavlbl.Rows) // { // string avlblyear1 = msngrow["year"].ToString(); // avlblyear = avlblyear + avlblyear1 + ","; // } //} //if (avlblyear == "") //{ // dr["msngavlbl"] = ""; //} //else //{ // dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1); //} } } if (dt1.Rows.Count > 0) { foreach (DataRow drow in dt.Rows) { foreach (DataRow dr in dt1.Rows) { if (dr["realstat"].ToString() == drow["realstat"].ToString() && dr["parameterid"].ToString() == drow["parameterid"].ToString()) { drow["availability"] = dr["availability"].ToString(); } } } } return(dt); }
public static DataTable getmisng() { DataTable dtmsngyr = new DataTable(); DbCommand tmpcmd = BaseDb.CreatetmpCommand(); DbCommand localcmd = BaseDb.CreateStrCommand(); tmpcmd.CommandText = "select distinct realstat,datatypeid,parameterid,tiunit,divider,frequency from misngdatadet where cancel=0 "; DataTable dt = BaseDb.ExecuteSelect(tmpcmd); dt.Columns.Add("year"); dt.Columns.Add("availability"); dt.Columns.Add("msngavlbl"); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { string myear = ""; string avlbl = ""; string avlblyear = ""; dtmsngyr.Clear(); string tiunit = dr["tiunit"].ToString(); if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; } else { tmpcmd.CommandText = "select year from misngdatadet where cancel=0 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } dtmsngyr = BaseDb.ExecuteSelect(tmpcmd); if (dtmsngyr.Rows.Count > 0) { foreach (DataRow msngrow in dtmsngyr.Rows) { string myear1 = msngrow["year"].ToString(); myear = myear + myear1 + ", "; } } if (myear == "") { dr["year"] = ""; } else { dr["year"] = myear.Substring(0, myear.Length - 2); } if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { tmpcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString()); if (cnt == 0) { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { localcmd.CommandText = "select count(*) from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(localcmd).ToString()); if (cnt1 > 0) { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { localcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dt1 = BaseDb.ExecuteSelect(localcmd); if (dt1.Rows.Count > 0) { avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"]; } } } else { if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' "; } else { tmpcmd.CommandText = "select convert(varchar,year(from_date)) as from_date,convert(varchar,year(to_date)) as to_date from avlbldatadet where realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dt1 = BaseDb.ExecuteSelect(tmpcmd); if (dt1.Rows.Count > 0) { avlbl = dt1.Rows[0]["from_date"] + " - " + dt1.Rows[0]["to_date"]; } } dr["availability"] = avlbl.ToString(); if (dr["tiunit"].ToString() == "" && dr["divider"].ToString() == "") { tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "'"; } else { tmpcmd.CommandText = "select year from misngdatadet where cancel=1 and realstat='" + dr["realstat"] + "' and parameterid='" + dr["parameterid"] + "' and tiunit=" + dr["tiunit"] + " and divider=" + dr["divider"]; } DataTable dtmsngavlbl = BaseDb.ExecuteSelect(tmpcmd); if (dtmsngavlbl.Rows.Count > 0) { foreach (DataRow msngrow in dtmsngavlbl.Rows) { string avlblyear1 = msngrow["year"].ToString(); avlblyear = avlblyear + avlblyear1 + ","; } } if (avlblyear == "") { dr["msngavlbl"] = ""; } else { dr["msngavlbl"] = avlblyear.Substring(0, avlblyear.Length - 1); } } } // tmpcmd.CommandText = "select distinct realstat,parameterid from misngdatadet where cancel=0 and querytype='update'"; return(dt); }
public static bool AddMemberData(MemberEntity objm) { //DateTime EntryDate, string Org,string Name,string Address,string State,string City,int PinCode,string Email,int Fax,string Sector,string Objective,string purpose,string Nature,string Frequency DbCommand dbComm = BaseDb.CreateStrCommand(); // dbComm.CommandText = "INSERT INTO Member_Details (Entry_Date, Organization, Name, Address, State, City, PinCode, Email_Id, Fax, Sector, Objective, Purpose, Nature, Frequency,Status)VALUES(@Entry_Date, @Organization, @Name, @Address, @State, @City, @PinCode, @Email_Id, @Fax, @Sector, @Objective, @Purpose, @Nature, @Frequency,@Status)";//name of stored procedure DbParameter dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Entry_Date"; dbparam.Value = DateTime.Today.Date; dbparam.DbType = DbType.DateTime; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Organization"; dbparam.Value = objm.GetOrganization; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Name"; dbparam.Value = objm.GetName; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Address"; dbparam.Value = objm.GetAddress; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@State"; dbparam.Value = objm.GetState; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@City"; dbparam.Value = objm.GetCity; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@PinCode"; dbparam.Value = objm.GetPinCode; dbparam.DbType = DbType.Int32; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Email_Id"; dbparam.Value = objm.GetEmailId; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Fax"; dbparam.Value = objm.GetFax; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Sector"; dbparam.Value = objm.GetState; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Objective"; dbparam.Value = objm.GetObjective; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Purpose"; dbparam.Value = objm.GetPurpose; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "@Nature"; dbparam.Value = objm.GetNature; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "Frequency"; dbparam.Value = objm.GetFrequency; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); dbparam = dbComm.CreateParameter(); dbparam.ParameterName = "Status"; dbparam.Value = "R"; dbparam.DbType = DbType.String; dbparam.Direction = ParameterDirection.Input; dbComm.Parameters.Add(dbparam); int result = -1; try { result = BaseDb.ExecuteNonQuery(dbComm); } catch (Exception exc) { throw exc; } finally { } return(result != -1); }