Category: ASP.NET

SNS coding Tip in ASP.NET

During my university small project doing Internet Technology, having opportunities doing Social Networking Service Website with using ASP.NET. I have done some coding that might help you and me. The coding below will accomplish some tasks, such as getting list of friend, list of friend of friend, and getting friendly times string.

List of Friend

First of all, we need a Friend Table and User Table, I have created like this

  1. create table Users (
  2. uid int constraint ctUser_pk primary key(uid) identity,
  3. username char(100) not null default ”,
  4. password char(32) not null default ”,
  5. gender int default ‘0’,
  6. email char(100) default ”,
  7. address char(255) default ”,
  8. avatar char(255) default ”
  9. );
  10. create table Friend (
  11. uid int not null,
  12. fuid int not null,
  13. fusername char(30) not null,
  14. status int,
  15. dateline datetime
  16. );

Then I have created a Friend Class with the method implementation below/

  1. public DataTable getFriendList(int uid, int limit)
  2.     {
  3.         SqlConnection sqlConn = Util.openDB();
  4.         SqlCommand sqlCommand = new SqlCommand(“Select Top(@limit) f.*,u.* From Friend f Left Join Users u On u.Uid = f.Uid Where f.uid = @uid”, sqlConn);
  5.         sqlCommand.Parameters.Add(“uid”, uid);
  6.         sqlCommand.Parameters.Add(“limit”, limit);
  7.         SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
  8.         DataTable dt = new DataTable();
  9.         sqlAdapter.Fill(dt);
  10.         Util.closeDB();
  11.         return dt;
  12.     }

List of Friend of Friend

Method implementation of getting friend of friend:

  1. public DataTable gerFriendOfFriend(int uid, int limit)
      {
  2.         SqlConnection sqlConn = Util.openDB();
  3.         SqlCommand sqlCommand = new SqlCommand(“select  distinct Top(@limit) f.fuid, f.fusername, u.avatar from friend f left join users u on u.uid = f.fuid where f.uid in (select fuid from friend where uid = @uid) and f.fuid != @uid”, sqlConn);
  4.         sqlCommand.Parameters.Add(“uid”, uid);
  5.         sqlCommand.Parameters.Add(“limit”, limit);
  6.         SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
  7.         DataTable dt = new DataTable();
  8.         sqlAdapter.Fill(dt);
  9.         Util.closeDB();
  10.         return dt;
  11.     }

Friendly Time String

I have created another class called Util. The method implementation of getting friendly time string as below:

  1. public static string gmtime(DateTime t)
      {
  2.         string timeString = “”;
  3.         // show x seconds ago, 1-59 minutes 1-23 hours 1-7 days ago, otherwise original
  4.         DateTime now = DateTime.Now;
  5.         // convert into 1970 time format (this called unix timestamp)
  6.         long nowtime = Util.ConvertToTimestamp(now);
  7.         long time = Util.ConvertToTimestamp(t);
  8.         long diff = nowtime – time;
  9.         if (diff < 0)
  10.         {
  11.             timeString = “Invalid Time”;
  12.         }
  13.         else if (diff < 60)
  14.         {
  15.             timeString = diff + ” seconds ago”;
  16.         }
  17.         else if (diff < 3600)
  18.         {
  19.             decimal minute = Math.Round((decimal) diff / 60, 0);
  20.             timeString = minute + ” minutes ago”;
  21.         }
  22.         else if (diff < 86400)
  23.         {
  24.             decimal hour = Math.Round((decimal) diff / 60 / 60, 0);
  25.             timeString = hour + ” hours ago”;
  26.         }
  27.         else if (diff < 604800) // 7 days before
  28.         {
  29.             decimal day = Math.Round((decimal) diff / 60 / 60 / 24, 0);
  30.             timeString = day + ” days ago”;
  31.         }
  32.         else
  33.         {
  34.             timeString = t.ToString(); // show original after 7 days
  35.         }
  36.         return timeString;
  37.     }
  38.     // reference: http://www.dreamincode.net/code/snippet2094.htm
  39.     // convert datetime to unix timestamp
  40.     public static long ConvertToTimestamp(DateTime value)
  41.     {
  42.         //create Timespan by subtracting the value provided from
  43.         //the Unix Epoch
  44.         TimeSpan span = (value – new DateTime(1970, 1, 1, 0, 0, 0, 0).ToLocalTime());
  45.         //return the total seconds (which is a UNIX timestamp)
  46.         return (long)span.TotalSeconds;
  47.     }