Simple SQL Cursor for Reference

SELECT * FROM #tmpTab;           

                DECLARE @x nvarchar(400)

                DECLARE cursorName CURSOR

                FOR Select Kju FROM #tmpTab

                                OPEN cursorName

                                FETCH NEXT FROM cursorName INTO @x

                                WHILE @@FETCH_STATUS = 0

                                                BEGIN

                                                EXEC sp_executesql @x

                                                PRINT @x

                                                FETCH NEXT FROM cursorName

                                                INTO @x

                                                END

                                CLOSE cursorName

                                DEALLOCATE cursorName

Export to Excel from DataTable without using Grid

public void ExportToExcel(DataTable dt)
{
 if (dt.Rows.Count > 0)
 {
 string filename = "DownloadMobileNoExcel.xls";
 System.IO.StringWriter tw = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
 DataGrid dgGrid = new DataGrid();
 dgGrid.DataSource = dt;
 dgGrid.DataBind();
 //Get the HTML for the control.
 dgGrid.RenderControl(hw);
 //Write the HTML back to the browser.
 //Response.ContentType = application/vnd.ms-excel;
 Response.ContentType = "application/vnd.ms-excel";
 Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
 this.EnableViewState = false;
 Response.Write(tw.ToString());
 Response.End();
 }
}

Simple Merge Stored Procedure with Function

Merge and Where in with String Values

Create proc [dbo].[LT_MergeRoleDetails]

(

@ID int,

@ValueId int,

@SelectedPermission varchar(max)

)

as

BEGIN

MERGE RoleDetail AS TARGET Using

(SELECT @ID,@ValueId) as NEW (ID,ValueId) ON

(

TARGET.ID=NEW.ID AND

TARGET.ValueId=NEW.ValueId

)

WHEN MATCHED THEN

UPDATE SET

TARGET.ID=NEW.ID,             

TARGET.ValueId=NEW.ValueId,

TARGET.UpdatedOn=GETDATE(),

TARGET.Obsolete=0

WHEN

NOT MATCHED BY TARGET THEN

INSERT (ID,ValueId,CreatedOn,UpdatedOn,Obsolete)

VALUES (NEW.ID,NEW.ValueId,GETDATE(),GETDATE(),0);

-- Update

Update RoleDetail set Obsolete=1 where cast(RoleDetId as varchar) not in (SELECT * FROM UF_CSVToTable(@SelectedPermission)) and ValueId=@ValueId

END

Function

Create FUNCTION [dbo].[UF_CSVToTable]

(

 @psCSString VARCHAR(8000)

)

RETURNS @otTemp TABLE(sID VARCHAR(20))

AS

BEGIN

 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0

 BEGIN

  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),

                    LEN(@psCSString)))

  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),

                               LEN(@psCSString)) + 1, LEN(@psCSString))

  INSERT INTO @otTemp VALUES (@sTemp)

 END

RETURN

END

How to Create Log File in Asp.Net 2010

1)  Add This in AssemblyInfo
[assembly: log4net.Config.XmlConfigurator(ConfigFile = "Web.config", Watch = true)]

2) Add This in web.Config

<configuration>

  <configSections>

    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>

  </configSections>

 

  <log4net debug="true">

  <appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">

  <file value="LogFile\\Log.txt"/>

  <appendToFile value="true"/>

  <rollingStyle value="Size"/>

  <maxSizeRollBackups value="10"/>

  <maximumFileSize value="2KB"/>

  <staticLogFileName value="true"/>

  <layout type="log4net.Layout.PatternLayout">

 <conversionPattern value="%-5p %d %5rms %-22.22c{1} %-18.18M - %m%n"/>

  </layout>

 </appender>

 <root>

    <level value="DEBUG"/>

    <appender-ref ref="RollingLogFileAppender"/>

 </root>

 </log4net>

</configuration>

3) Create one folder Called LogFile

4) Add this Page where you want to catch the Log

private static readonly log4net.ILog Log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

5) Use log4net dll

How to Call WCF Class file to Asp.net Application

How to call WCF Class file to Asp.net Application with out creating Class file in Asp.net Application

OssWebService.Service1Client ObjOssWeb = new OssWebService.Service1Client();
OssWebService.UserInfo ObjUserinfo = new OssWebService.UserInfo();


[DataContract]

public class UserInfo{
[DataMember]
public string CenterName { get; set; }[
DataMember]
public string PatientId { get; set; }[
DataMember]
public string PatientName { get; set; }[
DataMember]
public string PhoneNo { get; set; }[
DataMember]
public string Age { get; set; }[
DataMember]
public List<StudyInfo> StudyInfo { get; set; }}

How to get data from multiple datacontext (DBML) in Linq

var ObjPermission = dbOne.Permissions.ToList();

var ObjRoleMapping = (from RD in dbTwo.RoleDetails

join RM in dbTwo.RoleMasters on RD.RoleId equals RM.RoleID

                      where RM.RoleID == AgentId

                      select RD).ToList();

List<P> ObjResult = (from P in ObjPermission

join RML in ObjRoleMapping on P.PermissionID equals RML.PermissionId

              select P).ToList();

Multiple Table's Join In LINQ

List<PM> ObjPermission = (

from P in Permissions

join RD in RoleDetails on P.PermissionID equals RD.PermissionId

join RM in RoleMasters on RD.RoleId equals RM.RoleID

where RM.RoleID == AgentId

select P).ToList();