Reset/Restart/Set a Identity Column in SQL Server / MYSQL / PostgreSQL

July 15, 2009

 

A relative common task that you will do someday in your life will be to reset an identity columns. Yes, those that autoincrement their value when a new row is inserted in the database table.

SQL SERVER

   1: dbcc CHECKIDENT ('table',reseed,0)

You need to call the TSQL function checkindent. Pass in the table name (as string), then ‘reseed’ or ‘noressed’ and last but not least the number you want to set the current identity.

Reseed should be passed in, but you can use noreseed if you dont want to the current identity value to be corrected.

But.. hey.. what does corrected means??.

Well, simply, the table will fix its internal sequence, so the next insert action will produce a number that will not cause conflicts.

If you don’t reseed depending of your table configuration and your current records, you will get the:

Checking identity information: current identity value 'some-number', current column value 'some-number'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But be careful, anyway, if you have 100 records, and each of them have an ID from 1 to 100, and then you reseed your table to 1, you will get the conflict, because the values needs to be unique.

MYSQL

   1: ALTER TABLE tablename AUTO_INCREMENT = value

As easy as that. Simply set the autoincrement.

PostgreSQL

   1: ALTER SEQUENCE seqname RESTART WITH 1;

In PostgreSQL the tables can support n sequences. So, you need to alter the sequence that your auto numeric columns is using.

Simply use the alter clause, and set a value.


Send data using POST / HttpWebRequest

July 5, 2009

As you may know, the POST method embeds the contents inside the stream itself, but the way this vars are actually structured can be very similar to the query string /GET method. 

   1: HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create("http://www.url.com/login.php");
   2: httpRequest.CookieContainer = Cookies;
   3: httpRequest.Method = "POST";
   4: httpRequest.ContentType = "application/x-www-form-urlencoded";

We are using a HttpWebRequest object to configure a web request, we are also setting the method to POST, last but absolutely not least, we are adding a header in the request to tell we are actually posting data url encoded.

This means, our data can be like this:

   1: string poststring = string.Format("COOKIEusername={0}&COOKIEpass={1}", "user", "password", captcha.Text);

In this example, we are assuming we want to loging to a website, and this webside actually use some kind of cookie based authentication mechanism, this is why we are using a CookieContainer.

   1: CookieContainer Cookies = new CookieContainer();

Later on, if you want to do some other request, and you need the site remembers you, you can actually pass in the same CookieContainer to the next requests.

But anyway, back to the subject:

   1: byte[] bytedata = Encoding.UTF8.GetBytes(poststring);
   2: httpRequest.ContentLength = bytedata.Length;

First, We are getting a byte[] array from our post var data, and encoding it using UTF8. (extended ASCII support for special chars)

Then, we just tell the response the length of our content, which is the same of our post data string. Then:

   1: Stream requestStream = httpRequest.GetRequestStream();
   2: requestStream.Write(bytedata, 0, bytedata.Length);
   3: requestStream.Close();

We are getting the request byte stream, then writing the POST data inside it.

Then, we just need to get the response (action that will perform the request we just configured before).

   1: HttpWebResponse httpWebResponse =
   2: (HttpWebResponse)httpRequest.GetResponse();

We’re done.

After this, we may want to get the response text to do some actions with it

   1: Stream responseStream = httpWebResponse.GetResponseStream();
   2:  
   3: StringBuilder sb = new StringBuilder();
   4:  
   5: using (StreamReader reader =
   6: new StreamReader(responseStream, System.Text.Encoding.UTF8))
   7: {
   8:     string line;
   9:     while ((line = reader.ReadLine()) != null)
  10:     {
  11:         sb.Append(line);
  12:     }
  13: }
  14:  
  15: //use sb.ToString() to get the final html


Get UNIX Time Stamp using .NET

July 5, 2009

Sometimes we could need to get the UNIX time stamp, as you may know, that number is the amount of seconds since 1-1-1970 until now.

You can get this number using javascript this way:

   1: //Dialog the UNIX time stamp
   2: alert( (new Date()).getTime() );

If you want to get the same number using .NET framework, you can do something like:

   1: TimeSpan t = (DateTime.Now - new DateTime(1970, 1, 1).ToLocalTime());

We’re simply subtracting dates, Now – 1/1/1970

Then, we can get the totalSeconds:

   1: int timestamp = (int)t.TotalSeconds;

But wait!, if you render this now.

   1: System.Diagnostics.Debug.Write(timestamp);

You´ll maybe obtain a floating point number, this is because DateTime.Now is a very precise object, but maybe we will not need that, so, we can do it like this:

   1: DateTime n = DateTime.Now;
   2: DateTime now = new DateTime(n.Year , n.Month , n.Day , n.Hour , n.Minute, n.Second );
   3: TimeSpan t = (now - new DateTime(1970, 1, 1).ToLocalTime());
   4: int timestamp = (int)t.TotalSeconds;

Do some test, and do it in the way it fit your needs.