Friday, 23 January 2015

Text box will accept numeric value with two decimal point

Text box will accept numeric with two decimal point.

The below JQuery function used with onkeypress attribute of textbox.

Code
isNumberKey = function (evt, obj) {
                var charCode = (evt.which) ? evt.which : evt.keyCode + ".10";
               
                if (charCode > 31 && (charCode < 48 || charCode > 57) && charCode != 46
                    && charCode != "46.10" && charCode != "37.10" && charCode != "39.10"
                    && charCode != "9.10" && charCode != "35.10" && charCode != "36.10") {
                    return false;
                }
                else {
                    var cursorPosition = $(obj).getCursorPosition();
                    var dotPosition = obj.value.indexOf('.');                   
                    if (dotPosition == -1)
                    {
                       if (charCode == "9.10" || charCode == 8 || charCode == "37.10" || charCode == "39.10"
                            || charCode == "35.10" || charCode == "36.10" || charCode == "46.10") {
                            // tab, backspace, left arrow, right arrow, home, end, delete
                            return true;
                        }
                        var charBeforedot = obj.value.split('.')[0].length;
                        var valueBeforedot = parseInt(obj.value.split('.')[0]);
                        if (valueBeforedot == 10 && charCode == 48) {
                            return true;
                        }
                        if (valueBeforedot >= 10 && charCode != 46) {
                            return false;
                        }
                        if (valueBeforedot >= 100 || charBeforedot >= 3) {
                            return false;
                        }                       
                        if (dotPosition < 0) {
                            if (charBeforedot >= 2 && charCode != 46) {
                                return false;
                            }
                        }
                    }
                    else if (dotPosition >= cursorPosition) {
                        if (dotPosition >= 0 && charCode == 46) {
                            return false;
                        }
                        if (charCode == "9.10" || charCode == 8 || charCode == "37.10" || charCode == "39.10"
                            || charCode == "35.10" || charCode == "36.10" || charCode == "46.10") {
                            // tab, backspace, left arrow, right arrow, home, end, delete
                            return true;
                        }
                        var charBeforedot = obj.value.split('.')[0].length;
                        var valueBeforedot = parseInt(obj.value.split('.')[0]);
                        if (valueBeforedot == 10 && charCode == 48) {
                            var valueAfterdot = parseInt(obj.value.split('.')[1]);
                            if (valueAfterdot > 0) {
                                return false;
                            }
                            else {
                                return true;
                            }
                        }
                        if (valueBeforedot >= 10 && charCode != 46) {
                            return false;
                        }
                        if (valueBeforedot >= 100 || charBeforedot >= 3) {
                            return false;
                        }                                              
                    }
                    else {
                        if (charCode == "9.10" || charCode == 8 || charCode == "37.10" || charCode == "39.10"
                            || charCode == "35.10" || charCode == "36.10" || charCode == "46.10") {
                            // tab, backspace, left arrow, right arrow, home, end, delete
                            return true;
                        }
                        if (dotPosition >= 0) {
                            var valueBeforedot = parseInt(obj.value.split('.')[0]);
                            if (valueBeforedot >= 100 && charCode != 48)
                            {
                                return false;
                            }
                            var len = obj.value.length;
                            var charAfterdot = (len + 1) - dotPosition;
                            if (charCode == 46)
                            {
                                return false;
                            }
                            if (charAfterdot > 3) {
                                return false;
                            }
                        }
                    }
                }
                return true;
            }

Wednesday, 19 February 2014

Datatypes in SQL Server

Char DataType
Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.

varchar DataType
Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Bit DataType
This datatype represents a single bit that can be 0 or 1.

tinyint DataType
This datatype represents a single byte which is used to store values from 0 to 255 (MinVal: 0, MaxVal:255). Its storage size is 1 byte.

smallint DataType
This datatype represents a signed 16-bit integer which is used to store values from -2^15 (-32,768) through 2^15 - 1 (32,767) and its storage size is 2 bytes.

int DataType
This datatype represents a signed 32-bit integer which is used to store values from -2^31(-2,147,483,648) to 2 ^31-1(2,147,483,647). Its storage size is 4 bytes.

Bigint DataType
This datatype represents a signed 64-bit integer which is used to store values from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Its storage size is 8 bytes.

LEN and DATALENGTH Functions in SQL Server

Len() function

Len() function will return number of characters in the string expression excluding only the trailing blanks. Internally it performs RTRIM() operation and give you the count of characters. But it counts the leading blank character.

DataLength() function

DataLength() function will return number of bytes to represent any expression. It returns the storage space required for the characters. It counts the trailing blank space.

Difference between ExecuteScalar, ExecuteReader and ExecuteNonQuery?

  • ExecuteScalar is going to be the type of query which will be returning a single value. An example might be SELECT @@IDENTITY AS 'Identity'. 
  • ExecuteReader gives you a data reader back which will allow you to read all of the columns of the results a row at a time. A(e.g., SELECT col1, col2 from sometable). 
  • ExecuteNonQuery is any SQL which isn't returning values really, but is actually performing some form of work like inserting deleting or modifying something. (e.g., UPDATE, INSERT, etc.).
DataAccessLayer
public class MsSqlQueryParameters
    {
        private SqlParameter SQLParam;
        private ArrayList ParamArray;

        public MsSqlQueryParameters()
        {
            ParamArray = new ArrayList();
        }

        public void Add(string ParamName, SqlDbType ParamType, object ParamValue)
        {

            SQLParam = new System.Data.SqlClient.SqlParameter();
            SQLParam.SqlDbType = ParamType;
            SQLParam.ParameterName = ParamName;
            SQLParam.Value = ParamValue;
            ParamArray.Add(SQLParam);
        }

        public void Add(string ParamName, SqlDbType ParamType, object ParamValue, int ParamLength)
        {

            SQLParam = new System.Data.SqlClient.SqlParameter();
            SQLParam.SqlDbType = ParamType;
            SQLParam.ParameterName = ParamName;
            SQLParam.Value = ParamValue;
            SQLParam.Size = ParamLength;
            ParamArray.Add(SQLParam);
        }

        public void ClearAll()
        {
            try
            {
                ParamArray.Clear();
            }
            catch
            {

            }
        }

        public ArrayList QueryParam
        {
            get
            {
                return ParamArray;
            }
        }
    }

public DataSet GetDataSet(string SQLQuery, CommandType Type, MsSqlQueryParameters Parameters)
        {
            if (dbCon.State == ConnectionState.Closed)
                dbCon.Open();
            command = new SqlCommand(SQLQuery, dbCon);
            command.CommandType = Type;
            if (Parameters != null)
            {
                foreach (SqlParameter ParameterItem in Parameters.QueryParam)
                {
                    command.Parameters.Add(ParameterItem);
                }
            }
            dSet = new DataSet();
            dataAdapter = new SqlDataAdapter(command);
            if (maxrecords > 0)
                dataAdapter.Fill(dSet);
            else
                dataAdapter.Fill(dSet, tblName);

            //object AffCnt = null;
            //command.CommandTimeout = 120;
            //AffCnt = command.ExecuteScalar();
            ////AffCnt = command.ExecuteNonQuery();

            dataAdapter.Dispose();
            command.Dispose();
            dbCon.Close();
            return dSet;
        }

BusinessAccessLayer

private MsSqlDataProxy dataLayer = null;

private MsSqlQueryParameters parameters = null;

public DataSet GetAllAdmission()
        {
            parameters.ClearAll();
            parameters.Add("@method", SqlDbType.NVarChar, BLConstants.Query_GetALL, 25);
            parameters.Add("@admissionId", SqlDbType.BigInt, 0);
            dataSet = dataLayer.GetDataSet("SPGetAdmission", "TblAdmission", CommandType.StoredProcedure, parameters);
            return dataSet;

        }

private Admission fillAdmission(DataRow dr)
        {
            Admission admissionFile = new Admission();
            if (dr != null)
            {
                if (dr["admissionId"] != DBNull.Value)
                    admissionFile.AdmissionId = (long)dr["admissionId"];
                if (dr["admissionType"] != DBNull.Value)
                    admissionFile.AdmissionType = (int)dr["admissionType"];
                if (dr["title"] != DBNull.Value)
                    admissionFile.Title = (string)dr["title"];
                if (dr["shortDescription"] != DBNull.Value)
                    admissionFile.ShortDescription = (string)dr["shortDescription"];
                if (dr["filename"] != DBNull.Value)
                    admissionFile.Filename = (string)dr["filename"];
                if (dr["createdDate"] != DBNull.Value)
                    admissionFile.CreatedDate = Convert.ToDateTime(dr["createdDate"]);
                if (dr["modifiedDate"] != DBNull.Value)
                    admissionFile.ModifiedDate = Convert.ToDateTime(dr["modifiedDate"]);
            }
            return admissionFile;

        }

Wednesday, 23 October 2013

Show the Content in Grid Format using WebGrid and Foreach

Displaying data using Webgrid with ajax 

Controller.cs

public ActionResult WebgridSample()
{
            ObservableCollection<Student> FeeRemaining = new ObservableCollection<Student>();
            FeeRemaining.Add(new Student { RollNo = "08330001", Name = "Surbhi", Branch = "C.S", FeeRemaining = 18000 });
            FeeRemaining.Add(new Student { RollNo = "08330004", Name = "Arun", Branch = "C.S", FeeRemaining = 2500 });
            FeeRemaining.Add(new Student { RollNo = "08329006", Name = "Ankita", Branch = "I.T", FeeRemaining = 31000 });
            FeeRemaining.Add(new Student { RollNo = "08329007", Name = "Anshika", Branch = "I.T", FeeRemaining = 9450 });
            FeeRemaining.Add(new Student { RollNo = "08329014", Name = "Anubhav", Branch = "I.T", FeeRemaining = 2670 });
            FeeRemaining.Add(new Student { RollNo = "08311023", Name = "Girish", Branch = "E.N", FeeRemaining = 11200 });
            FeeRemaining.Add(new Student { RollNo = "08311024", Name = "Yogesh", Branch = "E.N", FeeRemaining = 3370 });
            return View(FeeRemaining);
}

View.cshtml

<html>
<head>
    <title>Fee Remaining in Webgrid</title>
    <script src="../../Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
    <style type="text/css">
        .table { margin: 4px;  width: 500px;  background-color:#FCFCFC;}
        .head { background-color: #C1D4E6; font-weight: bold; color: #FFF; }
        .webGrid th, .webGrid td { border: 1px solid #C0C0C0; padding: 5px; }
        .altRow { background-color: #E4E9F5; color: #000; }
        .gridHead a:hover {text-decoration:underline;}
        .description { width:auto}
        .selectRow{background-color: #389DF5}
    </style>
</head>
<body>
@{
    WebGridSampleApplication.Models.Student Student = new WebGridSampleApplication.Models.Student();
}
    @{
    var gd = new WebGrid(Model, canPage: true, rowsPerPage: 5, selectionFieldName: "selectedRow",ajaxUpdateContainerId: "gridContent");
        gd.Pager(WebGridPagerModes.NextPrevious);}
        <div id="gridContent">
        @gd.GetHtml(tableStyle: "table",
                headerStyle: "head",
                alternatingRowStyle: "altRow",
                selectedRowStyle: "selectRow",
                columns: gd.Columns(
                gd.Column("RollNo", format: (item) => item.GetSelectLink(item.RollNo)),
                gd.Column("Name", " Name"),
                gd.Column("Branch", "Branch", style: "description"),
                gd.Column("FeeRemaining", "FeeRemaining")
         ))
    @if (gd.HasSelection)
         {
             Student = (WebGridSampleApplication.Models.Student)gd.Rows[gd.SelectedIndex].Value;
             <b>Roll No</b> @Student.RollNo<br />
             <b>Name</b>  @Student.Name<br />
             <b>Branch</b> @Student.Branch<br />
             <b>Remaining Fee</b> @Student.FeeRemaining<br />
         }
    </div>    
</body>
</html>

Displaying data using foreach loop

 <table>
        @foreach (var item in Model)
        {
            <thead>
                <tr>
                    <th>RollNo</th>
                    <th>Name</th>
                    <th>Branch</th>
                    <th>Fee Remaining</th>
                </tr>
            </thead>
            <tr>
                <td class="left">@item.RollNo</td>
                <td class="left">@item.Name</td>
                <td class="left">@item.Branch</td>
                <td class="right">@item.FeeRemaining</td>
            </tr>
 
        }

    </table>