How to use transaction in LINQ using C#

LINQ generates DataContext class which provides classes and methods which is used in OR-Mapping. You can also use your stored procedures and views with LINQ. You may require to use transaction with your SPs during Insert, Delete or Update operations.

System.Data.Common.DbTransaction class provides the Transaction object. I have used Northwind database in this example. Lets start with new project, you can select new project from Start -> All Programs ->  Microsoft Visual Studio 2008 Beta 2  and click on Microsoft Visual Studio 2008 Beta 2. Create new Asp.net website. Right click on website from solution explorer and select LINQ to SQL classes from Add New Item as shown below.

Fig – (1)  LINQ to SQL classes 

           This will generate dbml file in App_Code folder. Select the tables, views, stored procedures and function from server explorer and drag it on dbml file. DataContext class generates methods for each SPs, functions and views. 

           I have used Category and Product tables in this example. I have created two SPs InsertCategory and InsertProduct for inserting records in appropriate tables. You can see your SPs when you create the object of DataContext class.

Fig – (2) DataContext class shows the methods generated for SPs 

          I will first insert the category and then insert product for newly created category. If you have used some parameters as OUT parameters in your SP, you need to pass these parameters as Ref in calling method. In my SPs I have used CategoryID and ProductID as OUT parameters. 

          Now, lets move towards the transaction. I want that either category and product both will be added in database or none of them will be inserted. Below is the code for that,

System.Data.Common.DbTransaction trans = null;
DataClassesDataContext objDataClass = new DataClassesDataContext
                 (ConfigurationManager.ConnectionStrings
                                       [Constants.ConnectionString].ConnectionString);
try{

                // Nullable data type as the methods generated for SP will use Nullable
                // type                int? intCategoryID =0;
                int? intProductID =0;

                // Open the connection
                objDataClass.Connection.Open();

                // Begin the transaction
                trans = objDataClass.Connection.BeginTransaction();
               
                // Assign transaction to context class
                // All the database operation perform by this object will now use
                //transaction 
                objDataClass.Transaction = trans;

                // Insert Category
                // I have to use Ref keyword CategoryID of newly added category will
                // be assign to this variable
                objDataClass.InsertCategory
                                          (
                                            ref intCategoryID, 
                                            txtName.Text.Trim().Replace(“‘”, “””), 
                                            txtDescription.Text.Trim().Replace(“‘”, “””),
                                            new byte[0]
                                          );
                               

                // Insert Product
                // I have to use Ref keyword as ProductID of newly generated product will
                // be assign to this variable
                objDataClass.InsertProduct
                                          (
                                            ref intProductID,
                                            txtProductName.Text.Trim().Replace(“‘”,“””),
                                            null,
                                            intCategoryID,
                                            txtQuantityPerUnit.Text.Trim().Replace(“‘”, “””),
                                            Convert.ToDecimal(
                                                      txtUnitPrice.Text.Trim().Replace(“‘”, “””)
                                                                                  ),
                                             null,
                                             null,
                                             null,
                                             0);
               

                // Commit transaction
                trans.Commit();

               
            }
            catch (Exception ex)
            {                

                    // Rollback transaction
                    if (trans != null)
                                 trans.Rollback();
            }
            finally            {

                      // Close the connection
                      if (objDataClass.Connection.State == ConnectionState.Open)
                                 objDataClass.Connection.Close();
            }

     Fig – (3) Code for Transaction in LINQ using  C#

Happy Programming !!

Read More »

Using Java Script Bytes Conversion

Function ConvertBytes(ByRef anBytes)
    Dim lnSize          ' File Size To be returned
    Dim lsType          ' Type of measurement (Bytes, KB, MB, GB, TB)
   
    Const lnBYTE = 1
    Const lnKILO = 1024                     ' 2^10
    Const lnMEGA = 1048576                  ' 2^20
    Const lnGIGA = 1073741824               ' 2^30
    Const lnTERA = 1099511627776            ' 2^40
 
 
 
    '    Const lnPETA = 1.12589990684262E+15        ' 2^50
    '    Const lnEXA = 1.15292150460685E+18        ' 2^60
    '    Const lnZETTA = 1.18059162071741E+21    ' 2^70
    '    Const lnYOTTA = 1.20892581961463E+24    ' 2^80
   
    If anBytes = "" Or Not IsNumeric(anBytes) Then Exit Function
   
    If anBytes < 0 Then Exit Function  

'    If anBytes < lnKILO Then
'        ' ByteConversion
'        lnSize = anBytes
'        lsType = "bytes"
'    Else      
        If anBytes < lnMEGA Then
            ' KiloByte Conversion
            lnSize = (anBytes / lnKILO)
            lsType = "kb"
        ElseIf anBytes < lnGIGA Then
            ' MegaByte Conversion
            lnSize = (anBytes / lnMEGA)
            lsType = "mb"
        ElseIf anBytes < lnTERA Then
            ' GigaByte Conversion
            lnSize = (anBytes / lnGIGA)
            lsType = "gb"
        Else
            ' TeraByte Conversion
            lnSize = (anBytes / lnTERA)
            lsType = "tb"
        End If
'    End If
    ' Remove fraction
    'lnSize = CLng(lnSize)
    lnSize = FormatNumber(lnSize, 2, True, False, True)
   
    ' Return the results
    ConvertBytes = lnSize & " " & lsType
End Function


Function ConvertBytes1(ByRef anBytes)
if anBytes <= 1024 then
response.write anBytes & " KB"
else
anBytes = anBytes/1024
response.write anBytes & " MB"
end if
End Function


 public string ConvertBytes(int anBytes)
    {
        if (anBytes == 0)
        {
           
        }

        return "";
    }


 //File Size To be returned
    string lnSize;
    // Type of measurement (Bytes, KB, MB, GB, TB)
    string lsType;
    public const int lnBYTE = 1;
    //2^10
    public const int lnKILO = 1024;
    //2^20
    public const int lnMEGA = 1048576;
    //2^30
    public const int lnGIGA = 1073741824;
    //2^40
    public const long lnTERA = 1099511627776;
    //2^50
    public const double lnPETA = 1.12589990684262E+15;
    //2^60
    public const double lnEXA = 1.15292150460685E+18;
    //2^70
    public const double lnZETTA = 1.18059162071741E+21;
    //2^80
    public const double lnYOTTA = 1.20892581961463E+24;

Read More »

Partial Classes in C#

Partial classes is a new feature of OOPs in .NET2.0 

Partial classes means split the class into multiple files.
When compiled all the files will be treated as a single class.

it may be helpful in large projects,so many people can work
on same class.

Advantage: 


It is especially useful for: Allowing multiple developers to work on a single class at
the same time without the need for later merging files in
source control.


One of the greatest benefits of partial classes is that it 
allows a clean separation of business logic and the user 
interface (in particular the code that is generated by the 
visual designer). 

Using partial classes, the UI code can be
hidden from the developer, who usually has no need to
access it anyway. Partial classes will also make debugging
easier, as the code is partitioned into separate files.

Example:

Program that uses partial class: C#

class Program
{
    static void Main()
    {
 A.A1();
 A.A2();
    }
}

Contents of file A1.cs: C#

using System;

partial class A
{
    public static void A1()
    {
 Console.WriteLine("A1");
    }
}

Contents of file A2.cs: C#

using System;

partial class A
{
    public static void A2()
    {
 Console.WriteLine("A2");
    }
}

Output

A1
A2

To split a class definition, use the partial keyword modifier, as shown below:

C#








public partial class Employee
{
    public void DoWork()
    {
    }
}

public partial class Employee
{
    public void GoToLunch()
    {
    }
}






The partial modifier can only appear immediately before the keywords class, struct, or interface.

Partial Class :

We were declaring a class in a single file but Partial class is a feature which allows us to write class across multiple files.
The partial indicates that the parts of the class, struct, or interface can be defined in the namespace. All the parts must be used with the partial keyword. All the parts must be available at compile time to form the final type or final class. All the parts must have the same accessibility level, such as public, private, protected, and so on.
If any part of the class is declared abstract, then the whole type is considered to be as abstract.
If any part is declared sealed, then the whole type is considered to be as sealed.
If any part declares a base type, then the whole type inherits that class.


Example: Test1.cs:-
namespace PartialClass
{
    public partial class MyTest
    {
        private int a;
        private int b;

        public void getAnswer(int a, int b)
        {
            this.a = a;
            this.b = b;
        }
    }
}
Test2.cs:-
namespace PartialClass
{
    public partial class MyTest
    {
        public void PrintCoOrds()
        {
            Console.WriteLine("Integer values: {0},{1}", a, b);
            Console.WriteLine("Addition: {0}", a+b);
            Console.WriteLine("Mulitiply: {0}", a * b);
        }
    }
}
Program.cs:-
namespace PartialClass
{
    class Program
    {
        static void Main(string[] args)
        {
            MyTest ts = new MyTest();
            ts.getAnswer(12, 25);
            ts.PrintCoOrds();
            Console.Read();
        }
    }
}
OUTPUT:
Integer values: 12,25
Addition: 37 
Mulitiply: 300

Partial Method :

Partial class or struct can contain Partial method.
One part of class contains signature or declaration of the method and the implementation or definition of method can be in same class or different class.
Partial methods enable the implementer of one part of a class to define a method, similar to an event. The implementer of the other part of the class can decide whether to implement the method or not. If the method is not implemented, then the compiler removes the method signature and all calls to the method .
A partial method declaration consists of two parts: 1. definition and 2. Implementation.

partial void onNameChanged();

// Implementation in file2.cs
partial void onNameChanged()
{
  // method body
}

Partial methods can have ref but not out parameters.
Partial method can have static or unsafe modifiers but can not be extern as presence of body decide whether they are defining or implementing.
Partial method can be Generic.
Partial methods are implicitly private, and therefore they cannot be virtual.

Read More »

SQL SERVER - Using sp_msforeachtable in sql server

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure

Display the size of all tables in a database

USE NORTHWIND

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Display Number of Rows in all Tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

Rebuild all indexes of all tables in a database

USE YOURDBNAME

GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO


Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."

Disable all constraints of all tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Disable all Triggers of all tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'


Delete all data from all tables in your database

-- disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?
'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


To RESEED all table to 0, use this script

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)
'

GO

The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

Reclaim space from dropped variable-length columns in tables or indexed views

USE YOURDBNAME

EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';

Update Statistics of all Tables in a database

USE YOURDBNAME

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

Read More »

SQL SERVER - Important Query Part-2

1. COPYING WHOLE DATA OF A TABLE

SELECT * INTO TABLE_DESTINATION FROM TABLE_SOURCE

2. SELECT ONLY DATE PART FROM DATETIME – BEST PRACTICE

Just a week ago my Database Team member asked me what is the best way to only select date part from datetime. When ran following command it also provide the time along with date.

SELECT GETDATE()

ResuleSet : 2007-06-10 7:00:56.107

The required outcome was only 2007/06/10.

I asked him to come up with solution by using date functions. The method he suggested was to use

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

I approved his method though, I finally suggested my method using function CONVERT.

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

The reason I use this because it is very convenient as well as provides quick support to convert the date in any format. The table which suggest many format are displayed on MSDN.

Some claims that using CONVERT is slower then using DATE functions but it is extremely negligible. I prefer to use CONVERT.

3. QUERY FOR UPDATING IN THE EXISTING TABLE COMMON COLUMN IN ALL THE TABLES

EXEC SP_MSFOREACHTABLE'

DECLARE @TBLNAME VARCHAR(255);

SET @TBLNAME =  PARSENAME("?",1);

DECLARE @SQL NVARCHAR(1000);

IF EXISTS(

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 

 WHERE TABLE_NAME = @TBLNAME AND COLUMN_NAME = ''ISDELETED''

) 

BEGIN        

SET @SQL = N''ALTER TABLE '' +  @TBLNAME + N'' ALTER COLUMN ISDELETED BIT NOT NULL;''       

EXEC SP_EXECUTESQL @SQL

END'

_________________________________________________________________
LIKE Query

--Using Like in SQL Query
SELECT * FROM ADDRESS WHERE CITY LIKE '%NAINITAL%'

-- Like Query with a Parameter
@word AS VARCHAR = NAINITAL
SELECT * FROM ADDRESS WHERE CITY LIKE '%' + @word + '%'

Read More »