Calling a function inside a while loop

[ace333]

I am calling a method inside a for loop and the problem is that the method calls another method. There is nothing wrong with the method if its called as a one off, it works fine but inside the loop the page just hangs.

The loop is going to iterate about 1300 times and the method called calls another method that queries a db etc... I'm not sure should i paste the code as well....
Any ideas guys and gals

 

[jbenson001]

yes paste code so we can see what you are doing..

 

[ace333]

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Derivatives.ops.lib;
using alterSecurityUsers;



namespace hrMimic
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lblWarning;
protected System.Web.UI.WebControls.TextBox txtSurname;
protected System.Web.UI.WebControls.Label lblSurname;

protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Button btnSearch;
protected System.Web.UI.WebControls.Label lblDetails;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Button btnSearchOmni;
protected System.Web.UI.WebControls.TextBox txtOmni;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label lblWarn;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here



if(txtSurname.Text!=""){lblWarn.Visible=false;}
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnSearch.Click += new System.EventHandler(this.btnSearch_Click);
this.btnSearchOmni.Click += new System.EventHandler(this.btnSearchOmni_Click);
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void btnSearch_Click(object sender, System.EventArgs e)
{

if(txtSurname.Text==""){lblWarn.Visible=true;}

else
{
DataGrid1.DataSource =HRData.GetInfoBySurname(txtSurname.Text);
DataGrid1.DataBind();
}
//Response.Write(HRData.getCon());

}
//End of method.

private void btnSearchOmni_Click(object sender, System.EventArgs e)
{
//lblDetails.Text = HRData.GetInfoByOmniAc(txtOmni.Text);
lblDetails.Text = HRData.GetEmailByOmniAc(txtOmni.Text);


}

private void Button1_Click(object sender, System.EventArgs e)
{
Label2.Text=WriteSQL.test();
//this is where i call the method
}
//End of method.





}
//End of class.

}
//End of namespace.

THE ABOVE CALLS THE FOLLOWING............


using System;
using System.Text;
using MCE.Tracing;
using System.Data.SqlClient;
using System.Data;
using System.Web;
using System.Text.RegularExpressions;
using System.Web.Caching;
using System.Configuration;
using MCE.Encryptor;
using Derivatives.ops.lib;

namespace alterSecurityUsers
{
/// <summary>
/// Summary description for Class3.
/// </summary>

public class WriteSQL
{
/// <summary>
/// The main entry point for the application.
/// </summary>
///
public WriteSQL(){}

public static TraceHelper TRACE = new TraceHelper("AppTrace", "");

//[STAThread]
//static void Main(string[] args)
public static string test()
{
string er="nnn";

///**

try
{
//string strConn = SecurityConfig.GetVersionSetting("DacConnection");
//Initial Catalog=Dacari;Data Source=SLON11531;Integrated Security=SSPI;
//Initial Catalog=DACari;Data Source=slon12d11012;User ID=Dacari_Web;Password=monday
string strConn="Initial Catalog=DACari;Data Source=slon12d11012;User ID=Dacari_Web;Password=monday";

TRACE.Info("Starting to create backpopulation DB script");
TRACE.Info("The connection string is " + strConn);

SqlConnection objConnection = new SqlConnection(strConn);

string strSQL;


strSQL = "GetSecurityUsers";

//strSQL = "select * from emp";

//strSQL = "select * from SecurityUsers";
string str = "";


//str = strEmail;

SqlCommand objSqlCommand1 = new SqlCommand(strSQL, objConnection);
objSqlCommand1.CommandType = CommandType.Text;
objConnection.Open();
SqlDataReader dr;
dr = objSqlCommand1.ExecuteReader();
int i = 0;
while(dr.Read())
{
i++;
string strUserID = dr["UserID"].ToString();
//str=str+"<br/>"+strUserID;
//string strFullName = getFullName(strUserID);
//string strEmail = HRData.getCon();
//str = strEmail;
//string strEmail = HRData.getCon();//= "noel.nicholson@csfb.com";


string strEmail = HRData.GetEmailByOmniAc(strUserID);

///**
if(strEmail != "")
{
if(strEmail.Substring(0,5).ToString() == "ERROR")
{
TRACE.Error(strEmail);
return "";
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE SecurityUsers SET EmailAddress = '");
sb.Append(strEmail);
sb.Append("' WHERE Userid = '");
sb.Append(strUserID);
sb.Append("'");

Console.WriteLine(sb.ToString());
Console.WriteLine("");

str = str + sb.ToString()+"<br/>";
}
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE SecurityUsers SET EmailAddress = '' WHERE Userid = '");
sb.Append(strUserID);
sb.Append("'");

Console.WriteLine(sb.ToString());
Console.WriteLine("");

str = str + sb.ToString()+"<br/>";
}
//**/
}
//End of while loop

//return "success";
//return "Success !";
return str;
}
catch(Exception ex)
{
er = ex.ToString();
}
//**/
return er;
}
//End of method.


public string test1()
{
return "xxx";
}


}

}

WHICH CALLS THE FOLLOWING..............

using System;
using System.Data;
using Microsoft.Data.Odbc;
using System.Configuration;
using System.Text;
using System.Text.RegularExpressions;
using MCE.Encryptor;
using security;
using log4net;
using System.ComponentModel;
using System.IO;


namespace Derivatives.ops.lib
{
/// <summary>
/// Summary description for Class1.
/// </summary>
public class HRData
{
public HRData()
{
}

static public string getCon()
{
string sConn=ConfigurationSettings.AppSettings.Get("TelConnectionString");
string sPwd=Security.Decrypt(ConfigurationSettings.AppSettings.Get("TelPassword"));
sConn="sdsdmmmmmmmmmmmmmmmmmmmmmmmmmmm";
return sConn;
}

/// <summary>
/// Method to connect to the database and execute the SQL string.
/// </summary>
/// <param name="sSQL">The SQL to run.</param>
/// <returns>The results of the SQL.</returns>
static public DataSet getTelData(string sSQL)
{
string sConn="DRIVER={MERANT 3.60 32-BIT Sybase};UID=bauweb;SRVR=dcsntp01;database=dbntpa;";//ConfigurationSettings.AppSettings.Get("TelConnectionString");
string sPwd="bauweb";//Security.Decrypt(ConfigurationSettings.AppSettings.Get("TelPassword"));
sConn=sConn+"PWD="+sPwd;


DataSet dsRet = new DataSet();
OdbcConnection objConn = new OdbcConnection(sConn);

try
{
objConn.Open();

OdbcDataAdapter objDA = new OdbcDataAdapter(sSQL, objConn);
objDA.Fill(dsRet);
}
catch (OdbcException ex)
{
//throw ex;
string Error = ex.ToString();

FileInfo t2 = new FileInfo("C:\\Temp\\Collin2.txt");
StreamWriter Tex2 =t2.CreateText();
Tex2.WriteLine("Collin has launced another article");
Tex2.Write(Tex2.NewLine);
Tex2.WriteLine("csharpfriends is the new url for c-sharp");
Tex2.Write(Tex2.NewLine);
Tex2.WriteLine(sConn);
Tex2.Write(Tex2.NewLine);
Tex2.WriteLine(sSQL);
Tex2.Close();

}
finally
{
objConn.Close();
objConn.Dispose();
}

return dsRet;

}

/// <summary>
/// Method to return a list of users from the database based on a surname.
/// Used for all surname lookups.
/// </summary>
/// <param name="sUser">Surname to find.</param>
/// <returns>User details that match the given surname.</returns>
static public DataSet GetInfoBySurname(string sUser)
{
DataSet ds = new DataSet();
string sSearch=sUser;

if (sUser.IndexOf("'") > 0)
sSearch = ConvertQuote(sUser);

StringBuilder sbSQL = new StringBuilder("SELECT e.cv_em_last_n as 'Surname', ");
sbSQL.Append("e.cv_em_first_n as 'FirstName', e.cv_em_title_c as 'Title', ");
sbSQL.Append("e.cv_email_a as 'Email', l.cv_ofce_n as 'Office' ");
sbSQL.Append("FROM csv_tcv_em_ref e, csv_tofce_ref l WHERE lower(cv_em_last_n) LIKE '");
sbSQL.Append(sSearch.ToLower() + "%' ");
sbSQL.Append("and e.cv_ofce_id_c = l.cv_ofce_id_c ");
sbSQL.Append("ORDER By cv_em_last_n, cv_em_first_n");

ds = getTelData(sbSQL.ToString());

return ds;

}

/// <summary>
/// Method to return the users office
/// </summary>
/// <param name="sNTLogin">The NT Login of the user to return info for</param>
/// <returns>The office name</returns>
static public string GetUserOffice(string sNTLogin)
{
string sReturn="";

StringBuilder sbSQL = new StringBuilder("SELECT l.cv_ofce_n as 'Office' ");
sbSQL.Append("FROM csv_tcv_em_ref e, csv_tofce_ref l ");
sbSQL.Append("WHERE nt_login_id_c = '");
sbSQL.Append(sNTLogin.ToLower() + "'");
sbSQL.Append("and e.cv_ofce_id_c = l.cv_ofce_id_c ");

DataSet ds = getTelData(sbSQL.ToString());

if (ds.Tables.Count > 0)
if (ds.Tables[0].Rows.Count > 0)
sReturn = ds.Tables[0].Rows[0]["Office"] as string;

return sReturn.TrimEnd();

}

/// <summary>
/// Method to remove the quote and replace it with a char value so the SQL statement will work
/// </summary>
/// <param name="sName">The string to replace the quote in</param>
/// <returns>The newly formatted string</returns>
static public string ConvertQuote(string sName)
{
string sReturn="";

int nIndex = sName.IndexOf("'");

if (nIndex != 0)
{
string sFirstLetter = sName.Substring(0,nIndex);
string sRest = sName.Substring(nIndex+1);

sReturn = sFirstLetter + "' +" + " char(39) +" + "'" + sRest;

}

return sReturn;

}

/// <summary>
/// Method to return one record from the user database.
/// </summary>
/// <param name="sFullName">The full name of the user to find.</param>
/// <returns>The user details.</returns>
static public DataSet GetUserData(string sFullName)
{
string sLastName=GetPart("Lastname",sFullName);
string sEmail=GetPart("Email",sFullName);

//Get rid of single quotes as it will break the SQL
if (sLastName.IndexOf("'",0) > 0)
sLastName = ConvertQuote(sLastName);

if (sEmail.IndexOf("'",0) > 0)
sEmail = ConvertQuote(sEmail);

DataSet ds = new DataSet();

StringBuilder sbSQL = new StringBuilder("SELECT * FROM csv_tcv_em_ref WHERE ");
sbSQL.Append(" cv_em_last_n = '" + sLastName + "' ");
sbSQL.Append("and lower(cv_email_a) = '" + sEmail + "'");

ds = getTelData(sbSQL.ToString());

return ds;

}

/// <summary>
/// Method to return part of the user details, i.e. either the user lastname or their email address.
/// </summary>
/// <param name="sPart">"Lastname" or "Email"</param>
/// <param name="sFullName">The full name to search</param>
/// <returns>Either the lastname or email address.</returns>
static public string GetPart(string sPart, string sFullName)
{
string sReturn="";
Regex regex;

switch (sPart)
{
case "Lastname":
regex = new Regex(",");
if ( regex.IsMatch(sFullName) )
{
string[] parts = regex.Split(sFullName);
foreach (string part in parts)
{
sReturn=part;
break;
}
}
break;

case "Email":
//get rid of the surname
string sTemp="";
regex = new Regex(",");
if ( regex.IsMatch(sFullName) )
{
string[] parts = regex.Split(sFullName);
foreach (string part in parts)
{
sTemp=part;
}
}

//now find email address
int nAt = sTemp.IndexOf("'");
sReturn = sTemp.Substring(nAt);
sReturn = sReturn.Remove(0,1);
sReturn = sReturn.Remove(sReturn.Length-1,1);
break;
}

return sReturn;

}

/// <summary>
/// Method to return the Omni Login of the user.
/// </summary>
/// <param name="sFullName">The full name to search for.</param>
/// <returns>The Email address.</returns>
static public string GetNTLogin(string sFullName)
{
string sReturnLogin="";
string sSearch=sFullName;

try
{

//Get the email address and omni logon for this user
DataSet ds = HRData.GetUserData(sSearch);

if ( ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
DataRow[] rows = ds.Tables[0].Select();

foreach (DataRow dr in rows)
{
if (dr["nt_login_id_c"].ToString().Length > 0)
sReturnLogin=dr["nt_login_id_c"].ToString().Trim();
}

}

}

}
catch (Exception ex)
{
throw ex;
}

return sReturnLogin;
}

/// <summary>
/// Returns the full name of a user, based on their Omni login.
/// </summary>
/// <param name="sUser">The omni login.</param>
/// <returns>The full name.</returns>

static public string GetInfoByOmniAc(string sUser)
{
StringBuilder sb = new StringBuilder();

StringBuilder sSQL = new StringBuilder("SELECT cv_em_last_n as 'Surname', cv_em_first_n as 'Firstname', ");
sSQL.Append("cv_email_a as 'Email' ");
sSQL.Append("FROM csv_tcv_em_ref WHERE lower(nt_login_id_c) = '" + sUser.ToLower() + "'");

///**
DataSet ds = getTelData(sSQL.ToString());

if (ds.Tables.Count > 0)
{
if ( ds.Tables[0].Rows.Count > 0 )
{
DataRow[] rows = ds.Tables[0].Select();

foreach (DataRow dr in rows)
{
if (dr["Surname"].ToString().Length > 0)
{
sb.Append(dr["Surname"].ToString().Trim());
sb.Append(", ");
}
if (dr["Firstname"].ToString().Length > 0)
{
sb.Append(dr["Firstname"].ToString().Trim());
sb.Append(" '");
}
if (dr["Email"].ToString().Length > 0)
{
sb.Append(dr["Email"].ToString().Trim());
sb.Append("'");
}
}
}
}
//**/
return sb.ToString();
}
//End of method.

static public string GetEmailByOmniAc(string sUser)
{


StringBuilder sb = new StringBuilder();

StringBuilder sSQL = new StringBuilder("SELECT cv_email_a as 'Email' ");
//sSQL.Append("cv_email_a as 'Email' ");
sSQL.Append("FROM csv_tcv_em_ref WHERE lower(nt_login_id_c) = '" + sUser.ToLower() + "'");

try
{
DataSet ds = getTelData(sSQL.ToString());

if (ds.Tables.Count > 0)
{
if ( ds.Tables[0].Rows.Count > 0 )
{
DataRow[] rows = ds.Tables[0].Select();

foreach (DataRow dr in rows)
{
//if (dr["Surname"].ToString().Length > 0)
//{
// sb.Append(dr["Surname"].ToString().Trim());
// sb.Append(", ");
//}
//if (dr["Firstname"].ToString().Length > 0)
//{
// sb.Append(dr["Firstname"].ToString().Trim());
// sb.Append(" '");
//}
if (dr["Email"].ToString().Length > 0)
{
sb.Append(dr["Email"].ToString().Trim());
//sb.Append("'");
}
}
}
}
}
catch(Exception ex)
{
sb.Append("ERROR "+ex.Message);


FileInfo t3 = new FileInfo("C:\\Temp\\Collin3.txt");
StreamWriter Tex3 =t3.CreateText();
Tex3.WriteLine("Collin has launced another article");
Tex3.Write(Tex3.NewLine);
Tex3.WriteLine("csharpfriends is the new url for c-sharp");
Tex3.Write(Tex3.NewLine);

Tex3.Write(Tex3.NewLine);
Tex3.WriteLine(ex.ToString());
Tex3.Close();

}
return sb.ToString();
}
//End of method.


}
//End of class
}
//End of namespace



In the test method u see a loop which calls the method

string strEmail = HRData.GetEmailByOmniAc(strUserID);

this works if its called once independently, but it needs to be called consecutively.

 

[ace333]

this is so weird, after about 10-12 minutes the application has given back the result I wanted I think, surely it should be faster !!!

 

[ace333]

Try to run it again and its taking ages, now i'm really confused, its been hanging for about 15 minutes