Friday, December 30, 2011

Make your code Speak by using C# Region

Shout it kick it on DotNetKicks.com
Post for the beginner developer or for the developer who want their code to speak and easy to understand to other developer who is going to maintain the code, review the code etc.

To achive this thing most of the people talks about commenting class,method, property etc.but no one talks about the making region in the code which make code compact and easy to understand in form of regions. Region allow to block of the code which you can expand and minimize.

Syntax to define the region in you code is
#region Properties
//your code
#endregion Properties
So above code defines the region with name Properties. What ever code you write between between region and endregion get minimize and expand in visual studio you can see the + when minimize and - when maximize the code block.

Example of the file with the region related to BAL or DAL layer of code  :

Example of Aspx file i.e presentation layer file

Why to use Region in your code
  • It allow to group the member which is related to same functionality 
    • For Example in above code : Insert/Update method , Get Method
  • It to group Class members (private, public, methods, properties, etc.) , which you can easily see in above image
  • With the help of region long code blocks can be minimize to small block, which you can expand when you want to see the code and minimize when work done with it.
SortCut keys and  Context menu for Region
  • Shortcut key for  region is (MSDN link for this : http://msdn.microsoft.com/en-us/library/td6a5x4s.aspx )
    • CTRL+M CTRL+M (that's two key presses!) - collapse/open the current parent region
    • CTRL+M CTRL+L - Collapse/Open all regions in document recursively (meaning you might get only one line in the document - one big namespace region which is collapsed or you'll see the entire page code uncollapsed
    • CTRL+M CTRL+O - Collapse all regions not recursively
  • You can also make use of Context menu for that in you text editor
When not to use Region in your code
  • To Hide 'ugly code' that is more complicated.
  • To hide misc. code so that lazy reviewers don't look and ask about that part of code.
  • Make region to hide the Commented code.

Saturday, December 17, 2011

Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

Shout it kick it on DotNetKicks.com
In this post I am going to show how you can insert bulk data by using DataTable of C# and OpenXML function available in Sql Server.

I got requirement that "Read data from the Excel file and than after validating data
push all record in the database table". Other thing is when inserting data in database if there is failure during insertion of record, I have to rollback all inserted record.

To achieve the task I did as following

OpenXML
I created procedure which make use of OpenXML function of the sql server which allow to insert multiple record in one time. OpenXML require xml string of record to insert data in the database.
ALTER PROCEDURE [dbo].[Ins_Employee]    
(    @XmlString text    )    
AS    
BEGIN    
 SET NOCOUNT ON    
 BEGIN TRANSACTION    
 Begin Try    

  DECLARE @XMLDocPointer INT    
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XmlString    

   INSERT INTO Employee
   (Name, Email, PhoneNo)    
   SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50),-- '@Name',     
         Email VARCHAR(50),-- '@Email',     
         PhoneNo VARCHAR(50) --'@PhoneNo')     

   EXEC sp_xml_removedocument @XMLDocPointer    
   COMMIT TRANSACTION    
   Return 0;     
 End Try    
 Begin Catch    
   ROLLBACK TRANSACTION    
 End Catch    
END 
As you see in above procedure OpenXML make use of xmlDocument as input which is get created by system define procedure sp_xml_preparedocument which take xmlString as input and return XmlDocument.
Once OpenXML done task of insertion sp_xml_removedocument system proceudre is require to remove that element.
All record get inserted in once by the OpenXML function as I used transaction if the one record insertion fails all inserted record get rollback.

Following line of the code used to execute code i.e stored procedure
As you see in I am passing Element centric xml to the proceudre.
Exec Ins_Employee
 '
  
    pranay
    pranayamr@gmail.com
    99007007
  
 '
Note
If you are passing XML string as Attribute centric in it as in procedure than you need to define variable so the select statement in procedure will be
SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50) '@Name',     
         Email VARCHAR(50) '@Email',     
         PhoneNo VARCHAR(50) '@PhoneNo')
Exec Ins_Employee
  '     
       
  '

Now after done with the database , code part of the application is as below.

Uploaded Excel File which contains Employee data


Presentation layer
Following function in presentation layer read data from the excel file, which is uploaded on server.
private void ReadAndInsertExcelData()
{
     int i;
     bool blValid = true;
     OleDbCommand ocmd;
     OleDbDataAdapter oda;
     DataTable dtDetails;
     DataSet dsDetails;

     OleDbConnection oconn = new OleDbConnection     
          (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
               Server.MapPath("~/Upload/MonthlyActual.xls") + ";Extended 
               Properties='Excel 8.0;HDR=YES;IMEX=1'");
     try
     {
          ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
          oda = new OleDbDataAdapter(ocmd);
          dsDetails = new DataSet();
          oda.Fill(dsDetails, "DATA");
          dtDetails = dsDetails.Tables[0];
          dsDetails.DataSetName = "ROOT";
          i = 0;

          DataRow[] drLst = dtDetails.Select("(Name is null) or (Email is 
                              null) or (PhoneNo is null)");
          if (drLst.Count() > 0)
               blValid = false;
          if (blValid)
          {
               XMLController xMLController = new XMLController();
               xMLController.Ins(BaseLineType, dtDetails);
          }
     }
     catch 
     {
          lblMsg.Text = ex.Message;
          lblMsg.ForeColor = System.Drawing.Color.Red;
     }
     finally
     {
          lblMsg.Text = "Data Inserted Sucessfully";
          oda = null;
          dtDetails = null;
          dsDetails = null;
     }
}

Business Layer
Function below takes DataTable as input and generate XML string, As you see below I used StringWriter which use StringBuilder object, DataTable make use of StringWriter and write XML string in StringBuilder object.
public int Ins(DataTable pImportTable)
{
     int IsSuccess = -100;
     try
     {
          StringBuilder sbXMLString = new StringBuilder();
          System.IO.StringWriter sw = new System.IO.StringWriter
                                                  (sbXMLString);
          pImportTable.WriteXml(sw);

          DALXML dALManualCost = new DALXML();
          dALManualCost.Ins(sbXMLString.ToString());
          IsSuccess = dALManualCost.IsSuccess;
     }
     catch
     {
          throw;
     }
     return IsSuccess;
}
Note:Above method generate Element centric XML string.

Now if you want to write out the Attribute centric xml file you just need to replace the line of datatable.WriteXml with the below code for loop also you dont require to use the StringWriter object.
sbXMLString.Append("");
          for (int i = 0; i < pImportTable.Rows.Count; i++)
          {
            sbXMLString.Append("<DATA ");
            sbXMLString.Append("Name='" + 
                         pImportTable.Rows[i][0].ToString().Trim() + "' ");
            sbXMLString.Append("Email='" + pImportTable.Rows
                         [i][1].ToString().Trim() + "' ");
            sbXMLString.Append("PhoneNo='" + 
                         pImportTable.Rows[i][2].ToString().Trim() + "' ");
            
            sbXMLString.Append(" />");
 
          }
          sbXMLString.Append("");

DataLayer 
Now this layer call the stored procedure which pass the xmlstring of employee to database. Return parameter will tell that its successfull insert or not.
public void Ins(string pXMLString)
{
     try
     {
          Database db = CommonHelper.GetDataBaseInstance();
          DbCommand cmdXML = db.GetStoredProcCommand
                         (SP_INSERT_STAGINGMANUALCOSTMONTHLY);

          db.AddInParameter(cmdXML, "XmlString", DbType.String, 
                                                       pXMLString);
          db.AddParameter(cmdXML, "ret", DbType.Int32,                     
          ParameterDirection.ReturnValue, "", DataRowVersion.Current, 
                                                            IsSuccess);

          db.ExecuteNonQuery(cmdXML);
          IsSuccess = Convert.ToInt32(db.GetParameterValue(cmdXML, "ret"));
     }
     catch
     {
          IsSuccess = -100;
          throw;
     }
}

Note : This is the one technique I found useful to enter bulk amount of data in database in one transaction. There are also other available which might be more efficient than this.

Sunday, December 4, 2011

Month and Year Picker UserControl

Shout it kick it on DotNetKicks.com
I am going to discuss about the pop-Up calender control created by me for selecting Month and Year. By this post you will get to know how to easily you can create pop-Up window using jQuery as well as little about how to use it as Asp.Net user control. Well that's not it you can also utilize this thing in you web project develop in any other language.

Below is the picture of the control


Now following is step by step Explanation of control created and how to use that control in your application.

Step 1 : Control Design i.e ASCX file
To create user control you need to left click on the solution and click on Add New Item >> than in screen select WebUserControl as below

After you click on Add button one ASCX file is get created , than you can add the following line of code to get display as show in the fist image above
<%@ Control Language="C#" AutoEventWireup="true" CodeFile="DateBox.ascx.cs" Inherits="DateBox" %>

lblText : - Label to hold the text to display with the text box.
txtDate : - TextBox to hold the text get selected by user from the pop-Up calender window shown in the second image above.
btnDate : - Button control, when get clicked it display the pop-Up calender window.

Step 2 : Control Code-Behind i.e .CS file
Once you done with the control design you can paste the following code in the codebehind i.e. .cs file
public partial class DateBox : System.Web.UI.UserControl
{
    public string LabelText
    {
        get
        {
            return lblText.Text;
        }
        set
        {
            lblText.Text = value;
        }
    }

    public string TextData
    {
        get
        {
            return txtDate.Text;
        }
        set
        {
            txtDate.Text = value;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (lblText.Text == string.Empty)
                labelContainer.Visible = false;

            this.btnDate.Attributes.Add("OnClick", "return buttonClick(this,'"+ txtDate.ClientID +"');");
        }
    }
}
Property
LabelText : - Used to get/set the text for the label control.
TextData : - Used to get/set the text of the textbox conrol which is going to display selected date.

Method 
Page_Load : - method get executed when page load check, if the label control don't have text make the container div set to visible off. Adding client script on button control and passing button control itself and textbox control client id which going to use by the jQuery/javascript to display selected date.

Step 3 : Code to create popUp Calender i.e. .JS file
After you done with Stpe 2, Add new javascript file to your solution and paste the following code in it
jQuery(document).ready(function ($) {

    var divContainer = $("");

    var divButtonHolder = $("
"); var buttonOk = $("
"); var buttonCancel = $("
"); var divSelectHolder = $("
Select Month and Year : 
"); var ddlmonth = $("    "); var ddlyear = $(""); var i = 0; var month = 1; for (i = 1985; i <= 2020; i++) { ddlyear.append(''); } i = 0; for (i = 1; i <= 12; i++) { if (i < 10) { month = "0" + month; } ddlmonth.append(''); month++; } divSelectHolder.append(ddlmonth); divSelectHolder.append(ddlyear); divContainer.append(divSelectHolder); divButtonHolder.append(buttonOk); divButtonHolder.append(buttonCancel); divContainer.append(divButtonHolder); $('body').append(divContainer); }); var txtDate; function buttonDoneClick() { var month = $("#ddlmonth").val(); var year = $("#ddlyear").val(); $(txtDate).val(month + year); Close_Popup() } function buttonClick(obj,id) { txtDate = ('#' + id); var position = $(obj).position(); $('#window').css({ top: position.top, left: position.left }).fadeIn('fast'); return false; } function Close_Popup() { $('#window').fadeOut('fast'); }
Variables
divContainer : - Main container which contains all controls of pop-Up.
divButtonHolder : - Hold button controls of the window i.e Done and Cancel.
buttonOk : - Hold reference of button control called Done.
buttonCancel : - Hold reference of button control called Cancel.
divSelectHolder : - Hold Select control i.e Month and Year combo.
ddlmonth : - Hold reference of select control called Month.
ddlmonth : - Hold reference of select control called Month.
ddlyear : - Hold reference of select control called Year.

jQuery method
.append : - allows to append control to the control selected by filter.
.ready : - method contains the code for initialize the variable, add the option to select control and attach all created control to body of the page.
.position : - method to get the location of the control which selected by selector.

Method
buttonDoneClick : - Get the selected value of the Month and Year combo box and display text in the textbox attached with the calender control.
buttonClick : - Display calender popUp window , the method make use of position method of jquery to get the location of button and assign it to popUp window.
Close_Popup : - To close the popUp window called when the Cancel button of the popUp window clicked.

Step 4 : popUp window Style sheet i.e .Css file
Add following style shit to Css file which you can able to add from the solution.
#window {
margin: 0 auto;
border: 1px solid #000000;
background: #ffffff;
position: absolute;
left: 25%;
width:250px;
height:50px;
padding:5px;
}
Style is get attached with the the div which is having id called window i.e the popUp window.

Step 5 : How to use control in your project ASPX file
<%@ Register  Src="~/DateBox.ascx" TagPrefix="UC" TagName="DateBox"   %>



So to use control in your application you just need to register the user control, need to include jquery, created js and css file. And you can make use of Lable property to display text with control.

Summary
So its quite easy to create popUp control and use it as User Control in your application. But if you are working on other than .net than just you need to use .Js and .Css file and need to create control on your page.