About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Monday, January 11, 2010

List Creator

This is one of my favorite pieces of code I've stolen borrowed from another site. I've had made some minor code changes and additions from the original, but basically I just need to send in a DataReader after running a sql command and it will return a list of objects of that type. (Very similar to Linq to Sql)

You just need to make sure that anything that is return through the query is defined in the object as a write property.

So here is the List Creator code, which does the creation via reflections:

Code Snippet
  1. public class ListCreator where T : new()
  2. {
  3.      public List FindAll(IDataReader iDataReader)
  4.      {
  5.          List returnList = new List();
  6.  
  7.          try
  8.          {
  9.              //need a Type and PropertyInfo object to set properties via reflection
  10.              Type tType = new T().GetType();
  11.              PropertyInfo pInfo;
  12.  
  13.              //x will hold the instance of until it is added to the list
  14.              T x;
  15.  
  16.              //use reader to populate list of objects
  17.              while (iDataReader.Read())
  18.              {
  19.                  x = new T();
  20.  
  21.                  //set property values
  22.                  //for this to work, command’s column names must match property names in object
  23.                  for (int i = 0; i < iDataReader.FieldCount; i++)
  24.                  {
  25.                      pInfo = tType.GetProperty(iDataReader.GetName(i));
  26.  
  27.                      pInfo.SetValue(x, (iDataReader[i] == DBNull.Value? null:iDataReader[i]), null);
  28.                  }
  29.  
  30.                  //once instance of is populated, add to list
  31.                  returnList.Add(x);
  32.              }
  33.          }
  34.          catch (Exception ex)
  35.          {
  36.              Logging.Logging.Error(ex.ToString());
  37.          }
  38.  
  39.          return returnList;
  40.      }
  41. }




So in my DAL I might have something like:

Code Snippet
  1. public List<PartsOrdering> GetAllPartsOrdering()
  2. {
  3.    List<PartsOrdering> li = new List<PartsOrdering>();
  4.    try
  5.    {
  6.       using (SqlConnection cn = new SqlConnection(connString))
  7.       {
  8.           ListCreator<PartsOrdering> PartsOrders = new ListCreator<PartsOrdering>();
  9.           SqlCommand cmd = new SqlCommand("dbo.selPartsOrdering", cn);
  10.           cmd.CommandType = CommandType.StoredProcedure;
  11.           cn.Open();
  12.           li = PartsOrders.FindAll(ExecuteReader(cmd));
  13.       }
  14.   }
  15.   catch (Exception ex)
  16.   {
  17.      Logging.Error(ex.ToString());
  18.   }
  19.   return li;
  20. }

Code Snippet
  1. [Serializable]
  2. public class PartsOrdering : IFormattable
  3. {
  4.     #region Properties
  5.     [DefaultValue(-1)]
  6.     public int PartsOrderingID
  7.     {
  8.         get;
  9.         set;
  10.     }
  11.     [DefaultValue("")]
  12.     public string ToolID
  13.     {
  14.         get;
  15.         set;
  16.     }
  17.     [DefaultValue("")]
  18.     public string PartNumber
  19.     {
  20.         get;
  21.         set;
  22.     }
  23.     [DefaultValue("")]
  24.     public string Description
  25.     {
  26.         get;
  27.         set;
  28.     }
  29.     [DefaultValue(0)]
  30.     public int Quantity
  31.     {
  32.         get;
  33.         set;
  34.     }
  35.     [DefaultValue(1)]
  36.     public int PartsOrderingLocationID
  37.     {
  38.         get;
  39.         set;
  40.     }
  41.     [DefaultValue(1)]
  42.     public int PartsOrderingUrgencyID
  43.     {
  44.         get;
  45.         set;
  46.     }
  47.     [DefaultValue("")]
  48.     public string Comment
  49.     {
  50.         get;
  51.         set;
  52.     }
  53.     public DateTime RequestedDateTime
  54.     {
  55.         get;
  56.         set;
  57.     }
  58.     [DefaultValue("")]
  59.     public string RequestedUser
  60.     {
  61.         get;
  62.         set;
  63.     }
  64.     public string LocationName
  65.     {
  66.         get
  67.          {
  68.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
  69.              return locations.Where(n =&gt; n.PartsOrderingLocationID.Equals(this.PartsOrderingLocationID)).Select(n =&gt; n.Name).First();
  70.          }
  71.         set
  72.          {
  73.              List&lt;PartsOrderingLocation&gt; locations = new PartsOrderingLocation().List();
  74.              this.PartsOrderingLocationID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingLocationID).First();
  75.          }
  76.  
  77.     }
  78.     public string UrgencyName
  79.     {
  80.         get
  81.          {
  82.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  83.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Name).First();
  84.          }
  85.         set
  86.          {
  87.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  88.              this.PartsOrderingUrgencyID = locations.Where(n =&gt; n.Name.Equals(value)).Select(n =&gt; n.PartsOrderingUrgencyID).First();
  89.          }
  90.     }
  91.     public int UrgencyRank
  92.     {
  93.         get
  94.          {
  95.              List&lt;PartsOrderingUrgency&gt; locations = new PartsOrderingUrgency().List();
  96.              return locations.Where(n =&gt; n.PartsOrderingUrgencyID.Equals(this.PartsOrderingUrgencyID)).Select(n =&gt; n.Rank).First();
  97.          }
  98.     }
  99.     [DefaultValue(-1)]
  100.     public int PartsOrderingGroupID
  101.     {
  102.         get;
  103.         set;
  104.     }
  105.     [DefaultValue(true)]
  106.     public bool IsActive
  107.     {
  108.         get;
  109.         set;
  110.     }
  111. }







Friday, January 08, 2010

How to update large count of rows without locking them

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:


 UPDATE dbo.Foo
SET Column = 'Value'


Try:


DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)
DECLARE @var INT
SELECT @var=0 -- this resets @@ROWCOUNT=1


WHILE @@ROWCOUNT >0
BEGIN
UPDATE TOP(1500) BAR
  SET Column='Value'
OUTPUT inserted.PK_ID
INTO  @UpdatedRows
FROM  dbo.BAR as BAR
WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)
END



I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0
BEGIN
 UPDATE TOP(1500) BAR
   SET Column='Value'
 WHERE
   Column <> 'Value'
END