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();
}