private void btnAddEmployee_Click(object sender, RoutedEventArgs e)
{
try
{
DatabaseConnections db = new DatabaseConnections();
db.AddEmployee(txtFirstName.Text, txtSurName.Text, txtPhoneNumber.Text, comboBoxDepartment.SelectedIndex + 1, comboBoxTeam.SelectedIndex + 1);
}
catch (PostgresException ex)
{
MessageBox.Show(ex.Message);
}
}
internal static void Initialize()
{
#region Environment Settings
_sqlDataConnections = new DatabaseConnections();
switch (Environment.Current.ToLower())
{
#region Production
case "production":
_sqlDataConnections.SqlServerName = "[Config_SqlServerName]";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
#region Stage
case "stage":
_sqlDataConnections.SqlServerName = "[Config_SqlServerName]";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
#region Local/Debug
case "debug":
_sqlDataConnections.SqlServerName = "[Config_SqlServerName]";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
case "local":
_sqlDataConnections.SqlServerName = "[Config_SqlServerName]";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
}
#endregion
}
private void button_Click(object sender, RoutedEventArgs e)
{
try
{
DatabaseConnections db = new DatabaseConnections();
List <Employee> employees = db.GetAllEmployees();
leftListBox.Items.Refresh();
leftListBox.ItemsSource = employees;
}
catch (PostgresException ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Initializes data connectors on beginning of each test.
/// </summary>
protected void InitializeTestLab()
{
this.RemoveDatabaseFileReadOnly();
FilePersistedTestLab.SetDefaultFileLocations();
this.SetDeploymentDirConnectionString();
// first reset the database password, then continue with other initializations
this.CheckDatabase = DatabaseConnections.CreateInstance();
this.CheckDatabase.UpdateMasterPassword(String.Empty);
this.PrimaryPersistence = CreateSqlPersistence();
this.SecondaryPersistence = CreateSqlPersistence();
this.ClearTestLab(); // because of failed previous tests
}
public int UpdateCheckIn()
{
try
{
SqlCommand cmd = new SqlCommand("usp_updateVisitornChkIN", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@VisitorName", Name);
cmd.Parameters.AddWithValue("@VisitorCNIC", CNIC);
cmd.Parameters.AddWithValue("@ContactNo", Contact);
cmd.Parameters.AddWithValue("@BadgeNo", BadgeNo);
// cmd.Parameters.AddWithValue("@Admin_NonAdmin", Admin_NonAdmin_ID);
cmd.Parameters.AddWithValue("@VisitPurpose", VisitPurpose);
cmd.Parameters.AddWithValue("@VisitorAddress", adress);
cmd.Parameters.AddWithValue("@EnterByUser_ID", UserID);
cmd.Parameters.AddWithValue("@VisitorID", VisiterID);
cmd.Parameters.AddWithValue("@Office_ID", 3);
cmd.Parameters.AddWithValue("@HostName", "test");
cmd.Parameters.AddWithValue("@VisitID", VisitID);
cmd.Parameters.AddWithValue("@EnterByUser_ID", UserData.uid);
//SqlParameter pLastInsertedID = new SqlParameter("@VisitorID", SqlDbType.Int);
//pLastInsertedID.Direction = ParameterDirection.Output;
//cmd.Parameters.Add(pLastInsertedID);
//pLastInsertedID.Value = "@VisitorID";
//SqlParameter pLastInsertedID1 = new SqlParameter("@VisitID", SqlDbType.Int);
//pLastInsertedID1.Direction = ParameterDirection.Output;
//cmd.Parameters.Add(pLastInsertedID1);
//pLastInsertedID1.Value = "@VisitID";
int result = Convert.ToInt32(cmd.ExecuteNonQuery());
// VisiterID = Convert.ToInt32(pLastInsertedID.Value);
// VisitID = Convert.ToInt32(pLastInsertedID1.Value);
DatabaseConnections.CloseConnection();
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(0);
}
}
public DataTable GetAllCheckINToday()
{
DataTable ds = new DataTable();
try
{
SqlCommand cmd = new SqlCommand("getallCheckINToday", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
//SqlParameter pDriverId = new SqlParameter("@TripId", TripId);
//cmd.Parameters.Add(pDriverId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DatabaseConnections.CloseConnection();
return(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(ds);
}
}
public DataTable SearchByCNIC()
{
DataTable ds = new DataTable();
try
{
SqlCommand cmd = new SqlCommand("SearchByCNIC", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pDriverId = new SqlParameter("@CNIC", CNIC);
cmd.Parameters.Add(pDriverId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DatabaseConnections.CloseConnection();
return(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(ds);
}
}
private void buttonX1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("insertUser", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", textBoxX1.Text);
cmd.Parameters.AddWithValue("@Password", textBoxX2.Text);
cmd.Parameters.AddWithValue("@RoleID", 1);
// con.Open();
int result = cmd.ExecuteNonQuery();
DatabaseConnections.CloseConnection();
if (result == 1)
{
MessageBox.Show("Record Inserted successfully ", "Record Insertion", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
refresh();
}
else
{
MessageBox.Show("Record Not Inserted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);;
}
}
public DataTable GetCheckoutInfo()
{
DataTable ds = new DataTable();
try
{
SqlCommand cmd = new SqlCommand("usp_getCheckOutPersonInfo", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pDriverId = new SqlParameter("@Badge", BadgeNo);
cmd.Parameters.Add(pDriverId);
SqlParameter pDriverId1 = new SqlParameter("@CDate", DateTime.Now);
cmd.Parameters.Add(pDriverId1);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DatabaseConnections.CloseConnection();
return(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(ds);
}
}
public ActionResult Get(int id)
{
Student student = null;
try
{
String connectionString = DatabaseConnections.getConnectionString("NARAYANA_CLIENT");
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
String sqlQuery = "SELECT * FROM [dbo].[student] WHERE ID = " + id;
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.Write(reader.GetString(1));
student = StudentUtility.getStudentFormReader(reader);
}
}
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
if (null != student)
{
return(Ok(student));
}
else
{
return(NotFound(new ErrorText("Student Not Found")));
}
}
public int CheckBadgeIssuance(int bno)
{
DataTable ds = new DataTable();
try
{
int result;
SqlCommand cmd = new SqlCommand("usp_CheckBadgeIssuedOnDate", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pFrom = new SqlParameter("@From", DateTime.Now.Date);
SqlParameter pTo = new SqlParameter("@To", DateTime.Now.Date);
SqlParameter pBadge = new SqlParameter("@BadgeNo", bno);
cmd.Parameters.Add(pFrom);
cmd.Parameters.Add(pTo);
cmd.Parameters.Add(pBadge);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DatabaseConnections.CloseConnection();
if (Convert.ToInt32(ds.Rows[0][0]) == 1)
{
result = 1; // badge is free
}
else
{
result = 0; //badge is not free
}
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(0);
}
}
public DataTable SelectUser()
{
DataTable ds = new DataTable();
try
{
SqlCommand cmd = new SqlCommand("usp_SelectUser", DatabaseConnections.OpenConnection());
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pUserName = new SqlParameter("@userName", UserName);
cmd.Parameters.Add(pUserName);
SqlParameter pPassword = new SqlParameter("@password", Password);
cmd.Parameters.Add(pPassword);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DatabaseConnections.CloseConnection();
return(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(ds);
}
}
public StorageContext(DatabaseConnections settings) : base(settings.Database)
{
}
public string[] UserListService()
{
string[] userList = null;
try
{
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items != null)
{
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
if (item.ServiceMethodName.ToLower() == "userlistservice")
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (DynamicDataType == null)
{
DynamicDataType = this;
}
if (DynamicDataType != null)
{
if (DynamicDataType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Data table containing the data.
DataTable dataTable = null;
string sql =
"SELECT [" + item.IndicatorColumnName + "], [" + item.DataObjectPropertyName + "] " +
"FROM [" + (String.IsNullOrEmpty(item.DatabaseOwner) ? "" : item.DatabaseOwner + "].[") + item.TableName.Replace(".", "].[") + "] ";
if ((!String.IsNullOrEmpty(item.ComparerColumnName)) && (!String.IsNullOrEmpty(item.ComparerValue)))
{
sql += "WHERE ([" + item.ComparerColumnName + "] = '" + item.ComparerValue + "')";
}
sql = Nequeo.Data.DataType.DataTypeConversion.
GetSqlConversionDataTypeNoContainer(connectionDataType, sql);
string providerName = null;
string connection = string.Empty;
string connectionString = string.Empty;
// Get the current database connection string
// from the configuration file through the
// specified configuration key.
using (DatabaseConnections databaseConnection = new DatabaseConnections())
connection = databaseConnection.DatabaseConnection(item.ConnectionName, out providerName);
// If empty string is returned then
// value should be the connection string.
if (String.IsNullOrEmpty(connection))
{
connectionString = item.ConnectionName;
}
else
{
connectionString = connection;
}
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Get the connection type
switch (connectionType)
{
// Get the permission data from the
// database through the sql provider.
case ConnectionContext.ConnectionType.SqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.PostgreSqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OracleClientConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OleDbConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OdbcConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.MySqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
default:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
}
// Permission data exists.
if (dataTable != null)
{
if (dataTable.Rows.Count > 0)
{
List <string> cols = new List <string>();
foreach (DataRow row in dataTable.Rows)
{
cols.Add("<a href=\"" + item.ServiceMethodRedirectionUrl + "?" + item.DataObjectPropertyName + "=" +
row[item.DataObjectPropertyName].ToString() + "\">" + row[item.IndicatorColumnName].ToString() + "</a>");
}
// Assign the collection.
userList = cols.ToArray();
break;
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
string errorMessage = ex.Message;
LogHandler.WriteTypeMessage(errorMessage, typeof(DynamicData).GetMethod("UserListService"));
}
// Return the list of users.
return(userList == null ? new string[0] : userList);
}
/// <summary>
/// Called during startup by each shared project
/// </summary>
public static void Initialize()
{
#region Initialize Database Connections
_sqlDataConnections = new DatabaseConnections();
switch (Environment.Current.ToLower())
{
#region Production
case "production":
_sqlDataConnections.SqlServerName = "oqj6b9n8qh";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
#region Stage
case "stage":
_sqlDataConnections.SqlServerName = "p9hs0x7ppr";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
#region Local/Debug
case "debug":
_sqlDataConnections.SqlServerName = "ze64uj4f8z";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
case "local":
_sqlDataConnections.SqlServerName = "ze64uj4f8z";
_sqlDataConnections.SqlUserName = "******";
_sqlDataConnections.SqlPassword = "******";
break;
#endregion
}
#endregion
#region Initialize Storage Connections
switch (Environment.Current.ToLower())
{
#region Production
case "production":
_storageConnections = new StorageConnections(
//Platform (Production)
"bltplatformproduction",
"6CANwClOo0nou1xiAhgMUlqx9hEwc+pWHpSiOvdoXD6pGvuxjintk0f3+YWJIQtoFqOKKfLz1bwiehDz/pe2kg==",
//Accounts (Production)
"bltaccountsproduction",
"p9fBtohGvjUrjGYF6nX4eVNLFfVgE6AF9tgnYfg3w+7fgW7JU4ws0UiHUsrNkcK7W6oDxOKbRGOnP8jEVvC8fQ=="
);
break;
#endregion
#region Stage
case "stage":
_storageConnections = new StorageConnections(
//Platform (Stage)
"bltplatformstage",
"834TeorlK8OZfmlRS7Fe/G/zwL1AYHV/O2ntLGPTurqMc/jVFWE6ECdYXOQQo1FNU73y+G71+Kyy4MG4WZV1TA==",
//Accounts (Stage)
"bltaccountsstage",
"VJEws8Ysqr7Tz7wosprTX8oFxigOZFIWGR6obpgJ/KNQZxJfdOSpDrcAodeNAD4tIxh2ziGXUBurBcg3eZmmpw=="
);
break;
#endregion
#region Local/Debug
case "debug":
_storageConnections = new StorageConnections(
//Platform (Debug)
"bltplatformdebug",
"tVEEEyg74acuJgL6GjwSq+XNeMAQZRuUbqf7wTqQ1FILykcqii8CERqLqaGULASsHAfA7k4BcU+9t6OnbL2rqA==",
//Accounts (Debug)
"bltaccountsdebug",
"2XKM26TvfOqcQiK60J2IG0Zf0lJjvEOoR2ppfr+5Y8+OJ81x1Y+Xi0if7+o5rfWHlkEYVn5e1UwYJUj2DKxrwQ=="
);
break;
case "local":
_storageConnections = new StorageConnections(
//Platform (Local)
"bltplatformdebug",
"tVEEEyg74acuJgL6GjwSq+XNeMAQZRuUbqf7wTqQ1FILykcqii8CERqLqaGULASsHAfA7k4BcU+9t6OnbL2rqA==",
//Accounts (Local)
"bltaccountsdebug",
"2XKM26TvfOqcQiK60J2IG0Zf0lJjvEOoR2ppfr+5Y8+OJ81x1Y+Xi0if7+o5rfWHlkEYVn5e1UwYJUj2DKxrwQ=="
);
break;
#endregion
default:
_storageConnections = null;
break;
}
#endregion
}
/// <summary>
/// Validates a user to a SQL or integrated account.
/// </summary>
/// <param name="username">The user name.</param>
/// <param name="password">The user password.</param>
private void ValidateUser(string username, string password)
{
try
{
bool authResult = false;
ConnectionStringExtensionElement[] items = ConnectionStringExtensionConfigurationManager.ConnectionStringExtensionElements();
if (items.Count() > 0)
{
// For each service host configuration find
// the corresponding service type.
foreach (ConnectionStringExtensionElement item in items)
{
// Get the current type name
// and create a instance of the type.
Type typeName = Type.GetType(item.TypeName, true, true);
object typeNameInstance = Activator.CreateInstance(typeName);
if (UserNamePasswordValidatorType == null)
{
UserNamePasswordValidatorType = this;
}
if (UserNamePasswordValidatorType != null)
{
if (UserNamePasswordValidatorType.GetType().FullName.ToLower() == typeNameInstance.GetType().FullName.ToLower())
{
Type dataAccessProviderType = Type.GetType(item.DataAccessProvider, true, true);
ConnectionContext.ConnectionType connectionType = ConnectionContext.ConnectionTypeConverter.GetConnectionType(item.ConnectionType);
ConnectionContext.ConnectionDataType connectionDataType = ConnectionContext.ConnectionTypeConverter.GetConnectionDataType(item.ConnectionDataType);
// Data table containing the data.
DataTable dataTable = null;
string sql =
"SELECT [" + item.IndicatorColumnName + "] " +
"FROM [" + (String.IsNullOrEmpty(item.DatabaseOwner) ? "" : item.DatabaseOwner + "].[") + item.TableName.Replace(".", "].[") + "] " +
"WHERE ([" + item.ComparerColumnName + "] = '" + username + "')";
sql = Nequeo.Data.DataType.DataTypeConversion.
GetSqlConversionDataTypeNoContainer(connectionDataType, sql);
string providerName = null;
string connection = string.Empty;
string connectionString = string.Empty;
// Get the current database connection string
// from the configuration file through the
// specified configuration key.
using (DatabaseConnections databaseConnection = new DatabaseConnections())
connection = databaseConnection.DatabaseConnection(item.ConnectionName, out providerName);
// If empty string is returned then
// value should be the connection string.
if (String.IsNullOrEmpty(connection))
{
connectionString = item.ConnectionName;
}
else
{
connectionString = connection;
}
// Create an instance of the data access provider
Nequeo.Data.DataType.IDataAccess dataAccess = ((Nequeo.Data.DataType.IDataAccess)Activator.CreateInstance(dataAccessProviderType));
// Get the connection type
switch (connectionType)
{
// Get the permission data from the
// database through the sql provider.
case ConnectionContext.ConnectionType.SqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.PostgreSqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OracleClientConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OleDbConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.OdbcConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
// Get the permission data from the
// database through the oracle provider.
case ConnectionContext.ConnectionType.MySqlConnection:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
default:
dataAccess.ExecuteQuery(ref dataTable, sql,
CommandType.Text, connectionString, true, null);
break;
}
// Permission data exists.
if (dataTable != null)
{
if (dataTable.Rows.Count > 0)
{
string permissionValueItem = dataTable.Rows[0][item.IndicatorColumnName].ToString();
if (!String.IsNullOrEmpty(permissionValueItem))
{
if (password.ToLower() == permissionValueItem.ToLower())
{
authResult = true;
break;
}
}
}
}
}
}
}
}
// If the user has been validated
// and autharised then allow connection.
if (!authResult)
{
throw new FaultException("Unknown Username or Incorrect Password");
}
}
catch (Exception)
{
throw;
}
}
private static TestConnectionResult TryTestDatabaseConnection(object objectState)
{
var connectionParams = objectState as Tuple <string, string>;
return(DatabaseConnections.ValidateDatabaseConnection(connectionParams.Item1, connectionParams.Item2));
}
public HomeController(IOptions <DatabaseConnections> databaseConnections)
{
_databaseConnections = databaseConnections.Value;
}
