Coding tricks in .net part 01

In this post i'm going to discuss some of the tricks in .net application that I've used.to avoid unnecessary code lines , defensive programming are the required talent of the programmer.

1)Error handling with showing message
always write your code starting point within try catch block. eg. only for raise it event root (button click event)
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtName.Text.Trim().Length == 0)
                {
                    MessageBox.Show("Name is required");
                    return;
                }
                /// do some coding here
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }
        }.
above code is nothing wrong, but it used unnecessary return statements.
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtName.Text.Trim().Length == 0)
                {
                    throw new ApplicationException("Name is required");
                }
                /// do some coding here
            }
            catch (ApplicationException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }
        }
Application level errors is captured by ApplicationException class.there're no any return and more clear and specific.

2) Windows application Stores object in textbox (suppose you want to display Employee name in text box and also required to hide Employee id form the screen.one textbox for employee and lable for empid which is invisible)
in Textbox has property called Tag , that can hold required data invisibly.
            Employee emp = new Employee() { ID = 11, Name = "NImal" };
            //Assing
            txtName.Text = emp.Name;
            txtName.Tag = emp;//obj is employee related data eg.ID,DateJoind etc

            //retrive
            Employee empObje =(Employee) txtName.Tag;

////////////////////////////////////
    public class Employee
    {
        public string Name { get; set; }
        public int ID { get; set; }

    }

3) add rows to DataGridView
           int i= dataGridView1.Rows.Add();
           dataGridView1.Rows[i].Cells["Column1"].Value = 22;

           dataGridView1.Rows[i].Cells["Column2"].Value = "SSS";
above code is to add new row to datagridview (which has 2 columns).nothing wrong it.what will happen if column names are changed.code will be complied at run time will be caused an error .because column names are not strongly type. dataGridView1.Rows is duplication among the code.to alternative this issue , here code snippet.

           DataGridViewRow gr =dataGridView1.Rows[dataGridView1.Rows.Add()];
           gr.Cells[Column1.Name].Value = 22;

           gr.Cells[Column2.Name].Value = "SSS";

4)strongly type session and viewstate (asp.net)
in asp.net application we stored data as globally (session) and page level (view state).
eg. Session["abc"]=1500;
      ViewState["aa"]="xx";
these are not strongly types ,only accept object and we have cast it before use it.
        //Session
        public int sEmpID
        {
            get {
                if ((Session["empID"] == null))
                {
                    return 0;//throw new applicationException("Session expired");
                }
                else
                {
                    return Convert.ToInt16(Session["empID"]);
                }
            }
            set { Session["empID"] = value; }
        }

        //ViewState
        public bool vsIsEdit
        {
            get { return Convert.ToBoolean(ViewState["IsEdit"]); }
                               set { ViewState["IsEdit"] = value; }
        }

/////////////// assign values
            sEmpID = 22;


            vsIsEdit = true;

happy coding.........

Table value parameter with .net (real world example) Part 01

hi all

these days dark clouds gives free bath chance while are traveling by bike. Happy feeling when my boots are getting wet.

In this post that I'm going to demonstrate another cool feature of .net with sql server. how to send bulk of data (ado.net) as parameter to stored procedure?  is it clear about the topic. if it's not here the scenario.

Suppose we're developing procurement systems which generate the purchase order .I'll take a small part of the system.There're lot of article in internet about the topic but I wanted to give it with real life example to get the idea.

In above has mentioned the tables which has one to - many relationship, it means one po has many items (simple example).in earlier what I did was when saving po , one sp for po and another sp for po details.

here is the code snippet c#.

first fill the user objects 

            List<PODetails> lstOfPoDetails = new List<PODetails>();

            PODetails pod;

            pod = new PODetails() {POItem="ABC",Qty=22 };

            lstOfPoDetails.Add(pod);

            pod = new PODetails() { POItem = "xyz",Qty=4 };

            lstOfPoDetails.Add(pod);

            PO po = new PO()

            {

                PODate = DateTime.Now,

                CreatedUser = 33,

                lstPODetails = lstOfPoDetails

            };

Save user objects to database

 using (var Ts=new  TransactionScope())

            {

                using (var con=new SqlConnection("con is here"))

                {

                    //First Save po get the po id to pass the po deatils

                    SqlCommand com;

                    com = new SqlCommand("InsertPO", con);

                    com.Parameters.AddWithValue("@podate", po.PODate);

                    com.Parameters.AddWithValue("@user", po.CreatedUser);

                    #region Get the POID to pass the po detils

                    SqlParameter para = new SqlParameter("@poid", 0);

                    para.Direction = ParameterDirection.InputOutput;

                    com.Parameters.Add(para);

                    com.ExecuteNonQuery();

                    int poID = (int)com.Parameters["@poid"].Value;//Task one completed

                    //Save po details with po id

                    foreach (PODetails p in po.lstPODetails)

                    {

                        com = new SqlCommand("InsertPODetails", con);

                        com.Parameters.AddWithValue("@poid", poID);

                        com.Parameters.AddWithValue("@item", p.POItem);

                        com.Parameters.AddWithValue("@Qty", p.Qty);

                        com.ExecuteNonQuery();

                    }

                    #endregion

                    Ts.Complete();

                }

            }

nothing wrong above code   ,but we have to specifically work with the stored procs, one for po and another for podetails.podeatils has iteration that calls to proc for its each iterations. This is a disadvantage of this method (performance issue).to alternative to this approach , sql serer database has table value parameter to accomplish this task it provides more efficient and less code approach.

accomplish this task in Table Type parameter in sql server.

In Sql server database under the programmability -> Types has User Defined Table Types

here are the script for the table
CREATE TYPE [dbo].[type_po] AS TABLE(
[poID] [int] NULL,
[CreatedDate] [datetime] NULL,
[poDetID] [int] NULL,
[Item] [varchar](50) NULL,
[qty] [decimal](18, 4) NULL
)

we have to use the create type script to create type table.

here are the script of insert po & podetails
create proc InsertPOs
-- which accepts one table value parameter. 
-- It should be noted that the parameter is readonly
@poType   dbo.typepo readonly

as
begin
declare @poID int

--Insert PO record and get poid
INSERT INTO  [dbo].[PO]  ([CreatedDate])    
(select top 1 [CreatedDate] from @poType)

set @poID=(select IDENT_CURRENT('PO'))

-- Insert PODetail reference with poid
INSERT INTO  [dbo].[PODetails] (poID,Item,qty)
select  @poID,Item,qty  from @poType
end

database part is done.
1)create type table
2)using that type table create proc to insert po

now we'll move on to c# side.now create DataTable (C#) mapping to typepo Type table.

//Create DataTable matching with the Type Table column names and data types
            //CREATE TYPE type_po AS TABLE 
            //(
            // [poID] [int]  ,
            // [CreatedDate] [datetime] ,
            // [poDetID] [int]  ,
            // [Item] [varchar](50) ,
            // [qty] [decimal](18, 4) 
            //)

            DataTable type_po = new DataTable();
            type_po.Columns.Add("poID", typeof(int));
            type_po.Columns.Add("CreatedDate", typeof(DateTime));
            type_po.Columns.Add("poDetID", typeof(int));
            type_po.Columns.Add("Item", typeof(string));
            type_po.Columns.Add("qty", typeof(decimal));

            DataRow dr;
            dr = type_po.NewRow();
            dr["poid"] = 0;
            dr["CreatedDate"] = DateTime.Now;
            dr["podetid"] = 0;
            dr["item"] = "ABC";
            dr["qty"] = 100;
            type_po.Rows.Add(dr);

            dr = type_po.NewRow();
            dr["poid"] = 0;
            dr["CreatedDate"] = DateTime.Now;
            dr["podetid"] = 0;
            dr["item"] = "xyz";
            dr["qty"] = 200;
            type_po.Rows.Add(dr);

pass data table to sp

                using (var ts = new TransactionScope())
                {
                    using (var con = new SqlConnection(Con))
                    {
                        con.Open();
                        SqlCommand com = new SqlCommand("DBO.InsertPOs", con);
                        com.Parameters.AddWithValue("@poType", t);
                        com.CommandType = CommandType.StoredProcedure;
                        int x = com.ExecuteNonQuery();
                    }
                    ts.Complete();
                }

that's all.this is much easy way to handle one to many scenario.
Advantages :round trip to the database is minimized(only one)
                   less coding and all logic in one place .

Disadvantage : can't alter type table and can't delete when have reference it.(solution : one type table to one sp )

in this part 01 , only demonstrate it's basic functionality of Table value parameters.in part 02 i intend to disuses some of the advance features of it (eg : debugging ).
download script
happy coding

Jquery Message box in asp.net application

In asp.net application, there is not any inbuilt message box feature .what developers do is display message to the Label. It’s not much get attention from the user because, not focused like windows application message box.

In java scripts alerts is one of the way display message, its ok but not much user friendly.

jquery (Java script library), that provides more facility to handle client side effects and data manipulations (calling to web services).In this post I’m going to demonstrate prompt message box using jquery UI library.cool feature is we can process server side codes and then we can display message. Click button1 and save and display message box.

        protected void Button1_Click(object sender, EventArgs e)

        {

            // do what ever task in asp.net (Server side coding)

            //save data to database and dispay message

            //

            //

            SiteMaster.NewMessageBox(" Data Saved", SiteMaster.ErrorType.Information, this);

        }

here is project structure;

download

happy coding....

Crystal Reports & Stored Proc in Visual Studio

Today I’m going talk about the reporting issues which I have been faced. In .net we can design crystal report in different ways. Using XML Schema, Dataset component some of methods which I’ve used, are much difficult to maintain. Suppose you have to change the report field, then you have to change xml or dataset by manually and after that refresh the report. Other disadvantage is, we have to provide the data source (DataTable,DataSet or List of objects)

Here is the eg.

this is method return data to display . tblReport datatable column names should be match with the xml or dataset columns names (mapping).

DataTable tblReport=GetAllDetails();

ReportDocument rptDoc = new ReportDocument();

rptViewWorkOrderDetailsWithYC rpt = new rptViewWorkOrderDetailsWithYC();

rptDoc = rpt;

rptDoc.SetDataSource(tblReport);

rpt.SetParameterValue("@@@Company", "cc");//Pameters passing to the report

rpt.SetParameterValue("@@@Heading", "cccc”);

crystalReportViewer1.ReportSource = rptDoc;  //Display report in Report Viewer

Above code is well running code, but it has to lot of mapping things.

with Proc as data source:

Design crystal reports with stored procedure are more efficiency way to build reports. It is not needed any data sources. We can use ado data provider to access the store proc in database. If proc has any parameters then it will be added automatically to report.

here are the steps :

Step 1:Make the connection to the crystal report.add crystal report to project and using selection expert set the connection (ole db - ado).

Step 2:after completing step 1, then (Assume we already have created a proc in database)add proc to report.         

Developer PC1 and DatabaseServer DevelopementServer1 (discuss later )

Test is the SP that i used.

create proc [dbo].[Test]

@UserName varchar(50)

as

select 'MYPC ' + @UserName as test

In Test sp has parameter as @Username, it will be automatically added to the report.

 report desining part is done.here code snippet to run the report.

        ReportDocument rptDoc = new ReportDocument();

        Report.TEST rpt = new Report.TEST();

        rptDoc = rpt;

        rpt.SetParameterValue("@UserName", "cc");//Pameters passing to the report

        crystalReportViewer1.ReportSource = rptDoc;

how easy it is.no any column mapping & only need proc that's all.this will be displayed report as you wished in production (in pc1 with DevelopementServer1 )

when this report is hosted into live system , there no errors but data will be wrong .because it will be displayed records in pc & DevelopementServer1 machines.

here is the issue.

PC01 is the pc which is developed, remember report is added to proc in this (pc01)machine database.now it has changed to new server.got the point.

now it is needed to change the server name as according to environment.in .net windows or web application we used file called app or web configuration file to share common-thing through out application.database connection is usually stored in this config file , we change it according to development environment.

  <connectionStrings>

    <add name="con" connectionString="Data Source=pc01;Initial Catalog=Teset;User ID=sa" providerName="System.Data.SqlClient" />

  </connectionStrings>

how do we overcome the these situation in crystal report with proc in .net.

simple thing, have to change the connection string in reprot dynamically according to config file connection string.

        #region change the report data server according to app.config

        ConnectionInfo getConnectionInfo()

        {

            ConnectionInfo ConnInfo = new ConnectionInfo();

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString());

            ConnInfo.UserID = "sa";

            ConnInfo.Password = "";

            ConnInfo.ServerName = con.DataSource;

            ConnInfo.DatabaseName = con.Database;

            return ConnInfo;

        }

        private void SetDBLogonForReport(ReportDocument reportDocument)

        {

            ConnectionInfo connectionInfo = getConnectionInfo();

            Tables tables = reportDocument.Database.Tables;

            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

            {

                TableLogOnInfo tableLogonInfo = table.LogOnInfo;

                tableLogonInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(tableLogonInfo);

            }

        }

        #endregion

here are the two method it changes the connection using connection in config file.

before set the crystalreportviewer1 report source add SetDBLogonForReport with report document parameter.

            SetDBLogonForReport(rptReportDocument);

            crystalReportViewer1.ReportSource = rptReportDocument;

that's all.hope this will help to all guys.

happy coding...

Sql server row version data type in .net application

Today I'm going to discuss one of the most critical problems but which is not being focused by most of developers.

Here this is the scenario: let’s say we have a Leave Management system, employee has requested a leave and it goes to approval stage. Every manager can see all leaves, and two managers has loaded the same request REQ1 in there computer. What will happen if both managers approved the same request or done some different option (OR something different). It means we have to check the request states (weather it is already approved) before saving it.

 Solution 1:

We have to check the records before saving it. This approach is good but has to be done with lot of data checking with the data base. (OR have to do lot of data checking with the database)

Solution 2: (suggested)

In sql server there is a data type called timespan or row version (sql server 2008). Here is the link about the row version row version. 

The real advantage is, when   we made an update to the table the row version column changes its status to another, just like its status is updated to next sequence. In development we can flag the status easily. But the problem is that it's not in readable format e.g. .0x0000000000018F17.we have to convert this value to bit array in c#. As an alternative for that we can cast row version into sql server big int.

Here is the example

Scripts:

Create a database name test and run below scripts

create TABLE [dbo].[Requests](

 [ReqID] [int] IDENTITY(1,1) NOT NULL,

 [EmpID] [int] NOT NULL,

 [IsApproved] [bit] NULL,

 [Remarks] [varchar](50) NULL,

 [rv] [timestamp] NOT NULL,

 )

insert into Requests values(10,null,'Remarks 1',null)

select * from Requests

The RV column is the row version and sees the results. Change the employee table and see the results.

select *, cast(rv as bigint) from Requests

Run the above select and see the different. We can get the bits of array into big-int data type.

In Requests table ReqID is the primary key and rv value also needs to be checked for the changes. Here is the sample code for validating the changes.

Here is the completed project 

download

happy coding

List of Custom Objects Bind to combobox or datagridview in single method in .net

hi all,
This is my first post regarding the .net to share my experience with you all guys.
I've faced much inconvenience when binding a combo box in net (Windows app), because we have to write more lines to complete the task.
Eg.

            Combobox1.DataSource =GetAllEmployees();

            Combobox1.DisplayMember = "EmpName";

            Combobox1.ValueMember = "EmpID";

Suppose GetAllEmployees() is a method that returns all list of employees. This is a typical binding code. In production environment systems are developed in some kind of architecture. In this example I’m using layered architecture to avoid the complexity of the application (Never develop application in single project because earlier it's easy for maintenance (OR to maintain) but day by day it will increase the complexity and then it will be nightmare to maintain.)          

How cool if we can convert above 3 lines to single line like this,

ComboBox1.EasyBind<Employee>(GetAllEmployees(), CmbSelection.Please_Select,"EmpID", "EmpName");

OR

ComboBox1.EasyBind<Employee>(GetAllEmployees(), "EmpID", "EmpName");

We can create user defined extension methods in .net to use like predefined methods like tostring() etc ,

EasyBind is .net customized extension method which is used to customize the binding.  if we have a list of employees, it will be bound to the combo box. CmbSelection is enum to attach the default selection (Please_Select).
Appreciate any comments or issues regarding this post.

Happy coding.

Download