internal ContractData[] GetUserContracts(string clientRegno) { List<ContractData> result = new List<ContractData>(); try { using (SqlConnection dbConn = new SqlConnection(connectionString)) { string qsGetContract = @"SELECT CONTRACT.ID, CONTRACT.CLIENT_ID, coalesce(CONTRACT.START_DATE, CONTRACT.CLIENT_START_DATE), CONTRACT.END_DATE, CONTRACT.AREA_ID, CONTRACT.FACILITY_ID, AREA.NAME, FACILITY.ADDRESS, AREA.NETWORK_OWNER, CLIENT.NAME AS Expr1, CLIENT.REG_NR, CLIENT.ADDRESS AS Expr2, CLIENT.PHONE, CLIENT.EMAIL FROM CONTRACT INNER JOIN AREA ON CONTRACT.AREA_ID = AREA.ID INNER JOIN FACILITY ON CONTRACT.FACILITY_ID = FACILITY.ID INNER JOIN CLIENT ON CLIENT.ID = CONTRACT.CLIENT_ID where CLIENT.REG_NR=@CLIENT_REGNO"; SqlCommand dbCommand = new SqlCommand(qsGetContract, dbConn); dbCommand.Parameters.AddWithValue("@CLIENT_REGNO", Int64.Parse(clientRegno)); dbConn.Open(); SqlDataReader dbReader = dbCommand.ExecuteReader(); while (dbReader.Read()) { try { ContractData cd = new ContractData(); cd.ContractId = dbReader.GetInt64(0); cd.ClientId = dbReader.GetInt64(1); cd.StartDate = (dbReader.GetValue(2) == DBNull.Value) ? DateTime.Now : dbReader.GetDateTime(2); cd.EndDate = (dbReader.GetValue(3) == DBNull.Value) ? DateTime.MinValue : dbReader.GetDateTime(3); cd.AreaId = dbReader.GetInt64(4); cd.FacilityId = dbReader.GetInt64(5); cd.AreaName = dbReader.GetString(6); cd.FacilityAddress = dbReader.GetString(7); cd.AreaNetworkOwner = dbReader.GetString(8); cd.ClientName = dbReader.GetString(9); cd.ClientRegNo = dbReader.GetInt64(10); cd.ClientAddress = dbReader.GetString(11); cd.ClientPhone = dbReader.GetString(12); cd.ClientEmail = dbReader.GetString(13); result.Add(cd); } catch (Exception rowExc) { Debug.WriteLine("Error reading contract row: " + rowExc.Message); } } dbReader.Close(); dbCommand.Connection.Close(); } } catch (Exception contractDataExc) { Debug.WriteLine("Error getting user contracts: " + contractDataExc.Message); } return result.ToArray(); }
public ContractData[] Frontend_GetContractsByClientRegno(string clientRegno) { Trace.WriteLine("Frontend_GetContractsByClientRegno:" + clientRegno); ContractData[] result = new ContractData[0]; DataManager dataMan = new DataManager(); result = dataMan.GetUserContracts(clientRegno); return result; }