C# MsSqlPersistence类代码示例

        public int GetDatabaseVersion()
        {
            try {
                int result = 0;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {

                            string sql = "IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'dbversion') SELECT dbversion FROM dbversion ELSE 	SELECT 0 AS dbversion";
                            DbCommand.CommandText = sql;
                            object value = DbConnection.ExecuteScalar(DbCommand);
                            if (value != DBNull.Value)
                                result = (int)value;
                        }
                    }
                    else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return result;
            }
            catch {
                throw new Exception("The database server is offline or unreachable");
            }
        }

        public int RemoveClientNote(int clientNoteId, int userId)
        {
            try {
                int rowsAffected;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspRemoveClientNote";
                            DbCommand.Parameters.Clear();
                            DbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = clientNoteId;
                            DbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
                            DbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;

                            rowsAffected = DbConnection.ExecuteCommand(DbCommand);
                        }
                    }else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return rowsAffected;
            }catch {
                throw;
            }
        }

 public Client GetClient(int id)
 {
     try {
         Client result = new Client();
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
             if (DbConnection.IsConnected()) {
                 using (DbCommand) {
                     result = this.GetClient(ref dbConnection, ref dbCommand, id);
                 }
             }else {
                 throw new Exception("Unable to Connect");
             }
         }
         return result;
     }catch {
         throw;
     }
 }

        public SmartCollection<Client> GetClientsRecent(int userId)
        {
            try {
                SmartCollection<Client> resultList = new SmartCollection<Client>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientsRecent";
                            DbCommand.Parameters.Clear();
                            DataTable clientDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in clientDT.Rows)
                            {
                                Client client = new Client();
                                client.ClientId = Convert.ToInt32(row["ClientID"]);
                                client.AccountingId = row["AccountingID"].ToString();
                                client.ClientName = row["ClientName"].ToString();
                                client.TermId = row["TermID"] != DBNull.Value ? (int)row["TermID"] : -1;
                                if (client.TermId != null && client.TermId != -1)
                                    client.Term = new Term() { TermId = (int)row["TermID"], TermName = row["TermName"].ToString() };
                                client.WebClientYN = row["WebClientYN"] != DBNull.Value ? Convert.ToBoolean(row["WebClientYN"]) : false;
                                client.CreditCheckYN = row["CreditCheckYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditCheckYN"]) : false;
                                client.CreditHoldYN = row["CreditHoldYN"] != DBNull.Value ? Convert.ToBoolean(row["CreditHoldYN"]) : false;
                                client.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false;
                                client.BillingAddress = row["BillingAddress"].ToString();
                                client.BillingCity = row["BillingCity"].ToString();
                                client.BillingStateId = row["BillingStateID"] != DBNull.Value ? (int)row["BillingStateID"] : -1;
                                if (client.BillingStateId != null && client.BillingStateId != -1)
                                    client.BillingState = new State() { StateId = (int)row["BillingStateID"], StateName = row["BillingStateName"].ToString() };
                                client.BillingZip = row["BillingZip"].ToString();
                                client.BillingCountryId = row["BillingCountryID"] != DBNull.Value ? (int)row["BillingCountryID"] : -1;
                                if (client.BillingCountryId != null && client.BillingCountryId != -1)
                                    client.BillingCountry = new Country() { CountryId = (int)row["BillingCountryID"], CountryName = row["BillingCountryName"].ToString() };
                                client.SameAsBillingYN = row["SameAsBillingYN"] != DBNull.Value ? Convert.ToBoolean(row["SameAsBillingYN"]) : false;
                                client.ShippingAddress = row["ShippingAddress"].ToString();
                                client.ShippingCity = row["ShippingCity"].ToString();
                                client.ShippingStateId = row["ShippingStateID"] != DBNull.Value ? (int)row["ShippingStateID"] : -1;
                                if (client.ShippingStateId != null && client.ShippingStateId != -1)
                                    client.ShippingState = new State() { StateId = (int)row["ShippingStateID"], StateName = row["ShippingStateName"].ToString() };
                                client.ShippingZip = row["ShippingZip"].ToString();
                                client.ShippingCountryId = row["ShippingCountryID"] != DBNull.Value ? (int)row["ShippingCountryID"] : -1;
                                if (client.ShippingCountryId != null && client.ShippingCountryId != -1)
                                    client.ShippingCountry = new Country() { CountryId = (int)row["ShippingCountryID"], CountryName = row["ShippingCountryName"].ToString() };
                                client.CreatedUser = row["CreatedUser"].ToString();
                                client.CreatedBy = row["CreatedBy"] != DBNull.Value ? (int)row["CreatedBy"] : -1;
                                client.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                                client.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? (int)row["ModifiedBy"] : -1;
                                client.ModifiedUser = row["ModifiedUser"].ToString();
                                client.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;

                                // Other client objects
                                client.Contacts = GetContacts(client.ClientId);
                                client.Prices = GetClientPricings(client.ClientId);
                                client.Documents = GetClientDocuments(client.ClientId);
                                client.Notes = GetClientNotes(client.ClientId);
                                client.Complaints = GetClientComplaints(client.ClientId);

                                resultList.Add(client);
                            }
                            clientDT = null;
                        }
                    }else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }catch {
                throw;
            }
        }

        public SmartCollection<ClientPricing> GetClientPricings(int ClientId)
        {
            try {
                SmartCollection<ClientPricing> resultList = new SmartCollection<ClientPricing>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspGetClientPricings";
                            dbCommand.Parameters.Clear();
                            dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = ClientId;

                            DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in returnDT.Rows) {
                                ClientPricing price = new ClientPricing();
                                price.ClientPricingId = Convert.ToInt32(row["ClientPricingID"]);
                                price.ClientId = Convert.ToInt32(row["ClientID"]);
                                if (row["MethodID"] != DBNull.Value)
                                    price.MethodId = Convert.ToInt32(row["MethodID"]);
                                if (row["MethodNumberID"] != DBNull.Value)
                                    price.MethodNumberId = Convert.ToInt32(row["MethodNumberID"]);
                                if (row["AnalyteID"] != DBNull.Value)
                                    price.AnalyteId = Convert.ToInt32(row["AnalyteID"]);
                                price.Description = row["Description"].ToString();
                                price.Discount = row["Discount"] != DBNull.Value ? Convert.ToDouble(row["Discount"]) : 0;
                                price.FlatRate = row["FlatRate"] != DBNull.Value ? Convert.ToDouble(row["FlatRate"]) : 0;
                                price.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32();
                                price.CreatedUser = row["CreatedUser"].ToString();
                                price.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                                price.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32();
                                price.ModifiedUser = row["ModifiedUser"].ToString();
                                price.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;

                                if (price.MethodId.HasValue)
                                    price.Method = new Method { MethodId = price.MethodId, MethodName = row["MethodName"].ToString() };
                                else
                                    price.Method = null;

                                if (price.MethodNumberId.HasValue)
                                    price.MethodNumber = new MethodNumber { MethodNumberId = price.MethodNumberId, MethodNumberName = row["MethodNumberName"].ToString() };
                                else
                                    price.MethodNumber = null;

                                if (price.AnalyteId.HasValue)
                                    price.AnalyteItem = new Analyte { AnalyteId = price.AnalyteId, AnalyteName = row["AnalyteName"].ToString() };
                                else
                                    price.AnalyteItem = null;

                                resultList.Add(price);
                            }
                            returnDT = null;
                        }
                    }else {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }catch {
                throw;
            }
        }

        public ClientNote GetClientNote(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? noteId)
        {
            try {
                ClientNote note = new ClientNote();
                if (dbConnection.IsConnected()) {
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.CommandText = "uspGetClientNote";
                    dbCommand.Parameters.Clear();
                    dbCommand.Parameters.Add("@ClientNoteId", System.Data.SqlDbType.Int).Value = noteId;

                    DataTable notesDT = dbConnection.ExecuteQuery(dbCommand);
                    if (notesDT.Rows.Count == 1) {
                        DataRow row = notesDT.Rows[0];
                        note.ClientNoteId = Convert.ToInt32(row["ClientNoteID"]);
                        note.ClientId = Convert.ToInt32(row["ClientID"]);
                        note.Note = row["Note"].ToString();
                        note.CopyToSampleYN = Convert.ToBoolean(row["CopyToSampleYN"]);
                        note.IncludeOnCOAYN = Convert.ToBoolean(row["IncludeOnCOAYN"]);
                        note.CreatedUser = row["CreatedUser"].ToString();
                        note.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                        note.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
                        note.ModifiedUser = row["ModifiedUser"].ToString();
                        note.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
                        notesDT = null;
                    }else {
                        notesDT = null;
                        return null;
                    }
                }else {
                    throw new Exception("Unable to Connect");
                }
                return note;
            }catch {
                throw;
            }
        }

        public SmartCollection<ClientDocument> GetClientDocuments(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId)
        {
            var result = new SmartCollection<ClientDocument>();

            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = "uspGetClientDocuments";
            dbCommand.Parameters.Clear();
            dbCommand.Parameters.AddWithValue("@ClientId", clientId);

            using (var reader = dbConnection.ExecuteReader(dbCommand)) {
                while (reader.Read()) {
                    result.Add(new ClientDocument {
                        ClientDocumentId = (int)reader["ClientDocumentID"],
                        Filename = reader["Filename"].ToString(),
                        CreatedBy = reader["CreatedBy"] != DBNull.Value ? Convert.ToInt32(reader["CreatedBy"]) : -1,
                        CreatedUser = reader["CreatedUser"].ToString(),
                        CreatedDate = reader["CreatedDate"] != DBNull.Value ? (DateTime)reader["CreatedDate"] : (DateTime)SqlDateTime.Null,
                        ModifiedBy = reader["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(reader["ModifiedBy"]) : -1,
                        ModifiedUser = reader["ModifiedUser"].ToString(),
                        ModifiedDate = reader["ModifiedDate"] != DBNull.Value ? (DateTime)reader["ModifiedDate"] : (DateTime)SqlDateTime.Null
                    });
                }
            }

            return result;
        }

        public byte[] GetClientDocumentData(int clientDocumentId)
        {
            try {
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientDocumentData";
                            DbCommand.Parameters.Clear();
                            DbCommand.Parameters.AddWithValue("@ClientDocumentId", clientDocumentId);
                            var result = DbConnection.ExecuteScalar(DbCommand);
                            if (result != null)
                                return result as byte[];
                            else
                                return new byte[0];
                        }
                    }
                }

                return null;
            }catch {
                throw;
            }
        }

        public int SaveClientComplaints(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ref Client client, int userId)
        {
            try {
                int returnValue = 0;
                string sql = string.Empty;
                foreach (ClientComplaint complaint in client.Complaints)
                {
                    if (complaint.IsDirty) {
                        SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
                            GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk != null ? complaint.Pk : -1),
                            complaint,
                            ModuleNames.Clients,
                            client.Pk,
                            userId);

                        dbCommand.Parameters.Clear();
                        sql = string.Empty;
                        if (complaint.ClientComplaintId == null || complaint.ClientComplaintId <= 0) {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspInsertClientComplaint";
                            dbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
                            dbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                        }else {
                            dbCommand.CommandType = CommandType.StoredProcedure;
                            dbCommand.CommandText = "uspUpdateClientComplaint";
                            dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
                            dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
                            dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                            dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = complaint.DeleteDate ?? SqlDateTime.Null;
                        }
                        dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = client.ClientId;
                        dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
                        dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Bit).Value = (bool)complaint.StatusYN;
                        dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
                        dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber != "" ? complaint.ARLNumber : SqlString.Null;
                        dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
                        dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
                        dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
                        dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
                        dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
                        returnValue += dbConnection.ExecuteCommand(dbCommand);
                    }
                }
                //Return Total Number of Inserted or Updated Records
                return returnValue;
            }catch {
                throw;
            }
        }

        public int SaveClientComplaint(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, ClientComplaint complaint, Identification identification)
        {
            try
            {
                int returnValue = 0;
                if (complaint.IsDirty)
                {
                    SystemDAO.SaveChangeAudit<ClientComplaint>(ref dbConnection, ref dbCommand,
                        GetClientComplaint(ref dbConnection, ref dbCommand, complaint.Pk),
                        complaint,
                        ModuleNames.Clients,
                        complaint.ClientId,
                        identification.UserId);

                    dbCommand.Parameters.Clear();
                    dbCommand.CommandType = CommandType.StoredProcedure;
                    dbCommand.CommandText = "uspUpdateClientComplaint";

                    dbCommand.Parameters.Add("@ClientComplaintId", System.Data.SqlDbType.Int).Value = complaint.ClientComplaintId;
                    dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = complaint.ClientId;
                    dbCommand.Parameters.Add("@ClassificationId", System.Data.SqlDbType.Int).Value = complaint.ClassificationId;
                    dbCommand.Parameters.Add("@StatusYN", System.Data.SqlDbType.Int).Value = (bool)complaint.StatusYN;
                    dbCommand.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 100).Value = complaint.Description;
                    dbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.NVarChar, 100).Value = complaint.ARLNumber;
                    dbCommand.Parameters.Add("@RootCause", System.Data.SqlDbType.NVarChar, 100).Value = complaint.RootCause;
                    dbCommand.Parameters.Add("@NotifyUserId", System.Data.SqlDbType.Int).Value = complaint.NotifyUserId;
                    dbCommand.Parameters.Add("@CorrectiveAction", System.Data.SqlDbType.NVarChar, 100).Value = complaint.CorrectiveAction;
                    dbCommand.Parameters.Add("@CorrectiveActionDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                    dbCommand.Parameters.Add("@CorrectiveActionUserId", System.Data.SqlDbType.Int).Value = complaint.CorrectiveActionUserId;
                    dbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = complaint.CorrectiveActionDate != null ? (SqlDateTime)complaint.CorrectiveActionDate : SqlDateTime.Null;
                    dbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = identification.UserId;
                    returnValue += dbConnection.ExecuteCommand(dbCommand);
                }
                return returnValue;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Save Client
        /// </summary>
        /// <returns></returns>
        public int SaveClient(Client client, Guid userToken, int userId)
        {
            try {
                int returnValue = -1;
                bool insertNewRecord = false;
                string sql = string.Empty;
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true)) {
                    if (DbConnection.IsConnected()) {
                        using (DbCommand) {
                            try { // Try Catch here allows other exceptions to rollback transactions
                                if (client.IsDirty) {
                                    SystemDAO.SaveChangeAudit<Client>(ref dbConnection, ref dbCommand,
                                        GetClient(ref dbConnection, ref dbCommand, client.Pk ?? 0),
                                        client,
                                        ModuleNames.Clients,
                                        client.Pk,
                                        userId);

                                    DbCommand.Parameters.Clear();
                                    if (client.ClientId == null || client.ClientId <= 0) {
                                        insertNewRecord = true;
                                        DbCommand.CommandType = CommandType.StoredProcedure;
                                        DbCommand.CommandText = "uspInsertClient";
                                        DbCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Int).Value = userId;
                                        DbCommand.Parameters.Add("@CreatedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                                    }else {
                                        DbCommand.CommandType = CommandType.StoredProcedure;
                                        DbCommand.CommandText = "uspUpdateClient";
                                        DbCommand.Parameters.Add("@ClientID", System.Data.SqlDbType.Int).Value = client.ClientId;
                                        DbCommand.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Int).Value = userId;
                                        DbCommand.Parameters.Add("@ModifiedDate", System.Data.SqlDbType.DateTime).Value = DateTime.Now;
                                    }
                                    DbCommand.Parameters.Add("@ClientName", System.Data.SqlDbType.Text, 100).Value = client.ClientName != null ? client.ClientName : SqlString.Null;
                                    DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId != null ? client.AccountingId : SqlString.Null;
                                    DbCommand.Parameters.Add("@TermId", System.Data.SqlDbType.Int).Value = client.Term.TermId != null ? client.Term.TermId : (int?)SqlInt32.Null;
                                    DbCommand.Parameters.Add("@CreditCheckYN", System.Data.SqlDbType.Bit).Value = client.CreditCheckYN != null ? client.CreditCheckYN : SqlBoolean.Null;
                                    DbCommand.Parameters.Add("@CreditHoldYN", System.Data.SqlDbType.Bit).Value = client.CreditHoldYN != null ? client.CreditHoldYN : SqlBoolean.Null;
                                    DbCommand.Parameters.Add("@WebClientYN", System.Data.SqlDbType.Bit).Value = client.WebClientYN != null ? client.WebClientYN : SqlBoolean.Null;
                                    DbCommand.Parameters.Add("@GMPYN", System.Data.SqlDbType.Bit).Value = client.GMPYN != null ? client.GMPYN : SqlBoolean.Null;
                                    DbCommand.Parameters.Add("@BillingAddress", System.Data.SqlDbType.Text, 100).Value = client.BillingAddress != null ? client.BillingAddress : SqlString.Null;
                                    DbCommand.Parameters.Add("@BillingCity", System.Data.SqlDbType.Text, 100).Value = client.BillingCity != null ? client.BillingCity : SqlString.Null;
                                    if (client.BillingState != null && client.BillingState.StateId != null)
                                        DbCommand.Parameters.Add("@BillingStateId", System.Data.SqlDbType.Int).Value = client.BillingState.StateId;
                                    DbCommand.Parameters.Add("@BillingZip", System.Data.SqlDbType.Text, 10).Value = client.BillingZip != null ? client.BillingZip : SqlString.Null;
                                    if (client.BillingCountry != null && client.BillingCountry.CountryId != null)
                                        DbCommand.Parameters.Add("@BillingCountryId", System.Data.SqlDbType.Int).Value = client.BillingCountry.CountryId;
                                    DbCommand.Parameters.Add("@SameAsBillingYN", System.Data.SqlDbType.Bit).Value = client.SameAsBillingYN != null ? client.SameAsBillingYN : SqlBoolean.Null;
                                    DbCommand.Parameters.Add("@ShippingAddress", System.Data.SqlDbType.Text, 100).Value = client.ShippingAddress != null ? client.ShippingAddress : SqlString.Null;
                                    DbCommand.Parameters.Add("@ShippingCity", System.Data.SqlDbType.Text, 100).Value = client.ShippingCity != null ? client.ShippingCity : SqlString.Null;
                                    if (client.ShippingState != null && client.ShippingState.StateId != null)
                                        DbCommand.Parameters.Add("@ShippingStateId", System.Data.SqlDbType.Int).Value = client.ShippingState.StateId;

                                    DbCommand.Parameters.Add("@ShippingZip", System.Data.SqlDbType.Text, 10).Value = client.ShippingZip != null ? client.ShippingZip : SqlString.Null;
                                    if (client.ShippingCountry != null && client.ShippingCountry.CountryId != null)
                                        DbCommand.Parameters.Add("@ShippingCountryId", System.Data.SqlDbType.Int).Value = client.ShippingCountry.CountryId;

                                    if (client.ClientId > 0)
                                        returnValue = DbConnection.ExecuteCommand(DbCommand);
                                    else {
                                        // returnValue = Primary Key Id
                                        returnValue = (int)DbConnection.ExecuteScalar(DbCommand);
                                        client.ClientId = returnValue;
                                    }
                                }

                                // Save Contacts
                                using (ClientDAO contactDao = new ClientDAO())
                                    contactDao.SaveContacts(ref dbConnection, ref dbCommand, ref client, userId);

                                // Save Pricing
                                this.SaveClientPricing(ref dbConnection, ref dbCommand, ref client, userId);

                                // Save Complaints
                                this.SaveClientComplaints(ref dbConnection, ref dbCommand, ref client, userId);

                                // Save Notes
                                this.SaveClientNotes(ref dbConnection, ref dbCommand, client, userId);

                                // Save Documents
                                this.SaveClientDocuments(ref dbConnection, ref dbCommand, client, userId);

                                // Accounting Interface
                                //if (insertNewRecord && Vars.AccountingSettings.InterfaceId > 0) {
                                //    client = new ClientInterface().ClientAdd(client);
                                //    if (!string.IsNullOrWhiteSpace(client.AccountingId)) {
                                //        sql = @"
                                //        UPDATE customers
                                //        SET accountingid = @AccountingId
                                //        WHERE id = @ID
                                //        ";
                                //        DbCommand.Parameters.Clear();
                                //        DbCommand.CommandText = sql;
                                //        DbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = client.ClientId;
                                //        DbCommand.Parameters.Add("@AccountingId", System.Data.SqlDbType.Text, 100).Value = client.AccountingId ?? string.Empty;
                                //        DbConnection.ExecuteCommand(DbCommand);
                                //    }
//.........这里部分代码省略.........

        public int RemoveContacts(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int clientId, int userId)
        {
            try
            {
                int returnValue = 0;

                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.CommandText = "uspRemoveContacts";
                dbCommand.Parameters.Clear();
                dbCommand.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = clientId;
                dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
                dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;

                returnValue = dbConnection.ExecuteCommand(dbCommand);

                return returnValue;
            }
            catch
            {
                throw;
            }
        }

 public int RemoveContact(int contactId, int userId)
 {
     try
     {
         DateTime deleteDate = System.DateTime.Now;
         int result = -1;
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings, true))
         {
             if (DbConnection.IsConnected())
             {
                 using (DbCommand)
                 {
                     dbCommand.CommandType = CommandType.StoredProcedure;
                     dbCommand.CommandText = "uspRemoveContact";
                     dbCommand.Parameters.Clear();
                     dbCommand.Parameters.Add("@ContactId", System.Data.SqlDbType.Int).Value = contactId;
                     dbCommand.Parameters.Add("@DeleteDate", System.Data.SqlDbType.DateTime).Value = System.DateTime.Now;
                     dbCommand.Parameters.Add("@UserId", System.Data.SqlDbType.Int).Value = userId;
                     result = dbConnection.ExecuteCommand(dbCommand);
                 }
             }
             else
             {
                 throw new Exception("Unable to Connect");
             }
         }
         return result;
     }
     catch
     {
         throw;
     }
 }

        public SmartCollection<ClientComplaint> GetClientComplaintsOpen(Identification identification)
        {
            try
            {
                SmartCollection<ClientComplaint> resultList = new SmartCollection<ClientComplaint>();
                using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
                {
                    if (DbConnection.IsConnected())
                    {
                        using (DbCommand)
                        {
                            DbCommand.CommandType = CommandType.StoredProcedure;
                            DbCommand.CommandText = "uspGetClientComplaintsOpen";
                            DbCommand.Parameters.Clear();

                            DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
                            foreach (DataRow row in returnDT.Rows)
                            {
                                ClientComplaint complaint = new ClientComplaint();
                                complaint.ClientComplaintId = Convert.ToInt32(row["ClientComplaintID"]);
                                complaint.ClientId = Convert.ToInt32(row["ClientID"]);
                                complaint.ClientName = row["ClientName"].ToString();
                                complaint.ClassificationId = row["ClassificationID"] != DBNull.Value ? Convert.ToInt32(row["ClassificationID"]) : -1;
                                if (complaint.ClassificationId.HasValue && complaint.ClassificationId != -1)
                                    complaint.Classification = new Complaint { ComplaintId = complaint.ClassificationId, ComplaintName = row["ComplaintName"].ToString(), Active = true };
                                complaint.Description = row["Description"].ToString();
                                complaint.ARLNumber = row["ARLNumber"].ToString();
                                complaint.StatusYN = (bool)(row["StatusYN"] ?? false);
                                complaint.RootCause = row["RootCause"].ToString();
                                complaint.CorrectiveAction = row["CorrectiveAction"].ToString();
                                if (row["CorrectiveActionDate"] != DBNull.Value)
                                    complaint.CorrectiveActionDate = (DateTime)row["CorrectiveActionDate"];
                                else
                                    complaint.CorrectiveActionDate = null;
                                complaint.CorrectiveActionUserId = row["CorrectiveActionUserID"] != DBNull.Value ? Convert.ToInt32(row["CorrectiveActionUserID"]) : -1;
                                complaint.CorrectiveActionUser = row["CorrectiveUser"].ToString();
                                complaint.NotifyUserId = row["NotifyUserID"] != DBNull.Value ? Convert.ToInt32(row["NotifyUserID"]) : -1;
                                complaint.NotifyUser = row["NotifyUser"].ToString();
                                complaint.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : -1;
                                complaint.CreatedUser = row["CreatedUser"].ToString();
                                complaint.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null;
                                complaint.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : -1;
                                complaint.ModifiedUser = row["ModifiedUser"].ToString();
                                complaint.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null;
                                resultList.Add(complaint);
                            }
                            returnDT = null;
                        }
                    }
                    else
                    {
                        throw new Exception("Unable to Connect");
                    }
                }
                return resultList;
            }
            catch
            {
                throw;
            }
        }

 public string GetClientComplaintsOpenCount(Identification identification)
 {
     try
     {
         using (DbConnection = new MsSqlPersistence(DbConnectionSettings))
         {
             if (DbConnection.IsConnected())
             {
                 using (DbCommand)
                 {
                     return this.GetClientComplaintsOpenCount(ref dbConnection, ref dbCommand, identification);
                 }
             }
             else
             {
                 throw new Exception("Unable to Connect");
             }
         }
     }
     catch
     {
         throw;
     }
 }