DataSource Controls :: How To Record Audit Trail In EF
May 24, 2010
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.
We are developing a web application using asp.net and sql server. We are required to do an Audit trail for the application. As I understand this, an audit trail would basically be for all the Inserts, Updates and Deletes on the data base right? Now the way to approach this is that I have an Audit Trail Table in the DB that populates after every insert,update or delete is fired (Manually writing the script within the DAL). However any DB changes directly fired from SQL Management studio will NOT be recorded (for obvious reasons :P).
To cater for that I could create a trigger and that takes care of everything. I also did some googling and found out that SQL server has the ability to do audit trail. However the problem with using triggers is that I will NOT get the user information that logged in the website. I will get the sql user but I dont give two hoots about that, I am concerned about the website user. A solution that I figured out was either
a) Have an audit trail from my web application and also have trigger set up. On the audit report, I simply show an audit log from web application and and audit log from sql server.Obvious problems with this approach: over head. Writing to two different sets of tables on EVERY DB CHANGE.
b) I have a column called UserId ON EVERY DB TABLE. Then I create a trigger to capture all the DB changes. I pass this userId on every table I change (insert,update,delete) and the get this id from the trigger.
Obvious setbacks: unneccesary userid column in every table I do appologize for the long post. Basically I need an audit log that does log all the db changes (including direct hack to db) but at the same times gives me user login information for those db changes that were made FROM the web application.
I want to implement audit trail in my project. This implies keeping track of changes made in DB and later generating a report for specific data.
I'm looking for solutions other than triggers as in to have a generic solution for DB Audit trail in my project.
We'll probably be using Entity Framework 4.0. I want to explore the audit trail capabilities for the same any pointers in this directions would be helpful. also any ideas for DB Audit trail if we use Enterprise Library Data Application Block. Note:SQL Server 2008 has auditing features, but we'll not be using the Enterprise version.
I am in the process of creating an audit trail system. Simple enough. Certain fields require audit trail. What is the best design concept to allow this to work in multiple applications without having to change much? I would of course leave it to the admin of the site to add what fields should be audited, but the logic is the problem. Would implementing the interface IComparable be a place to start? My initial thinking is to compare 2 arrays against eachother and do an insert of what fields are in question. Array1 are the fields in the form, array2 are the fields from the sql table that require auditing.
My ASP.NET intranet web application uses Windows Authentication, and I would like to record the following details:
1) Windows ID 2) Session Start Time 3) Session Stop Time 4) URL being browsed to (optional)
I've got some basic code setup in "Session_Start" method of the Global.ASAX to log session start times (seen below), but that's it so far. I have the feeling this is a primitive approach and there are "better" ways of doing this. So I really have two questions:
1) Is this the right way to go about doing this? If not what are some other options? 2) If this is the right way, do I just need to drop some code in the "Session_End" method to record the time they exit, and thats a complete solution? Does this method always get called when they close the browser tab they have the site open in, or do they have to close the entire browser (I don't have logout functionality)? Any way users can skip over this session end method (or start for that case)?
Dim connsql As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionstring").ConnectionString) Dim cmdsql As System.Data.SqlClient.SqlCommand = connsql.CreateCommand cmdsql.CommandText = "BeginUserSession" cmdsql.CommandType = Data.CommandType.StoredProcedure Try cmdsql.Parameters.Add("@windowsid", System.Data.SqlDbType.VarChar, 30, "windowsid") cmdsql.Parameters("@windowsid").Value = Session("UserInfo").identity.name If connsql.State <> System.Data.ConnectionState.Open Then connsql.Open() cmdsql.ExecuteNonQuery() connsql.Close() Catch ex As Exception Finally If connsql.State <> Data.ConnectionState.Closed Then connsql.Close() End Try
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.
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 am counting from a table for that i have written code as below
protected void get_Id_Afterpoint() { int counter = 0; string strSql = "select count(*) as ID from tblEnergy where ID=?"; OdbcCommand com = new OdbcCommand(strSql, con); com.Parameters.AddWithValue("ID", DropDownList1.SelectedValue); OdbcDataAdapter oda = new OdbcDataAdapter(com); DataTable dt = new DataTable(); oda.Fill(dt); if (dt.Rows.Count == 0) { lblID2.Text = "1"; } else { counter = dt.Rows.Count; counter = counter + 1; lblID2.Text = Convert.ToString(counter); } }
there is no record related to DropDownList1.SelectedValue. but as i am counting if(dt.rows.count) and i put break point on the bolded part it shows 1 record. how it can be possible?
I am creating an album using the following stored procedure, which returns the primary key for the newly created record:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[fpa_sp_albums_update_insert] @album_id int, @album_name nvarchar (50), @album_descr nvarchar(250), @album_img_cover_id_FK int, @album_creation_date date AS If @album_id > 0 UPDATE [fpa_albums] SET album_name=@album_name, album_descr= @album_descr, album_img_cover_id_FK = @album_img_cover_id_FK, album_creation_date = @album_creation_date Where ((album_id = @album_id)) Else INSERT INTO [fpa_albums] ( album_name, album_descr, album_img_cover_id_FK, album_creation_date) VALUES ( @album_name, @album_descr, @album_img_cover_id_FK, @album_creation_date) Return SCOPE_IDENTITY();
I execute the above stored procedure using the SQLHELPER. The VB.NET code for the page is as follows:
[Code]....
However, the createdAlbumID always shows -1 instead of the id for the newly created album.
I'm very new to this area and am using ASP.NET 3.5 with WMD 2008 Express Edition and VB code.
How do I write records to the SQL database using VB? I want to use the connection string, etc used in the ASP.NET controls if possible.
My website is a Rugby Club site and I've an SQL database of match fixtures which include TeamId(1XV, 2XV, etc.), Season(2008, 2009, etc) and match date.
I allow some users to update the fixture details but want to insert a series of new fixture records for the neext season which contain just TeamId Season and Match Dates. I therefore want to take the season and Team Id from label fields and then create match dates by adding seven days to a start date.
There are several articles and blogs that are sort of about this subject but I haven't found one on point. Wondering if anyone has done this? Using VS2008 creating an ASP.NET 3.5 app with a strongly-typed dataset connecting to an MS SQL backend. In one TableAdapter, I have a custom Insert statement that inserts the record into the primary table. There are two other tables the also need records inserted into them and those records contain the record ID from the primary table. Here's an example of what I'm trying to accomplish:
If I have an ID, I would like to retrieve the next item that also has the same category as the item ID I have. So, for example, if I have ID=2, I would like to return the item with ID=4 (as item ID=3 has a different category).
Just have a problem, I want people to join a mailing list, I just have a textbox and button. When a user enter's their email address, the address and time are inserted into a database. However, if the same email is added again I would like for a message to appear saying the record already exists.
Can someone show me how to do this, here is my current code:
I have 3 pages, one to add a category, one to edit the category, and one to delete the category. The add and edit work fine. The delete is not working for some reason. I click the link on my site to delete it, and I'm just staring at the green bar in my browser taking a long time. The add and edit don't take long at all to perform their function. I'm thinking this is a connection string issue, but I don't see how it can be the case. It relies on web.config for it just like the other pages. Following is the delete page code behind and also delete cascade from the business logic area. After the page refreshes, I'm not getting any diagnostics or error messages which I have in the code. Doesn't this have to mean a db connection issue?
I followed this BRILLIANT article and found out how to make a data layer, i just need the insert method in this code to return the ID of the record inserted.
For example, when the user captures the details, the save button is clicked, i will want to display in a label "Your Reference Number is "
try and have look at the article so you will understand how the code is, so it will not be hard to edit my code.
I don't know if I'm posting this in the right place - I have a feeling it can't be too difficult but I can't seem to figure it out. I've come across this problem many times and I've never solved it. I would imagine the scenario is commonplace - I have a table of Products with a PK of ProductID, and a table of related images with a FK of ProductID. Each product can have many images, but for my initial display I want to display one product record with one image record. I have a Sequence field in the Images table and I could pick the lowest one. Right and left Joins return too many records - if I have three images for Product 1 then Product 1 is listed three times.
I've gotten around this before using a flag in the Products table, or listing an image in both tables but I'm sure there must be a SQL statement that can get the information that I need.
Im trying to get the ID out of the record that i'm inserting using scope_identity. I've add the following line of code to the Insert statements in the sqldatasource - SET @insID = Scope_Identity() I've add the following line to the Insert parameter list
I now want to insert a number records into the database using the following:
Dim insID As Object = e.Command.Parameters("@insID").Value ' gets out latest ID Dim ordernumber As String = "CX" & insID.ToString & DateTime.Now.DayOfYear.ToString sqlOrders.InsertParameters("orderNumber").DefaultValue = ordernumber sqlOrders.InsertParameters("orderDate").DefaultValue = DateTime.Now sqlOrders.InsertParameters("customerID").DefaultValue = userID sqlOrders.InsertParameters("productID").DefaultValue = productID sqlOrders.InsertParameters("totalPrice").DefaultValue = "0.00" sqlOrders.InsertParameters("status").DefaultValue = "Paid"
I currently have this code in the SQLDataSource_Inserting sub but the first line only works once the record is inserted. How can i get the ID out as im inserting the parameters?
I use the following code to insert recode to a sql 2008 table, the field ID of DBProgram is IDENTITY, so SQL 2008 SERVER will pass a value to it automatically. How can I get the ID value of the record I just insert ?
DBProgram dbProgram = new DBProgram(); db.DBPrograms.InsertOnSubmit(dbProgram); db.SubmitChanges();
I converted code from VB that works to c# to insert a record in an SQL daTA BASE.
The VB code is as follows:
Insert a new record into Contact Dim connectionString As String = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString").ConnectionString Dim insertSql As String = "INSERT INTO Contact(UserId, Notes) VALUES(@UserId, @Notes)" Using myConnection As New SqlConnection(connectionString)
I am using Microsoft SQL database, C# and stored procedures. How would I check if record exists on the form or in SQL. I will design a from that has employee name, id and 3 other fields.
If record exists (I could for example test if EmployeeId exists, and then display a message saying that "record already exists". Should I do it in Stored procedure or in code?
I have the logged-in user's Guid stored in a session variable. My understanding is that when the Guid is passed to the session variable it becomes a string. When I use the detailsview control I have to convert the session variable back to a guid and I've been doing it like this and it seems to work fine:
e.values("UserId") = session("LoggedInUserId") Dim UserId as Guid = New Guid()
When I try to use the Insert() command after the following:
I get an "InvalidCastException" error with the comment "Conversion from type 'Guid' to type 'String' is not valid." I'm assuming the session variable needs to be converted to a type Guid since that's what I've had to do with the Detailsview control. I've tried a few different ways of converting the session variable back to a Guid so the insert command will work, but no matter which method of conversion I try I get errors such as "value of type system.guid cannot be converted to 'string'. I'm confused because I thought I was supposed to convert it from a string to a Guid. The "UserId" parameter is a non-primary key uniqueidentifier in the database.
