Max Id of table find out by linq

Var idd = dt.bankdetails.Max(s => s.id);
        if (idd == null)
        {
            idd = 0;
        }
        bt.id = idd + 1;

Here dt is dataclass and bankdetails is table name.

Reterive Result Value In Linq (Using C#)

var s = dt.maxid().ToList();
        int id = 0; ;
        foreach (var a in s)
        {
           id= a.id.Value;
        }
       var aa= dt.insertuser(id, txtuname.Text, txtpas.Text, txtname.Text, txtcnum.Text, txtadd.Text, txtemail.Text,DateTime.Now.Date);
       string a1 = "";
       foreach (var aaa in aa)
       {
           a1 = aaa.sts.ToString();
       }
       ScriptManager.RegisterStartupScript(this, typeof(Page), "1", "alert('"+a1.ToString()+"')", true);

Stored Procedure To Select User From Table

Alter proc selectuser
@uname nvarchar(max),
@pas nvarchar(max)

as

select count(*) as count1 from ulogin where username=@uname and password=@pas

return

Execute SP

Execute selectuser @uname=k,@pas=124

Stored Procedure For Insert With Check Duplicasy

Create procedure insertuser
@id int,
@uname nvarchar(max),
@pas nvarchar(max),
@name nvarchar(max),
@ponnumber nvarchar(max),
@add nvarchar(max),
@emailid nvarchar(max),
@regtime nvarchar(max)
as
declare @ss as int
declare @msg as nvarchar(50)
set @ss=(select count(*) from ulogin where emailid=@emailid)

if @ss>0
begin
set @msg=('Email Address Already Exists.')
select @msg as sts
end
else
begin
insert into ulogin values(@id,@uname,@pas,@name,@ponnumber,@add,@emailid,@regtime)
set @msg=('Registration Sucessfully.')
select @msg as sts
end
return

Execute Store Procedure

execute insertuser @id=1,@uname=asd,@pas=asdd,@name=qwewe,@ponnumber=awqwewq,@add=sdadad,@emailid=14,@regtime=sdd

Select By Stored Procedure

Create proc maxid

 as

(select isnull(max(id),0) as id from tablename)

return

 To Execute 

Exec maxid

Open a page in ModalPopUP

To Open a page in modal pop up   extender of ajax we have to use these source:

<ajaxToolkit:ModalPopupExtender ID="ModalPopupExtender1" runat="server" TargetControlID="lnkPopup" PopupControlID="panOpen" BackgroundCssClass="ModalBackground" CancelControlID="btnCancel" PopupDragHandleControlID="panOpen" > </ajaxToolkit:ModalPopupExtender>

<asp:Panel ID="panOpen" runat="server" Height="400px" Width="400px" CssClass="ModalWindow">

<IFRAME id="frame1" src="SourcePage.Extension / URL of the external Site" Scrolling="auto">

</IFRAME>

<asp:Button ID="btnCancel" runat="server" Text="Close" />

</asp:Panel>

<a id="lnkPopup" runat="server">Show Popup</a>

Drop all the tables by query

If you need to perform the same actions for all tables in a database, you can create cursor or you can use the sp_MSforeachtable undocumented stored procedure to accomplish the same goal with less work.

Drop all tables from database

Query is:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

Connection string for Excel File

For excel 2003

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book2.xls;Extended Properties=\"Excel 8.0;HDR='YES'\"";

And For Excel 2007

 string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Book1.xlsx        Extended Properties=\"Excel 8.0;HDR=YES;\"";

Copy File From One Directory To Another

By using this code 
     string source = @"C:\Untitled-1.html";
        string dest = Server.MapPath("~/");
        string filename = "Untitled-1.html";
       File.Copy(source, dest + filename + (new FileInfo(source).Extension));








          

Count nested id in sql server 2005

Suppose

i have a table treeview(tablename)  and four fields

nodeid,parentid,client_id and nodetype(it may be file or folder) 

Nodeid	Parentid	Client_id	nodetype	Folder_Name
1	1	2	Folder	A
2	1	2	Folder	B
3	1	2	Folder	C
4	2	2	Folder	D
5	2	2	Folder	E
6	3	2	Folder	F
7	3	2	File	G
8	4	2	Folder	H
9	4	2	Folder	I
10	3	2	Folder	J
11	3	2	Folder	K
12	5	2	Folder	L
13	8	2	Folder	M
14	9	2	Folder	N
15	10	2	Folder	O

These folders are nested like:

A

     B

          D

                H

                       M

                I

                       N     

          E

                L

     C

           F

           G

           J

                O

           K                 

Means ‘B’ is inside ‘A’ and ‘D’ is inside ‘B’

Now what I want to do

I want to count the folder if user click on ‘B’  it count all the folder that inside the ‘B’ and nested  in ‘B’

Show count=7 Folders

And  for the ‘C’ it count

Count=5

the query for this is:

WITH EmpCTE(nodeid, parentid, nodetype, lvl)
AS
(

  -- Anchor Member (AM)
  SELECT nodeid, nodetype, parentid, 0
  FROM treeview
  WHERE nodeid = 1
  UNION ALL
    
  -- Recursive Member (RM)
  SELECT E.nodeid, E.parentid, E.nodetype, M.lvl+1
  FROM treeview AS E
    JOIN EmpCTE AS M
      ON E.parentid = M.nodeid where e.nodetype='Folder'
)
SELECT * FROM EmpCTE

Split function in sql server 2005

 CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))      
 returns @temptable TABLE (items varchar(8000))      
 as      
 begin      
     declare @idx int      
    declare @slice varchar(8000)      
     
    select @idx = 1      
         if len(@String)<1 or @String is null  return      
      
    while @idx!= 0      
     begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)     
         else      


             set @slice = @String      
        
        if(len(@slice)>0) 
           insert into @temptable(Items) values(@slice)      
 
        set @String = right(@String,len(@String) - @idx)      
         if len(@String) = 0 break      
    end  
 return      
 end


and run it like:

select  * from dbo.split('Kshama,Parashar,Pushkar',',')

How to convert bytes in kb,mb and gb by sql server2005

A function to convert bytes into kb,mb,gb

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Create FUNCTION [dbo].[udf_FormatBytes]
(
   @InputNumber   DECIMAL(38,7),
   @InputUOM      VARCHAR(5) = 'Bytes'
)
RETURNS VARCHAR(20)
WITH SCHEMABINDING
AS
BEGIN
   -- Declare the return variable here
   DECLARE @Output VARCHAR(48)
   DECLARE @Prefix MONEY
   DECLARE @Suffix VARCHAR(6)
   DECLARE @Multiplier DECIMAL(38,2)
   DECLARE @Bytes  DECIMAL(38,2)

   SELECT @Multiplier =
      CASE @InputUOM
         WHEN 'Bytes'         THEN 1
         WHEN 'Byte'          THEN 1
         WHEN 'B'             THEN 1
         WHEN 'Kilobytes'     THEN 1024
         WHEN 'Kilobyte'      THEN 1024
         WHEN 'KB'            THEN 1024
         WHEN 'Megabytes'     THEN 1048576
         WHEN 'Megabyte'      THEN 1048576
         WHEN 'MB'            THEN 1048576
         WHEN 'Gigabytes'     THEN 1073741824
         WHEN 'Gigabyte'      THEN 1073741824
         WHEN 'GB'            THEN 1073741824
         WHEN 'Terabytes'     THEN 1099511627776
         WHEN 'Terabyte'      THEN 1099511627776
         WHEN 'TB'            THEN 1099511627776
         WHEN 'Petabytes'     THEN 1125899906842624
         WHEN 'Petabyte'      THEN 1125899906842624
         WHEN 'PB'            THEN 1125899906842624
         WHEN 'Exabytes'      THEN 1152921504606846976
         WHEN 'Exabyte'       THEN 1152921504606846976
         WHEN 'EB'            THEN 1152921504606846976
         WHEN 'Zettabytes'    THEN 1180591620717411303424
         WHEN 'Zettabyte'     THEN 1180591620717411303424
         WHEN 'ZB'            THEN 1180591620717411303424
         WHEN 'Yottabytes'    THEN 1208925819614629174706176
         WHEN 'Yottabyte'     THEN 1208925819614629174706176
         WHEN 'YB'            THEN 1208925819614629174706176
         WHEN 'Brontobytes'   THEN 1237940039285380274899124224
         WHEN 'Brontobyte'    THEN 1237940039285380274899124224
         WHEN 'BB'            THEN 1237940039285380274899124224
         WHEN 'Geopbytes'     THEN 1267650600228229401496703205376
         WHEN 'Geopbyte'      THEN 1267650600228229401496703205376
      END

   SELECT @Bytes = @InputNumber*@Multiplier

   SELECT @Prefix =
      CASE
         WHEN ABS(@Bytes) < 1024 THEN @Bytes --bytes
         WHEN ABS(@Bytes) < 1048576 THEN (@Bytes/1024) --kb
         WHEN ABS(@Bytes) < 1073741824 THEN (@Bytes/1048576) --mb
         WHEN ABS(@Bytes) < 1099511627776 THEN (@Bytes/1073741824) --gb
         WHEN ABS(@Bytes) < 1125899906842624 THEN (@Bytes/1099511627776) --tb
         WHEN ABS(@Bytes) < 1152921504606846976 THEN (@Bytes/1125899906842624) --pb
         WHEN ABS(@Bytes) < 1180591620717411303424 THEN (@Bytes/1152921504606846976) --eb
         WHEN ABS(@Bytes) < 1208925819614629174706176 THEN (@Bytes/1180591620717411303424) --zb
         WHEN ABS(@Bytes) < 1237940039285380274899124224 THEN (@Bytes/1208925819614629174706176) --yb
         WHEN ABS(@Bytes) < 1267650600228229401496703205376 THEN (@Bytes/1237940039285380274899124224) --bb
         ELSE (@Bytes/1267650600228229401496703205376) --geopbytes
      END,
          @Suffix =
     CASE
         WHEN ABS(@Bytes) < 1024 THEN ' Bytes'
         WHEN ABS(@Bytes) < 1048576 THEN ' KB'
         WHEN ABS(@Bytes) < 1073741824 THEN ' MB'
         WHEN ABS(@Bytes) < 1099511627776 THEN ' GB'
         WHEN ABS(@Bytes) < 1125899906842624 THEN ' TB'
         WHEN ABS(@Bytes) < 1152921504606846976 THEN ' PB'
         WHEN ABS(@Bytes) < 1180591620717411303424 THEN ' EB'       
         WHEN ABS(@Bytes) < 1208925819614629174706176 THEN ' ZB'       
         WHEN ABS(@Bytes) < 1237940039285380274899124224 THEN ' YB'       
         WHEN ABS(@Bytes) < 1267650600228229401496703205376 THEN ' BB'       
         ELSE ' Geopbytes'
      END

   -- Return the result of the function
   SELECT @Output = CAST(@Prefix AS VARCHAR(39)) + @Suffix
   RETURN @Output

END
use like
select  *  from dbo.udf_FormatBytes(123456,'Bytes')