CLASS FILE FOR DATABASE OPERATION

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Net;
using System.Net.Mail;
using System.Web.Configuration;


///

/// Summary description for MyFunction
///

public class EBizFunction
{
public string ConnectionStr = ConfigurationManager.ConnectionStrings["EBizConnectionString"].ToString();
SqlConnection con;
SqlCommand com;
SqlDataReader dr;
SqlDataReader Drr;
SqlDataAdapter da;
DataSet ds;
ArrayList ArrList = new ArrayList();
int Result;
static Random random = new Random();
string FromMail = ConfigurationManager.AppSettings["SenderUsername"].ToString();
string password = ConfigurationManager.AppSettings["SenderPassword"].ToString();
string host = ConfigurationManager.AppSettings["HostIP"].ToString();
//string portNo = ConfigurationManager.AppSettings[""].ToString();
string portNo = "587";
string[] StrArr = null;


public EBizFunction()
{
//
// TODO: Add constructor logic here
//
}

public SqlConnection DBConnect()
{
try
{
con = new SqlConnection(ConnectionStr);
if (con.State != ConnectionState.Open)
{
con.Open();
}
return con;
}
catch (Exception)
{

throw;
}

}

public string ExecScalar(string Qry)
{
try
{
com = new SqlCommand(Qry, DBConnect());
return Convert.ToString(com.ExecuteScalar());
}
catch (Exception)
{
throw;
}
finally
{

con.Close();
}
}

public int ExecQry(string Qry)
{
try
{
com = new SqlCommand(Qry, DBConnect());
return com.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{

con.Close();
}
}

public string[] ExecDataReader(string Qry)
{
try
{

com = new SqlCommand(Qry, DBConnect());
dr = com.ExecuteReader();
return StrArr;
}
catch
{
return StrArr = null;
}
finally
{

con.Close();
}
}

public DataSet FillDataset(string Qry)
{
ds = new DataSet();
ds.Clear();
da = new SqlDataAdapter(Qry, DBConnect());
da.Fill(ds);
return ds;
}

public ArrayList FillArrayList(string Qry)
{
try
{
ArrList.Clear();
FillDataset(Qry);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if(ds.Tables[0].Rows[i][0].ToString()!="") ArrList.Add(ds.Tables[0].Rows[i][0].ToString()); } return ArrList; } catch (Exception) { throw; } finally { ds.Clear(); con.Close(); } }

public ArrayList FillArrayListTwoValues(string Qry)
{ try
{ ArrList.Clear(); FillDataset(Qry); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (ds.Tables[0].Rows[i][0].ToString() != "") { ArrList.Add(ds.Tables[0].Rows[i][0].ToString() + " (" + ds.Tables[0].Rows[i][1].ToString()+ ")"); } } return ArrList; } catch (Exception) { throw; } finally { ds.Clear(); con.Close(); } }

public void GenerateRandomValue()
{ Result= RandomNumber(0, 1000); }

public int RandomNumber(int min, int max)
{ Random random = new Random(); return random.Next(min, max); }

public string RandomString(int size, bool lowerCase)

{ StringBuilder builder = new StringBuilder(); Random random = new Random(); char ch; for (int i = 0; i < size; i++) { ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65))); builder.Append(ch); } if (lowerCase) return builder.ToString().ToLower(); return builder.ToString(); }

public void SendMail(string ToMail, string MailBody ,string SubjecT)

{ System.Configuration.Configuration config = WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath); System.Net.Configuration.MailSettingsSectionGroup settings = (System.Net.Configuration.MailSettingsSectionGroup)config.GetSectionGroup("system.net/mailSettings"); System.Net.NetworkCredential credential = new System.Net.NetworkCredential(settings.Smtp.Network.UserName, settings.Smtp.Network.Password); int i = 0; //Create the SMTP Client SmtpClient client = new SmtpClient(); client.Host = settings.Smtp.Network.Host; client.Credentials = credential; HttpResponse response = HttpContext.Current.Response; MailMessage email = new MailMessage(); email.IsBodyHtml = true; email.From = new MailAddress(FromMail); email.To.Add(ToMail);


//email.To.Add(strToEmail)
email.Subject = SubjecT;
email.IsBodyHtml = true;
email.Body = MailBody;
email.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;

try
{
client.Send(email);
}
catch (Exception exc)
{
response.Write("Send failure: " + exc.ToString());
}
}

public void SendMail(string FrmMail, string ToMail, string MailBody, string SubjecT)
{
System.Configuration.Configuration config = WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath);
System.Net.Configuration.MailSettingsSectionGroup settings = (System.Net.Configuration.MailSettingsSectionGroup)config.GetSectionGroup("system.net/mailSettings");
System.Net.NetworkCredential credential = new System.Net.NetworkCredential(settings.Smtp.Network.UserName, settings.Smtp.Network.Password);

int i = 0;
//Create the SMTP Client
SmtpClient client = new SmtpClient();
client.Host = settings.Smtp.Network.Host;
client.Credentials = credential;
HttpResponse response = HttpContext.Current.Response;
MailMessage email = new MailMessage();



email.IsBodyHtml = true;
email.From = new MailAddress(FrmMail);

email.To.Add(ToMail);

//email.To.Add(strToEmail)
email.Subject = SubjecT;
email.IsBodyHtml = true;
email.Body = MailBody;
email.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;

try
{
client.Send(email);

}
catch (Exception exc)
{
response.Write("Send failure: " + exc.ToString());

}
}

public string SplitUp(string StrToSpilt, char cut,int Indx,char EndCut)
{
string[] Splt = null;
Splt = StrToSpilt.Split(cut);
return Splt[Indx].TrimEnd(EndCut);
}

public string[] SplitUp(string StrToSpilt, char cut)
{
string[] Splt = null;
Splt = StrToSpilt.Split(cut);
return Splt;
}

public int NoOfDays(DateTime FromDate, DateTime ToDate)
{
TimeSpan ts = ToDate.Subtract(FromDate);
int day = ts.Days;
return day+1;
}

public string IndianDateConverter(string Dat)
{
string[] StrArr;
string str;
StrArr = Dat.Split('/');
str = Convert.ToString(StrArr[0]);
StrArr[0] = Convert.ToString(StrArr[1]);
StrArr[1] = str;
Dat = "";
Dat = StrArr[0] + "/";
Dat += StrArr[1] + "/";
Dat += StrArr[2];
return Dat;
}

}