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.
the code for the event handler looks like this:
[Code]....
View 2 Replies
Similar Messages:
Jul 13, 2010
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.
View 4 Replies
Oct 11, 2010
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.
View 1 Replies
Jun 9, 2010
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.
View 8 Replies
Feb 1, 2010
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
'Stored Proc records start time
View 3 Replies
Mar 11, 2010
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.
View 3 Replies
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...
View 4 Replies
Jun 24, 2010
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?
View 5 Replies
Dec 20, 2010
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.
View 1 Replies
Mar 31, 2010
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.
View 4 Replies
Feb 5, 2010
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:
[Code]....
View 3 Replies
Jan 28, 2010
I have the following xml:
[Code]....
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).
View 2 Replies
Apr 22, 2010
This is my table.
I want ms sql query to get result
ID ProcessID Processname Date
105 123
Sammying 2010-04-22 12:09:02.827
View 8 Replies
Mar 17, 2010
If I run the following SQL script:
[Code]....
View 3 Replies
May 14, 2010
best Way to find out if record exists?
[Code]....
View 2 Replies
Jan 6, 2010
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:
[Code]....
View 3 Replies
May 25, 2010
I need to get one record from rows. For example:
DB:
id, name, age, nick, gender , etc...
1, John, 18, gobb, M, ....
2, Andrew, 22 , AND, M, .....
3, Simon, 35, Lucky, M, ......
How to Get name (For example Simon) from DB and enter it into label? Or perhaps this is impossible?
View 2 Replies
Mar 5, 2010
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?
[Code]....
View 10 Replies
Jan 12, 2010
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.
[URL]
View 7 Replies
Feb 2, 2010
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.
View 5 Replies
Jun 9, 2010
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
asp:Parameter
Name="insID"
Type="Int32"
Direction="Output"
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?
View 3 Replies
May 25, 2010
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();
View 1 Replies
May 31, 2010
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)
[Code]....
View 3 Replies
Jul 12, 2010
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?
View 4 Replies
Jan 14, 2010
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:
SqlDataSource3.InsertParameters("UserId").DefaultValue = Session("LoggedInUserId")
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.
View 2 Replies
