• Peforming CRUD with LINQ to SQL

    by Venkata Koppaka | Oct 28, 2010

    LINQ to SQL is an ORM(Object-relational mapping) tool introduced by Microsoft.

    In this post I will walk through how to perform basic CRUD(Create, Retrieve, Update, Delete) operations using LINQ to SQL.

    (NOTE: This post assumes you have basic knowledge of using LINQ to SQL like adding the DBML class and dragging tables on to a DBML designer)

    Lets get started -

    The table we are going use for this example is a simple Person Table. Here is the schema for the table -

     

    Create

    To perform a Create operation on a table using LINQ to SQL you need to create an instance of the class and populate the data into the instance. Once you are done call "InsertOnSubmit" and "SaveChanges" to insert the object.

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 Person aPersonToInsert = new Person(); 
    4                 aPersonToInsert.Name = "Test User"; 
    5                 aPersonToInsert.Age = 26; 
    6                 aPersonToInsert.Email = "test@test.com"; 
    7                 context.Persons.InsertOnSubmit(aPersonToInsert); 
    8                 context.SubmitChanges(); 
    9             } 

    The above code will insert the object only after context.SubmitChanges() is called.

    Retrieve

    To perform a retrieve from a table using you can use one of the two syntax. The first one is a normal LINQ syntax which reads more like plain SQL. The second syntax is called "Lamda" syntax which is just a more convenient way to create a delegate.

    Here are the code samples for doing both.

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Normal Syntax 
    4                 Person person = (from aPerson in context.Persons 
    5                                  where aPerson.PersonId == 1 
    6                                  select aPerson).SingleOrDefault(); 
    7  
    8                 //Lambda Syntax 
    9                 Person personLamda = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    10                  
    11             } 

    The SingleOrDefault() method above gets a single entity that matches the criteria or returns NULL if nothing is found.

    Update

    To perform a Update on a database record using LINQ to SQL, you have to get the record using one of the two syntax shown above and make changes to record and then Submit the changes to the database using SubmitChanges().

    Here is the code sample for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5                 person.Name = "Updated User"; 
    6                 person.Age = 99; 
    7                 person.Email = "noone@someone.com"; 
    8  
    9                 context.SubmitChanges(); //this will save your changes to the database 
    10             } 

    Again please note unless you call SubmitChanges() none of your changes are saved to the database.

     

    Delete

     Delete operation using LINQ to SQL is similar to Update operation. You have to get the record, delete the record and Submit the changes to the database using SubmitChanges().

    Here is the code for doing so -

    1 using (var context = new TestDbDataContext()) 
    2             { 
    3                 //Lambda Syntax 
    4                 Person person = context.Persons.SingleOrDefault(p => p.PersonId == 1); 
    5  
    6                 context.Persons.DeleteOnSubmit(person); 
    7                 context.SubmitChanges(); //this will save your changes to the database 
    8             } 

    Again unless you call SubmitChanges() nothing is deleted from the database.

    So we are done performing a simple CRUD operations using LINQ to SQL.

    Hope you find it useful,

     Cheers,

    Venkata

    Go comment!

  • Round to Nearest 10,100, or 10 POW N

    by Venkata Koppaka | Apr 02, 2010

    This one is a quick code snippet,

    Here is a method which can round a value to nearest 100,1000, or any 10 Pow N.

    This method follows the rounding rules –

    1private static int RoundToNDigits(double value,int digits)  
    2        {  
    3            int valueAfterDecimal = Convert.ToInt32(value % Math.Pow(10, digits));  
    4            int valueBeforeDecimal = Convert.ToInt32(value) / Convert.ToInt32(Math.Pow(10, digits));  
    5            if (valueAfterDecimal < 50)  
    6            {  
    7                return valueBeforeDecimal * Convert.ToInt32(Math.Pow(10, digits));  
    8            }  
    9            else if (valueAfterDecimal > 50)  
    10            {  
    11 
    12                return Convert.ToInt32((valueBeforeDecimal + 1) * Math.Pow(10, digits));  
    13            }  
    14            else  
    15            {  
    16                if (valueBeforeDecimal % 2 == 0)  
    17                    return (valueBeforeDecimal + 1) * Convert.ToInt32(Math.Pow(10, digits));  
    18                else  
    19                    return valueBeforeDecimal * Convert.ToInt32(Math.Pow(10, digits));  
    20            }  
    21        } 

    Hope this helps,
    Cheers,
    Venkata
    Go comment!

  • Shuffling a List in a random unbiased way

    by Venkata Koppaka | Mar 22, 2010

    I had to come up with a random and unbiased way of shuffling a list of items.

    The first thing that came up to my mind is to sort using a simple for loop which iterates through the list of objects and calls the random function, something like this -

    1 public static void Shuffle<T>(this IList<T> list)   
    2 {   
    3     Random rng = new Random();   
    4     int n = list.Count;   
    5     while (n > 1) {   
    6         n--;   
    7         int k = rng.Next(n + 1);   
    8         T value = list[k];   
    9         list[k] = list[n];   
    10         list[n] = value;   
    11     }   
    12 } 
    13  

    Then I read this post by Jeff Atwood - http://www.codinghorror.com/blog/2007/12/shuffling.htmlwhere he says the best way to do a random unbiased shuffling is to do a sort by a Random Number in .NET world a NewGuid().

    Interesting, so I started to write a little sample to prove  that shuffling using a random sort indeed works. Here is an example snippet.

    1     class Program 
    2     { 
    3         static void Main(string[] args) 
    4         { 
    5             List<Int32> intlist = new List<int>(); 
    6             for (int i = 0; i < 10; i++) 
    7             { 
    8                 intlist.Add(i); 
    9             } 
    10  
    11             intlist = intlist.OrderBy(il => Guid.NewGuid()).ToList(); 
    12  
    13             foreach (int i in intlist) 
    14             { 
    15                 Console.WriteLine(i); 
    16             } 
    17         } 
    18     } 

    Notice at Line number 11 I call a .OrderBy(il=> Guid.NewGuid()).. Man it works like a champ. Here are some test results.

    Iteration 1 :

    3
    2
    9
    6
    1
    0
    8
    7
    4
    5

    Iteration 2 :

    9
    6
    5
    7
    4
    2
    8
    1
    0
    3

    Hope this helps.

    Cheers,

    Venkata


    Go comment!

  • How to FTP a file through C#

    by Venkata Koppaka | Feb 05, 2010

    .NET Framework gives us a nice little API to FTP a file to a server using C#, VB.NET . This article is an attempt to put it to code.

    Please note that I am only doing a File Upload using FTP. File Download will follow soon.

     Add a reference to Sytem.Net to the application - and use the following fragment. -

    1 string ftpFileName = @"C:\temp\Foo.txt;
    2
    3             //Get the fileInfo to be uploaded
    4             FileInfo fileInfo = new FileInfo(ftpFileName);
    5
    6                 //Create a FTPWebRequest
    7                 FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://" + Settings.Default.FTPServer + @"/" + fileInfo.Name); 
    8                 request.Method = WebRequestMethods.Ftp.UploadFile; 
    9  
    10                 //Setting Credentials 
    11                 request.Credentials = new NetworkCredential(Settings.Default.FTPUserName, Settings.Default.FTPPassword); 
    12  
    13                 //Setup a stream for the request and a stream for the file that we are uploading 
    14                 Stream ftpStream = request.GetRequestStream(); 
    15                 FileStream file = File.OpenRead(ftpFileName); 
    16  
    17  
    18                 //Variables to read the file 
    19                 int length = 1024; 
    20                 byte[] buffer = new byte[length]; 
    21                 int bytesRead = 0; 
    22  
    23                 //Write the file to requerst stream 
    24                 do 
    25                 { 
    26                     bytesRead = file.Read(buffer, 0, length); 
    27                     ftpStream.Write(buffer, 0, bytesRead); 
    28                 } while (bytesRead != 0); 
    29  
    30                 //Flush / Close 
    31                 file.Close(); 
    32                 ftpStream.Close(); 

    Please note that I am using a Settings file to read the FTPServer, FTPUsername and FTPPassword. You could use a hardcoded / read from DB / XML value there.

    Hope this helps,

    Cheers,

    Venkata


    Go comment!