private void AddValuesToTables()
{
try
{
_objTwoColumnTables = new TwoColumnTables();
_objTwoColumnTables.TableName = txtTableName.Text == string.Empty ? null : txtTableName.Text;
_objTwoColumnTables.EntryMode = txtEntryMode.Text == string.Empty ? null : txtEntryMode.Text;
_objTwoColumnTables.RelatedTo = txtRelatedTo.Text == string.Empty ? null : txtRelatedTo.Text;
_objTwoColumnTables.RelatedUserRoleID = ddlRelatedUserRoleID.SelectedValue == "-1" ? 0 : Convert.ToInt32(ddlRelatedUserRoleID.SelectedValue);
_objTwoColumnTables.EntrySystem = ddlEntrySystem.SelectedValue == "-1" ? null : ddlEntrySystem.SelectedValue;
_objTwoColumnTablesController = new TwoColumnTablesController();
if (btnSave.Text == "Save")
{
_objTwoColumnTablesController.Save(_connectionString, _objTwoColumnTables);
}
else
{
_objTwoColumnTables.TableID = Convert.ToInt32(Session["selectedIndex"].ToString());
_objTwoColumnTablesController.Update(_connectionString, _objTwoColumnTables);
}
}
catch (Exception msgException)
{
throw msgException;
}
}
public AutoTableData(TwoColumnTables objTwoColumnTables)
{
TableID = objTwoColumnTables.TableID;
dtAutoData = new DataTable();
dtAutoData.Columns.Add(new DataColumn("FieldOfID", typeof(String)));
dtAutoData.Columns.Add(new DataColumn("FieldOfName", typeof(String)));
}
private void ShowsysTwoColumnTables()
{
try
{
_objTwoColumnTables = new TwoColumnTables();
_objTwoColumnTables.RelatedUserRoleID = Convert.ToInt32(Session["relatedUserRoleID"].ToString());
_objTwoColumnTables.EntrySystem = "A";
EmployeeSetup objEmployeeSetup = new EmployeeSetup();
objEmployeeSetup.CompanyID = LoginUserInformation.CompanyID;
objEmployeeSetup.EmployeeID = LoginUserInformation.EmployeeCode;
_objTwoColumnTablesController = new TwoColumnTablesController();
DataTable dtTablesName = _objTwoColumnTablesController.GetRecord(_connectionString, _objTwoColumnTables, objEmployeeSetup);
grdTableName.DataSource = null;
grdTableName.DataBind();
if (dtTablesName.Rows.Count > 0)
{
grdTableName.DataSource = dtTablesName;
grdTableName.DataBind();
}
}
catch (Exception msgException)
{
throw msgException;
}
}
public void Delete(string connectionString, TwoColumnTables objTwoColumnTables)
{
try
{
string sqlString = "UPDATE sysTwoColumnTables SET DataUsed = 'I' WHERE TableID = "+ objTwoColumnTables.TableID + "";
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, sqlString);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void SaveUserRole(EmployeeSetup objEmployeeSetup, UserPermission objUserPermission)
{
try
{
if (objEmployeeSetup.EmployeeID == null)
{
throw new Exception("User code is required ");
}
if (objUserPermission.RoleType == null)
{
throw new Exception("Role type is required ");
}
string storedProcedureComandTextNode = null;
foreach (var itemNo in objUserPermission.roleList)
{
objUserPermission.RoleID = itemNo;
storedProcedureComandTextNode += @" INSERT INTO [uUsersInRoles] ([CompanyID],[UserId],[RoleTypeID],[RoleID],[DataUsed],[EntryUserID],[EntryDate]) VALUES(" +
objEmployeeSetup.CompanyID + ",'" +
objEmployeeSetup.EmployeeID + "','" +
objUserPermission.RoleType + "'," +
objUserPermission.RoleID + ",'" +
"A" + "','" +
objEmployeeSetup.EntryUserName + "'," +
"CAST(GETDATE() AS DateTime)" + ");";
}
if (storedProcedureComandTextNode != null)
{
foreach (var itemNo in objUserPermission.RelatedUserRoleList)
{
TwoColumnTables objTwoColumnTables = new TwoColumnTables();
objTwoColumnTables.RelatedUserRoleID = itemNo;
storedProcedureComandTextNode += @"INSERT INTO [uUsersInRelatedRoles] ([CompanyID],[UserId],[RoleID],[DataUsed],[EntryUserID],[EntryDate]) VALUES(" +
objEmployeeSetup.CompanyID + ",'" +
objEmployeeSetup.EmployeeID + "'," +
objTwoColumnTables.RelatedUserRoleID + ",'" +
"A" + "','" +
objEmployeeSetup.EntryUserName + "'," +
"CAST(GETDATE() AS DateTime)" + ");";
}
clsDataManipulation.StoredProcedureExecuteNonQuery(this.ConnectionString, storedProcedureComandTextNode);
}
}
catch (Exception msgException)
{
throw msgException;
}
}
public DataTable GetRecord(string connectionString, TwoColumnTables objTwoColumnTables, EmployeeSetup objEmployeeSetup)
{
try
{
DataTable dtItem = null;
string sqlString = @"SELECT DISTINCT A.[TableID],A.[TableName],A.[RelatedUserRoleID] FROM [sysTwoColumnTables] A INNER JOIN uUsersInRelatedRoles B ON A.RelatedUserRoleID = B.RoleID
WHERE A.DataUsed = 'A' AND B.DataUsed = 'A' AND A.EntrySystem = '" + objTwoColumnTables.EntrySystem + "' AND " +
" B.CompanyID = " + objEmployeeSetup.CompanyID + " AND B.UserId = '" + objEmployeeSetup.EmployeeID + "' ORDER BY A.[TableName],A.[RelatedUserRoleID]";
dtItem = clsDataManipulation.GetData(connectionString, sqlString);
return(dtItem);
}
catch (Exception msgException)
{
throw msgException;
}
}
public DataTable GetRecord(string connectionString, TwoColumnTables objTwoColumnTables)
{
try
{
DataTable dtItem = null;
string sqlString = @"SELECT [TableID]
,[TableName]
FROM [sysTwoColumnTables] WHERE DataUsed = 'A' AND EntrySystem = '" + objTwoColumnTables.EntrySystem + "' AND RelatedUserRoleID = " + objTwoColumnTables.RelatedUserRoleID + " ORDER BY [TableName],[RelatedUserRoleID]";
dtItem = clsDataManipulation.GetData(connectionString, sqlString);
return(dtItem);
}
catch (Exception msgException)
{
throw msgException;
}
}
protected void grdTwoColumnTables_RowCommand(object sender, GridViewCommandEventArgs e)
{
int selectedIndex = Convert.ToInt32(e.CommandArgument.ToString());
string lblTableID = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblTableID")).Text;
if (e.CommandName.Equals("Select"))
{
try
{
string lblTableName = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblTableName")).Text;
string lblEntryMode = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblEntryMode")).Text;
string lblRelatedTo = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblRelatedTo")).Text;
string lblEntrySystem = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblEntrySystem")).Text;
string lblRelatedUserRoleID = ((Label)grdTwoColumnTables.Rows[selectedIndex].FindControl("lblRelatedUserRoleID")).Text;
txtTableName.Text = lblTableName;
txtEntryMode.Text = lblEntryMode;
txtRelatedTo.Text = lblRelatedTo;
ddlRelatedUserRoleID.SelectedValue = lblRelatedUserRoleID;
ddlEntrySystem.SelectedValue = lblEntrySystem;
btnSave.Text = "Update";
Session["selectedIndex"] = lblTableID;
}
catch (Exception msgException)
{
clsTopMostMessageBox.Show(msgException.Message);
}
}
else if (e.CommandName.Equals("Delete"))
{
try
{
_objTwoColumnTables = new TwoColumnTables();
_objTwoColumnTables.TableID = Convert.ToInt32(lblTableID);
_objTwoColumnTablesController = new TwoColumnTablesController();
_objTwoColumnTablesController.Delete(_connectionString, _objTwoColumnTables);
ShowRecord();
}
catch (Exception msgException)
{
clsTopMostMessageBox.Show(msgException.Message);
}
}
}
public void Save(string connectionString, TwoColumnTables objTwoColumnTables)
{
try
{
var storedProcedureComandTest = "exec [ACT_sysTwoColumnTables] " +
0 + ",'" +
objTwoColumnTables.TableName + "','" +
objTwoColumnTables.EntryMode + "','" +
objTwoColumnTables.RelatedTo + "'," +
objTwoColumnTables.RelatedUserRoleID + ",'" +
objTwoColumnTables.EntrySystem + "','" +
objTwoColumnTables.EntryUserName + "'";
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, storedProcedureComandTest);
}
catch (Exception msgException)
{
throw msgException;
}
}
public void Save(string connectionString, TwoColumnsTableDataAuto objTwoColumnsTableDataAuto)
{
try
{
DataTable dtDefaultData = new DataTable();
var storedProcedureComandTestTemp = "";
storedProcedureComandTestTemp = SqlCreateTable();
storedProcedureComandTestTemp += SqlDeleteGarbageData(objTwoColumnsTableDataAuto);
TwoColumnTables objTwoColumnTables = new TwoColumnTables();
objTwoColumnTables.TableID = objTwoColumnsTableDataAuto.TableID;
AutoTableData objAutoTableData = new AutoTableData(objTwoColumnTables);
dtDefaultData = objAutoTableData.ControlTableID();
foreach (DataRow dtRow in dtDefaultData.Rows)
{
objTwoColumnsTableDataAuto.EieldOfID = dtRow[0].ToString();
objTwoColumnsTableDataAuto.FieldOfName = dtRow[1].ToString();
var storedProcedureComandTest = "INSERT INTO [TwoColumnsTableAuto] ([TableID], [FieldOfID], [FieldOfName], [DataUsed], [EntryUserID], [EntryDate]) VALUES ( " +
objTwoColumnsTableDataAuto.TableID + ",'" +
objTwoColumnsTableDataAuto.EieldOfID + "', '" +
objTwoColumnsTableDataAuto.FieldOfName + "', '" +
"A" + "', '" +
"160ea939-7633-46a8-ae49-f661d12abfd5" + "'," +
"CAST(GETDATE() AS DateTime));";
storedProcedureComandTestTemp += storedProcedureComandTest;
}
storedProcedureComandTestTemp += SqlCreateView(objTwoColumnsTableDataAuto);
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, storedProcedureComandTestTemp);
}
catch (Exception msgException)
{
throw msgException;
}
}