For all programmers
For all programmer, just remember :
http://timstall.dotnetdevelopersjournal.com/coding_is_just_the_tip_of_the_iceberg.htm
And he says nothing about clients, marketing, sales, promotion, SEO, SMM, and other usual stuff…
For all programmer, just remember :
http://timstall.dotnetdevelopersjournal.com/coding_is_just_the_tip_of_the_iceberg.htm
And he says nothing about clients, marketing, sales, promotion, SEO, SMM, and other usual stuff…
I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see http://msdn.microsoft.com/en-us/library/ms178598.aspx )
The code is for sql server 2005 and I have not realized without the help of http://sqlserver.ro/forums/thread/8332.aspx
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :
CREATE TABLE [Audit]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProcName] [varchar](128) [IdentifierAccess] [varchar](max) [DateAccess] [datetime] NULL DEFAULT (getdate()) ) ON [PRIMARY]
2. Stored proc to insert data :
alter proc dbo.proc_Log(@procname varchar(100)) as begin declare @userid varchar(100) BEGIN TRY select @userid=client_net_address from sys.[dm_exec_connections] with(nolock) where session_id = @@SPID End TRY BEGIN CATCH --GRANT SELECT ON sys.dm_exec_connections TO the user or --grant view server state to the user ! http://msdn.microsoft.com/en-us/library/ms186717.aspx set @userid = SUSER_NAME() End CATCH INSERT INTO [Audit] ( [ProcName] ,[IdentifierAccess] ) VALUES (@procname, @userid ) end
3. In each stored procedure put this at beginning
DECLARE @ProcName nvarchar(128) SET @ProcName = OBJECT_NAME(@@PROCID) exec proc_Log @ProcName
And this at the end of stored proc :
exec proc_Log @ProcName
4. See what the procedure calling are and the time (basic)
select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) from [Audit] a1 inner join [Audit] a2 on a2.ID = ( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID ) order by 1
From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)
Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)
More than one time you need to let users / application import bulk data in your system .
I have had many cases – data is old data from old software, that is exported in some text files, usually csv.
In order to import fast the data, I used FileHelpers from http://www.filehelpers.com/
Let’s say you have the following data for an car evidence :
Date, StartMiles, Destination, EndMiles
And the data comes from an Excel that has those columns. The requirements is the user can copy/paste from Excel data
When they copy the data comes in this form
01/01/2010 1000 Washington 1550
02/01/2010 1550 Dallas 2550
and so on.
It is clear that you :
Now the code in logical steps :
1. Accomodate for dates :
internal class ConvertDate : ConverterBase { /// <summary> /// different forms for date separator : . or / or space /// </summary> /// <param name="from">the string format of date - first the day</param> /// <returns></returns> public override object StringToField(string from) { DateTime dt; if (DateTime.TryParseExact(from, "dd.MM.yyyy", null, DateTimeStyles.None, out dt)) return dt; if (DateTime.TryParseExact(from, "dd/MM/yyyy", null, DateTimeStyles.None, out dt)) return dt; if (DateTime.TryParseExact(from, "dd MM yyyy", null, DateTimeStyles.None, out dt)) return dt; throw new ArgumentException("can not make a date from " + from, "from"); } }
2. Create the class that will hold one record:
[IgnoreEmptyLines(true)] [DelimitedRecord(",")] internal class DestinationReader { //[FieldConverter(ConverterKind.Date,"dd.MM.yyyy")] [FieldConverter(typeof(ConvertDate))] public DateTime Date; [FieldConverter(ConverterKind.Int32)] public int StartMiles; [FieldQuoted(QuoteMode.OptionalForBoth)] public string Destination; [FieldConverter(ConverterKind.Int32)] public int EndMiles; }
3. Now read the entire string:
string Text = text that comes from the user string TextDelim = Text.Substring(10, 1);// the date has 10 chars - so the eleven is the separator while (Text.IndexOf(TextDelim + TextDelim) > 0)//consolidate for 2 delimiters { Text = Text.Replace(TextDelim + TextDelim, TextDelim); } DelimitedFileEngine<DestinationReader> flh=new DelimitedFileEngine<DestinationReader>(); flh.Options.Delimiter = TextDelim; var data =flh.ReadString(Text);
In data you have a list of DestinationReader
So for any structured import of data use FileHelpers from http://www.filehelpers.com/
In my experiences with MVC I have some utilities to share . Some of them are :
Please see the project and give feedback
On some cases you need to test the whole web interface. Why ? Suppose you have some Ajax call. You can test the call on server, but how do you ensure that user experience is OK ?
There are several testing projects for Web – for example selenium and Watin
I will show how to test with Selenium + NUNIT
using System; using System.Text; using System.Text.RegularExpressions; using System.Threading; using NUnit.Framework; using Selenium; namespace InvoiceTest { [TestFixture] public class TestWeb { private ISelenium selenium; private StringBuilder verificationErrors; [SetUp] public void SetupTest() { //java -jar selenium-server.jar //selenium = new DefaultSelenium("localhost", 4444, @"*firefox d:\Program Files\Mozilla Firefox\firefox.exe","<a href="http://localhost/");">http://localhost/");</a> selenium = new DefaultSelenium("localhost", 4444, @"*iexplore", "<a href="http://localhost/");">http://localhost/");</a> //selenium = new DefaultSelenium("localhost", 4444, @"*iexploreproxy", "<a href="http://localhost/");">http://localhost/");</a> selenium.Start(); verificationErrors = new StringBuilder(); } [TearDown] public void TeardownTest() { try { selenium.Stop(); } catch (Exception) { // Ignore errors if unable to close the browser } Console.WriteLine(verificationErrors.ToString()); Assert.AreEqual("", verificationErrors.ToString()); } [Test] public void FindTextAfterClickOnButton() { selenium.Open("/<strong>your application</strong>"); selenium.Focus(""); selenium.WindowMaximize(); selenium.Type("the textbox", "the text"); selenium.Click("button "); selenium.WaitForPageToLoad("30000"); try { Assert.IsTrue(selenium.IsTextPresent("<strong>new text from your application</strong>")); } catch (AssertionException e) { verificationErrors.Append(e.Message); Console.WriteLine(e.Message); } } } }
Many times I’ve had problem with the following error when inserting objects with dates with Entity Framework :
System.Data.UpdateException An error occurred while updating the entries. See the inner exception for details.SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Ok, it’s my faute – but to remember each one date is too much for me…
The usual method was to start SqlProfiler, monitor the database and see how the sql is constructed. However, the database being used by all developers, it was not so simple to differentiate between all sql’s.
Other alternative was to log the entity framework generated sql’s . I have discovered Ayende Rahien Entity Framework profiler . Simple to use , as is wrote here in 2 simple steps
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
and start the exe. That will be all.
Do not forget to remove it on release version!
Pros:
Easy to use, valuable information, good!
Cons :
Not free …
Powered by Twitter Tools
In every application you have some data that is more read-more, write-once or twice. For example you can have the list of Cities of a country, the list of Countries of the world or list of exchange currency. This data is modified rarely. Also, you can have data that is not very sensitive to be real-time , such as the list of invoices for the day.
In .NET 3.5 you have several options
1. ASP.NET caching – and implementing in other applications with HttpRuntime ( even if MS says “The Cache class is not intended for use outside of ASP.NET applications”)
2. Enterprise caching block : hard to configure
3. memcached , velocity, sharedcache and other third party providers – that comes with their options and configuration
In .NET 4.0 you have a new kid : objectcache and , more important , an implementation : MemoryCache
http://msdn.microsoft.com/en-us/library/system.runtime.caching.memorycache%28v=VS.100%29.aspx
What is very good is that now you can cache in Memory what do you want – and apply easily to your Business Layer. More, the object is a singleton for the application – that is even better (see the test on the final of the post)
What it is missing is an easy implementation for List and an implementation to remove data after a defined time.
So I decided to do my implementation for that (ok, it is wrong to have both implementations in a single class – but you can separate easily )
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Runtime.Caching; namespace CachingData{ /// <summary> /// List<int> i = new List<int>() { 1, 10, 100 }; //CacheData_List<List<int>, int> data = new CacheData_List<List<int>, int>(2); //data.Add(i); //Assert.AreEqual(3, data.Items().Count, "must be 3"); //data = new CacheData_List<List<int>, int>(); //Assert.AreEqual(3, data.Items().Count, "must be 3"); //Assert.IsTrue(data.ContainsData, "must have data"); //Thread.Sleep(1000 * 3); //Assert.IsFalse(data.ContainsData, "must not have data"); /// </summary> /// <typeparam name="T">and generic ILIST </typeparam> /// <typeparam name="U"></typeparam> public class CacheData_List<T, U> where T:class,IList<U>, new() { /// <summary> /// use this for adding in cache /// </summary> public event EventHandler RemovedCacheItem; private System.Timers.Timer timerEvent; private MemoryCache buffer; private int TimeToLiveSeconds; private DateTimeOffset dto; private string Key; /// <summary> /// default constructor - cache 600 seconds = 10 minutes /// </summary> public CacheData_List() : this(600) { } /// <summary> /// constructor cache the mentioned TimeSeconds time /// </summary> /// <param name="TimeSeconds">value of time for cache</param> public CacheData_List(int TimeSeconds) { TimeToLiveSeconds = TimeSeconds; timerEvent=new System.Timers.Timer(TimeToLiveSeconds * 1000); timerEvent.AutoReset = true; timerEvent.Elapsed += new System.Timers.ElapsedEventHandler(timerEvent_Elapsed); dto = new DateTimeOffset(DateTime.UtcNow.AddSeconds(TimeToLiveSeconds)); Key = typeof(T).ToString(); buffer = MemoryCache.Default; } void timerEvent_Elapsed(object sender, System.Timers.ElapsedEventArgs e) { if (RemovedCacheItem != null) { RemovedCacheItem(this, EventArgs.Empty); } } /// <summary> /// remove item from cache /// </summary> public void Remove() { if (buffer.Contains(Key)) { buffer.Remove(Key); } dto=new DateTimeOffset(DateTime.UtcNow.AddSeconds(TimeToLiveSeconds)); } /// <summary> /// add multiple items to cache /// </summary> /// <param name="items">items to add to the cache</param> public void Add(T items) { if (buffer.Contains(Key)) { T data = Items(); foreach (var t in data) { items.Add(t); } buffer.Remove(Key); } buffer.Add(Key, items, dto); } /// <summary> /// add a item to the IList of the cache /// </summary> /// <param name="item">an item to add</param> public void AddItem(U item) { T data=new T(); if (buffer.Contains(Key)) { data = buffer.Get(Key) as T; buffer.Remove(Key); } data.Add(item); buffer.Add(Key, data,dto); } /// <summary> /// usefull if you do not intercept the removed event /// </summary> public bool ContainsData { get { return buffer.Contains(Key); } } /// <summary> /// retrieve items /// </summary> /// <returns></returns> public T Items() { if (!buffer.Contains(Key)) return null; return buffer.Get(Key) as T; } } }
Please note that the test for usage is in the summary :
</pre> List i = new List() { 1, 10, 100 }; CacheData_List <List<int>, int> data = new CacheData_List<List<int>, int>(2); data.Add(i); Assert.AreEqual(3, data.Items().Count, "must be 3"); data = new CacheData_List<List<int>, int>(); Assert.AreEqual(3, data.Items().Count, "must be 3"); Assert.IsTrue(data.ContainsData, "must have data"); Thread.Sleep(1000 * 3); Assert.IsFalse(data.ContainsData, "must not have data");
You can download the file from
http://msprogrammer.serviciipeweb.ro/wp-content/uploads/2010/05/CachingData.zip
(Last Note : for synchonization maybe it was better to lock on readerwriterlockslim
Powered by Twitter Tools