//pooja vachhani on 2/7/15
private DataTable GetUserTypeFromUserId(IDbDataAdapter adapter, String UserID, ref String msg)
{
try
{
DataSet ds = new DataSet();
adapter.SelectCommand.CommandText = "SELECT distinct user_mst.* FROM user_mst WHERE (user_mst.user_id = @userid) and user_mst.is_active = 'Y'";
adapter.SelectCommand.Parameters.Clear();
IDataParameter parma1 = DBObjectFactory.GetParameterObject();
parma1.ParameterName = "@userid";
parma1.DbType = DbType.String;
parma1.Value = UserID;
adapter.SelectCommand.Parameters.Add(parma1);
adapter.TableMappings.Clear();
adapter.TableMappings.Add("Table", "user_mst");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count >= 1)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
msg = ex.Message;
return(null);
}
}
public static IDataParameter MakeParameter(String ParameterName, DbType ParameterType, Object ParameterValue)
{
IDataParameter parameter = DBObjectFactory.GetParameterObject();
parameter.ParameterName = ParameterName;
parameter.DbType = ParameterType;
parameter.Value = ParameterValue;
return(parameter);
}
public ServerBase()
{
DBConnection = DBObjectFactory.GetConnectionObject();
DBConnection.ConnectionString = ConfigurationManager.ConnectionStrings["campus_conciergeConnectionString"].ToString();
DBCommand = DBObjectFactory.GetCommandObject();
DBCommand.Connection = DBConnection;
DBDataAdpterObject = DBObjectFactory.GetDataAdapterObject(DBCommand);
DBDataAdpterObject.SelectCommand.CommandTimeout = 60;
}
public ServerBase()
{
AddonPaymentUrl = ConfigurationManager.AppSettings["AddonPayment"].ToString();
PaymentUrl = ConfigurationManager.AppSettings["Payment"].ToString();
Trackurl = ConfigurationManager.AppSettings["Track"].ToString();
CbmUrl = ConfigurationManager.AppSettings["cbm"].ToString();
DBConnection = DBObjectFactory.GetConnectionObject();
DBConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
DBCommand = DBObjectFactory.GetCommandObject();
DBCommand.Connection = DBConnection;
DBDataAdpterObject = DBObjectFactory.GetDataAdapterObject(DBCommand);
DBDataAdpterObject.SelectCommand.CommandTimeout = 60;
}
private DataTable GetUserInformation(IDbDataAdapter adapter, String UserID, ref String msg)
{
try
{
DataSet ds = new DataSet();
adapter.SelectCommand.CommandText =
//"SELECT user_mst.*, w_employee_master.full_name, w_employee_master.first_name, w_employee_master.middle_name, w_employee_master.last_name, " +
//"w_employee_master.user_short_name FROM user_mst LEFT OUTER JOIN w_employee_master ON user_mst.user_id = w_employee_master.user_id " +
//"WHERE (user_mst.user_id = @userid)";
"SELECT distinct user_mst.* FROM user_mst WHERE (user_mst.user_id = @userid )";
adapter.SelectCommand.Parameters.Clear();
IDataParameter parma1 = DBObjectFactory.GetParameterObject();
parma1.ParameterName = "@userid";
parma1.DbType = DbType.String;
parma1.Value = UserID;
adapter.SelectCommand.Parameters.Add(parma1);
adapter.TableMappings.Clear();
adapter.TableMappings.Add("Table", "user_mst");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count >= 1)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
msg = ex.Message;
return(null);
}
}
//pooja vachhani on 2/7/15
private DataTable GetUserTypeFromUserId(IDbDataAdapter adapter, String UserID, ref String msg)
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT distinct user_mst.*,driver_mst.driver_photo FROM user_mst ");
sb.Append(" LEFT JOIN driver_mst on driver_mst.driver_id = user_mst.unique_id ");
sb.Append(" WHERE (user_mst.user_id = @userid) and and user_mst.user_status_flag = 'A' and driver_mst.active_flag = 'Y' ");
DataSet ds = new DataSet();
adapter.SelectCommand.CommandText = sb.ToString();
adapter.SelectCommand.Parameters.Clear();
IDataParameter parma1 = DBObjectFactory.GetParameterObject();
parma1.ParameterName = "@userid";
parma1.DbType = DbType.String;
parma1.Value = UserID;
adapter.SelectCommand.Parameters.Add(parma1);
adapter.TableMappings.Clear();
adapter.TableMappings.Add("Table", "user_mst");
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds);
if (ds.Tables[0].Rows.Count >= 1)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
msg = ex.Message;
return(null);
}
}
public Boolean W_GetNextDocumentNo(ref IDbCommand comm, String DocumentType, String UserID, String HostName, ref String NextDocumentNo, ref String Message)
{
comm.CommandText = " SELECT next_doc_no FROM next_doc_no WHERE " +
" doc_type = @document_type";
comm.Parameters.Clear();
IDataParameter para2 = DBObjectFactory.GetParameterObject();
para2.ParameterName = "@document_type";
para2.DbType = DbType.String;
para2.Value = DocumentType;
comm.Parameters.Add(para2);
Object objRetVal = null;
Decimal doc_no;
//MessageBox.Show("Before getting the next document no");
objRetVal = comm.ExecuteScalar();
if (objRetVal != null)
{
doc_no = Convert.ToDecimal(objRetVal);
}
else
{
doc_no = 0;
}
if (doc_no <= 0)
{
Message = "An error occured while getting next document number for Document Type " + DocumentType + ".";
return(false);
}
//comm.CommandText = " UPDATE next_doc_no SET next_doc_no = next_doc_no + 1, " +
// " last_modified_by = @UserId, last_modified_date = @AsOn," +
// " last_modified_host = @HostName WHERE " +
// " next_doc_no = @sa_id AND " +
// " doc_type = @document_type AND next_doc_no = @doc_no";
comm.CommandText = " UPDATE next_doc_no SET next_doc_no = next_doc_no + 1, " +
" last_modified_by = @UserId, last_modified_date = @AsOn," +
" last_modified_host = @HostName WHERE " +
" doc_type = @document_type AND next_doc_no = @doc_no";
IDataParameter para3 = DBObjectFactory.GetParameterObject();
para3.ParameterName = "@doc_no";
para3.DbType = DbType.Decimal;
para3.Value = doc_no;
comm.Parameters.Add(para3);
IDataParameter para4 = DBObjectFactory.GetParameterObject();
para4.ParameterName = "@UserId";
para4.DbType = DbType.String;
para4.Value = UserID;
comm.Parameters.Add(para4);
IDataParameter para5 = DBObjectFactory.GetParameterObject();
para5.ParameterName = "@AsOn";
para5.DbType = DbType.DateTime;
para5.Value = DateTime.Now;
comm.Parameters.Add(para5);
IDataParameter para6 = DBObjectFactory.GetParameterObject();
para6.ParameterName = "@HostName";
para6.DbType = DbType.String;
para6.Value = HostName;
comm.Parameters.Add(para6);
//MessageBox.Show("Before updating the document no");
if (comm.ExecuteNonQuery() <= 0)
{
Message = "An error occured while Updating next document number for Document Type " + DocumentType + ".";
return(false);
}
//NextDocumentNo = FormatDocumentNo(DocumentType, doc_no);
NextDocumentNo = doc_no.ToString();
return(true);
}
private DataTable GetAppMenuList(IDbDataAdapter adapter, string UserID, string countryCODE, ClientType ClientType)
{
try
{
//String Org_Codes = "";
//for (int i = 0; i < Org_ID.Count; i++)
//{
// Org_Codes += "'" + Org_ID[i].ToString() + "'";
// if (i != Org_ID.Count - 1)
// Org_Codes += ",";
//}
String client_type = String.Empty;
if (ClientType == ClientType.DeskTop)
{
client_type = "D";
}
else if (ClientType == ClientType.Browser)
{
client_type = "B";
}
DataSet ds = new DataSet();
// adapter.SelectCommand.CommandText = @"SELECT menu_id, parent_menu_id, menu_display_name, menu_page_heading, menu_sort_id,menu_type,transaction_label,is_active,
// transaction_page_name,menu_color,layout_id,is_default, menu_link, href_id FROM menu_master WHERE (menu_id IN (SELECT DISTINCT menu_id
// FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id) ) )) ) ";
adapter.SelectCommand.CommandText = @"SELECT menu_id, parent_menu_id, menu_display_name, menu_page_heading, menu_sort_id,menu_type,transaction_label,is_active,
transaction_page_name,menu_color,layout_id,is_default, menu_link, href_id FROM menu_master WHERE (menu_id IN (SELECT DISTINCT menu_id
FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id AND country_code = '" + countryCODE + "') ) )) ) ";
//adapter.SelectCommand.CommandText = @"SELECT menu_id, pmenu_id, parameter, sort_id, transaction_type, menu_desc, window_name FROM menu_mst WHERE (menu_id IN (SELECT DISTINCT menu_id FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id) AND (country_code IN ()) AND (menu_mst.client_type = 'B') AND (start_date <= @today) AND (end_date > @today OR end_date IS NULL))) AND (start_date <= @today) AND (end_date > @today OR end_date IS NULL)))";
adapter.SelectCommand.Parameters.Clear();
IDataParameter parma1 = DBObjectFactory.GetParameterObject();
parma1.ParameterName = "@today";
parma1.DbType = DbType.DateTime;
parma1.Value = System.DateTime.UtcNow;
IDataParameter parma2 = DBObjectFactory.GetParameterObject();
parma2.ParameterName = "@user_id";
parma2.DbType = DbType.String;
parma2.Value = UserID;
adapter.SelectCommand.Parameters.Add(parma1);
adapter.SelectCommand.Parameters.Add(parma2);
adapter.Fill(ds);
//ds.Tables[0].TableName = "dt_menu_info";
//MessageBox.Show("fill",ds.Tables[0].Rows.Count.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
//MessageBox.Show("return");
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ServerLog.Log(ex.Message + " " + ex.StackTrace);
return(null);
//throw ExceptionManager.Help(ex, null);
}
}
public static UpdateTableInfo UpdateTable(ref IDbCommand comm, DataTable dt, UpdateWhereMode updateWhereMode)
{
UpdateTableInfo structUpdateTableInfo;
try
{
#region Initial Validations
if (comm == null)
{
structUpdateTableInfo.ErrorMessage = "Command object is not initialized.Operation cancelled.";
structUpdateTableInfo.ErrorSQLStatement = "";
structUpdateTableInfo.ErrorRow = null;
structUpdateTableInfo.Status = false;
structUpdateTableInfo.TotalRowsAffected = 0;
return(structUpdateTableInfo);
}
if (comm.Connection == null)
{
structUpdateTableInfo.ErrorMessage = "Database connection object is not set.Operation cancelled.";
structUpdateTableInfo.ErrorSQLStatement = "";
structUpdateTableInfo.ErrorRow = null;
structUpdateTableInfo.Status = false;
structUpdateTableInfo.TotalRowsAffected = 0;
return(structUpdateTableInfo);
}
if (comm.Connection.State != ConnectionState.Open)
{
structUpdateTableInfo.ErrorMessage = "Database connection is not open.Operation cancelled.";
structUpdateTableInfo.ErrorSQLStatement = "";
structUpdateTableInfo.ErrorRow = null;
structUpdateTableInfo.Status = false;
structUpdateTableInfo.TotalRowsAffected = 0;
return(structUpdateTableInfo);
}
if (comm.Transaction == null)
{
structUpdateTableInfo.ErrorMessage = "Transaction object is not initialized.Operation cancelled.";
structUpdateTableInfo.ErrorSQLStatement = "";
structUpdateTableInfo.ErrorRow = null;
structUpdateTableInfo.Status = false;
structUpdateTableInfo.TotalRowsAffected = 0;
return(structUpdateTableInfo);
}
#endregion //Initial Validations
int TotalRowsSentForUpdate = 0;
foreach (DataRow drow in dt.Rows)
{
#region Logic for generating the query
String SQL = "";
Boolean IsChangeFound = false;
switch (drow.RowState)
{
case DataRowState.Added:
comm.Parameters.Clear();
IsChangeFound = true;
String ColumnNames = "";
String ParameterNames = "";
foreach (DataColumn dcol in dt.Columns)
{
if (dcol.Ordinal < dt.Columns.Count - 1)
{
ColumnNames += dcol.ColumnName + ",";
ParameterNames += "@" + dcol.ColumnName + ",";
}
else
{
ColumnNames += dcol.ColumnName;
ParameterNames += "@" + dcol.ColumnName;
}
IDataParameter param = DBObjectFactory.GetParameterObject();
param.ParameterName = "@" + dcol.ColumnName;
param.Value = drow[dcol, DataRowVersion.Current];
comm.Parameters.Add(param);
}
if (ColumnNames == "" || ParameterNames == "")
{
break;
}
SQL = "INSERT INTO " + dt.TableName +
" ( " + ColumnNames + ") VALUES (" + ParameterNames + ")";
//MessageBox.Show(SQL, "INSERT");
break;
case DataRowState.Modified:
comm.Parameters.Clear();
IsChangeFound = true;
String SetColumnValues = "";
String WhereColumns = "";
if (updateWhereMode == UpdateWhereMode.KeyAndModifiedColumns)
{
#region KeyAndModifiedColumns
foreach (DataColumn dcol in dt.Columns)
{
//Form the SET part for the columns whose Current Value != Original Value
if (drow[dcol, DataRowVersion.Current].ToString() != drow[dcol, DataRowVersion.Original].ToString())
{
SetColumnValues += dcol.ColumnName + " = @" + dcol.ColumnName + ",";
IDataParameter NewValueParam = DBObjectFactory.GetParameterObject();
NewValueParam.ParameterName = "@" + dcol.ColumnName;
NewValueParam.Value = drow[dcol, DataRowVersion.Current];
comm.Parameters.Add(NewValueParam);
if (drow[dcol, DataRowVersion.Original] != System.DBNull.Value)
{
WhereColumns += dcol.ColumnName + " = @" + dcol.ColumnName + "1 AND ";
IDataParameter OldValueParam = DBObjectFactory.GetParameterObject();
OldValueParam.ParameterName = "@" + dcol.ColumnName + "1";
OldValueParam.Value = drow[dcol, DataRowVersion.Original];
comm.Parameters.Add(OldValueParam);
}
else
{
WhereColumns += dcol.ColumnName + " IS NULL AND ";
}
}
}
//Remove the last comma from SET string
if (SetColumnValues != "")
{
SetColumnValues = SetColumnValues.Substring(0, SetColumnValues.Length - 1);
}
else
{
IsChangeFound = false;
break;
}
//Set the Primary Key Field
foreach (DataColumn dcol in dt.PrimaryKey)
{
//Avoid including those PK fields in WHERE clause if they are modified as
//this is already done in previous loop
if (drow[dcol, DataRowVersion.Current] == drow[dcol, DataRowVersion.Original])
{
WhereColumns += dcol.ColumnName + " = @" + dcol.ColumnName + " AND ";
IDataParameter KeyValueParam = DBObjectFactory.GetParameterObject();
KeyValueParam.ParameterName = "@" + dcol.ColumnName;
KeyValueParam.Value = drow[dcol, DataRowVersion.Original];
comm.Parameters.Add(KeyValueParam);
}
}
//Remove the last AND from WHERE string
if (WhereColumns != "")
{
WhereColumns = WhereColumns.Substring(0, WhereColumns.Length - 4);
}
else
{
IsChangeFound = false;
break;
}
#endregion //KeyAndModifiedColumns
}
else if (updateWhereMode == UpdateWhereMode.KeyColumnsOnly)
{
#region KeyColumnsOnly
foreach (DataColumn dcol in dt.Columns)
{
//Form the SET part for the columns whose Current Value != Original Value
SetColumnValues += dcol.ColumnName + " = @" + dcol.ColumnName + ",";
IDataParameter NewValueParam = DBObjectFactory.GetParameterObject();
NewValueParam.ParameterName = "@" + dcol.ColumnName;
NewValueParam.Value = drow[dcol, DataRowVersion.Current];
comm.Parameters.Add(NewValueParam);
}
//Remove the last comma from SET string
if (SetColumnValues != "")
{
SetColumnValues = SetColumnValues.Substring(0, SetColumnValues.Length - 1);
}
else
{
IsChangeFound = false;
break;
}
//Set the Primary Key Field
foreach (DataColumn dcol in dt.PrimaryKey)
{
WhereColumns += dcol.ColumnName + " = @" + dcol.ColumnName + "1 AND ";
IDataParameter KeyValueParam = DBObjectFactory.GetParameterObject();
KeyValueParam.ParameterName = "@" + dcol.ColumnName + "1";
KeyValueParam.Value = drow[dcol, DataRowVersion.Original];
comm.Parameters.Add(KeyValueParam);
}
//Remove the last AND from WHERE string
if (WhereColumns != "")
{
WhereColumns = WhereColumns.Substring(0, WhereColumns.Length - 4);
}
else
{
IsChangeFound = false;
break;
}
#endregion //KeyColumnsOnly
}
SQL = "UPDATE " + dt.TableName +
" SET " + SetColumnValues + " WHERE " + WhereColumns;
//MessageBox.Show(SQL, "UPDATE");
break;
case DataRowState.Deleted:
comm.Parameters.Clear();
IsChangeFound = true;
String DeleteWhereColumns = "";
//Set the Primary Key Field
foreach (DataColumn dcol in dt.PrimaryKey)
{
DeleteWhereColumns += dcol.ColumnName + " = @" + dcol.ColumnName + " AND ";
IDataParameter KeyValueParam = DBObjectFactory.GetParameterObject();
KeyValueParam.ParameterName = "@" + dcol.ColumnName;
KeyValueParam.Value = drow[dcol, DataRowVersion.Original];
comm.Parameters.Add(KeyValueParam);
}
if (DeleteWhereColumns != "")
{
//Remove the last AND from WHERE string
DeleteWhereColumns = DeleteWhereColumns.Substring(0, DeleteWhereColumns.Length - 4);
}
else
{
IsChangeFound = false;
break;
}
SQL = "DELETE FROM " + dt.TableName + " WHERE " + DeleteWhereColumns;
//MessageBox.Show(SQL, "DELETE");
break;
}
#endregion
#region Fire the query
if (IsChangeFound)
{
comm.CommandType = CommandType.Text;
comm.CommandText = SQL;
if (comm.ExecuteNonQuery() != 1)
{
structUpdateTableInfo.ErrorMessage = "Database updation failed.";
structUpdateTableInfo.ErrorSQLStatement = SQL;
structUpdateTableInfo.ErrorRow = drow;
structUpdateTableInfo.Status = false;
structUpdateTableInfo.TotalRowsAffected = TotalRowsSentForUpdate;
return(structUpdateTableInfo);
}
TotalRowsSentForUpdate++;
IsChangeFound = false;
}
#endregion
}
structUpdateTableInfo.ErrorMessage = "";
structUpdateTableInfo.ErrorSQLStatement = "";
structUpdateTableInfo.ErrorRow = null;
structUpdateTableInfo.Status = true;
structUpdateTableInfo.TotalRowsAffected = TotalRowsSentForUpdate;
return(structUpdateTableInfo);
}
catch (Exception e)
{
comm.Transaction.Rollback();
throw (e);
}
}
