public static SqlConnection BuildConnecion(String host, String instance, String username, String password, String database)
{
///////////////////////////////
/// Creating Sql connection ///
///////////////////////////////
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = host + "\\" + instance;
builder.UserID = username;
builder.Password = password;
builder.InitialCatalog = database;
SqlConnection cnn = new SqlConnection(builder.ConnectionString);
LogfileHandler.Log("Connectionstring: " + builder.ConnectionString);
return(cnn);
}
public static Models.Person GetContact(SqlConnection cnn, string phoneNumber)
{
List <Models.Person> person = new List <Models.Person>();
SqlCommand queryCommand;
SqlDataReader queryReader;
phoneNumber = phoneNumber.Replace(" ", "");
phoneNumber = phoneNumber.Replace("-", "");
phoneNumber = phoneNumber.Replace("/", "");
string shortNumber = phoneNumber.Substring(5);
//////////////////////////////////////////////
/// Building and Executing the Sql String ///
////////////////////////////////////////////
queryCommand = new SqlCommand(null, cnn);
queryCommand.CommandText = "Select * From dbo.adrKontakte Where TelNrN LIKE @telNr;";
queryCommand.Parameters.Add("@telNr", SqlDbType.VarChar, shortNumber.Length + 2).Value = "%" + shortNumber + "%";
queryCommand.Prepare();
queryReader = queryCommand.ExecuteReader();
//Console.WriteLine("Reading dbo.adrKontakte AdrTel");
LogfileHandler.Log("Reading dbo.adrKontakte AdrTel");
///////////////////////////////////
/// Reading Sql Query Response ///
/////////////////////////////////
while (queryReader.Read())
{
string phoneNumberTemp = queryReader["TelNrN"].ToString();
phoneNumberTemp = phoneNumberTemp.Replace(" ", "");
phoneNumberTemp = phoneNumberTemp.Replace("-", "");
phoneNumberTemp = phoneNumberTemp.Replace("/", "");
if (phoneNumberTemp == phoneNumber)
{
person.Add(new Models.Person {
AdrNrGes = queryReader["AdrNrGes"].ToString(),
Telefonnummer = phoneNumber
});
LogfileHandler.Log(person.ToString());
break;
}
}
////////////////////////////////////
/// Closing Query Return Reader ///
//////////////////////////////////
queryReader.Close();
if (person.Count == 0)
{
return(null);
}
//////////////////////////////////////////////
/// Building and Executing the Sql String ///
////////////////////////////////////////////
queryCommand = new SqlCommand(null, cnn);
queryCommand.CommandText = "Select * From dbo.adrAdressen Where AdrNrGes = @AdrNrGes;";
queryCommand.Parameters.Add("@AdrNrGes", SqlDbType.VarChar, person[0].AdrNrGes.Length).Value = person[0].AdrNrGes;
queryCommand.Prepare();
queryReader = queryCommand.ExecuteReader();
//Console.WriteLine("Reading dbo.adrAdressen ProjAdr");
LogfileHandler.Log("Reading dbo.adrAdressen ProjAdr");
///////////////////////////////////
/// Reading Sql Query Response ///
/////////////////////////////////
while (queryReader.Read())
{
person[0].Nachname = queryReader["Name"].ToString();
person[0].Vorname = queryReader["Vorname"].ToString();
person[0].Strasse = queryReader["Strasse"].ToString();
person[0].OrtID = (int)queryReader["Ort"];
}
//closing reader
queryReader.Close();
//Get all City Values for all Projects from the Database
//////////////////////////////////////////////
/// Building and Executing the Sql String ///
////////////////////////////////////////////
queryCommand = new SqlCommand(null, cnn);
queryCommand.CommandText = "Select * From dbo.adrOrte Where OrtID = @OrtID;";
queryCommand.Parameters.Add("@OrtID", SqlDbType.Int, person[0].OrtID.ToString().Length).Value = person[0].OrtID;
queryCommand.Prepare();
queryReader = queryCommand.ExecuteReader();
//Console.WriteLine("Reading dbo.adrOrte");
LogfileHandler.Log("Reading dbo.adrOrte");
///////////////////////////////////
/// Reading Sql Query Response ///
/////////////////////////////////
while (queryReader.Read())
{
person[0].PLZ = queryReader["PLZ"].ToString();
person[0].Ort = queryReader["Ort"].ToString();
}
////////////////////////////////////
/// Closing Query Return Reader ///
//////////////////////////////////
queryReader.Close();
if (person.Count <= 1)
{
return(person[0]);
}
else
{
return(null);
}
}
private static void Main(string[] args)
{
if (args.Length > 1)
{
if (args[1].Contains("visible"))
{
allowHiding = false;
}
}
hWnd = GetConsoleWindow();
cnn = DatabaseHandler.BuildConnecion(host, instance, username, password, database);
try
{
if (allowHiding)
{
//Thread.Sleep(5000);
//ShowWindow(hWnd, 0);
}
////////////////////////////////////////
/// Try to Connect to the Sql Server ///
////////////////////////////////////////
//Console.WriteLine("Connecting to SQL Server");
cnn.Open();
//Console.WriteLine("!!!Connection Open!!!");
LogfileHandler.Log("Sql Server Connected");
//Console.Write("Press any key to exit... ");
//Console.ReadKey();
if (args.Length > 0 && args[0].Length > 5)
{
Person person = DatabaseHandler.GetContact(cnn, args[0]);
if (person != null)
{
Console.WriteLine(new JObject(
new JProperty("vorname", person.Vorname),
new JProperty("nachname", person.Nachname),
new JProperty("telefonnummer", person.Telefonnummer),
new JProperty("ort", person.Ort),
new JProperty("strasse", person.Strasse),
new JProperty("plz", person.PLZ)
).ToString());
}
}
////////////////////////////////////
/// Closing Sqlserver Connection ///
////////////////////////////////////
cnn.Close();
//Console.WriteLine("!!!Connection Closed!!!");
LogfileHandler.Log("Sql Server disonnected");
}
catch (SqlException e)
{
ShowWindow(hWnd, 1);
TextWriter errorWriter = Console.Error;
errorWriter.WriteLine(e.ToString());
//MessageBox.Show("Bitte lassen sie Dieses Fenster Geöffnet und informieren sie ihren Systemadministrator \n\n\n" + e.ToString(), "KWP -> JAnrufMonitor Schwerwiegender Fehler!!! ", MessageBoxButtons.OK, MessageBoxIcon.Error);
cnn.Close();
LogfileHandler.Log("Sql Error: " + e.ToString());
Environment.Exit(1);
}
catch (ArgumentNullException e)
{
ShowWindow(hWnd, 1);
TextWriter errorWriter = Console.Error;
errorWriter.WriteLine(e.ToString());
//MessageBox.Show("Bitte lassen sie Dieses Fenster Geöffnet und informieren sie ihren Systemadministrator \n\n\n" + e.ToString(), "KWP -> JAnrufMonitor Schwerwiegender Fehler!!! ", MessageBoxButtons.OK, MessageBoxIcon.Error);
cnn.Close();
LogfileHandler.Log("ArgumentNull Error: " + e.ToString());
Environment.Exit(1);
}
catch (NullReferenceException e)
{
ShowWindow(hWnd, 1);
TextWriter errorWriter = Console.Error;
errorWriter.WriteLine(e.ToString());
//MessageBox.Show("Bitte lassen sie Dieses Fenster Geöffnet und informieren sie ihren Systemadministrator \n\n\n" + e.ToString(), "KWP -> JAnrufMonitor Schwerwiegender Fehler!!! ", MessageBoxButtons.OK, MessageBoxIcon.Error);
cnn.Close();
LogfileHandler.Log("NullReference Error: " + e.ToString());
Environment.Exit(1);
}
catch (Exception e)
{
ShowWindow(hWnd, 1);
TextWriter errorWriter = Console.Error;
errorWriter.WriteLine(e.ToString());
//MessageBox.Show("Bitte lassen sie Dieses Fenster Geöffnet und informieren sie ihren Systemadministrator \n\n\n" + e.ToString(), "KWP -> JAnrufMonitor Schwerwiegender Fehler!!! ", MessageBoxButtons.OK, MessageBoxIcon.Error);
cnn.Close();
LogfileHandler.Log("Error: " + e.ToString());
Environment.Exit(1);
}
}