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.