Thursday, July 30, 2015

Cannot convert null to 'bool' because it is a non-nullable value type

I got following error: Exception Details: Microsoft.CSharp.
RuntimeBinder.RuntimeBinderException: Cannot convert null to 'bool' because it is a non-nullable value type, today while referencing a .dll file and creating object of a class.

I could hardly solve the issue by just looking into the stack trace. But I could back trace the changes I had made and point out the spot it occurred at. I found that it was caused by improper passing of null object where constructor of my repository class expected object. Some hit and trail got me out of the issue. Please write in the comments if you have faced the exceptions and solved it.

Thank you.

Wednesday, July 29, 2015

Convert datetime to MMDDYY format in sql server

If you ever needed to convert datetime field to MMDDYY format in sql server, read on. I am going to present you a quick tips on how to get date or datetime field in sql server in MMDDYY format. Let's do it step by step.

First of all I would like to remind you to learn how to convert date to different date string using Convert function of ms sql server. This will prepare background for the technique used in this article.

For the sake of easiness, I will use current date and time as the date field we would like to convert to MMDDYY format. As you all know, getdate() returns you current date and time, as below:

select getdate() as Today
Output is: 2015/07/29 05:00:47.563

First we will get this date in mm/dd/yyyy format. Here is how:
select convert(varchar(10),getdate(),1)
Output is: 07/29/15

Now we are almost there! Let replace those forward slashes (/) with empty string. And voila! We have MMDDYY. Here is the script:

select replace(convert(varchar(10),getdate(),1),'/','')
Output is: 072915

All the steps are put together below.
















Now we have successfully converted datetime field in sql server into MMDDYY format.

Thursday, March 14, 2013

Object Relational Database Features Implemented with Oracle

Oracle is a modern and feature-full database that supports many features of an object relational database system. Oracle supports type definition, type inheritance, collection type, member function, nested table, association and aggregation relationships. In this article, I have implemented all features other than association and aggregation relationships.

1. Database Structure
I have considered a database for a simple ecommerce site. The database will consist of different type of products having a bunch of attributes inherited from Products table whereas each table will have some other attributes of its own. So here I have a product object and a Clothes table that inherits the product object. Each clothe is available in multiple sizes. Further, each clothe tuple is available in multiple colors represented by a collection array in this example. For simplicity, I have included only one such table that inherits from products table where there could be other tables like Jewellery, Bags, Cosmetics and so on. Further, each product has multiple categories which is implemented by a nested category table. In our example, we charger 10% extra on the price of each product. Hence I have used a member function that returns 10% extra of the price.

2. Object Relational Structure
Product is an object, not the table, from which another object or type ClotheType is inherited. Product also contains a nested table Category so that each product row contains a reference to an instance of the table Category. Finally we create a table Clothes of the type ClotheType. ClotheType consists of two attributes: clothesize and clothecolor where clothesize is another Type and clothecolor is a collection array.

The object oriented concept of function has been included in ClotheType object which we call member function in Oracle.

3. DDL Script for the Database Objects

Create a type CategoryType
Create or replace type CategoryType as object(
categoryid int,
categoryname varchar2(50)
) not final

Create a table of CategoryType
Create or replace type ProductCategory as Table of CategoryType

Create an inheritable (specified by "not final" construct) object Product
Create or replace type Product as object(
productid int,
productname varchar2(100),
price number(7,2)
) not final

Create type ProductSize
Create or replace type ProductSize as object(
sizecode varchar2(20),
sizevalue varchar2(20)
)

Create collection array for colors
create or replace type ColorArray as varray(20) of varchar2(20)

Create a type ClotheType that inherits from Product
Create or replace type ClotheType under Products(
clothesize productsize,
clothecolor colorarray,
clothecategory productcategory,
member function total_price return number
)

--define the body for the member function total_price
create or replace type body ClotheType as
 member function  total_price return number is
 begin
                 return (price + price * 0.1);  
 end;
end;

Finally create a table of the type ClotheType
create or replace table Clothes of clothetype
nested table clothecategory store as prod_cat

4. Inserting rows into the Clothes table
Now we insert rows into the Clothes table.
  
   insert into clothes
   values(3,'V-neck tshirts with full sleeve',250,
   productsize('M','Medium'),
   colorarray('White','Blue','Black'),
          productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
   )
      );
   
   insert into clothes
   values(2,'Round-neck tshirts',450,
   productsize('L','Large'),colorarray('Red','Black','Green'),
          productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
      )
      );
     
   insert into clothes
   values(1,'Both side design tshirts',600,
   productsize('S','Small'),colorarray('Red','Blue','Black'),
             productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
      )
      );    
5. Querying the table Clothes
a. Normal query to select the columns productid, productname, pirce and productsize's columns sizecode and sizevalue

select c.productid,c.productname,c.price,
 c.clothesize.sizecode,c.clothesize.sizevalue
from clothes c

select from a table in object relational database Oracle


b. Querying the categories in a product from the nested table clothecategory

select c.productid,c.productname,p.categoryid,p.categoryname
 from clothes c,table(c.clothecategory) p
where c.productid=1;

select c.productid,c.productname,p.categoryid,p.categoryname
 from clothes c,table(c.clothecategory) p
where c.productid=2;

select from nested table in object relational database Oracle


c. Querying the colors associated with a product

select c.productid, c.productname,p.*
 from clothes c, table(c.clothecolor) p
where c.productid=2;

select from collection array in object relational database Oracle


d. Querying the total price by calling the member function total_price

select p.productid, p.productname,p.price,p.total_price() "Total Price"
 from clothes p;

select using member function in object relational database Oracle


6.Conclusion
we realized and implemented a bunch of object relational features of Oracle database. Some of the features we worked with were type inheritance, type as a column, datatype collection and nested table, as well as member function. We developed DDL script to create such objects and performed select queries. All in all, we got the good glimspe of object oriented programming in Oralce database.

Tuesday, February 19, 2013

Add clickable rows in csharp DataTable and bind to asp.net gridview

Responding to a good demand from a lot of readers, I have now presented a way to create DataTable programmatically where rows accept html and hence they can be made clickable. Complementing to my previous post on creating DataTable programmatically in asp.net, this tips will bind DataTable to an asp.net GridView where one of the columns in each row contains clickable html anchor.

Below is the code snippet in C#.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //get data from programmatically created DataTable and bind to GridView
            gvTable.DataSource = CreateTable();
            gvTable.DataBind();
        }
    }


//Create DataTable programmatically
    //In this example, there are three columns
    //namely ID, WebsiteName and URL
    private DataTable CreateTable()
    {
        //create datatable
        DataTable table = new DataTable("Websites");
        
        //add columns to the table
        table.Columns.Add("ID", typeof(int));
        table.Columns.Add("WebsiteName", typeof(string));
        table.Columns.Add("URL", typeof(string));
        //add as many rows as you want
        AddNewRow(1, "dotnetspidor", "http://dotnetspidor.blogspot.com", table);
        AddNewRow(1, "asp.net", "http://asp.net", table);
        AddNewRow(1, "codeplex", "http://codeplex.com", table);
        return table;
    }


//Add new row to a table
    private void AddNewRow(int id,string website, string url,DataTable table)
    {
        DataRow row = table.NewRow();
        row["ID"] = id;
        row["WebsiteName"] = website;
        //get url from GetURL method
        string link = GetURL(website, url);
        row["URL"] = HttpUtility.HtmlDecode(link);
        table.Rows.Add(row);
    }


//create html anchor from website name and it's url
    private string GetURL(string website, string url)
    { 
        return "<a href=\""+url+"\">"+website+"</a>";
    }

Here goes the design code for the GridView. Please note the property HtmlEncode="false" in the third BoundField. This prevents the GridView from rendering encoded html so we retain the html code from our DataTable column.

    <asp:gridview autogeneratecolumns="false" id="gvTable" runat="server" width="500px">  
<columns>
<asp:boundfield datafield="ID" headertext="ID">
<asp:boundfield datafield="WebsiteName" headertext="Website">
<asp:boundfield datafield="URL" headertext="URL" htmlencode="false">
</asp:boundfield></asp:boundfield></asp:boundfield></columns>
</asp:gridview>

The output looks like below.
Fig: Output of programmatically generated DataTable in a gridview - with clickable row
Happy Programming!!
Shout it

Saturday, February 9, 2013

Strip html tags and extract subset of string from text using regular expression in c-sharp

Today I am presenting a quick tips on how to strip html from text using regular expression (with Regex class) in C#. In a scenario like presenting a blurb or summary of certain characters we may need to remove html tags from a html string (of news details, article details etc.). I have following function in my Helper library for the very problem.


    /// 
    /// Strip out html tags from text
    /// 
    /// Source string
    /// 
    public static string StripTagsFromHtml(string source)
    {
        return Regex.Replace(source, "<.*?>", string.Empty);
    }


To extract a number of characters from the source string, we can extend the function as following.

    /// 
    /// Strip out html tags from text and return extract from it
    /// 
    /// Source string
    /// Number of characters to extract
    /// 
    public static string StripTagsFromHtml(string source, int characterCount)
    {
        string stripped = Regex.Replace(source, "<.*?>", string.Empty);
        if (stripped.Length <= characterCount)
            return stripped;
        else
            return stripped.Substring(0, characterCount);
    }

Happy programming!

Shout it

Popular Posts

Recent Articles