October 2011

Build an EntityConnection Connection String (edmx) model connectionstring

Change edmx model connectionstring dynamically in class library (Data Access Layer)

// Specify the provider name, server and database.
string providerName = “System.Data.SqlClient”;
string serverName = “.”;
string databaseName = “AdventureWorks”;

// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();

// Set the properties for the data source.
sqlBuilder.DataSource = serverName;
sqlBuilder.InitialCatalog = databaseName;
sqlBuilder.IntegratedSecurity = true;

// Build the SqlConnection connection string.
string providerString = sqlBuilder.ToString();

// Initialize the EntityConnectionStringBuilder.
EntityConnectionStringBuilder entityBuilder =
new EntityConnectionStringBuilder();

//Set the provider name.
entityBuilder.Provider = providerName;

// Set the provider-specific connection string.
entityBuilder.ProviderConnectionString = providerString;

// Set the Metadata location.
entityBuilder.Metadata = @”res://*/AdventureWorksModel.csdl|
res://*/AdventureWorksModel.ssdl|
res://*/AdventureWorksModel.msl”;
Console.WriteLine(entityBuilder.ToString());

using (EntityConnection conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
Console.WriteLine(“Just testing the connection.”);
conn.Close();
}

Detect request come from mobile, tablet or desktop

IPhone application are growing in the market. In this example, I will give you a simple tips that how to detect request is coming from iPhone in ASP.NET application. When ASP.NET page is requested at runtime, the information in the request header to determine what type of browser has made the request by the user agent. In this example, you will see that if the request will come from iPhone then we can do what we have required for our application and if request will not from iPhone then we don’t need to do anything.

Here’s an example

protected bool isAgent()
{
if (HttpContext.Current.Request.UserAgent.ToLower().Contains(“iphone”))
return true;
return false;
}

protected void Page_Load(object sender, EventArgs e)
{
if (isAgent())
{
Response.Write(“iPhone Detected”);// what ever you do
}
}

Calculate duration of time in crystal reports

Create one formula field give name “tot_seconds”

local stringvar array completetime;
local numbervar totalseconds;

if({dtAttendance.Time}”N/A”) Then
(
completetime:=split({dtAttendance.Time},”:”);
totalseconds:= ((3600*cdbl(completetime[1])) + (60*cdbl(completetime[2]))+(cdbl(“00”)));
)

{dtAttendance.Time} time is a report field which is display time in report.

If Check In times agains chechout time not found then display ‘N/A’ and vice versa so that we haven’t calculate that time in total therefore i have to put if condition regarding this.

Now, create another formula field “totalTime”, paste below code in “edit formula” section.

WhilePrintingRecords;
NumberVar TotalSec := sum({@tot_seconds},{dtAttendance.Name});

NumberVar Hours := Truncate ( TotalSec / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);

Totext ( Hours, ‘####’) + ‘:’+
Totext ( Minutes,’00’)

If you are putting “totalTime” field into grouo section it will display group wise total.

Create or Remove HTML controls dynamically using javascript

Download Source code: live.com = create-remove-control.zip

Create file input control using javascript
function addFileUploadBox() {
if (!document.getElementById || !document.createElement)
return false;

var uploadArea = document.getElementById(“upload-area”);

if (!uploadArea)
return;

var newUploadBox = document.createElement(“input”);

// Set up the new input for file uploads
newUploadBox.type = “file”;
newUploadBox.size = “70”;

// The new box needs a name and an ID
if (!addFileUploadBox.lastAssignedId)
addFileUploadBox.lastAssignedId = 100;

newUploadBox.setAttribute(“id”, “dynamic” + addFileUploadBox.lastAssignedId);
newUploadBox.setAttribute(“name”, “dynamic:” + addFileUploadBox.lastAssignedId);
newUploadBox.setAttribute(“onchange”, “CheckFileAlongWithDropdown(this)”);
uploadArea.appendChild(newUploadBox);

var newLine = document.createElement(“br”);
newLine.setAttribute(“id”, “br” + addFileUploadBox.lastAssignedId);
uploadArea.appendChild(newLine);

var hdn1 = document.getElementById(‘hdn1’);
hdn1.value = addFileUploadBox.lastAssignedId;
addFileUploadBox.lastAssignedId++;
}

Create “Select” or “Dropdown” control dynamically using javascript
var id = 100;
function AddDropDown() {

if (!document.getElementById || !document.createElement)
return false;

var uploadArea = document.getElementById(“upload-area”);

if (!uploadArea)
return;

//create delete button element
var newBtn = document.createElement(“input”);
newBtn.type = “button”;
if (!AddDropDown.lastAssignedId)
AddDropDown.lastAssignedId = 100;

newBtn.setAttribute(“id”, “btn” + AddDropDown.lastAssignedId);
newBtn.setAttribute(“name”, “btn:” + AddDropDown.lastAssignedId);
newBtn.setAttribute(“value”, “Remove”);
newBtn.setAttribute(“onclick”, “DeleteElement(this)”);
uploadArea.appendChild(newBtn);
//end delete button element creation

$(document).ready(function() {
var data = { ‘ContentPage’: ‘Content Page’, ‘ContentPagewithTitlePage’: ‘Content Page with Title Page’, ‘TitlePage’: ‘Title Page (One Page Only)’,
‘Movie’: ‘Movie’
};
var s = $(”);
s[0].id = “ddl” + id;
s[0].name = “ddl:” + id;

id++;
for (var val in data) {
$(”, { value: val, text: data[val] }).appendTo(s);
}

s.appendTo(uploadArea);
AddDropDown.lastAssignedId++;
});
}

Remove added element from page
function DeleteElement(ctrl) {
var uploadArea = document.getElementById(“upload-area”);
var findDdlId = ctrl.name.split(“:”);
var ddl = document.getElementById(‘ddl’ + findDdlId[findDdlId.length – 1]);
var upload = document.getElementById(‘dynamic’ + findDdlId[findDdlId.length – 1]);
var br1 = document.getElementById(‘br’ + findDdlId[findDdlId.length – 1]);
uploadArea.removeChild(upload);
uploadArea.removeChild(ddl);
uploadArea.removeChild(ctrl);
uploadArea.removeChild(br1);
return false;
}

Clear HTML File Input

HTML file

– javascript function
function clearFileInput()
{
var oldInput = document.getElementById(“fileInput”);

var newInput = document.createElement(“input”);

newInput.type = “file”;
newInput.id = oldInput.id;
newInput.name = oldInput.name;
newInput.className = oldInput.className;
newInput.style.cssText = oldInput.style.cssText;
// copy any other relevant attributes

oldInput.parentNode.replaceChild(newInput, oldInput);
}

User Dynamic query for search records and use uniqueidentifier

Declare @SearchText nvarchar(max)
set @SearchText = ‘sah.CheckInCheckOutDateTime>=”2011-09-28 10:00:00.000”’

Declare @SchoolYearId uniqueidentifier
set @SchoolYearId=’25511F7F-8F87-4085-A242-2004F4419D36′

Declare @Str as nvarchar(max)
set @Str=’
select s.LastName +”, ” + s.FirstName as ”Name”,CheckInCheckOut,sah.CheckInCheckOutDateTime as ”ClockOut”
from StaffAttendenceHistory sah
join Staffschoolyear ssy on sah.staffschoolyearid = ssy.id
join Staff s on ssy.staffid = s.id
where ssy.SchoolYearId=@i’

If(LEN(ISNULL(@SearchText,0))>0)
set @Str = @Str + ‘ and ( ‘+ @SearchText + ‘)’

set @Str = @Str + ‘ order by CheckInCheckOutDateTime asc ‘

Declare @tbGI table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tbGI(Name,CheckInCheckOut,CheckInCheckOutDateTime)
exec sp_executesql @Str,N’@i uniqueidentifier’,@i=@SchoolYearId

select * from @tbGI

Attendance Reports CheckIn CheckOut in SQL Server

declare @tb1 table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)Values(‘Poter, Harry’,1,’2011-08-30 17:00:15.090′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:20.140′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-30 17:00:24.890′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:29.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-31 17:00:38.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-31 17:00:44.967′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-01 17:00:54.950′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-09-01 17:01:03.590′)
–insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-07 15:07:22.140′)
–select * from @tb1 order by CheckInCheckOutDateTime asc

declare @tb2 table (Id int, Name nvarchar(50), CheckInCheckOut bit, ClockIn datetime, ClockOut datetime, lastadd bit)
Declare @cnt int
set @cnt = 0;
while (@cnt< (select count(*) from @tb1))
Begin
if((select id from @tb1 where Id=@cnt+1)=1)
Begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=1)
End
Else
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=1)
End
End
else
Begin
if((select top 1 lastadd from @tb2 order by Id desc)(select CheckInCheckOut from @tb1 where Id=@cnt+1))
Begin
if((select top 1 lastadd from @tb2 order by Id desc)=1)
Begin
update @tb2
set ClockOut = (select CheckInCheckOutDateTime from @tb1 where Id=@cnt+1)
,lastadd = (select CheckInCheckOut from @tb1 where Id=@cnt+1)
where Id = (select top 1 id from @tb2 order by Id desc)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
End
else
begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
end
End
set @cnt=@cnt+1
End
select * from @tb2