Posts

How to add auto increament with string of number and letter?

CREATE TABLE [dbo].[tbl_Product](

[Id] [int] IDENTITY(101,1) NOT NULL,

ProductId As 'PRO'+Right('000'+convert (varchar(10),Id),6)persisted,

[ProductName] [nvarchar](100) NULL,

[Quantiy] [int] NULL,

[Price] [decimal](18, 1) NULL,

[CreateOn] [datetime] NULL
) ON [PRIMARY]


 Output-

Cursor In SQL SERVER

Image
Cursor:- Cursor is  a database object to retrieve data from a result set one row at time. we use cursor when we need to update record databse table in Sigleton fashion means row wise.



  For Exampl :
   select * from Products

 know i want change  unit price of unit of each based on some condition.

Declare @UnitPrice decimal(5,2)
Declare @ProductId int
Declare UnitPriceUpdateCursore  Cursor FOR
Select ProductID From Products
open  UnitPriceUpdateCursore
Fetch Next From UnitPriceUpdateCursore into @ProductId
While (@@Fetch_Status=0)
Begin
select @UnitPrice=UnitPrice from Products

Begin Update Products set UnitPrice = case when UnitPrice between 5 And 10 then 15
                     when UnitPrice  between 11 AND 15  then 20
     else UnitPrice
                    end
Where ProductID=@ProductId
Fetch Next From UnitPriceUpdateCursore into @ProductId
END
End
Close UnitPriceUpdateCursore
Deallocate UnitPriceUpdateCursore
Set NoCount Off


MVC Validation

Expressions for input fields Alphabets and Space [a-zA-Z ]+$ Alphabets ^[A-z]+$ Numbers ^[0-9]+$ Alphanumeric ^[a-zA-Z0-9]*$ Email [a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])? Mobile no. ^([7-9]{1})([0-9]{9})$ Date Format( mm/dd/yyyy | mm-dd-yyyy | mm.dd.yyyy) /^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\\d\\d+$/ Website URL ^http(s)?://([\\w-]+.)+[\\w-]+(/[\\w- ./?%&=])?$ Credit Card Numbers Visa ^4[0-9]{12}(?:[0-9]{3})?$ MasterCard ^5[1-5][0-9]{14}$ American Express ^3[47][0-9]{13}$ Decimal number ((\\d+)((\\.\\d{1,2})?))$

Login page using AS.NET MVC C#.

How to bind data in Drop down List from Enum usig Array in Asp.net C#

Add New Class and rename as CommonEnum.
Note:- Set location in App_Code.So it can access any in a project. 
Add method  nameSpace Enum { Public ClassCommonEnum() {
} Public enum Operation  {Insert =1, Update=2, Delete=3, Select=4 } }
Add New  Web Page   Add Asp .Net DropDownList control in .aspx page
<asp:DropDownList Id="ddlOperation"  runat= "Server"/>
Add name Space on web Page.cs
 Array arrop=Enum.GetValues(typeof(CommonEmum.Operation)); foreach(CommonEnum.Operation opt  in arrop) { ddlOperation.Items.Add(new ListItem(opt.ToString(),((Int)opt).Tostring())); }

Store Procedure for crud operation with transaction and exception handling (try catch block)

CREATE TABLE [dbo].[tblCustomer](
[CustID] [bigint] NOT NULL,
[CustName] [nvarchar](50) NULL,
[CustEmail] [nvarchar](50) NOT NULL,
[CustAddress] [nvarchar](256) NULL,
[CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustID] ASC,
[CustEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[SP_CUSTOMER_INSERT] ( @CustName NVarchar(50)       ,@CustEmail NVarchar(50)       ,@CustAddress NVarchar(256)       ,@CustContact  NVarchar(50)   )   AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION DECLARE @CustID Bigint   SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')     Insert INTO tblCustomer(CustID,CustName,CustEmail,CustAddress,CustContact)Values(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)   Select 1   Commit Transaction   End Try   BEGIN CATCH    DECLARE @ErrorMessage NVARCHAR(4000…