public List <GeneralCodeFile> GetGeneralCodeFileByFileTypeNFileLevel(Int32 FileTypeCode, Int32 LevelCode, Int32 COMPANY_CODE) { List <GeneralCodeFile> listGeneralCodeFile = new List <GeneralCodeFile>(); GeneralCodeFile objGeneralCodeFile; string vComTxt = @"SELECT [FileCode] ,[UserCode] ,[ActionType] ,[ActionDate] ,[FileId] ,[FileName] ,[FileShortName] ,[SortOrder] ,[ParentFileCode] ,[FileTypeCode] ,[LevelCode] ,[CompanyCode] ,[ModuleCode] ,[IsReadOnly] FROM [dbo].[General_CodeFile] where FileTypeCode=@FileTypeCode and LevelCode=@LevelCode AND CompanyCode = " + COMPANY_CODE + "" + " order by [SortOrder]"; SqlConnection connection = _supplierDbContext.GetConn(); connection.Open(); SqlDataReader dr; SqlCommand objDbCommand = new SqlCommand(vComTxt, connection); objDbCommand.Parameters.AddWithValue("FileTypeCode", FileTypeCode); objDbCommand.Parameters.AddWithValue("LevelCode", LevelCode); dr = objDbCommand.ExecuteReader(); while (dr.Read()) { objGeneralCodeFile = new GeneralCodeFile(); objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode"].ToString()); objGeneralCodeFile.UserCode = dr["UserCode"].ToString(); objGeneralCodeFile.ActionType = dr["ActionType"].ToString(); objGeneralCodeFile.FileId = dr["FileId"].ToString(); objGeneralCodeFile.FileName = dr["FileName"].ToString(); objGeneralCodeFile.FileShortName = dr["FileShortName"].ToString(); objGeneralCodeFile.SortOrder = dr["SortOrder"].ToString(); objGeneralCodeFile.ParentFileCode = dr["ParentFileCode"].ToString(); objGeneralCodeFile.FileTypeCode = Convert.ToInt32(dr["FileTypeCode"].ToString()); objGeneralCodeFile.LevelCode = Convert.ToInt32(dr["LevelCode"].ToString()); if (!string.IsNullOrEmpty(dr["CompanyCode"].ToString())) { objGeneralCodeFile.CompanyCode_FK = Convert.ToInt32(dr["CompanyCode"].ToString()); } objGeneralCodeFile.ModuleCode = dr["ModuleCode"].ToString(); if (!string.IsNullOrEmpty(dr["IsReadOnly"].ToString())) { objGeneralCodeFile.IsReadOnly = Convert.ToBoolean(Convert.ToInt16(dr["IsReadOnly"].ToString())); } listGeneralCodeFile.Add(objGeneralCodeFile); } objDbCommand.Dispose(); return(listGeneralCodeFile); }
public IActionResult SaveGeneralCodeFile(GeneralCodeFile objGeneralCodeFile) { objGeneralCodeFile.ActionDate = CommonValidation.FormatDate(DateTime.Today.ToString("dd-MM-yyyy"), "yyyy-mm-dd", "dd-mm-yyyy"); if (objGeneralCodeFile.FileCode_PK == 0) { objGeneralCodeFile.IsNew = true; objGeneralCodeFile.ActionType = "INSERT"; } else { objGeneralCodeFile.IsNew = false; objGeneralCodeFile.ActionType = "UPDATE"; } string Vmsg = _ICommon.SaveGeneralCodeFile(objGeneralCodeFile); return(Ok(new { message = Vmsg })); }
public string SaveGeneralCodeFile(GeneralCodeFile objGeneralCodeFile) { objGeneralCodeFile.TableName_TBL = GeneralCodeFile_TBL; ArrayList vQueryList = new ArrayList(); string vOut = "Exception Occured !"; int vResult = 0; int count = 0; int count_nm = 0; SqlConnection connection = _supplierDbContext.GetConn(); connection.Open(); using (SqlTransaction objDbTransaction = connection.BeginTransaction()) { try { // Query for checking duplicate FileCode string vValidationQuery = @"select Count(FileCode)cnt_filecode from " + GeneralCodeFile_TBL + " where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileId=" + objGeneralCodeFile.FileId + ""; // Query for checking duplicate name string vCheckDuplicateQuery = "select count(FileCode)cnt_filename from General_CodeFile where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileName='" + objGeneralCodeFile.FileName + "'"; if (string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode)) { vValidationQuery = vValidationQuery + " and ParentFileCode is null"; // Query for checking duplicate FileName vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode is null"; } else { vValidationQuery = vValidationQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + ""; // Query for checking duplicate FileName vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + ""; } if (!objGeneralCodeFile.IsNew) { vValidationQuery = vValidationQuery + " and FileCode!=@FileCode"; vCheckDuplicateQuery = vCheckDuplicateQuery + " and FileCode!=@FileCode"; } SqlDataReader drCommandValidation; //for checking duplicate FileCode SqlCommand objDbCommandValidation = new SqlCommand(vValidationQuery, connection); SqlDataReader drCommandCheck; //for checking duplicate FileName SqlCommand objDbCommandCheckDuplicate = new SqlCommand(vCheckDuplicateQuery, connection); objDbCommandValidation.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode); objDbCommandValidation.Parameters.AddWithValue("FileId", objGeneralCodeFile.FileId); objDbCommandCheckDuplicate.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode); objDbCommandCheckDuplicate.Parameters.AddWithValue("FileName", objGeneralCodeFile.FileName); if (!string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode)) { objDbCommandValidation.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode); objDbCommandCheckDuplicate.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode); } if (!objGeneralCodeFile.IsNew) { objDbCommandValidation.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK); objDbCommandCheckDuplicate.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK); } objDbCommandValidation.Transaction = objDbTransaction; drCommandValidation = objDbCommandValidation.ExecuteReader(); if (drCommandValidation.Read()) { //CommandValidationCount++; count = Convert.ToUInt16(drCommandValidation["cnt_filecode"].ToString()); } if (count > 0) { //connections.Close(); drCommandValidation.Close(); objDbTransaction.Rollback(); vOut = "Code is not unique within the scope"; } else { drCommandValidation.Close(); objDbTransaction.Rollback(); using (SqlTransaction objDbTransactions = connection.BeginTransaction()) { objDbCommandCheckDuplicate.Transaction = objDbTransactions; drCommandCheck = objDbCommandCheckDuplicate.ExecuteReader(); //int CommandCheckCount = 0; if (drCommandCheck.Read()) { count_nm = Convert.ToUInt16(drCommandCheck["cnt_filename"].ToString()); } if (count_nm > 0) { //connections.Close(); drCommandCheck.Close(); objDbTransactions.Rollback(); vOut = "Duplicate Name"; } else { drCommandCheck.Close(); objDbTransactions.Rollback(); using (SqlTransaction trans = connection.BeginTransaction()) { string vComTxt = @"select isnull(max(FileCode),0) + 1 as FileCode_PK from General_CodeFile"; //SqlConnection connection = _supplierDbContext.GetConn(); //connection.Open(); SqlDataReader dr; SqlCommand objDbCommand = new SqlCommand(vComTxt, connection, trans); dr = objDbCommand.ExecuteReader(); while (dr.Read()) { objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode_PK"].ToString()); } objDbCommand.Dispose(); //connections.Close(); dr.Close(); vQueryList.Add(GetQuery(objGeneralCodeFile)); vResult = 0; //SqlConnection connectionn = _supplierDbContext.GetConn(); //connectionn.Open(); try { using (SqlCommand command = _supplierDbContext.GetCommand()) { foreach (string obj_temp in vQueryList) { command.CommandText = obj_temp; command.Transaction = trans; vResult = command.ExecuteNonQuery(); } if (vResult > 0) { trans.Commit(); vOut = "Records Saved."; } else { vOut = " 0 Records Saved."; } //if (vResult > 0 && string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode) && objGeneralCodeFile.FileTypeCode == 27) //{ //} } } catch (DbException ex) { //connection.Close(); //trans.Rollback(); throw ex; } finally { connection.Close(); } return(vOut); } } } } } catch (DbException ex) { throw ex; } finally { connection.Close(); } return(vOut); } //using (SqlTransaction objDbTransaction = connections.BeginTransaction()) //{ // // Query for checking duplicate FileCode // string vValidationQuery = @"select Count(FileCode)cnt_filecode from " + GeneralCodeFile_TBL + " where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileId=" + objGeneralCodeFile.FileId + ""; // // Query for checking duplicate name // string vCheckDuplicateQuery = "select count(FileCode)cnt_filename from General_CodeFile where CompanyCode = 1 AND FileTypeCode=" + objGeneralCodeFile.FileTypeCode + " and FileName='" + objGeneralCodeFile.FileName + "'"; // if (string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode)) // { // vValidationQuery = vValidationQuery + " and ParentFileCode is null"; // // Query for checking duplicate FileName // vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode is null"; // } // else // { // vValidationQuery = vValidationQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + ""; // // Query for checking duplicate FileName // vCheckDuplicateQuery = vCheckDuplicateQuery + " and ParentFileCode =" + objGeneralCodeFile.ParentFileCode + ""; // } // if (!objGeneralCodeFile.IsNew) // { // vValidationQuery = vValidationQuery + " and FileCode!=@FileCode"; // vCheckDuplicateQuery = vCheckDuplicateQuery + " and FileCode!=@FileCode"; // } // SqlDataReader drCommandValidation; // //for checking duplicate FileCode // SqlCommand objDbCommandValidation = new SqlCommand(vValidationQuery, connections); // SqlDataReader drCommandCheck; // //for checking duplicate FileName // SqlCommand objDbCommandCheckDuplicate = new SqlCommand(vCheckDuplicateQuery, connections); // objDbCommandValidation.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode); // objDbCommandValidation.Parameters.AddWithValue("FileId", objGeneralCodeFile.FileId); // objDbCommandCheckDuplicate.Parameters.AddWithValue("FileTypeCode", objGeneralCodeFile.FileTypeCode); // objDbCommandCheckDuplicate.Parameters.AddWithValue("FileName", objGeneralCodeFile.FileName); // if (!string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode)) // { // objDbCommandValidation.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode); // objDbCommandCheckDuplicate.Parameters.AddWithValue("ParentFileCode", objGeneralCodeFile.ParentFileCode); // } // if (!objGeneralCodeFile.IsNew) // { // objDbCommandValidation.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK); // objDbCommandCheckDuplicate.Parameters.AddWithValue("FileCode", objGeneralCodeFile.FileCode_PK); // } // objDbCommandValidation.Transaction = objDbTransaction; // drCommandValidation = objDbCommandValidation.ExecuteReader(); // //int CommandValidationCount = 0; // if (drCommandValidation.Read()) // { // //CommandValidationCount++; // count = Convert.ToUInt16(drCommandValidation["cnt_filecode"].ToString()); // } // if (count > 0) // { // //connections.Close(); // drCommandValidation.Close(); // objDbTransaction.Rollback(); // vOut = "Code is not unique within the scope"; // } // else // { // drCommandValidation.Close(); // objDbTransaction.Rollback(); // } // using (SqlTransaction objDbTransactions = connections.BeginTransaction()) // { // objDbCommandCheckDuplicate.Transaction = objDbTransactions; // drCommandCheck = objDbCommandCheckDuplicate.ExecuteReader(); // //int CommandCheckCount = 0; // if (drCommandCheck.Read()) // { // count_nm = Convert.ToUInt16(drCommandCheck["cnt_filename"].ToString()); // } // if (count_nm > 0) // { // //connections.Close(); // drCommandCheck.Close(); // objDbTransactions.Rollback(); // vOut = "Duplicate Name"; // } // else // { // drCommandCheck.Close(); // objDbTransactions.Rollback(); // } // } // using (SqlTransaction trans = connections.BeginTransaction()) // { // string vComTxt = @"select isnull(max(FileCode),0) + 1 as FileCode_PK from General_CodeFile"; // //SqlConnection connection = _supplierDbContext.GetConn(); // //connection.Open(); // SqlDataReader dr; // SqlCommand objDbCommand = new SqlCommand(vComTxt, connections, trans); // dr = objDbCommand.ExecuteReader(); // while (dr.Read()) // { // objGeneralCodeFile.FileCode_PK = Convert.ToInt32(dr["FileCode_PK"].ToString()); // } // objDbCommand.Dispose(); // //connections.Close(); // dr.Close(); // vQueryList.Add(GetQuery(objGeneralCodeFile)); // vResult = 0; // //SqlConnection connectionn = _supplierDbContext.GetConn(); // //connectionn.Open(); // try // { // using (SqlCommand command = _supplierDbContext.GetCommand()) // { // foreach (string obj_temp in vQueryList) // { // command.CommandText = obj_temp; // command.Transaction = trans; // vResult = command.ExecuteNonQuery(); // } // if (vResult > 0) // { // trans.Commit(); // vOut = "Records Saved."; // } // else // vOut = " 0 Records Saved."; // //if (vResult > 0 && string.IsNullOrEmpty(objGeneralCodeFile.ParentFileCode) && objGeneralCodeFile.FileTypeCode == 27) // //{ // //} // } // } // catch (DbException ex) // { // //connection.Close(); // //trans.Rollback(); // throw ex; // } // finally // { // connections.Close(); // } // return vOut; // } //} }