C# - Using A Trigger To Record Audit Information Vs. Stored Procedure?
Apr 19, 2010
Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving this? I know I could remove the trigger and have the delete stored procedure insert the audit entry prior to deleting but are there any other recommeded alternative?
If a username was passed as a parameter to the delete stored procedure, is there anyway to get this value in the trigger that's excuted when the record is deleted? I'm just throwing this out there...
I'm using VB.net 2.0 with a SQL server (2005) backend db. I've got a very odd problem. I'm trying to use a stored procedure with a parameter to delete a record from a table. I've tested the stored procedure by running it directly from visual studio and it works fine, but when running it through my application nothing happens. There's no error and it appears to have worked but the record is not deleted.
I've checked permissions on the backend db and giving the network ASPNet account the db owner role has no effect either.
Here's the code, there are 3 layers - the webform code, then the business logic class, then the dataconnection class.
'firstly the original code that is run from the webform when the user selects to delete the record
Partial Public Class NewPerson
Inherits System.Web.UI.Page Private newConn As New DBConnection Private currentNewRequest As New NewRequest(newConn) Protected Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click Dim requestID As Integer = CInt(Me.lblRecordID.Text) Me.currentNewRequest.newRequestID = requestID Me.newConn.openConnection() Me.currentNewRequest.DeleteRecord() Me.newConn.closeConnection() End Sub End Class 'the business logic class that is called to set the parameters etc. The newRequestID integer is set at 19, I have checked this out. Public Class NewRequest Private dBConnection As DBConnection Public newRequestID As Integer Public Sub New(ByVal dBConnection As DBConnection) 'check params If dBConnection Is Nothing Then Throw New ArgumentNullException("dBConnection") End If Me.dBConnection = dBConnection End Sub Public Sub DeleteRecord() 'This sub deletes a request With Me.dBConnection .AddParameter("@newrequestID", SqlDbType.Int, 0, newRequestID) .ExecNonQuerySP("sp_DeleteNewRequest") End With End Sub End Class 'then the data connection class Imports System.Data.SqlClient Public Class DBConnection Private _ParamList As SqlParameterCollection Private _MyComm As New SqlCommand Private _ConnectionStr As String = ConfigurationManager.ConnectionStrings("Conn").ToString 'shared connection Dim conn As New SqlConnection(_ConnectionStr) 'the parameter is added Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As Data.SqlDbType, ByVal Paramlength As Int16, ByVal ParamValue As String) MyComm.Parameters.Add(ParamName, paramType, Paramlength) If ParamValue = Nothing Or ParamValue = "00:00:00" Or ParamValue = "" Then _MyComm.Parameters(ParamName).Value = DBNull.Value Else _MyComm.Parameters(ParamName).Value = ParamValue End If End Sub 'and this is where the stored procedure is supposed to run, no errors are generated Public Sub ExecNonQuerySP(ByVal SPName As String) Dim MyAdapter As New SqlDataAdapter(_MyComm) _MyComm.CommandType = CommandType.StoredProcedure _MyComm.CommandText = SPName _MyComm.Connection = conn If Not _MyTrans Is Nothing Then _MyComm.Transaction = _MyTrans End If Try _MyComm.ExecuteNonQuery() Catch ex As Exception Dim errorMessage As String = ex.GetType.ToString & " " & ex.Message.ToString System.Web.HttpContext.Current.Session("DBError") = "True" System.Web.HttpContext.Current.Session("DBEx") = errorMessage Throw ex Finally End Try End Sub Public Sub openConnection() conn.Open() End Sub Public Sub closeConnection() conn.Close() End Sub End Class
The stored procedure looks like this:
ALTER PROCEDURE sp_DeleteNewRequest /*this sp is used to delete a new account request record*/ @newrequestID int AS DELETE FROM NewAccountRequest WHERE NewRequestID = @newrequestID
ALTER PROCEDURE Pro_members_Insert @id int outPut, @LoginName nvarchar(50), @Password nvarchar(15), @FirstName nvarchar(100), @LastName nvarchar(100), @signupDate smalldatetime, @Company nvarchar(100), @Phone nvarchar(50), @Email nvarchar(150), @Address nvarchar(255), @PostalCode nvarchar(10), @State_Province nvarchar(100), @City nvarchar(50), @countryCode nvarchar(4), @active bit, @activationCode nvarchar(50) AS declare @usName as varchar(50) set @usName='' select @usName=isnull(LoginName,'') from members where LoginName=@LoginName if @usName <> '' begin set @ID=-3 RAISERROR('User Already exist.', 16, 1) return end set @usName='' select @usName=isnull(email,'') from members where Email=@Email if @usName <> '' begin set @ID=-4 RAISERROR('Email Already exist.', 16, 1) return end declare @MemID as int select @memID=isnull(max(ID),0)+1 from members INSERT INTO members (................................
When I run this page, signup.aspx, provide required fields and click submit, the page simply reloads and the database table does not reflect the newly-inserted record. How do I catch the error messages that might be returned from the sproc? how to change signup.aspx so that the insert occurs.
After this and in the same procedure i want to update the inserted or the updated record which occared in the above code with the follwing update statment :
[Code]....
updating mark is completed successfully if the record is existed (Update Case) but if the record does not exist (new record) ... the update of the mark is not occured ..
I am trying to use a stored procedure to insert new records from aspx page. I have created an stored procedure for that and it works fine but I want to display an error message when record exist. It does not display any error message however it does not insert when record exist. How can I display an error message when record exist flag in stored procedure -
I have a question regarding the record audit trail with EF. Recently we have started working on a application that has to have an record audit trail.Just few tables have Guid as PK, the rest of them have Int32 standard Identity setup.
The quick solution we have found is to wire up an event handler on SavingChanges and get all Added, Deleted and Modified records. But the problem is that the Inserted records for the tables that have Int32 as PK are audited with PK value of 0.
I have QuoteNumber(ddquotes.text),partnumber(txtpart),Lastmodifieddate(txtLMD) which is a time stamp on my webform.I am sending all these information to my vendaor as an email with attachment .
When i click on email button a message should be recorded in a Textbox saying so and so quotenumber,so and so partnumber has been sent on ,so and so datetime to so and so vendor.how can i acheive this with single button click.
I am trying to update record via stored procedure, but i got error at very start point. Problem is when i click on Edit link button within the Gridview it produce error.
I can populate values from database fine but its produce error when i click on edit link button. see the code below.
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
i have a stored procedure with this values and i need just to make a button that will send something to "Equip" column,how to do it? write the values on my webpage and make a button to exec the procedu
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float) RETURNS float AS BEGIN RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) ) END sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance from business [code]...
I have created stored procedure and student database and also asp.net application for asp.net page but it could not found stored procedure what is the mistake actually I don't no
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
I have a stored procedure which is working fine and also code in my vb class, which when I click on save, looks as though it has executed but no information is saved into the database....
My code used to save the data into the database is below.
