Tuesday, March 13, 2012

Access Modifier of Derived Class Member

 Access Modifier in csharp is one of the most neglected topic,we many times try to to modify our code to remove the errors yet we seldom look into it as topic of interest,here in this article I explore access modifier in reference to  virtual function and inheritance

Consider Code Sample Below of a simple console application.

namespace AccessiblityAndMemberClass
{
class Program
{
static void Main(string[] args)
{
}
}

class MyBaseClass
{
protected virtual string GetClassName()
{
return "MyBaseClass";
}
}
class MyDerivedClass : MyBaseClass
{
public override string GetClassName()
{
return "MyDerivedClass";
}
}

}

Here MyBaseClass is Base class having protected virtual method "GetClassName",now when "MyDerivedClass" class which inherit from "MyBaseClass" try to override this method by widening access modifier code doesn't compile

This Scenario explains in derived class can't widen restrictive access modifier if specified in Base class.

Now consider one more code sample

class MyBaseClass1
{
public virtual string GetClassName()
{
return "MyBaseClass";
}
}
class MyDerivedClass1 : MyBaseClass1
{
override string GetClassName()
{
return "MyDerivedClass";
}
}

Here I tried to restrict access modifiers in derived class for method GetClassName that one is also not allowed.

Moral of the story is in csharp keep access modifier of derived class member same as base class if member is virtual method/function.

Sunday, March 11, 2012

Working with a Typed DataSet


We frequently use dataset object to pass queried data (from our DBMS queries & XML) to and fro between functions or data access layer & business access layer.

    This simple use of Dataset class is an example un typed dataset, here we retrieve data as collection of rows, each row in turn is collection of objects representing value e.g.

string MyColValue =     Ds.Table [0].Rows[0][“MyColName”].ToString ();
                                   Or
string MyColValue =     Ds.Table[0].Rows [0] [MyColIndex].ToString ();

   Point to note that here we used collection of tables, rows & objects in same order to come at data, our data may be from column of any Sql data type in DBMS table its output is still an object.

What is typed dataset?

      A typed DataSet is a class that derives from a DataSet. As such, it inherits all the methods, events, and properties of a DataSet. Additionally, a typed DataSet provides strongly typed methods, events, and properties. This means you can access tables and columns by name, instead of using collection-based methods.

    In a strongly typed DataSet, type mismatch errors are caught when the code is compiled rather than at run time.
   An untyped dataset differ from typed dataset in way the tables and columns in the untyped dataset are exposed. Untyped dataset exposes it as collections while typed dataset exposes them as strongly typed properties.

Why to use strongly typed dataset?
     The benefits we receive with strongly-typing our relational data is reliability, fewer mistakes, and less time spent debugging.
 
    The strongly typed DataSet offers advantages over the untyped DataSet in terms of speed and easy maintainability. The speed in accessing a typed DataSet is comparable to the faster techniques in accessing an untyped DataSet (since a typed DataSet is just a layer over an untyped DataSet) and the readability of the typed DataSet is the best.

     The XSD file stores the XML that defines the schema for the strongly typed DataSet.
It is hence easier to minimize impact of database schema changes and implement them by modifying underlying xml.




How to create strongly Typed DataSet?

I am using adventureworks sample SQL Server Database

a) Open Visual Studio, and create a new ASP.NET Website.

b) In Solution Explorer, right-click to add a new item, and select DataSet. Give it the name AdventureWorks.xsd (say). Visual Studio will recommend placing the DataSet file inside the App_Code folder, which you should allow it to do for you.

c) The AdventureWorks.xsd will open in design mode

d) Locate the Server Explorer Toolbox; navigate to your SQL Server database, and the AdventureWorks database.


e) Drag the SalesOrderHeader and SalesOrderDetail tables to your DataSet Designer window. For each table we added, Visual Studio created a strongly typed DataTable (the name is based on the original table) and a TableAdapter. The DataTable has each column defined for us. The table adapter is the object we will use to fill the table. By default we have a Fill() method that will find every row from that table.
      We add our own method to along with Fill().

How we can add our own method?
     To add own method
a)     right click on SalesOrderHeaderTableAdapter then select Add->Query
b)     Select Query Type here I am selecting “Select which return rows”
c)      Next We Need to specify actual select query
What I am specifying is

SELECT
   SalesOrderID,RevisionNumber,OrderDate,DueDate,ShipDate,Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber,CustomerID,ShipToAddressID,BillToAddressID,ShipMethod,CreditCardApprovalCode,SubTotal,TaxAmt,Freight,TotalDue,Comment,rowguid,ModifiedDate

FROM
             SalesLT.SalesOrderHeader
WHERE
            (OrderDate > @OrderDate)

Now Compile your Project.

How to Use this Type dataset?
     To your webpage say default.aspx first add reference to namespace
Like in my case
using WebApplication1.AdventureWorksTableAdapters;

Here point to note is WebApplication1 is name of website while  AdventureWorks is name of Dataset.
   Now I add a grid view to page say GridView1 ,In Page Load call BindGrid() Method.

BindGrid() method could be as follows

  public void BindGrid()
        {
            // Create the SalesOrderHeaderTableAdapter
            SalesOrderHeaderTableAdapter salesAdapter =  new SalesOrderHeaderTableAdapter();

            // Get orders that took place after July 1st, 2004
            AdventureWorks.SalesOrderHeaderDataTable Orders =   salesAdapter.GetDataBy(new DateTime(2004, 5, 1));

            // Bind the order results to the GridView
            this.GridView1.DataSource = Orders;
            this.GridView1.DataBind();    
        }

Here SalesOrderHeaderDataTable acts as a collection of SalesOrderHeader table entity.
    Let’s add following code line somewhere before binding gridview
 
DateTime dt = Orders[0].OrderDate;

Here in this code line ‘Orders’ is collection of SalesOrderHeader table entity out of which we are concern with entity at first index, from this entity we are looking at ‘OrderDate’ property, Here unlike untyped dataset we are not getting data as an object but it’s datatype is analogous to it’s actual datatype in dbms.
i.e. OrderDate is datetime column in DBMS and it’s analogous type in .Net is datetime.

We don’t to first call string then cast it as date time isn’t it a nice improvement.


Has there any utility to help us in this regard?

XSD.exe is a tool in .NET framework SDK that generates the typed dataset using xml (schema) file generated when we drag a table into Dataset Item of Visual Studio

A) xsd.exe /d /l:CS XSDSchemaFileName.xsd /n:XSDSchema.Namespace
    In this syntax, the /d directive tells the tool to generate a DataSet, and the /l: tells the tool what language to use (for example, C# or Visual Basic .NET). The optional /n: directive tells the tool to also generate a namespace for the DataSet called XSDSchema.Namespace.
     The output of the command is XSDSchemaFileName.cs, which can be compiled and used in an ADO.NET application. The generated code can be compiled as a library or a module.
The following code shows the syntax for compiling the generated code as a library using the C# compiler (csc.exe).
B) csc.exe /t:library XSDSchemaFileName.cs /r:System.dll    
   /r:System.Data.dll
The /t: directive tells the tool to compile to a library, and the /r: directives specify dependent libraries required to compile. The output of the command is XSDSchemaFileName.dll, which can be passed to the compiler when compiling an ADO.NET application with, the /r: directive.

What is alternative?

   There are alternative methods to accomplish strong typing, one can create custom classes that are more lightweight than Datasets and correspond exactly to your database.  

Conclusion
      The names of the tables and columns that strongly typed DataSet represent are properties of the typed DataSet class, writing code with typed DataSets is more intuitive and easier to maintain. By making development time faster, easier, less prone to typing errors, and by making the code more maintainable, strongly typed DataSets are a great help to developers who want to write more effective code more efficiently.

We Can also add existing stored procedure to TableAdapter we will go into it in our next blog on this subject.



Thursday, March 1, 2012

LINQ Query Vs SQL Query

While understanding LINQ it immensely helpful to compare LINQ Query
 that we like to learn with equivalent SQL query that we mastered over the years.

  Bellow are some LINQ queries and there equivalent SQL query. If anybody found it useful 
the tables used here are as bellow
   

A] Inner Join:

SQL Query
    SELECT
         B.TITLE,
         D.NAME
    FROM
         BOOKS  B
         INNER JOIN BOOKAUTHORS C  ON B.ID = C.BOOK
         INNER JOIN AUTHORS D ON C.AUTHOR = D.ID
    ORDER BY
          D.NAME
LINQ Query:
      var q = from b in db.Books
               join c in db.BookAuthors on b.Id equals c.Book
               join d in db.Authors on c.Author equals d.Id
               orderby d.Name
               select new { b.Title, d.Name };

B] Inner Query

SQL Query:
SELECT
      X.*  
FROM
      BOOKS X
WHERE
      X.CATEGORY IN (
SELECT
                              B.ID
FROM
                              BOOKCATEGORIES B
WHERE
                              B.ID % 2=0
  )
LINQ Query:
     var s = from b in db.BookCategories
                      where b.Id % 2==0
                      select b.Id;
    var t = from x in db.Books
                      where s.Contains((int)x.Category)
                      select x;
C] Like

SQL Query:

SELECT X.* FROM BOOKS X WHERE X.TITLE LIKE 'C#%'
LINQ Query:
      var t2 = from x in db.Books
              where x.Title.StartsWith("C#")
              select x;
D] Distinct:
     SQL Query:
                SELECT DISTINCT X.AUTHOR
            FROM  BOOKAUTHORS X
     LINQ Query:
          var t3 = (
                   from x in db.BookAuthors
                   select x.Author
                  ).Distinct();

E] NOT IN:
     SQL Query:
                SELECT Y.*
FROM AUTHORS Y
WHERE Y.ID NOT IN (
SELECT
DISTINCT X.AUTHOR
                        FROM
BOOKAUTHORS X
       )
     LINQ Query:
          var t5 = (from x in db.BookAuthors
                 select x.Author).Distinct();

       var t6 = from y in db.Authors
          where t5.Contains(y.Id) == false
           select y;











F] LEFT JOIN:
     SQL Query:
                SELECT
      X.NAME,
      P.TITLE
             FROM
                 BOOKCATEGORIES X
                 LEFT OUTER JOIN BOOKS P ON X.ID = P.CATEGORY
     LINQ Query:
          var t7 = from x in db.BookCategories
       join p in db.Books on x.Id equals p.Category into x_p
       from q1 in x_p.DefaultIfEmpty()
       select new {x.Name,q1.Title };

G] LEFT JOIN:
     SQL Query:
                SELECT
                  X.NAME,
P1.TITLE
FROM
                  BOOKCATEGORIES X
                  LEFT JOIN BOOKS P1 ON P1.CATEGORY = X.ID
ORDER BY
                  X.NAME,
                  P1.TITLE

     LINQ Query:
var t8 = from x in db.BookCategories
         from p1 in db.Books.Where(
p1 => p1.Category == x.Id
).DefaultIfEmpty()
         select new
                  {
                           x.Name,
                           p1.Title
                  };













  H] Group By & Count
       SQL Query:
                SELECT L.NAME,COUNT(*)
FROM
(
SELECT B.TITLE, D.NAME FROM BOOKS B
INNER JOIN  BOOKAUTHORS C ON B.ID = C.BOOK
INNER JOIN  AUTHORS D ON C.AUTHOR = D.ID
)L
GROUP  BY L.NAME

     LINQ Query:
from l in (
          from b in db.Books
          join c in db.BookAuthors on b.Id equals c.Book
          join d in db.Authors on c.Author equals d.Id
          orderby d.Name
          select new {b.Title, d.Name}
         )
group l by l.Name into g
select new { Author = g.Key, BookCount = g.Count() };







  I] Group By & HAVING
       SQL Query:
                SELECT L.NAME,COUNT(*)
FROM
(
SELECT
B.TITLE,
D.NAME
      FROM BOOKS B
INNER JOIN  BOOKAUTHORS C ON B.ID = C.BOOK
INNER JOIN  AUTHORS D ON C.AUTHOR = D.ID
)L
GROUP  BY
L.NAME
HAVING
COUNT(*) >2




     LINQ Query:
   var r1 = from l in
                          (from b in db.Books
                           join c in db.BookAuthors on b.Id equals c.Book
                           join d in db.Authors on c.Author equals d.Id
                           orderby d.Name
                           select new { b.Title, d.Name }
                           )
                 group l by l.Name into g
                 where g.Count() > 2
                 select new { Author = g.Key, BookCount = g.Count() };