629 lines
22 KiB
C#
629 lines
22 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using System.Data.SqlClient;
|
|
using System.Drawing;
|
|
using System.Globalization;
|
|
using Microsoft.Data.SqlClient;
|
|
|
|
namespace De.Lambertz.Essentials
|
|
{
|
|
public class DBTools
|
|
{
|
|
public static int STANDORT_UNBEKANNT = 0;
|
|
public static int STANDORT_AACHEN = 1;
|
|
public static int STANDORT_WUERSELEN = 1;
|
|
public static int STANDORT_NEU_ULM = 2;
|
|
public static int STANDORT_LADBERGEN = 3;
|
|
public static string IP_MSSQLSERVER = "SQL01AC.intra.lambertz.de";
|
|
private static CultureInfo germanCultureInfo = new CultureInfo("de-DE");
|
|
|
|
public static CultureInfo GermanCultureInfo
|
|
{
|
|
get { return DBTools.germanCultureInfo; }
|
|
}
|
|
|
|
public static String Txt(String text)
|
|
{
|
|
StringBuilder result = new StringBuilder("'");
|
|
if (!String.IsNullOrEmpty(text))
|
|
{
|
|
//Böse Zeichen escapen...
|
|
text = text.Replace("'", "''");
|
|
result.Append(text.Trim());
|
|
}
|
|
result.Append("'");
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static Dictionary<String, int> GetFelderIndex(SqlDataReader reader)
|
|
{
|
|
Dictionary<String, int> result = new Dictionary<string, int>();
|
|
|
|
for (int i = 0; i < reader.FieldCount; i++)
|
|
{
|
|
String name = reader.GetName(i);
|
|
result.Add(name, i);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static String TxtRemoveSpecialCharsForSQL(String text)
|
|
{
|
|
StringBuilder result = new StringBuilder("'");
|
|
if (!String.IsNullOrEmpty(text))
|
|
{
|
|
//Böse Zeichen escapen...
|
|
text = text.Replace("'", "''");
|
|
|
|
StringBuilder temp = new StringBuilder();
|
|
char c;
|
|
for (int i = 0; i < text.Length; i++)
|
|
{
|
|
c = text[i];
|
|
if (c < 33 || c > 125)
|
|
{
|
|
c = '_';
|
|
|
|
}
|
|
temp.Append(c);
|
|
}
|
|
|
|
result.Append(temp.ToString().Trim());
|
|
}
|
|
result.Append("'");
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static String Dat(DateTime date)
|
|
{
|
|
StringBuilder result = new StringBuilder("'");
|
|
//result.Append(date.ToString("yyyy-MM-dd"));
|
|
result.Append(date.ToString("dd-MM-yyyy"));
|
|
result.Append("'");
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static String DatTime(DateTime date)
|
|
{
|
|
StringBuilder result = new StringBuilder("'");
|
|
//result.Append(date.ToString("yyyy-MM-dd"));
|
|
result.Append(date.ToString("dd-MM-yyyy hh:mm:ss"));
|
|
result.Append("'");
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static String Dbl(double zahl)
|
|
{
|
|
StringBuilder result = new StringBuilder();
|
|
string z = zahl.ToString();
|
|
z = z.Replace(',', '.');
|
|
result.Append(z);
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static String Dbl(string text)
|
|
{
|
|
double zahl = 0;
|
|
try
|
|
{
|
|
zahl = Convert.ToDouble(text);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
//Nothing
|
|
}
|
|
StringBuilder result = new StringBuilder();
|
|
string z = zahl.ToString();
|
|
z = z.Replace(',', '.');
|
|
result.Append(z);
|
|
|
|
return result.ToString();
|
|
}
|
|
|
|
public static string DBGetString(SqlDataReader reader, int index, String nullValue = null)
|
|
{
|
|
string result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetString(index).Trim();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static string DBGetStringUnTrimmed(SqlDataReader reader, int index, String nullValue = null)
|
|
{
|
|
string result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetString(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static char DBGetChar(SqlDataReader reader, int index)
|
|
{
|
|
char result = ' ';
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
String temp = reader.GetString(index);
|
|
result = temp.ToCharArray()[0];
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static Double DBGetDouble(SqlDataReader reader, int index, Double nullValue = 0)
|
|
{
|
|
Double result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = Convert.ToDouble(reader.GetDecimal(index));
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static Double DBGetDezimal(SqlDataReader reader, int index, Double nullValue = 0)
|
|
{
|
|
Double result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = Convert.ToDouble(reader.GetDecimal(index));
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static Decimal DBGetRealDezimal(SqlDataReader reader, int index, Decimal nullValue = 0)
|
|
{
|
|
Decimal result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDecimal(index);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static Double DBGetRealDouble(SqlDataReader reader, int index, Double nullValue = 0)
|
|
{
|
|
Double result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDouble(index);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
public static int DBGetInt(SqlDataReader reader, int index, int nullValue = 0)
|
|
{
|
|
int result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetInt32(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static int DBGetShort(SqlDataReader reader, int index, int nullValue = 0)
|
|
{
|
|
int result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = (int)reader.GetInt16(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static int DBGetByteAsInt(SqlDataReader reader, int index)
|
|
{
|
|
int result = 0;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = (int)reader.GetByte(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static long DBGetLong(SqlDataReader reader, int index, long nullValue = 0)
|
|
{
|
|
long result = nullValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetInt64(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static DateTime DBGetDate(SqlDataReader reader, int index)
|
|
{
|
|
//DateTime result = new DateTime(1900,1,1);
|
|
DateTime result = DateTime.MinValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDateTime(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static Object DBGetDateNullIfMin(SqlDataReader reader, int index)
|
|
{
|
|
//DateTime result = new DateTime(1900,1,1);
|
|
Object result = null;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDateTime(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static String DBGetDateEmptyIfMin(SqlDataReader reader, int index)
|
|
{
|
|
//DateTime result = new DateTime(1900,1,1);
|
|
String result = "";
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDateTime(index).ToShortDateString();
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static string DBGetDate(SqlDataReader reader, int index, int dateFormat)
|
|
{
|
|
//DateTime result = new DateTime(1900,1,1);
|
|
string result = "";
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
DateTime dt = reader.GetDateTime(index);
|
|
|
|
switch (dateFormat)
|
|
{
|
|
case 1:
|
|
result = dt.ToString("d", germanCultureInfo);
|
|
break;
|
|
default:
|
|
result = dt.ToString("D", germanCultureInfo);
|
|
break;
|
|
}
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static DateTime DBGetTimeStamp(SqlDataReader reader, int index)
|
|
{
|
|
DateTime result = DateTime.MinValue;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetDateTime(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static bool DBGetBool(SqlDataReader reader, int index)
|
|
{
|
|
bool result = false;
|
|
|
|
if (!reader.IsDBNull(index))
|
|
{
|
|
result = reader.GetBoolean(index);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static DateTime MakeDate(string YYYYMMDD)
|
|
{
|
|
DateTime result = new DateTime();
|
|
try
|
|
{
|
|
int year = Convert.ToInt32(YYYYMMDD.Substring(0, 4));
|
|
int month = Convert.ToInt32(YYYYMMDD.Substring(4, 2));
|
|
int day = Convert.ToInt32(YYYYMMDD.Substring(6, 2));
|
|
result = new DateTime(year, month, day);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
result = DateTime.MinValue;
|
|
//Nothing
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringAnmeldung()
|
|
{
|
|
//string result = "Data Source=APP4_AACHEN\\BKUPEXEC;Initial Catalog=AGENT2;User Id=LA;Password=la;";
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=AGENT2;User Id=LA;Password=la;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=AGENT2;User Id=LA;Password=la;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringAS400()
|
|
{
|
|
string result = "DataSource=172.16.4.1;userid=PMMVIEWS;password=PMMVIEWS;LibraryList=YLBZPR.V3";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringAS400ODBC()
|
|
{
|
|
string result = "DSN=HabelStammdatenODBC;Uid=PMMVIEWS;Pwd=PMMVIEWS;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringPortal()
|
|
{
|
|
//string result = "database=AGENT2;Server=172.16.1.9:50000;uid=adminhla;pwd=hlahenry;";
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=AGENT2;User Id=LA;Password=la;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=AGENT2;User Id=LA;Password=la;";
|
|
return result;
|
|
}
|
|
|
|
public static int StandortOhne0(int standort)
|
|
{
|
|
if (standort == 0)
|
|
{
|
|
standort = 1;
|
|
//System.Net.IPHostEntry host = System.Net.Dns.GetHostEntry("");
|
|
//System.Net.IPAddress myIP = null;
|
|
//foreach (System.Net.IPAddress a in host.AddressList)
|
|
//{
|
|
// if (a.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
|
|
// {
|
|
// myIP = a;
|
|
// break;
|
|
// }
|
|
//}
|
|
//if (myIP != null)
|
|
//{
|
|
// //Bestimmen des UpdatePfades anhand der lokalen IP
|
|
// string ip = myIP.ToString();
|
|
// String[] parts = ip.Split('.');
|
|
|
|
// if (parts[0] == "172")
|
|
// {
|
|
// if (parts[1] == "17")
|
|
// {
|
|
// //Kinkartz
|
|
// standort = 1;
|
|
// }
|
|
// else
|
|
// {
|
|
// //Aachen
|
|
// standort = 1;
|
|
// }
|
|
// }
|
|
// else if (parts[0] == "10")
|
|
// {
|
|
// //Weiss
|
|
// standort = 2;
|
|
// }
|
|
// else if (parts[0] == "192")
|
|
// {
|
|
// if (parts[2] == "14")
|
|
// {
|
|
// standort = 1;
|
|
// }
|
|
// else if (parts[2] == "44")
|
|
// {
|
|
// //Wolff
|
|
// standort = 2;
|
|
// }
|
|
// else if (parts[2] == "8")
|
|
// {
|
|
// //Hemmann
|
|
// standort = 3;
|
|
// }
|
|
// else if (parts[2] == "40" || parts[2] == "10")
|
|
// {
|
|
// //Schuhmann (Lokal ist 40 / VPN ist 10)
|
|
// standort = 99;
|
|
// }
|
|
// else if (parts[2] == "0" || parts[2] == "33")
|
|
// {
|
|
// //Polen
|
|
// standort = 99;
|
|
// }
|
|
// }
|
|
//}
|
|
}
|
|
|
|
return standort;
|
|
}
|
|
|
|
public static String ConnectionStringKassenSystem(int standort)
|
|
{
|
|
string result = null;
|
|
|
|
if (standort == 0)
|
|
{
|
|
standort = StandortOhne0(standort);
|
|
}
|
|
|
|
switch (standort)
|
|
{
|
|
case 2:
|
|
result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystem2;User Id=KassenManager;Password=ks;Max Pool Size=600;Connection Timeout=3000;encrypt=false;"; //Neu-Ulm
|
|
break;
|
|
case 3:
|
|
result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystem3;User Id=KassenManager;Password=ks;Max Pool Size=600;Connection Timeout=3000;encrypt=false;"; //Ladbergen
|
|
break;
|
|
case 9:
|
|
result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystemTest;User Id=KassenManager;Password=ks;Max Pool Size=600;Connection Timeout=3000;encrypt=false;";//Test
|
|
break;
|
|
default:
|
|
result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystem;User Id=KassenManager;Password=ks;Max Pool Size=600;Connection Timeout=3000;encrypt=false;"; //Aachen
|
|
break;
|
|
}
|
|
|
|
//NICHT DEN STRING ÄNDERN, SONDERN DIE AUSKOMMENTIERTE ZEILE WECHSELN!!!!!!!!!!!!!!!!
|
|
//string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystem;User Id=KassenManager;Password=ks;Timeout=90000; Connection Timeout=9999"; //Echsystem
|
|
//result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystemTest;User Id=KassenManager;Password=ks;"; //Testystem
|
|
//string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystem;User Id=KassenManager;Password=ks;"; //Echsystem
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=KassenSystem;User Id=KassenManager;Password=ks;"; //Echsystem
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringKassenSystemUniversal()
|
|
{
|
|
//NICHT DEN STRING ÄNDERN, SONDERN DIE AUSKOMMENTIERTE ZEILE WECHSELN!!!!!!!!!!!!!!!!
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=KassenSystemUniversal;User Id=KassenManager;Password=ks;encrypt=false;"; //Echsystem
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringLambertzMail()
|
|
{
|
|
//NICHT DEN STRING ÄNDERN, SONDERN DIE AUSKOMMENTIERTE ZEILE WECHSELN!!!!!!!!!!!!!!!!
|
|
string result = "Data Source=\" + IP_MSSQLSERVER + \";Initial Catalog=LambertzMail;User Id=LambertzMail;Password=^s7#Ux1G1;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringPortal_IC()
|
|
{
|
|
//string result = "database=AGENT2;Server=172.16.1.9:50000;uid=adminhla;pwd=hlahenry;";
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=AGENT3;User Id=LA;Password=la;encrypt=false;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=AGENT3;User Id=LA;Password=la;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringPortal_TEST()
|
|
{
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=AGENT_TEST;User Id=sa;Password=hlahenry;encrypt=false;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=AGENT_TEST;User Id=sa;Password=hlahenry;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringODAL()
|
|
{
|
|
//string result = "Data Source=172.16.1.125\\HLAEXPRESS,1433;Network Library=DBMSSOCN;Initial Catalog=ODAL;User Id=ODALUSER;Password=OdaL;MultipleActiveResultSets=true;";
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=ODAL;User Id=ODAL;Password=OdaL;MultipleActiveResultSets=true;encrypt=false;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=ODAL;User Id=ODAL;Password=OdaL;MultipleActiveResultSets=true;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringBILDDATEN()
|
|
{
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=Bilddaten;User Id=LA;Password=la;MultipleActiveResultSets=true;encrypt=false;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=Bilddaten;User Id=LA;Password=la;MultipleActiveResultSets=true;";
|
|
return result;
|
|
}
|
|
public static String ConnectionStringLAMBERTZ()
|
|
{
|
|
string result = "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=Lambertz;User Id=LA;Password=la;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
//string result = "Data Source=APP3AACHEN;Initial Catalog=Lambertz;User Id=LA;Password=la;MultipleActiveResultSets=true;Connection Timeout=6000";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringPROJEKTVERWALTUNG()
|
|
{
|
|
return "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=HLAProjektVerwaltung;User Id=HLAUser;Password=hlaUSER1!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
}
|
|
public static String ConnectionStringGRS()
|
|
{
|
|
return "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=GRS-SIGNUM;User Id=HLAUser;Password=hlaUSER1!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
}
|
|
|
|
public static String ConnectionStringEnaioLambertz()
|
|
{
|
|
return "Data Source=" + IP_MSSQLSERVER + ";Initial Catalog=EnaioLambertz;User Id=EnaioUser;Password=ena1O!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
}
|
|
|
|
public static String ConnectionStringDocuWareTest()
|
|
{
|
|
string result = "Data Source=APP14AC;Initial Catalog=Lambertz;User Id=DocuWare;Password=Dokuware22!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
return result;
|
|
}
|
|
|
|
public static String ConnectionStringDocuWare()
|
|
{
|
|
string result = "Data Source=APP14AC;Initial Catalog=Lambertz;User Id=DocuWare;Password=Dokuware22!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
return result;
|
|
}
|
|
public static String ConnectionStringDocuWareErweiterteRechte()
|
|
{
|
|
string result = "Data Source=APP14AC;Initial Catalog=dwsystem;User Id=sa;Password=DokuWare22!;MultipleActiveResultSets=true;Connection Timeout=6000;encrypt=false;";
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Holt die Verbindung zur DB2, oder stellt eine her.
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static SqlConnection GetConnectionPortal(string formName)
|
|
{
|
|
System.Diagnostics.Debug.WriteLine(formName);
|
|
|
|
SqlConnection con = new SqlConnection();
|
|
String extension = formName.Substring(formName.Length - 3, 3);
|
|
if (extension == "_ic")
|
|
{
|
|
con.ConnectionString = DBTools.ConnectionStringPortal_IC();
|
|
}
|
|
else if (extension == "_xx")
|
|
{
|
|
con.ConnectionString = DBTools.ConnectionStringPortal_TEST();
|
|
}
|
|
else
|
|
{
|
|
con.ConnectionString = DBTools.ConnectionStringPortal();
|
|
}
|
|
con.Open();
|
|
|
|
System.Diagnostics.Debug.WriteLine(con.ConnectionString);
|
|
|
|
return con;
|
|
}
|
|
|
|
public static object NullZuDBNull(object wert)
|
|
{
|
|
if (wert == null)
|
|
{
|
|
return DBNull.Value;
|
|
}
|
|
return wert;
|
|
}
|
|
|
|
public static object EmptyZuDBNull(String value)
|
|
{
|
|
if (String.IsNullOrWhiteSpace(value))
|
|
{
|
|
return DBNull.Value;
|
|
}
|
|
else
|
|
{
|
|
return value;
|
|
}
|
|
}
|
|
}
|
|
}
|