Solved

Store Database / Gridview changes until Saved

Posted on 2010-11-16
19
688 Views
Last Modified: 2012-05-10
At this point I'm looking for a nudge in the right direction or a statement telling me this isn't possible...

Let's say I have a GridView that is linked to tblContacts. As it stands, the user can modify any of the displayed records within this GridView by choosing Edit, making the changes, and clicking Update which in turn updates the database in real-time which is okay; however, I would like to be informed of the changes that have taken place - I suppose via email. Ideally, the user would make all of the changes they desire within the GridView and then click Save Changes or the likes and the appropriate statements would be passed to the database along with an email fired off with a list of the changes made. Sorry for the rather broad topic. I'm unsure as to where I should start. Would I put all of the update/insert commands from the GridView into Storedprocedures which don't fire until Save Changes is clicked? :S TIA
0
Comment
Question by:alright
  • 6
  • 5
  • 5
  • +1
19 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34148337
This is what I use.

Hope it helps you.

I just converted it from vb to c#
protected void btnSendUpdated_Click()
{
	string projUpdater = Session("userid");
	SqlConnection Conn = default(SqlConnection);
	//Dim param As OleDbParameter
	//Dim cmdcommand As SqlCommand
	Conn = new SqlConnection("data source = servername;initial catalog = DBName;Integrated Security=SSPI;");
	Conn.Open();

	SqlCommand GetprojecTName = new SqlCommand("SELECT  fieldnames from tblContacts where id = " + Request("id") + ";", Conn);
	dynamic dr = GetprojecTName.ExecuteReader();
	if (dr.Read()) {
		field1 = dr.GetString(0);
		field2 = dr.GetString(1);
		field3 = dr.GetString(2);
		field4 = dr.GetString(3);
		field5 = dr.GetString(4);

	}

		//code for other email requests
		SmtpClient objSmtpClient = new SmtpClient("emailservername", 25);
		MailAddress objSender = new MailAddress("youremailAddress", "Your Name");
		MailMessage objMail = new MailMessage("youremailAddressv", "youremailAddress");
		objMail.To.Add(youremailAddressv);
		objMail.Subject = "Update Report";
		objMail.Body = "This is to inform you that <b>" + field1 + "</b>. has just been updated " This project is currently listed as <b>" + Status + ".</b> <br> Thank you ";
		objMail.IsBodyHtml = true;
		objSmtpClient.Send(objMail);
	}
	dr.Close();
}

Open in new window

0
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 34148403
In the gridview's RowUpdating event, you could build a mailmessage from the e object which contains OldValues and NewValues of the bounded fields in the the gridview.  This does send you the email before the actual save via the datasource's update method.  The storedprocedures are specified in the datasource.
0
 

Author Comment

by:alright
ID: 34149047
sammy: I don't know that the code converted over so well to C#. On top of that could you explain a bit of what is taking place? It looks as though it is only reading the values of the fields from that DataSource and emailing them.

cmdoprog2: Thank you - utilizing these commands I am able to pass the old and new values as strings, but it is going to get rather spammy if I'm receiving an email for every Item which is updated (say the user updates the information for 8 different contacts within the same GridView, that's 8 separate emails). Can you think of any clever way to store these values into strings that are then emailed as a group... maybe through the onbeforeunload command? Just brainstorming at this point
0
 
LVL 12

Assisted Solution

by:CmdoProg2
CmdoProg2 earned 62 total points
ID: 34149302
The problem with storing until the last change is that you are never sure when the last change was made.  
Here's a thought...
Depending upon your notification time requirement, you could have the SQL 2008 kept track of changes on your table through an update trigger into another table (an audit type table).  You would then have a periodic service then email the changes and their correspondding current record to you.  On the audit table you would have an indicator or use a timestamp to determine if the changes have already been sent.  With Active Directory, you can alse use SUSER_NAME() to determine the user that changed the data.
0
 

Author Comment

by:alright
ID: 34149712
While an interesting idea, I don't particularly care which user made the changes (each record can only be accessed by one user) or when the changes were made (the email will determine this for me, I think), only that a change was infact made and list the Old & New values. I was thinking that by using the onbeforeunload command the user would be oblivious to the fact that an email of changes was being sent. They would just close the browser window and go about their business.

I'm looking now at clumsily appending a Session["Test"] variable for each ItemUpdating command (there are several DetailsViews and GridViews, though...) and then sending that entire garbled mess of text as an email in the onbeforeunload command of the Page. Please save me from myself, this is embarassing:

        protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
        {
            if (e.OldValues[0].ToString() != e.NewValues[0].ToString())
            {
                Session["Test"] = Session["Test"] + " Field 0 old value: " + e.OldValues[0].ToString() + ". Field 0 new value: " + e.NewValues[0].ToString() + ".";
            }
            if (e.OldValues[1].ToString() != e.NewValues[1].ToString())
            {
                Session["Test"] = Session["Test"] + " Field 1 old value: " + e.OldValues[1].ToString() + ". Field 1 new value: " + e.NewValues[1].ToString() + ".";
            }
            if (e.OldValues[2].ToString() != e.NewValues[2].ToString())
            {
                Session["Test"] = Session["Test"] + " Field 2 old value: " + e.OldValues[2].ToString() + ". Field 2 new value: " + e.NewValues[2].ToString() + ".";
            }    
}
0
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 34149918
Instead of the Session, place a StringBuilder object and loop through fields then send the email in the page unload event..

 
public partial class WebForm1 : System.Web.UI.Page

{

  System.Text.StringBuilder sb;



    protected void Page_Load(object sender, EventArgs e)

    {

      sb = new System.Text.StringBuilder();



    }

    protected void Page_Unload(object sender, EventArgs e)

    {

      // send email here     



    }



    protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)

    {

      sb.Append("Changes to this record...");

      for (int i = 0; i < e.OldValues.Count; i++)

      {

        if (e.OldValues[i].ToString() != e.NewValues[i].ToString())

        {

          sb.AppendFormat("Field {0} old value: {1} Field {0} new value: {2}", i, e.OldValues[i].ToString(), e.NewValues[i].ToString());

        }

      }



    }

}

Open in new window

0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34150038
-->>It looks as though it is only reading the values of the fields from that DataSource and emailing them.

There is a where clause there.

Usually, you update *one* record at a time unless due to special circumstances.

So, each record that is updated, you get an email indicating that a record is updated.

That code is exactly what we use here, except that the version I posted is c#.


Only bit that is missing is calling the          
 btnSendUpdated_Click() event on your page_load() event or wherever your update code is.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34150066
To explain further, assume that you update a record where id=someid

   you retrieve the same record you just updated with the code I attached
      SqlCommand GetprojecTName = new SqlCommand("SELECT  fieldnames from tblContacts where id = " + Request("id") + ";", Conn);
      dynamic dr = GetprojecTName.ExecuteReader();


You query the db to retrieve the same
0
 

Author Comment

by:alright
ID: 34151138
sammy: That makes sense, thanks so much for that! I'm liking the OldValues/NewValues method though thus far as it's easy to display the changes made.

cmdoprog2: Great idea to use Stringbuilder! Question though; with the current code, each time the DetailsView is updated, the string is being overwritten and only the latest changes are recorded rather than it being Appended to and generating a long nightmarish concatenating abomination (which is what I would like, despite how garbled it seems it may become), despite the code alluding to the fact that it should be Appending. Any ideas?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 12

Expert Comment

by:CmdoProg2
ID: 34154630
I have an idea, but have not done this..
Consider the concept of using a table and table from a dataset instead of using a datasourceID.  It involves a lot more coding.  Using the tableadapter to initial load a table that will be kept in a session variable, each update would update the row in the table.  The table would be the DataSource property of the DetailsView and then databind.  Then in the btnSendUpdated_Click(), update the database via the tableadapter and send your email.   To get the differences for the email, The GetChanges() would build another table of changed rows and then RejectChanges on the original table.
0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34157191
why not use the rowupdating event to pass the row ID to a stored proc to add a line in an audit table, with the rowupdatedevent adding a followup line with the new values?
0
 

Author Comment

by:alright
ID: 34157535
cmdoprog2: That is admittedly getting over my head :( I think the Stringbuilder method would suffice if only the string could remain persistent throughout the session.

nadger: thanks for the input! I'm reading up on audit tables now. Could you provide an example? If we assume the control is a GridView which allows editing of the Name and Addresses within the table:

CREATE TABLE tblContacts
(
   contactid INT IDENTITY(1,1) PRIMARY KEY,
   FirstName varchar(100),
   MiddleName varchar(100),
   LastName varchar(100),
   Address varchar(200),
   userid varchar(100)
)

SELECT [contactid], [firstname], [middlename], [lastname], [address] FROM [tblContacts] WHERE ([userid] = @userid)

Would the audit table need to contain an identical structure in which updates/inserts are tracked (essentially inputting the data into two tables at once - tblContacts and tblContactsAudit?)... then through maybe the onbeforeunload command email all the audit table records with that userid and subsequently delete them from the table to prevent it from growing too much? As I said, reading on audit tables now.. perhaps I'm missing the basic concept.
0
 
LVL 7

Accepted Solution

by:
mr_nadger earned 63 total points
ID: 34158673
I think I may have been a bit of a plum here, thinking about it you amend the SQL of the update command in the HTML, you don't just have to leave it as the basic update statement autogenerated by visual studio.

so instead of just

"update tblcontacts
set firstname=@firstname,
middlename=@middlename,
lastname=@lastname,
address=@address
where contactid=@contactID"

you would prefix it with
insert into contactaudit(contactid,firstname,middlename,lastname,address,userid)
select contactid,firstname,middlename,lastname,address,userid from tblcontact where contactid=@contactid

This would add a copy of the tblcontacts row you're updating into the audit table, and you don't need a second row as the current values will be what it's been changed to.

(Full statement in the code box thingy)

Now, the rowupdating and rowupdated events are handy things to look into, and I would recommend doing so when you have the time.

insert into contactaudit(contactid,firstname,middlename,lastname,address,userid)

select contactid,firstname,middlename,lastname,address,userid from tblcontact where contactid=@contactid

update tblcontacts

set firstname=@firstname,

middlename=@middlename,

lastname=@lastname,

address=@address

where contactid=@contactID

Open in new window

0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34158781
The audit table is really up to you, I usually have a datetime column with a default value of getdate() to timestamp changes, and you'll want the ID of whoever's done it.

You could amend the SQL statement further, turning it into a stored procedure with the names, address, contactid and userid as parameters, and use SQL email to send the update.

If you want to manage the log, you could add a job to delete any rows created over so many days, it really depends on whether or not you want to have a reviewable record rather than a one off email. If you just want the email, have the stored procedure load the current values into variables, compare the values against the parameters passed to it to work out which ones you want to flag as changed, and fire the email without involving the audit table at all.
e.g.
select @currentfirst=firstname,@currentmiddle=middlename,@currentlast=lastname from tblcontacts where contactid=@contactid

set @changes as varchar(500)
set @changes=''
if @currentfirst<>@newfirst set @changes='First name changed from '+@currentfirst+ ' to ' +@newfirst+char(13)
if @currentmiddle<>@newmiddle set @changes='Middle name changed from '+@currentmiddle+ ' to ' +@newmiddle+char(13)
if @currentlast<>@newlast set @changes='Last name changed from '+@currentlast+ ' to ' +@newlast+char(13)
etc

(by the way, I wouldn't generally use a single varchar field for the address if any functionality is going to be based on it)

then

EXEC master..xp_sendmail
        @recipients='you@you.com',
        @message = 'Contact '+cast(@contactid as varchar(10))+ 'has been updated:'+char(13) +@changes,
        @subject = 'Contact update'

Job done! (I hope)




0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34158792
oops for middle and last names  there it should be set @contact=@contact+.... otherwise you'll lose the previous change
0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34158806
sorry yet another typo there, not enough coffee at the end of a long day...
if @currentmiddle<>@newmiddle set @changes=@changes+'Middle name changed from '+@currentmiddle+ ' to ' +@newmiddle+char(13)
if @currentlast<>@newlast set @changes=@changes+'Last name changed from '+@currentlast+ ' to ' +@newlast+char(13)
0
 
LVL 12

Expert Comment

by:CmdoProg2
ID: 34158869
if you really want to wait until the user ends their session, try using the global.asax session end event. Consider the two snippets...

public partial class WebForm1 : System.Web.UI.Page

{

  System.Text.StringBuilder sb;



    protected void Page_Load(object sender, EventArgs e)

    {

      sb = new System.Text.StringBuilder((String)Session["Test"]);

    }

    protected void Page_Unload(object sender, EventArgs e)

    {

      Session["Test"] = sb.ToString();    

    }



    protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)

    {

      sb.Append("Changes to this record...");

      for (int i = 0; i < e.OldValues.Count; i++)

      {

        if (e.OldValues[i].ToString() != e.NewValues[i].ToString())

        {

          sb.AppendFormat("Field {0} old value: {1} Field {0} new value: {2}", i, e.OldValues[i].ToString(), e.NewValues[i].ToString());

        }

      }



    }

}



//  in Global.asax



    void Session_End(object sender, EventArgs e) 

    {

        // Code that runs when a session ends. 

        // Note: The Session_End event is raised only when the sessionstate mode

        // is set to InProc in the Web.config file. If session mode is set to StateServer 

        // or SQLServer, the event is not raised.

      // Email       Session["Test"] if not empty  



    }

Open in new window

0
 

Author Comment

by:alright
ID: 34159361
Thank you so much for all of the additional info! I'll take a bit to digest it all; however, the more I look into/read about audit tables, the better the idea seems. I've created a trigger on the table to insert the old/new values into an audit table when a record is Updated. If I have this audit table, I can then create perhaps an Audit Review page in which all of the records from the Audit table are listed (filtered with a WHERE clause of some sort), allowing an easy overview of changes made. By having this audit review page/table, I can just fire off a single email when a user's session ends if changes have been made (based off something like, in ItemUpdating change a session variable from 0 to 1 to signify a change) which would remind me to review the audit log to see the changes... How does that sound? All of the info in here has been incredibly helpful and I at least feel like I have some sort of end-goal in mind and a path (albeit rather rocky atm) to get there planned out.
0
 
LVL 7

Expert Comment

by:mr_nadger
ID: 34160142
you could also add a column to the audit table to hold a guid created at the start of each session, which would allow you to track changes made per session, and allow a change from one email per session to one per day, or even a report based on sessions/day etc.

Good luck with the project!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 21
dynamic menu in asp.net c# 11 29
Hide Tab Page 3 19
replication - alerts? 4 23
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now