Link two tables from two separate databases from separate location

select * from Databasename1.dbo.tblEmployees t1
inner join OPENDATASOURCE('SQLOLEDB','Data Source=databasename;User ID=sa;Password=123').Databasename2.dbo.leave t2 on t1.Id = t2.Id

Encrypting Connection Strings in web.config file using Code

//Runthis method in page load

public void EncryptConnString()
{
Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
ConfigurationSection section = config.GetSection("connectionStrings");
if (section.SectionInformation.IsProtected)
{
//To Encript
section.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider");
config.Save();

//To Decript
//section.SectionInformation.UnprotectSection();
//config.Save();
}
}

C# string formation

// two decimal places
String.Format("{0:0.00}", 324.4567); // "324.46"
String.Format("{0:0.00}", 324.4); // "324.40"
String.Format("{0:0.00}", 324.0); // "324.00"

// maximum two decimal places
String.Format("{0:0.##}", 324.4567); // "324.46"
String.Format("{0:0.##}", 324.4); // "324.4"
String.Format("{0:0.##}", 324.0); // "324"

// at least 2 digits before decimal point
String.Format("{0:00.0}", 324.4567); // "324.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"

Javascript valdation for textbox langth valdation

< script type="text/javascript" language="javascript" >
function checkCharacterLimit(TB)
{
var maxCharacters = 200;
var withinCharacterLimit = true;
var ValidateTB = document.getElementById(TB.id);
var charCode = ( event.which ) ? event.which : event.keyCode;
var bool=(charCode =="8" || charCode =="46");
if (ValidateTB.value.length > maxCharacters && !bool)
{
withinCharacterLimit = false;
}
return withinCharacterLimit;
}
< /script >

in c# code Add following code

txtJobDiscription1.Attributes.Add("onKeyDown", "return checkCharacterLimit(this);");

Command field - Giving a confirmation when deleting C#

protected void gvProjectList_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// loop all data rows
foreach (DataControlFieldCell cell in e.Row.Cells)
{
// check all cells in one row
foreach (Control control in cell.Controls)
{
//Links as the command button.
LinkButton button = control as LinkButton;
if (button != null && button.CommandName == "Delete")
// Add delete confirmation
button.OnClientClick = "if (!confirm('Are you sure " +
"you want to delete this record?')) return false;";
}
}
}
}

Cannot create database diagrams Issue - Configuration Fix

--Run this Code in sql qurry window

EXEC sp_dbcmptlevel 'your Database Name', '90';
go
ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO "YourLoginName"
go
use [DatabaseName]
go
EXECUTE AS USER = N'dbo' REVERT
go

String Format for DateTime [C#]

String Format for DateTime [C#]


DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt); // "8 08 008 2008" year
String.Format("{0:M MM MMM MMMM}", dt); // "3 03 Mar March" month
String.Format("{0:d dd ddd dddd}", dt); // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}", dt); // "4 04 16 16" hour 12/24
String.Format("{0:m mm}", dt); // "5 05" minute
String.Format("{0:s ss}", dt); // "7 07" second
String.Format("{0:f ff fff ffff}", dt); // "1 12 123 1230" sec.fraction
String.Format("{0:F FF FFF FFFF}", dt); // "1 12 123 123" without zeroes
String.Format("{0:t tt}", dt); // "P PM" A.M. or P.M.
String.Format("{0:z zz zzz}", dt); // "-6 -06 -06:00" time zone

// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)


// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt); // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"

// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt); // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt); // "Sunday, March 9, 2008"

// two/four digit year
String.Format("{0:MM/dd/yy}", dt); // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"

Could not load type 'System.Web.UI.Compatibility.CompareValidator' from assembly 'System.Web.Extensions' Issue Fixing

Things were working good but after installing ASP.NET AJAX 1.0 I was Getting most probably this error


FIXES Bellow

Step 1. I have downloaded validators from:
http://blogs.msdn.com/mattgi/attachment/1516974.ashx

Step 2. I had below mappings in web.config:
Step 2. I had below mappings in web.config:
<tagMappingglt;
<add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="System.Web.UI.Compatibility.CompareValidator, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
<add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="System.Web.UI.Compatibility.CustomValidator, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
<add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="System.Web.UI.Compatibility.RangeValidator, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
<add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="System.Web.UI.Compatibility.RegularExpressionValidator, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
<add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="System.Web.UI.Compatibility.RequiredFieldValidator, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
<add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="System.Web.UI.Compatibility.ValidationSummary, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/glt;
</tagMappingglt;

Replaced above with :
<tagMappingglt;
<add tagType="System.Web.UI.WebControls.CompareValidator" mappedTagType="Sample.Web.UI.Compatibility.CompareValidator, Validators, Version=1.0.0.0"/glt;
<add tagType="System.Web.UI.WebControls.CustomValidator" mappedTagType="Sample.Web.UI.Compatibility.CustomValidator, Validators, Version=1.0.0.0"/glt;
<add tagType="System.Web.UI.WebControls.RangeValidator" mappedTagType="Sample.Web.UI.Compatibility.RangeValidator, Validators, Version=1.0.0.0"/glt;
<add tagType="System.Web.UI.WebControls.RegularExpressionValidator" mappedTagType="Sample.Web.UI.Compatibility.RegularExpressionValidator, Validators, Version=1.0.0.0"/glt;
<add tagType="System.Web.UI.WebControls.RequiredFieldValidator" mappedTagType="Sample.Web.UI.Compatibility.RequiredFieldValidator, Validators, Version=1.0.0.0"/glt;
<add tagType="System.Web.UI.WebControls.ValidationSummary" mappedTagType="Sample.Web.UI.Compatibility.ValidationSummary, Validators, Version=1.0.0.0"/glt;
</tagMappingglt;

Asp.net datagrid Using template field det particular datakey

protected void gvleaveHistory_RowCommand(object sender, GridViewCommandEventArgs e)
{
string strLeaveId = e.CommandArgument.ToString();//gvleaveHistory.DataKeys[e.RowIndex]["LeaveId"].ToString().Trim();
if (strLeaveId != string.Empty)
{

Leave objLeave = new Leave();
objLeave.DeleteLeaveByLeaveId(Convert.ToInt32(strLeaveId));
LoadInitialDataForHistry();
LoadInitialData();
}
}


======ASPX Code============
<ItemTemplate >
<asp:LinkButton ID="lnkCancel" runat="server" OnClick="lnkCancel_Click" CommandArgument='<%# Eval("LeaveId")%>'> Cancel </asp:LinkButton >
</ItemTemplate >

javascript function delete()

function delete()
{
if (confirm("Are you sure you want to delete?")==true)
return true;
else
return false;
}


_myButton.Attributes.Add("onclick", "return delete();");

Add a Default item to drop down list /combo box Asp.net

ddlUser.Items.Insert(0, new ListItem("", "-1"));

Load Years and Month to Drop down list Easely C#

public void LoadDDLYears(DropDownList ddlYear)
{
for (int i = 30; i > -30; i--)
{
ddlYear.Items.Add(System.DateTime.Now.AddYears(i).Year.ToString());
}
ddlYear.SelectedValue = System.DateTime.Now.Year.ToString();
}

public void LoadMonth(DropDownList ddlMonth)
{
DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
for (int month = 1; month < 13; month++)
{
ListItem li = new ListItem();
li.Text = dtfi.GetMonthName(month);
li.Value = month.ToString();
ddlMonth.Items.Add(li);
}
}

Using regular expression for the time format validation

public bool IsValidTime(string thetime)
{
string reg=@"([0-1]\d|2[0-3]):([0-5]\d)";
Regex checktime = new Regex(@reg);

//Regex checktime = new Regex(@"^(20|21|22|23|[01]d|d)(([:][0-5]d){1,2})$");

return checktime.IsMatch(thetime);
}

Sql mail (2005 )send using stored procedure

ALTER PROCEDURE [dbo].[uspGenerateMailTimeSheetDailyReminder]
AS
--Declaration area
DECLARE @firstName VARCHAR(50)
DECLARE @lastName VARCHAR(50)
DECLARE @email VARCHAR(255)
declare @body1 varchar(500)

------Send mail To Employees end of the day for timesheet tasks
DECLARE Mailing CURSOR FOR


SELECT Email, EmployeeFirstName,EmployeeSurname
FROM UserGroup INNER JOIN
tblEmployees ON UserGroup.UserName = tblEmployees.UserName

WHERE UserGroup.GroupName='Employee' AND IsActive='1' AND tblEmployees.EmployeeFirstName='sanjeewa'



OPEN Mailing
FETCH NEXT FROM Mailing INTO @email, @firstName,@lastName
WHILE 0 = @@FETCH_STATUS
BEGIN

set @body1 = 'Dear ' + CAST(@firstName AS VARCHAR) + ' ' + CAST(@lastName AS VARCHAR)+',< br/>' +'< br/ >'+ 'Please update your time sheets before you sign out. ' +'< br/>'+'< br/ >' + 'Regards' + '< br/ >'+ 'Admin.'
EXEC msdb.dbo.sp_send_dbmail @recipients=@email,
@subject = 'Please update your time sheets. ',
@body = @body1,
@body_format = 'HTML' ;

FETCH NEXT FROM Mailing INTO @email, @firstName,@lastName
END

CLOSE Mailing
DEALLOCATE Mailing

get summery total in datagrid C# - rowdatabound event

protected void gvWeeklySummeryReport_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
switch ((int)(e.Row.RowType))
{
case (int)ListItemType.Item:
case (int)ListItemType.AlternatingItem:
//Calculate total for the field of each row and alternating row.
myTotal += Convert.ToDouble(gvWeeklySummeryReport.DataKeys[e.Row.RowIndex].Values["SpentHours"]);
//Format the data, and then align the text of each cell to the right.
e.Row.Cells[3].Attributes.Add("align", "right");
break;
}
}
else if(e.Row.RowType == DataControlRowType.Footer)
{
gvWeeklySummeryReport.FooterStyle.Font.Bold = true;
//Use the footer to display the summary row.
e.Row.Cells[3].Text = "Total hours";
e.Row.Cells[4].Attributes.Add("align", "left");
e.Row.Cells[4].Attributes.Add("align", "center");
e.Row.Cells[4].Attributes.Add("style", "color:black;font-weight:bold");
e.Row.Cells[3].Attributes.Add("style", "color:black;font-weight:bold");
e.Row.Cells[4].Text = myTotal.ToString();
}
}

Get particular Date / Day C#

DateTime sundayDate = DateTime.Now.Subtract(new TimeSpan((int)DateTime.Now.DayOfWeek, 0, 0, 0));

SQL SP for Update with the Condition cheking

ALTER PROCEDURE [dbo].[uspUpdateEmployee]
@UserName varchar(50),
@JoinDate datetime,
@PermantApprovedDate datetime,
@IsActive bit,
@EmployeeStatus int
AS

DECLARE @IsPermentEmployeeVal BIT
IF(@EmployeeStatus = 1)

BEGIN
SET @IsPermentEmployeeVal=1
END

ELSE
BEGIN
SET @IsPermentEmployeeVal = 0
END

UPDATE tblEmployees

SET
[LastModifiedDate] = Getdate(),
[PermantApprovedDate] = @PermantApprovedDate,
[JoinedDate] = @JoinDate,
[IsActive]=@IsActive,
[EmployeeStatus] =@EmployeeStatus,
IsPermentEmployee=@IsPermentEmployeeVal
WHERE UserName=@UserName

Changing the text in a Div using java script

java script code
================
document.getElementById("divMenuText").innerText="Title for top level menu";

html code
=========
< td width="130" > <div id="divMenuText" > Menu Text:</div > </td >

Close the browser window Using C# code

StringBuilder postBackScript = new StringBuilder();
postBackScript.Append("< script language=JavaScript >");
postBackScript.Append("self.close(); ");
postBackScript.Append("</script >");

if (!Page.ClientScript.IsClientScriptBlockRegistered("parentCallBack"))
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "parentCallBack", postBackScript.ToString());

return false in javascript message box

this.btnLogOut.Attributes.Add("OnClick", "window.close(); return false;");

Set Culture setting from web config and get required Date time format

< globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="en-AU" uiCulture="en-AU"/>

Data formatting String in data grid asp.net

"£{0:F2}"—112.0000 as (pundMark)112.00

You can keep it blank or any currency name replaceing “£”

Combo box default selected item

ddlDissection.Items.Insert(0, new ListItem("--Select Item--", "-1"));
ddlDissection.SelectedValue = "-1";

Write a trigger in sql server

//Write a trigger in sql server
-- =============================================
CREATE TRIGGER [dbo].[trigUpdateDissection_Cat_Vendor]
ON [dbo].[tblProductDissection]
AFTER INSERT,DELETE
AS
BEGIN
DECLARE
@VendorId bigint,
@DissectionId bigint,
@CategoryId bigint,
@ProductId bigint

IF (SELECT Count(*) FROM INSERTED) > 0
BEGIN
SELECT
@ProductId=tblProductDissection.ProductId,
@DissectionId=tblProductDissection.DissectionId

FROM INSERTED tblProductDissection

insert into dbo.tblDissection_Cat_Vendor
(
VendorId,
DissectionId,
CategoryId
)

SELECT VendorId ,@DissectionId,CategoryId from tblProduct where tblProduct.productId=@ProductId
END


--End inserted

IF (SELECT Count(*) FROM DELETED) > 0
BEGIN
SELECT
@ProductId=tblProductDissection.ProductId,
@DissectionId=tblProductDissection.DissectionId

FROM DELETED tblProductDissection

SELECT @VendorId=VendorId from tblProduct where tblProduct.productId=@ProductId
SELECT @CategoryId=CategoryId from tblProduct where tblProduct.productId=@ProductId

DELETE from dbo.tblDissection_Cat_Vendor
WHERE DissectionId=@DissectionId and
CategoryId=@CategoryId and VendorId=@VendorId
END

END

Onclient click navigation-java scrip

OnClick="btnCancel_Click" OnClientClick="location.href='OrderStatus.aspx';return false;"

Using dictionaries for the check box area includeig page

//Property
protected Dictionary DicSelectedDissections
{
get
{
if (dicSelectedDissections == null)
{
if (Session["SelectedDissectionIds"] != null)
{
dicSelectedDissections = (Dictionary)Session["SelectedDissectionIds"];
}
else
{
dicSelectedDissections = new Dictionary();
}
}
return dicSelectedDissections;
}
set
{
dicSelectedDissections = value;
Session["SelectedDissectionIds"] = value;
}
}


//Handle area

private void UpdateSeletedtoDissectionList()
{
try
{
foreach (GridViewRow row in gvDissections.Rows)
{

if (((CheckBox)row.Cells[0].Controls[1]).Checked)
{
if (!DicSelectedDissections.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString())))
{
DicSelectedDissections.Add(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()), "");
}

if (DicSelectedDissections.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString())))
{
DicSelectedDissections.Remove(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()));
DicSelectedDissections.Add(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()), "");
}

if (DicDeletedDissection.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString())))
{
DicDeletedDissection.Remove(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()));
}
}
else
{
if (!DicDeletedDissection.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()))
&& DicSelectedDissections.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString())))
{
DicDeletedDissection.Add(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()),"");
}

if (DicSelectedDissections.ContainsKey(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString())))
{
DicSelectedDissections.Remove(Int32.Parse(gvDissections.DataKeys[row.RowIndex]["DissectionId"].ToString()));
}



}


}
Session["SelectedDissectionIds"] = DicSelectedDissections;
Session["DeletedDissectionIds"] = DicDeletedDissection;

}

can increase your asp.net application session timeout

You can increase your asp.net application session timeout in below methods:
1) Include this in you web.config file:


2) in the session_start of global.asax

protected void Session_Start(Object sender, EventArgs e)
{
Session.Timeout = 540;
}
The value of session timeout is in minutes.

get the ip and find the what countre from where it comming

private void SetUserCountry(Order newOrder)
{
string strIPaddress = HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"];

//strIPaddress = "220.136.40.211";
string[] arrIPNumber = strIPaddress.Split('.');

//IP Number = A x 16777216 + B x 65536 + C x 256 + D
Int64 A = Int64.Parse(arrIPNumber[0]) * 16777216;
Int64 B = Int64.Parse(arrIPNumber[1]) * 65536;
Int64 C = Int64.Parse(arrIPNumber[2]) * 256;
Int64 D = Int64.Parse(arrIPNumber[3]);


Int64 iPNumber = A + B + C + D;

//lblError.Text = "IP :" + strIPaddress + " ipnumber : " + iPNumber;

string strUserCountry = newOrder.GetUserCountry(iPNumber);

if (strUserCountry != string.Empty)
{
//ddlBillingCountry.SelectedValue = ddlBillingCountry.Items.FindByText(strUserCountry).Value;

}



}



public string GetUserCountry(Int64 IPNumber)
{
try
{
string strCountry = string.Empty;

Database db = DatabaseFactory.CreateDatabase(Constants.Database_Connection_Name);
DbCommand dbCommand = db.GetStoredProcCommand(Constants.SP_GetUserCountry);
db.AddInParameter(dbCommand, "@nIPNumber", DbType.String, IPNumber);

DataSet dsUserCountry = db.ExecuteDataSet(dbCommand);

if (dsUserCountry != null)
{
if (dsUserCountry.Tables[0].Rows.Count > 0)
{
strCountry = dsUserCountry.Tables[0].Rows[0]["Country"].ToString();


}
}

return strCountry;

}

catch (Exception ex)
{
ex.Data.Add("BusinessLayerException", GetType().ToString() + Constants.Error_Seperator + "public DataTable GetDeliveryCities()");
throw ex;

}


}
//Stored procedure
-----
ALTER PROCEDURE [dbo].[uspGetUsercountry]

@nIPNumber BIGINT

AS

BEGIN

SELECT Country
FROM dbo.tblIPToCountry
WHERE (ToIPAddress >= @nIPNumber) AND (FromIPAddress <= @nIPNumber)




END

Function for set UploadMaximum image file size

//Function for set UploadMaximum image file size
private void SetUploadSize()
{
decimal sizeInBytes = 0;

if (hdnOrderID.Value != string.Empty)
{
string directory = Server.MapPath("PhotoPrintImages/" + hdnOrderID.Value);

DirectoryInfo dir = new DirectoryInfo(directory);


foreach (FileInfo f in dir.GetFiles())
{
sizeInBytes += f.Length;
}
imageSize = (sizeInBytes / 1048576);
if (imageSize > 0)
{
lblSize.Text = imageSize.ToString("n2");
}
else
{
lblSize.Text = "0";
}
decimal maximumSize = Decimal.Parse(Constants.CONST_PRINT_MAXIMUM_SIZE);

decimal progressSize = Math.Round((imageSize / maximumSize) * 300, 0);
if (progressSize < 1)
{
imgProgressBar.Width = 0;
}
else
{
imgProgressBar.Width = (int)progressSize;

}

}