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 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 void clearall()
{
DbCommand tmpdb = BaseDb.CreatetmpCommand();
tmpdb.CommandText = "Delete from avlbldatadet";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from misngdatadet";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from dataparameter";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from datatype";
BaseDb.ExecuteNonQuery(tmpdb);
tmpdb.CommandText = "delete from location";
BaseDb.ExecuteNonQuery(tmpdb);
}
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 int updatedatadescription(MetaDataEntity objm, string parameterid)
{
DbCommand cmd = BaseDb.CreatetmpCommand();
cmd.CommandText = "update dataparameter set parameterdesc='" + objm.getparameter + "' where parameterid='" + parameterid + "'";
int error = -1;
try
{
BaseDb.ExecuteNonQuery(cmd);
error = 0;
return(error);
}
catch (Exception ex)
{
throw (ex);
}
finally
{
}
}
public static int updatedesc(string SaveLocation)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SaveLocation);
conn.Open();
DataTable paradescdt = new DataTable();
OleDbCommand cmd = new OleDbCommand("SELECT UCase(LTrim(RTrim([Data Type]))) AS parameterid,Unit.[Data Type Description] as descr FROM Unit ", conn);
OleDbDataReader rdr = null;
rdr = cmd.ExecuteReader();
paradescdt.Load(rdr);
try
{
DbCommand tmpcmd = BaseDb.CreatetmpCommand();
if (paradescdt.Rows.Count > 0)
{
for (int i = 0; i < paradescdt.Rows.Count; i++)
{
tmpcmd.CommandText = "select count(*) from dataparameter where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'";
int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(tmpcmd).ToString());
if (cnt > 0)
{
tmpcmd.CommandText = "update dataparameter set parameterdesc='" + paradescdt.Rows[i]["descr"].ToString() + "' where upper(ltrim(rtrim(parameterid)))='" + paradescdt.Rows [i]["parameterid"].ToString() + "'";
BaseDb.ExecuteNonQuery(tmpcmd);
}
}
}
conn.Close();
DbCommand cmd1 = BaseDb.CreatetmpCommand();
cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null";
int cnt1 = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString());
return(cnt1);
//{
// rowindex = 2 + index;
// if ((int)rowindex > rg.Row)
// {
// break;
// }
// else
// {
// string abc = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex1]).Value2.ToString();
// string def = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowindex, colindex2]).Value2.ToString();
// index++;
// DbCommand cmd = BaseDb.CreatetmpCommand();
// cmd.CommandText = "select count(*) from dataparameter where parameterid='" + abc + "'";
// int i = Convert.ToInt32(BaseDb.ExecuteScalar(cmd).ToString());
// if (i > 0)
// {
// cmd.CommandText = "update dataparameter set parameterdesc='" + def + "' where parameterid='" + abc + "'";
// BaseDb.ExecuteNonQuery(cmd);
// }
// }
//}
//DbCommand cmd1 = BaseDb.CreatetmpCommand();
//cmd1.CommandText = "select count(*) from dataparameter where parameterdesc is null";
//int cnt = Convert.ToInt32(BaseDb.ExecuteScalar(cmd1).ToString());
//return (cnt);
}
catch (Exception ex)
{
throw (ex);
}
//string strconn = "Provider=Microsoft.Jet.OLEDB.4.0 ;" +
// "Data Source=C:\\units1.xls;" + "Extended Properties= Excel 8.0";
}
public static bool AddHduInfo(string MemberCode, string Organization, string Name, string Address, String State, String City, int Pincode, int ContactNo, int MobileNo, string EmailId, int Fax, string RegNo, string Sector, string Objective, string Purpose, string Nature, string Frequency, DateTime RegDate, int RegFee, DateTime RenewDt, int RenFee, DateTime MemberExpDt, string CreatedBy, DateTime CreatedDate, string UpdatedBy, DateTime UpdatedDate)
{
DbCommand dbComm = BaseDb.CreateCommand();
dbComm.CommandText = "AddHduInformation"; //name of stored procedure
DbParameter dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@MemberCode";
dbparam.Value = MemberCode;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Organization";
dbparam.Value = Organization;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Name";
dbparam.Value = Name;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Address";
dbparam.Value = Address;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@State";
dbparam.Value = State;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@City";
dbparam.Value = City;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@PinCode";
dbparam.Value = Pincode;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@ContactNo";
dbparam.Value = ContactNo;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@MobileNo";
dbparam.Value = MobileNo;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@EmailId";
dbparam.Value = EmailId;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Fax";
dbparam.Value = Fax;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegNo";
dbparam.Value = RegNo;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Sector";
dbparam.Value = Sector;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Objective";
dbparam.Value = Objective;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Purpose";
dbparam.Value = Purpose;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Nature";
dbparam.Value = Nature;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Frequency";
dbparam.Value = Frequency;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegDate";
dbparam.Value = RegDate;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegFee";
dbparam.Value = RegFee;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RenewDt";
dbparam.Value = RenewDt;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RenFee";
dbparam.Value = RenFee;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@MemberExpDt";
dbparam.Value = MemberExpDt;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
//dbparam = dbComm.CreateParameter();
//dbparam.ParameterName = "@EntrDt";
//dbparam.Value = EntryDate;
//dbparam.DbType = DbType.Date;
//dbparam.Direction = ParameterDirection.Input;
//dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@CreatedBy";
dbparam.Value = CreatedBy;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@CreatedDt";
dbparam.Value = CreatedDate;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@UpdatedBy";
dbparam.Value = UpdatedBy;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@UpdatedDt";
dbparam.Value = UpdatedDate;
dbparam.DbType = DbType.Date;
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);
}
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);
}
public static bool UpdateHduData(string Address, string State, string City, int PinCode, int Contactno, int Fax, string RegNo)
{
DbCommand dbComm = BaseDb.CreateCommand();
// dbComm.CommandText = "UpdateHduDetails"; //name of stored procedure
DbParameter dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Address";
dbparam.Value = Address;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@State";
dbparam.Value = State;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@City";
dbparam.Value = City;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@PinCode";
dbparam.Value = PinCode;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@contactNo";
dbparam.Value = Contactno;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Fax";
dbparam.Value = Fax;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegNo";
dbparam.Value = RegNo;
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);
}
//Update Member Details
public static bool UpdateMemberData(int MemberId, DateTime RegDate, int RegFee, DateTime RenewDt, int RenFee, DateTime MemExpDate, string RegNo)
{
DbCommand dbComm = BaseDb.CreateCommand();
dbComm.CommandText = "UpdateMember"; //name of stored procedure
DbParameter dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegDate";
dbparam.Value = RegDate;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegFee";
dbparam.Value = RegFee;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RenewDt";
dbparam.Value = RenewDt;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@MemberExpDt";
dbparam.Value = MemExpDate;
dbparam.DbType = DbType.Date;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RenewFee";
dbparam.Value = RenFee;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@RegNo";
dbparam.Value = RegNo;
dbparam.DbType = DbType.String;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "Member_Id";
dbparam.Value = MemberId;
dbparam.DbType = DbType.Int32;
dbparam.Direction = ParameterDirection.Input;
dbComm.Parameters.Add(dbparam);
dbparam = dbComm.CreateParameter();
dbparam.ParameterName = "@Status";
dbparam.Value = "A";
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);
}
