コード例 #1
0
        public async Task <BaseResponse> DeleteContact(ContactData contactData)
        {
            this.logger.LogInfoMessage(this.GetType(), "Begin DeleteContact");

            BaseResponse response = new BaseResponse();

            try
            {
                ParamArray ps = new ParamArray();

                ps.Add(new SqlParameter("@ContactID", SqlDbType.BigInt)
                {
                    Value = contactData.ContactID
                });
                await this.ExecuteSP_NonQuery("sproc_DeleteContact", ps);

                response.Success();
            }
            catch (Exception ex)
            {
                response.RunTimeException(ex, "Exception in DeleteContact");
                this.logger.LogErrorMessage(this.GetType(), "Exception in DeleteContact", ex);
            }

            this.logger.LogInfoMessage(this.GetType(), String.Format("End DeleteContact:{0}", response.ReturnValueInt));

            return(response);
        }
コード例 #2
0
ファイル: SQLDB.cs プロジェクト: vijaylonkar/EHExercise
        public async Task <int> ExecuteSP_NonQuery(String strSPName, ParamArray ps)
        {
            return(await Task.Run(() =>
            {
                Int32 ReturnValue = 0;
                Int32 nRowCount = 0;
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandText = strSPName;
                        cmd.CommandType = CommandType.StoredProcedure;
                        foreach (SqlParameter parm in ps)
                        {
                            cmd.Parameters.Add(parm);
                        }
                        SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
                        retval.Direction = ParameterDirection.ReturnValue;

                        cmd.CommandTimeout = queryTimeout;
                        nRowCount = cmd.ExecuteNonQuery();
                        if (cmd.Parameters["return_value"].Value != System.DBNull.Value)
                        {
                            ReturnValue = Convert.ToInt32(cmd.Parameters["return_value"].Value);
                        }
                    }
                }
                return ReturnValue;
            }));
        }
コード例 #3
0
ファイル: SQLDB.cs プロジェクト: vijaylonkar/EHExercise
        public async Task <DataSet> ExecuteSP(String storedProcName, ParamArray ps)
        {
            return(await Task.Run(() =>
            {
                DataSet dataset = new DataSet();
                using (var connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = storedProcName;
                        foreach (SqlParameter parm in ps)
                        {
                            cmd.Parameters.Add(parm);
                        }
                        cmd.CommandTimeout = queryTimeout;

                        using (SqlDataAdapter adapter = new SqlDataAdapter())
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(dataset);
                        }
                    }
                }
                return dataset;
            }));
        }
コード例 #4
0
        public async Task <BaseResponse> EditContact(ContactData contactData)
        {
            this.logger.LogInfoMessage(this.GetType(), "Begin EditContact");

            BaseResponse response = new BaseResponse();

            try
            {
                ParamArray ps = new ParamArray();

                ps.Add(new SqlParameter("@ContactID", SqlDbType.BigInt)
                {
                    Value = contactData.ContactID
                });
                ps.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 96)
                {
                    Value = contactData.FirstName
                });
                ps.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 96)
                {
                    Value = contactData.LastName
                });
                ps.Add(new SqlParameter("@EmailAddress", SqlDbType.NVarChar, 256)
                {
                    Value = contactData.EmailAddress
                });
                ps.Add(new SqlParameter("@PhoneNumber", SqlDbType.NVarChar, 32)
                {
                    Value = contactData.PhoneNumber
                });

                await this.ExecuteSP_NonQuery("sproc_EditContact", ps);

                response.Success();
            }
            catch (Exception ex)
            {
                response.RunTimeException(ex, "Exception in EditContact");
                this.logger.LogErrorMessage(this.GetType(), "Exception in EditContact", ex);
            }

            this.logger.LogInfoMessage(this.GetType(), String.Format("End EditContact:{0}", response.ReturnValueInt));

            return(response);
        }
コード例 #5
0
        public async Task <BaseResponse> AddContact(ContactData contactData)
        {
            this.logger.LogInfoMessage(this.GetType(), "Begin AddContact");
            BaseResponse response = new BaseResponse();

            try
            {
                //response = await this.Add("contacts", JsonConvert.SerializeObject(contact));
                ParamArray ps = new ParamArray();

                ps.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 96)
                {
                    Value = contactData.FirstName
                });
                ps.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 96)
                {
                    Value = contactData.LastName
                });
                ps.Add(new SqlParameter("@EmailAddress", SqlDbType.NVarChar, 256)
                {
                    Value = contactData.EmailAddress
                });
                ps.Add(new SqlParameter("@PhoneNumber", SqlDbType.NVarChar, 32)
                {
                    Value = contactData.PhoneNumber
                });
                SqlParameter outNewContactIDParam = ps.AddParam("@NewContactID", SqlDbType.BigInt, null, ParameterDirection.Output);

                await this.ExecuteSP_NonQuery("sproc_AddContact", ps);

                response.ReturnValueCustomObject = outNewContactIDParam.Value;
                response.Success();
            }
            catch (Exception ex)
            {
                response.RunTimeException(ex, "Exception in AddContact");
                this.logger.LogErrorMessage(this.GetType(), "Exception in AddContact", ex);
            }

            this.logger.LogInfoMessage(this.GetType(),
                                       String.Format("End AddContact:{0}", response.ReturnValueInt));

            return(response);
        }
コード例 #6
0
        public async Task <BaseResponse> GetContacts()
        {
            this.logger.LogInfoMessage(this.GetType(), "Begin GetContacts");

            BaseResponse response = new BaseResponse();

            try
            {
                ParamArray ps = new ParamArray();
                DataSet    ds = await this.ExecuteSP("sproc_GetContact", ps);

                response.ReturnValueCustomObject = this.Convert(ds);
                response.Success();
            }
            catch (Exception ex)
            {
                response.RunTimeException(ex, "Exception in GetContacts");
                this.logger.LogErrorMessage(this.GetType(), "Exception in GetContacts", ex);
            }

            this.logger.LogInfoMessage(this.GetType(), String.Format("End GetContacts:{0}", response.ReturnValueInt));

            return(response);
        }