Get the objects(stored procedures and functions) from database based on the keyword

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'
AND ROUTINE_TYPE='PROCEDURE'

URL for if 503 error comes for IIS

IIS:If not opening even localhost also, we need to change some settings. See this url.
(error : 503 service unavailable)
http://www.west-wind.com/weblog/posts/9436.aspx

Sqlcache dependency URLs

Sql cache dependency examples URLS

http://davidhayden.com/blog/dave/archive/2006/04/29/2929.aspx
http://www.asp.net/data-access/tutorials/using-sql-cache-dependencies-cs
http://www.codeproject.com/KB/database/QueryNotifications.aspx
http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/

Sql cache dependency in 3 tier architecure
http://www.comanswer.com/question/sqlcachedependency-in-n-tier-architekture

Get columns names for a table

Get columns names from table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FASTNOTIFICATION'

To restrict Ctrl+C for paste in textbox(using for Confirm Password secnarios)

Excel to Sql Server using Oledb connection

Hi,

If we want upload the excel file date to sql server database we have to use the following code.

1. Add the "System.Data.OleDb" namespace

eg : using System.Data.OleDb;

2. Use the following connection string for Oledb Connection. It will support both 2003 and 2007 excel formats. I am adding this, in web.config.



3. wrie query for read data from excel
string strQuery = "SELECT FirstName,LastName,EmailID,Organisation,MotherTongue,PreferredLanguage,PhoneNumber,Role FROM [Sheet1$]";

4. following is the code for insert the data from excel using Oledb connection
public void UploadExcelFile(string query, string excelConnectionString, int intBulkUploadID)
{
using(var oleDbConnection = new OleDbConnection(excelConnectionString))
using (var destinationConnection = new SqlConnection(sConn))
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{

bulkCopy.ColumnMappings.Add("FirstName", "FirstName");
bulkCopy.ColumnMappings.Add("LastName", "LastName");
bulkCopy.ColumnMappings.Add("EmailID", "EmailID");
bulkCopy.ColumnMappings.Add("Organisation", "Organisation");
bulkCopy.ColumnMappings.Add("MotherTongue", "MotherTongue");
bulkCopy.ColumnMappings.Add("PreferredLanguage", "PreferredLanguage");
bulkCopy.ColumnMappings.Add("PhoneNumber", "PhoneNumber");
bulkCopy.ColumnMappings.Add("Role", "Role");
bulkCopy.ColumnMappings.Add("BulkUploadID", "BulkUploadID");
bulkCopy.DestinationTableName = "dbo.Bulk_EntryUserMaster";

using (var myCommand = new OleDbCommand(query, oleDbConnection))
{
oleDbConnection.Open();
destinationConnection.Open();
int count = bulkCopy.NotifyAfter;

OleDbDataAdapter oleAdapter = new OleDbDataAdapter();
oleAdapter.SelectCommand = new OleDbCommand(query, oleDbConnection);
DataTable dt = new DataTable("Bulk_EntryUserMaster");

dt.Columns.Add("BulkUploadID", Type.GetType("System.Int32")).DefaultValue = intBulkUploadID;

oleAdapter.FillSchema(dt, SchemaType.Source);
oleAdapter.Fill(dt);

// Removing the empty rows
dt.DefaultView.RowFilter = "FirstName Is Not Null or LastName Is Not Null or EmailID Is Not Null or MotherTongue Is Not Null or PreferredLanguage Is Not Null or Organisation Is Not Null or PhoneNumber Is Not Null or Role Is Not Null";
DataTable dtNew = dt.DefaultView.ToTable();

//IEnumerable results = dtNew.AsEnumerable().Distinct();

//DataTable results = dtNew.AsEnumerable().Distinct().CopyToDataTable();

bulkCopy.WriteToServer(dtNew);

}
}
}

If you want validate excel columns see this.

private string ValidateExcelColumns(string excelConnectionString)
{
using (var oleDbConnection = new OleDbConnection(excelConnectionString))
{
oleDbConnection.Open();
DataTable dt = new DataTable();
String[] restrection = { null, null, "Sheet1$", null };

dt = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrection);

if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

DataRow[] rows = dt.Select("", "ORDINAL_POSITION ASC");

foreach (DataRow row in rows)
{
excelSheets[i] = row["COLUMN_NAME"].ToString();
i++;
}

String[] databaseColumns = { "FirstName", "LastName", "EmailID", "Organisation","Role", "MotherTongue", "PreferredLanguage", "PhoneNumber" };

for (int j = 0; j < excelSheets.Length; j++)
{
if (excelSheets[j].ToString().Trim() != databaseColumns[j].ToString().Trim())
{
return "Columns Names not Proper";
}

}
}
return string.Empty;


}

Download in Excel or CSV format

Hi,

if we want only simple table format. This is enough.
public void ExportToCSVTitle(DataTable dtExportToCSV, string fileName)
{
string strCSVSeparator = ",";
HttpContext context = HttpContext.Current;
context.Response.Clear();
//Added for Special Character Export
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF32;

context.Response.Write(Environment.NewLine);
foreach (DataColumn dcExportToCSV in dtExportToCSV.Columns)
{
context.Response.Write("\"" + dcExportToCSV.ColumnName + "\"" + strCSVSeparator);
}
// In Org and Group search if we do not leave a line , the data comes in the same line as header
context.Response.Write(Environment.NewLine);
foreach (DataRow drExportToCSV in dtExportToCSV.Rows)
{
for (int i = 0; i < dtExportToCSV.Columns.Count; i++)
{
context.Response.Write("\"" + drExportToCSV[i].ToString() + "\"" + strCSVSeparator);
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
context.Response.End();
}

suppose, if we want lables type of things at top of the table, then we should use this code...

public void ExportToCSVTitle(DataTable dtExportToCSV, string fileName, List listTitle)
{
string strCSVSeparator = ",";
HttpContext context = HttpContext.Current;
context.Response.Clear();
//Added for Special Character Export
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF32;
foreach (string title in listTitle)
{
context.Response.Write("\"" + title + "\"" + strCSVSeparator);
context.Response.Write(Environment.NewLine);
}
context.Response.Write(Environment.NewLine);
foreach (DataColumn dcExportToCSV in dtExportToCSV.Columns)
{
context.Response.Write("\"" + dcExportToCSV.ColumnName + "\"" + strCSVSeparator);
}
// In Org and Group search if we do not leave a line , the data comes in the same line as header
context.Response.Write(Environment.NewLine);
foreach (DataRow drExportToCSV in dtExportToCSV.Rows)
{
for (int i = 0; i < dtExportToCSV.Columns.Count; i++)
{
context.Response.Write("\"" + drExportToCSV[i].ToString() + "\"" + strCSVSeparator);
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
context.Response.End();
}

Validate the calender control dates using javascript

Hi,

For this use the following javascript methods.

function ReturnDate(strDt)
{
var str = strDt;
var posn = str.indexOf("/");
var dd = str.substr(0,posn);
var str1 = str.substr(posn+1);
posn = str1.indexOf("/");
var strMm = str1.substr(0,posn);
if(strMm.length==1)
{
strMm="0"+ strMm;
}
var mm=strMm
var yy = str1.substr(posn+1);
if(mm=="01") mm=0;
else if(mm=="02") mm=1;
else if(mm=="03") mm=2;
else if(mm=="04") mm=3;
else if(mm=="05") mm=4;
else if(mm=="06") mm=5;
else if(mm=="07") mm=6;
else if(mm=="08") mm=7;
else if(mm=="09") mm=8;
else if(mm=="10") mm=9;
else if(mm=="11") mm=10;
else if(mm=="12") mm=11;
var ReturnDate = new Date(yy,mm,dd)
return ReturnDate;
}

function GetMonth(intMonth){
var MonthArray = new Array("01", "02", "03",
"04", "05", "06",
"07", "08", "09",
"10", "11", "12");
return MonthArray[intMonth];
}



function ValidateDates()
{

var otxtFromDate = document.getElementById('<%= txtFrom.ClientID %>')

var otxtToDate = document.getElementById('<%= txtTo.ClientID %>')

var toDate = otxtToDate.value;
var fromDate = otxtFromDate.value;

var currDate=new Date();

var currYear=currDate.getFullYear();


var currDay=currDate.getDate();

var currMonth=GetMonth(currDate.getMonth());

currDate=currDay+"/"+currMonth+"/"+currYear;

if(toDate != null && toDate != '')
{
if(ReturnDate(toDate) {

otxtToDate.focus();

return false;
}
}
if(fromDate != null && toDate != null && fromDate != '' && toDate != '')
{
if(ReturnDate(fromDate)>ReturnDate(toDate))
{

otxtToDate.focus();

return false;
}
}

return true;
}

Clear the File upload control value using Javascript

Hi,

If you want clear the file upload control value use the following code.

// To Clear the value in File Upload(For IE and Firefox)
var oFileUpload=document.getElementsByName('<%=fuUserList.UniqueID%>')[0];
oFileUpload.value="";
var oFileUpload2= oFileUpload.cloneNode(false);
oFileUpload2.onchange= oFileUpload.onchange;
oFileUpload.parentNode.replaceChild(oFileUpload2,oFileUpload);

Validate file format(.xls or .xlsx) using File upload control using Javascript

Hi,

If you want the validate file type using the file upload control means like the wheather it is .doc or .xls or etc., using Javascript

fuUserList -- File upload control

var fuData = document.getElementById('<%=fuUserList.ClientID%>');
var fileUploadPath = fuData.value;

var extension = fileUploadPath.substring(fileUploadPath.lastIndexOf('.')
+ 1).toLowerCase();

if (extension == "xls" || extension == "xlsx")
{
return true; // Valid file type
}
else
{
document.getElementById('<%=divExcelError.ClientID%>').style.display = "block";
return false; // Not valid file type
}

Copy the XML Document

Hi,

The copy of XML documnet is different from our normal objects. Because while coping the XML documnet it is coping with ref obeject. Means

XMLDocument obj1 = "Some XML documnet or XML document obeject"

XMLDocument obj2 = obj1;

If you make any changes for obj2, same thing will reflect in obj1 also. Because it is having the ref while copying. To avoid this type problems use this for coping

obj2 = (XmlDocument)obj1 .Clone();

It's works better!!

Alert Message from Code behind with ASP.Net and C.Net

public static void ShowAlertMessage(string message)
{
Page page = HttpContext.Current.Handler as Page;
if (page != null)
{
ScriptManager.RegisterStartupScript(page, page.GetType(), "err_msg", "alert('" + message + "');", true);
}
}

Here, "err_msg" unique key value for alert message. It is your wish .