SQL server Capitalize First Letter

 ---Input String 'PABITRA MICROSOFT PabiTra miCroSoft'

declare @SQLColumnName nvarchar(max)

set @SQLColumnName = 'PABITRA MICROSOFT PabiTra miCroSoft'

declare @SpaceInWord char(1) = ' '

-- Upper case first letter and all others in lower case letters

select @SQLColumnName = STUFF(LOWER(@SQLColumnName), 1, 1, UPPER(LEFT(@SQLColumnName,1)) )

-- search for space character

declare @i int = CHARINDEX(@SpaceInWord, @SQLColumnName, 1)

while @i > 0

begin

 select @i = @i + 1

 -- replace character after space with its upper case letter

 select @SQLColumnName = STUFF(@SQLColumnName, @i, 1, UPPER( SUBSTRING(@SQLColumnName, @i, 1)) )

 -- search for next space character

 select @i = CHARINDEX(@SpaceInWord, @SQLColumnName, @i)

end

select @SQLColumnName

---Out Put : 'Pabitra Microsoft Pabitra Microsoft'

using cursor

----------

 

 

 declare @PrinterId int,@SQLColumnName varchar(1000)

declare _cursor cursor read_only for

select PrinterId,PAddress from PrinterDetails

open _cursor

fetch next from _cursor into @PrinterId,@SQLColumnName

while @@FETCH_STATUS=0

begin

-----------------------------Body

declare @SpaceInWord char(1) = ' '

-- Upper case first letter and all others in lower case letters

select @SQLColumnName = STUFF(LOWER(@SQLColumnName), 1, 1, UPPER(LEFT(@SQLColumnName,1)) )

-- search for space character

declare @i int = CHARINDEX(@SpaceInWord, @SQLColumnName, 1)

while @i > 0

begin

 select @i = @i + 1

 -- replace character after space with its upper case letter

 select @SQLColumnName = STUFF(@SQLColumnName, @i, 1, UPPER( SUBSTRING(@SQLColumnName, @i, 1)) )

 -- search for next space character

 select @i = CHARINDEX(@SpaceInWord, @SQLColumnName, @i)

end

--select @SQLColumnName

update PrinterDetails set PAddress=@SQLColumnName where PrinterId=@PrinterId

 -----------------------------Body end 

fetch next from _cursor into  @PrinterId,@SQLColumnName

end

close _cursor

deallocate _cursor

SQL queries to change the column datatype type

 Column with data type : AadhaarNumner int 

SQL queries to change the column datatype type

Alter table dbo.OrderPaperBooks alter column AadhaarNumner varchar(20)

SQL query for alter column name

 table name: dbo.OrderPaperBooks

old column name : PPNoOfCopy

assign new column name :NoOfCopy 

SQL query for alter column name 

 EXEC sp_rename 'dbo.OrderPaperBooks.PPNoOfCopy', 'NoOfCopy', 'COLUMN';

drop and create SQL Server procedure simple way

 IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'USP_BookTypes')

DROP PROCEDURE USP_BookTypes

GO

CREATE  PROC USP_BookTypes

(

   @action varchar(20)

  ,@BookTypeId int=null

  ,@BookType varchar(100)=null

)

AS BEGIN

  IF(@action='SELECTALL')

   BEGIN

    SELECT B.BookTypeId,B.BookType FROM BookTypes B WITH(NOLOCK)  

WHERE B.IsActive='Y'  order by B.BookTypeId; 

   END

   ELSE IF(@action='SELECTBYID')

   BEGIN

    SELECT B.BookTypeId,B.BookType FROM BookTypes B WITH(NOLOCK)  

WHERE B.BookTypeId=@BookTypeId and B.IsActive='Y' order by B.BookTypeId; 

   END

 END 

singleton design pattern c#

 

 Calling the Singleton Design Pattern c# Class

namespace OrderProcessing.BusinessLayer

{

    public class BLLClass

    {

        public BLLClass()

        {

            //

            // TODO: Add constructor logic here

            //

        }

        public string bllAddClass(ClassModelAddUpdateRequst Requst)

        {

            string response = "error";

            response = Helper.Instance.ExecuteScalar(

                                        commandText: "USP_CLASS"

                                       , commandType: CommandType.StoredProcedure

                                       , sqlParameters:

                                        new SqlParameter[]{

                                                          new SqlParameter("@action", SqlDbType.Char, 20) { Value =Requst.action }

                                                         ,new SqlParameter("@ClassNameNumber", SqlDbType.Int) { Value =Requst.ClassNameNumber }

                                                         ,new SqlParameter("@ClassNameText", SqlDbType.VarChar, 20) { Value = Requst.ClassNameText }

                                                         ,new SqlParameter("@ClassNameRoman", SqlDbType.NVarChar, 20) { Value = Requst.ClassNameRoman }

                                                         ,new SqlParameter("@ClassNameOdia", SqlDbType.NVarChar, 20) { Value = Requst.ClassNameOdia }

                                                         ,new SqlParameter("@EntryBy", SqlDbType.VarChar, 20) { Value = Requst.userId }

                                                          }

                                   );

            return response;

        }

}

Singleton Design Pattern c# Class

public sealed class Helper

    {

        private Helper() { }

        private static Helper instance = null;

        public static Helper Instance

        {

            get

            {

                if (instance == null)

                {

                    instance = new Helper();

                }

                return instance;

            }

        }

        private string objCon;

        private SqlConnection Conn;

        public SqlConnection Connect()

        {

            objCon = ConfigurationManager.ConnectionStrings["sqlServerConn"].ConnectionString;

            Conn = new SqlConnection(objCon);

            if (Conn.State == ConnectionState.Open)

                Conn.Close();

            Conn.Open();

            return Conn;

        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] sqlParameters)

        {

            int x = 0;

            try

            {

                Conn = Connect();

                SqlCommand cmd = new SqlCommand(commandText, Conn);

                cmd.CommandType = commandType;

                cmd.CommandTimeout = 0;

                if (sqlParameters != null && sqlParameters.Length != 0)

                {

                    cmd.Parameters.AddRange(sqlParameters);

                }

                x = cmd.ExecuteNonQuery();

                Conn.Close();

                Conn.Dispose();

                cmd.Dispose();

            }

            catch (Exception ex)

            { throw ex; }

            return x;

        }

}

Contact Form

 <!DOCTYPE html>

<html>

<head>

  <meta charset="utf-8">

  <meta name="viewport" content="width=device-width">

  <title>Contact Form</title>

  <link href="https://netdna.bootstrapcdn.com/bootstrap/3.1.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">

</head>

<body class="well well-sm">

<div class="container">

  <div class="row">

      <div class="col-md-12 col-md-offset-0.5">

       

          <form class="form-horizontal">

            <div class="col-md-12">

              <div class="col-md-4"></div>

            <div class="col-md-4">

            <h1>Contact Us</h1>

           

            </div>

            <div class="col-md-4"></div>

          </div>

          <hr>

            <!-- Name input-->

            <div class="form-group">

              <label class="col-md-3 control-label" for="name">Name</label>

              <div class="col-md-9">

                <input id="txtName" name="name" tabindex="1" type="text" maxlength="100" title="Enter Your Name" placeholder="Your name" class="form-control">

              </div>

            </div>

            <!-- City input-->

            <div class="form-group">

              <label class="col-md-3 control-label" for="name">City</label>

              <div class="col-md-9">

                <input id="txtCity" name="city" tabindex="2"  type="text" maxlength="100"  title="Enter Your City"  placeholder="Your city" class="form-control">

              </div>

            </div>

            <!-- Email input-->

            <div class="form-group">

              <label class="col-md-3 control-label" for="email">Your E-mail</label>

              <div class="col-md-9">

                <input id="txtEmail" name="email"  tabindex="3"  type="text" maxlength="100"  title="Enter Your E-mail"  placeholder="Your email" class="form-control">

              </div>

            </div>

          <!-- DOB input-->

            <div class="form-group">

              <label class="col-md-3 control-label" for="email">Your Date Of Birth</label>

              <div class="col-md-9">

                <input id="txtDOB" name="dob"  tabindex="4"  type="date"  maxlength="10" title="Enter Your Date Of Birth" placeholder="Your Date Of Birth" class="form-control">

              </div>

            </div>

             <!-- Contact number input-->

            <div class="form-group">

              <label class="col-md-3 control-label" for="email">Your Contact number</label>

              <div class="col-md-9">

                <input id="txtContactNumber" name="contactnumber" title="Enter Your Contact number"  tabindex="5"  type="number"  maxlength="10"  placeholder="Your Contact number" class="form-control">

              </div>

            </div>

            <!-- Message body -->

            <div class="form-group">

              <label class="col-md-3 control-label" for="message">Your message</label>

              <div class="col-md-9">

                <textarea class="form-control" id="txtMessage" title="Enter Your message"  maxlength="150"   tabindex="6"  name="message" placeholder="Please enter your message here..." rows="5"></textarea>

              </div>

            </div>

   

            <!-- Form actions -->

            <div class="form-group">

              <div class="col-md-12 text-right">

                <button type="submit" class="btn btn-primary btn-lg"  title="Click here to submit the form "  tabindex="7" id="btnSubmit" >Submit</button>

              </div>

            </div>

          </form>

        </div>

      </div>

 

</div>

  <script src="https://netdna.bootstrapcdn.com/bootstrap/3.1.0/js/bootstrap.min.js"></script>

<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>

  <script type="text/javascript">

  $(document).ready(function() {

   

      $('#btnSubmit').click(function () {

          if (validateForm()) {

              if (SubmitData()) {

                  alert('Data save succssfully !!!');

              }

          } else {

              return false;

          }

      });

     

  });

  function SubmitData() {

      alert('Server side code call ');

      var saveStatus = false;

      if (saveStatus)

          return true;

      else

      return false;

  }

  function validateForm()

  {

     

      var status =false;

      if ($('#txtName').val() == '')

      {

          $('#txtName').focus();

          alert('Name Field can not be blank');

      }else if($('#txtName').val().length>100)

      {

          $('#txtName').focus();

          alert('Name Field must not grater than 100');

      }

      else if ($('#txtCity').val() == '') {

          $('#txtCity').focus();

          alert('City Field can not be blank');

      } else if ($('#txtCity').val().length > 100) {

          $('#txtCity').focus();

          alert('City Field must not grater than 100');

      }

      else if ($('#txtEmail').val() == '') {

          $('#txtEmail').focus();

          alert('Email Field can not be blank');

      } else if ($('#txtEmail').val().length > 100) {

          $('#txtEmail').focus();

          alert('Email Field must not grater than 100');

      }

      else if ($('#txtDOB').val() == '') {

          $('#txtDOB').focus();

          alert('DOB Field can not be blank');

      } else if ($('#txtDOB').val().length > 10) {

          $('#txtDOB').focus();

          alert('DOB Field must not grater than 10');

      }

      else if ($('#txtContactNumber').val() == '') {

          $('#txtContactNumber').focus();

          alert('Contact number Field can not be blank');

      } else if ($('#txtContactNumber').val().length > 10) {

          $('#txtContactNumber').focus();

          alert('Enter 10 digit contact number ');

      } else if ($('#txtMessage').val() == '') {

          $('#txtMessage').focus();

          alert('message Field can not be blank');

      } else if ($('#txtMessage').val().length > 150) {

          $('#txtMessage').focus();

          alert('DOB Field must not grater than 150');

      }else {

          status = true;

      }

      return status;

  }

    </script>

 

 

</body>

</html>

DataTable to json serializer

 void loadData()

{

DataTable dtRes=new DataTable();

dtRes=Dll.GetEmployee();

 stringJsonData=toJson(dtRes);

}

public string toJson(DataTable dt)

    {

        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();

        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

        Dictionary<string, object> row;

        foreach (DataRow dr in dt.Rows)

        {

            row = new Dictionary<string, object>();

            foreach (DataColumn col in dt.Columns)

            {

                row.Add(col.ColumnName, dr[col]);

            }

            rows.Add(row);

        }

        return serializer.Serialize(rows);

    }