public Supplier GetSupplier(int id) { Supplier supplier = new Supplier(); SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString); var commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," + "[ZIPCode],[FAXNo] FROM [dbo].[supplier] WITH(NOLOCK) WHERE id = '{0}'", id); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader.Read()) { supplier.Id = Convert.ToInt32(dataReader["Id"]); supplier.CompanyId = Convert.ToInt32(dataReader["CompanyId"]); supplier.Name = Convert.ToString(dataReader["Name"]); supplier.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]); supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]); supplier.EmailID = Convert.ToString(dataReader["EmailID"]); supplier.Address = Convert.ToString(dataReader["Address"]); supplier.City = Convert.ToString(dataReader["City"]); supplier.State = Convert.ToString(dataReader["State"]); supplier.Country = Convert.ToString(dataReader["Country"]); supplier.ZIPCode = Convert.ToString(dataReader["ZIPCode"]); supplier.FAXNo = Convert.ToString(dataReader["FAXNo"]); supplier.Address = Convert.ToString(dataReader["Address"]); supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]); } conn.Close(); } List <SupplierTerms> lstTerms = new List <SupplierTerms>(); commandText = string.Format("SELECT [id],[supplierId],[sequenceNo],[terms] FROM [dbo].[supplierterms] WITH(NOLOCK) WHERE supplierid = '{0}'", supplier.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var terms = new SupplierTerms(); terms.Id = Convert.ToInt32(dataReader1["Id"]); terms.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]); terms.Terms = Convert.ToString(dataReader1["Terms"]); lstTerms.Add(terms); } } supplier.Terms = lstTerms; conn.Close(); return(supplier); }
public async Task <IEnumerable <Supplier> > GetAllSupplierAsync(int companyId, int userId) { List <Supplier> suppliers = new List <Supplier>(); var userInfo = await userRepository.GeUserbyIdAsync(userId); var commandText = ""; if (userInfo.UserTypeId == 1) { commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," + "[ZIPCode],[FAXNo],[DateFormat],[noofstages],[CompanyProfileID],[PoLetterHead] FROM [dbo].[supplier] WITH(NOLOCK) WHERE CompanyId = '{0}'", companyId); } if (userInfo.UserTypeId == 2) { return(suppliers); } if (userInfo.UserTypeId == 3) { string companylist = string.Join(",", userInfo.CompanyIds); commandText = string.Format("SELECT [id],[CompanyId],[Name],[ContactPersonName],[PhoneNo],[EmailID],[Address],[City],[State],[Country]," + "[ZIPCode],[FAXNo],[DateFormat],[noofstages],[CompanyProfileID],[PoLetterHead] FROM [dbo].[supplier] WITH(NOLOCK) WHERE CompanyId = '{0}' and [id] in ({1})", companyId, companylist); } SqlConnection conn = new SqlConnection(ConnectionSettings.ConnectionString); using (SqlCommand cmd = new SqlCommand(commandText, conn)) { cmd.CommandType = CommandType.Text; conn.Open(); var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection); while (dataReader.Read()) { var supplier = new Supplier(); supplier.Id = Convert.ToInt32(dataReader["Id"]); supplier.CompanyId = Convert.ToInt32(dataReader["CompanyId"]); supplier.Name = Convert.ToString(dataReader["Name"]); supplier.ContactPersonName = Convert.ToString(dataReader["ContactPersonName"]); supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]); supplier.EmailID = Convert.ToString(dataReader["EmailID"]); supplier.Address = Convert.ToString(dataReader["Address"]); supplier.City = Convert.ToString(dataReader["City"]); supplier.State = Convert.ToString(dataReader["State"]); supplier.Country = Convert.ToString(dataReader["Country"]); supplier.ZIPCode = Convert.ToString(dataReader["ZIPCode"]); supplier.FAXNo = Convert.ToString(dataReader["FAXNo"]); supplier.Address = Convert.ToString(dataReader["Address"]); supplier.PhoneNo = Convert.ToString(dataReader["PhoneNo"]); supplier.DateFormat = Convert.ToString(dataReader["DateFormat"]); supplier.noofstages = Convert.ToInt32(dataReader["noofstages"]); supplier.CompanyProfileID = Convert.ToInt32(dataReader["CompanyProfileID"]); supplier.PoLetterHead = Convert.ToInt32(dataReader["PoLetterHead"]); suppliers.Add(supplier); } conn.Close(); } foreach (Supplier supplier in suppliers) { List <SupplierTerms> lstTerms = new List <SupplierTerms>(); commandText = string.Format("SELECT [id],[supplierId],[sequenceNo],[terms] FROM [dbo].[supplierterms] WITH(NOLOCK) WHERE supplierid = '{0}'", supplier.Id); using (SqlCommand cmd1 = new SqlCommand(commandText, conn)) { cmd1.CommandType = CommandType.Text; conn.Open(); var dataReader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection); while (dataReader1.Read()) { var terms = new SupplierTerms(); terms.Id = Convert.ToInt32(dataReader1["Id"]); terms.SequenceNo = Convert.ToInt32(dataReader1["SequenceNo"]); terms.Terms = Convert.ToString(dataReader1["Terms"]); lstTerms.Add(terms); } } supplier.Terms = lstTerms; conn.Close(); } return(suppliers.OrderBy(x => x.Name)); }