A Database Trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

Difference between a trigger and a stored procedure

1- when you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2-trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3- within a trigger you can call specific s.p but within a s.p you cann;t call atrigger

68 unique visitors

SQL Server Database ConnectionString

string strCon;
SqlConnection objCon = new SqlConnection();
string mstrConnectionstring = String.Empty;
public DataObject()
{
objCon = new SqlConnection();
strCon = ConfigurationSettings.AppSettings.Get("mstrConnectionstring");
objCon = new SqlConnection(strCon);
objCon.Open();
----------------------------------------
Inside WebConfig. Replace the ( and ) by <>.

(appSettings)(add key="mstrConnectionstring" value="user id= ab;password=**********; server=VAIJAYANTA-PC\SQLEXPRESS; database = Vaijayanta-Test")(/add)(/appSettings)

65 unique visitors

Export Gridview to Pdf

Error: - The document has no pages.
Soution: -Check the column name of your gridview. Whether they match with the ones you send to the data table or not. If they match. Then this error will not be shown.
Export Gridview to Pdf: -
1) First of all add reference the dll file called itextsharp.dll to your solution under References.
It can be obtained from: -

2) Then write the code below: -
using iTextSharp;using iTextSharp.text;using iTextSharp.text.pdf;
protected void btnExportToPdf_Click(object sender, EventArgs e) { ExportToPDF(); }
private void ExportToPDF() { Document document = new Document(PageSize.A4, 0, 0, 50, 50); System.IO.MemoryStream msReport = new System.IO.MemoryStream();
try { // creation of the different writers PdfWriter writer = PdfWriter.GetInstance(document, msReport);
// we add some meta information to the document document.AddAuthor("Vaichatt"); document.AddSubject("Export to PDF");
document.Open();
iTextSharp.text.Table datatable = new iTextSharp.text.Table(5);
datatable.Padding = 2; datatable.Spacing = 0;
//float[] headerwidths = { 6, 20, 32, 18, 8, 8, 8 }; float[] headerwidths = { 10, 50, 10, 15, 15 }; datatable.Widths = headerwidths;
// the first cell spans 7 columns // the first cell spans 5 columns Cell cell = new Cell(new Phrase("Manage Category Report", FontFactory.GetFont(FontFactory.HELVETICA, 16, Font.BOLD))); cell.HorizontalAlignment = Element.ALIGN_CENTER; cell.Leading = 30; //cell.Colspan = 7; cell.Colspan = 5; cell.Border = Rectangle.NO_BORDER; cell.BackgroundColor = new iTextSharp.text.Color(System.Drawing.Color.Gray); datatable.AddCell(cell);
// These cells span 2 rows datatable.DefaultCellBorderWidth = 1; datatable.DefaultHorizontalAlignment = 1; datatable.DefaultRowspan = 2; datatable.AddCell("Status"); datatable.AddCell(new Phrase("Category Name", FontFactory.GetFont(FontFactory.HELVETICA, 14, Font.NORMAL))); datatable.AddCell("Edit"); datatable.AddCell("Delete"); datatable.AddCell("Products");
// This cell spans the remaining 3 columns in 1 row //datatable.DefaultRowspan = 1; //datatable.DefaultColspan = 3; //datatable.AddCell("Just Put Anything");
// These cells span 1 row and 1 column //datatable.DefaultColspan = 1; //datatable.AddCell("Col 1"); //datatable.AddCell("Col 2"); //datatable.AddCell("Col 3");
datatable.DefaultCellBorderWidth = 1; datatable.DefaultRowspan = 1;
for (int i = 1; i < dgdManageCategory.Rows.Count; i++) { datatable.DefaultHorizontalAlignment = Element.ALIGN_LEFT; //datatable.AddCell(i.ToString()); //datatable.AddCell("This is my name."); //datatable.AddCell("I have a very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very long long address."); //datatable.AddCell("0123456789");
//datatable.DefaultHorizontalAlignment = Element.ALIGN_CENTER; //datatable.AddCell("No"); //datatable.AddCell("Yes"); //datatable.AddCell("No");
CheckBox cbIsActive = (CheckBox)dgdManageCategory.Rows[i].FindControl("cbIsActive"); datatable.AddCell(Convert.ToString(cbIsActive.Checked ));
Label lblCategoryName = (Label)dgdManageCategory.Rows[i].FindControl("lblCategoryName"); datatable.AddCell(lblCategoryName.Text);
//Label lblCatName = (Label)dgdManageCategory.Rows[i].FindControl("lblCatName"); //datatable.AddCell(lblCatName.ToString());
Button btnEdit = (Button)dgdManageCategory.Rows[i].FindControl("lbnEdit"); datatable.AddCell(btnEdit.Text);
Button btnDelete = (Button)dgdManageCategory.Rows[i].FindControl("lbnDelete"); datatable.AddCell(btnDelete.Text);
Button btnProducts = (Button)dgdManageCategory.Rows[i].FindControl("lbnProducts"); datatable.AddCell(btnProducts.Text); }
document.Add(datatable); } catch (Exception e) { string s = e.Message; Console.Error.WriteLine(e.Message); }
// we close the document document.Close();
Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=Export.pdf"); Response.ContentType = "application/pdf"; Response.BinaryWrite(msReport.ToArray()); Response.End();
//http://july-code.blogspot.com/2008/06/export-gridview-to-pdf.html }
//Get Help From:-// http://july-code.blogspot.com/2008/06/export-gridview-to-pdf.html//http://www.pdftron.com/net/usermanual.html

42 unique visitors

How to take backup of a SQL Server Database Data

How to take backup of a SQL Server Database Data: -

1) Database (right click) -> Tasks -> Backup -> Database -> OK

(the database backup will be done)

42 unique users

Import to Gridview from Excel Sheet (xlsx)

To import from an xlsx file to a Gridview, the whole code will be same as the post before, but the connection string should be changed.
It should be made: -


OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" +
Server.MapPath("~/App_Data/Running.xlsx") + ";" + "Extended Properties=\"Excel 12.0;HDR=Yes\"");

Rest is same.
The code will run properly.

Now the challenge is to select the path of the file at runtime.

38 visitors

Import to Gridview from Excel Sheet (xls)

Step 1 – Create Excel worksheet
1) Open Microsoft Excel and create a new Worksheet.
2) The sample data is as follows: - (any sample data)
We will keep the default name for the Worksheet (Sheet1).
3) Let us name and save the excel file as Running.xls.
Note: You can download the Excel file along with the code files from the Downloads section at the end of this article.

Step 2 –Display Excel Data using GridView Control
1) Start Visual Studio 2005.
2) Select Create Website and choose the Template ASP.NET Web Site. We can choose the language as C#/VB. Set the name for the project as ExcelGV.
3) Add the existing Excel file (Running.xls) that we created to the App_data folder.
4) Add a new .aspx file. Choose language C#/VB and, depending on the language, we will set the appropriate names: ExcelGVCS.aspx for C# or ExcelGVVB for VB.NET.
4) Drag and drop the GridView control on the .aspx page.
5) We will add the following statement in code behind above the namespace section.

-----------------------------------------------------------------------------------
using System.Data.OleDb;
------------------------------
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/App_Data/Running.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
IDataReader DBReader = DBCommand.ExecuteReader();
GridView1.DataSource = DBReader;
GridView1.DataBind();
DBReader.Close();
DBConnection.Close();
}
-----------------------------------------------------------------------------------
38 visitors

Code for Export data from GridView to Excel: -

Code for Export data from GridView to Excel: -
--------------------------------------------------------------Response.Clear(); Response.AddHeader("content-disposition", "attachment;filename=FileName.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls";
StringWriter stringWrite = new StringWriter(); HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); try { dgdManageCategory.RenderControl(htmlWrite); } catch (Exception ex) { string s = ex.Message; Response.Write("Cannot Export to Excel"); }
Response.Write(stringWrite.ToString()); Response.End(); }
--------------------------------------------------------------
As I tried to Export data from GridView to Excel I got this error: -
Control 'dgdManageCategory' of type 'GridView' must be placed inside a form tag with runat=server.
So I searched the net on various sites and saw that I shouild add an overridden method that would solve the problem: -
So I used the method: -
--------------------------------------------------------------
public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time. }
--------------------------------------------------------------
But I still got an error: -
"RegisterForEventValidation can only be called during Render();"
So I again searched the net and got this solution: -
Under the tag
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ManageCategory.aspx.cs" Inherits="CartTest.ManageCategory" %>
I should add "EnableEventValidation = "false" " to the above tag and thus the problem was solved.

Sites from where I got the help was: -
1) http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=118285
2) http://gridviewguy.com/ArticleDetails.aspx?articleID=182
3) http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
--
4) http://geekswithblogs.net/azamsharp/archive/2005/12/21/63845.aspx

Can AJAX be implemented in Windows Application?

Well technically you can use it in a windows application....

The long way to implement AJAX in a windows application.

just not in the way you may of intended. Since ajax is essentially asynchronous javascript and xml, you would need to use some sort of javascript engine (mozilla has a good one called SpiderMonkey which is written in c) and an XML parser, MSDOM or any standard lib would do. Create a class that implements the functionaly similar to a .NET 2.0 HttpRequest ( especially its asynchronous features ) and then export that class as a custom object so that it can be accessed by the javascript engine, being sure to implement the standard exception and errors found in regular XmlHttp calls. Once all of this is written you can write a windows application that loads a javascript file from anywhere, processes it (Either synchronously, or asynchronously) and returns the results so you can handle it appropriately.

The short way to implement AJAX in a windows application.

Quite simply, don't. The biggest feature of Ajax is that it simulates many windows applicsations ability to provide data on demand with out any postbacks or whatever. You can tweak your windows application to go above and beyond what Ajax could do by researching multithreading and the WinForms API and learning how to stream data from its source and automatically propagate that to the UI with out causing a noticeable wait to the main ui thread. But thats more into data visualization & web 2.0 centric than AJAX in itself.

GridView to Excel Sheet

Excel Sheet to GridView: -
Write this code in button click event: -
-------------------------------------------------------------------------------------
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
myDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
-------------------------------------------------------------------------------------

Create a Local Version of the SQL Server Database

Qs) How to create a local version of the database if security permissions do not allow to copy a database in a sql server?

Ans) In this procedure the data will no be copied. Follow the steps: -
1) Right click on the Database name at server
2) Tasks
3) Generate Scripts
4) Follow the "SQL Server Script Wizard" steps, step by step as required.
(A script will be generated)
5) Execute this script generated by copying it on a query window in a local sql server.
(All the tables, stored procedures & scripts will be created without the data inside it)

Create Windows Services Installer through C# .NET

1) http://www.grinn.net/blog/dev/2008/02/windows-services-in-c-part-2-adding.html

2) http://www.developerfusion.co.uk/show/3441/3/

Thread Concepts

1. http://www.albahari.com/threading/ (nice website)

Add Data to the DataTable (in earlier post)

public void addDataToDataTable()
{
FreshCart fc = new FreshCart();
DataSet ds = new DataSet();
ViewState["ProdID"] = Request.QueryString["ProductID"];
ds = fc.GetProductDetailsByProductID(Convert.ToInt32(ViewState["ProdID"]));
System.Data.DataTable table = new DataTable("ManageShoppingCartTable");
table = (DataTable)(Session["DataTableHolder"]);
DataRow row;
row = table.NewRow();
row["ProductID"] = Convert.ToInt32(ds.Tables[0].Rows[0][("ProductID")]);
row["ProductName"] = ds.Tables[0].Rows[0][("ProductName")].ToString().Trim();
//if (table.Rows.Count < 1)
//{
row["Quantity"] = 1;
//}
//else if (table.Rows.Count >= 1)
//{
// row["Quantity"] = Convert.ToInt32(gvManageShoppingCart.FindControl("txtQuantity.Text"));
//}
row["Price"] = Convert.ToDouble(ds.Tables[0].Rows[0][("Price")].ToString().Trim());
row["NetPrice"] = Convert.ToInt32(row["Quantity"].ToString()) * Convert.ToDouble(row["Price"]);
//row["NetPrice"] = 2*Convert.ToDouble(row["Price"]);
//int s = 0;
int rowVal = Convert.ToInt32(row["ProductID"]);
bool blnIsProductExist = false;
// if current product id is already present then dont insert the row.
if (Convert.ToInt32(table.Rows.Count) == 0)
{
table.Rows.Add(row);
}
else
{
for (int i = 0; i < table.Rows.Count; i++)
{
//int rowVal = Convert.ToInt32(row["ProductID"]);
if (Convert.ToInt32(table.Rows[i][0]) == rowVal)
{
//s = s + 1;
blnIsProductExist = true;
}
else
{
//s = 0;
}
}


if (!blnIsProductExist)
{
lblMessageUpdate.Visible = false;
table.Rows.Add(row);
}

else
{
lblMessageUpdate.Visible = true;
lblMessageUpdate.Text = "The data is already present and cannot be updated".ToString();
}
}
lblFlag.Text = "1"; //set the flag to avoid postback after populating the gridview
Session["DataTableHolder"] = table;
gvManageShoppingCart.DataSource = ((DataTable)table).DefaultView;
gvManageShoppingCart.DataBind();
}

Create a DataTable

public void createSecondDataTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable("UpdatedManageShoppingCartTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn column;

//Add 1st Column
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "ProductID";
//column.ReadOnly = true;
column.Unique = true;
//Add Column to table
table.Columns.Add(column);

//Add 2nd Column
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ProductName";
//column.ReadOnly = true;
column.Unique = false;
//Add Column to table
table.Columns.Add(column);

//Add 3rd Column
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "Quantity";
//column.ReadOnly = true;
column.Unique = false;
//Add Column to table
table.Columns.Add(column);

//Add 4th Column
column = new DataColumn();
column.DataType = System.Type.GetType("System.Double");
column.ColumnName = "Price";
//column.ReadOnly = true;
column.Unique = false;
//Add Column to table
table.Columns.Add(column);

//Add 5th Column
column = new DataColumn();
column.DataType = System.Type.GetType("System.Double");
column.ColumnName = "NetPrice";
//column.ReadOnly = true;
column.Unique = false;

//Add Column to table
table.Columns.Add(column);

//Make the ProductID column the primary column
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["ProductID"];
table.PrimaryKey = PrimaryKeyColumns;

//Instantiate the dataset
//DataSet dataset = new DataSet();
//Add datatable to the dataset
//dataset.Tables.Add(table);
Session["UpdatedManageShoppingCartTableHolder"] = table;
}

Interview questions of .NET

1) http://www.dotnetuncle.com/

THIS DATASOURCE DOES NOT SUPPORT SERVER-SIDE DATA PAGING

To solve this problem (THIS DATASOURCE DOES NOT SUPPORT SERVER-SIDE DATA PAGING ), you must set GridView DataSource Property as DataSet not DataReader .

An Example of Column Sorting

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace CartTest
{
public partial class RevenueReport : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblGrandTotal.Visible = false;
}
}
public void populateGridView()
{
FreshCart fc = new FreshCart();
fc.RevenueReportFromDate = Convert.ToDateTime(txtFromDate.Text.Trim());
fc.RevenueReportToDate = Convert.ToDateTime(txtToDate.Text.Trim());
DataSet ds = new DataSet();
ds = fc.populategvRevenueReport();
ViewState["SortExpression"] = "OrderDate";
ViewState["SortDirection"] = "DESC";
// check whether the date is present or not
string returnValue;
returnValue = ds.Tables[0].Rows[0][("ReturnValue")].ToString().Trim();
if (returnValue == "0")
{
//no matching date
gvRevenueReport.Visible = false;
lblGrandTotal.Visible = false;
lblQtyTotal.Visible = false;
lblRevenueTotal.Visible = false;
lblMessage.Visible = true;
lblMessage.Text = "No Matching Date";
}
else if (returnValue == "1")
{
lblMessage.Visible = false;
gvRevenueReport.Visible = true;
gvRevenueReport.DataSource = ds.Tables[1].DefaultView;
ViewState["DataSource"] = ds.Tables[1];
lblGrandTotal.Visible = true;
lblQtyTotal.Visible = true;
lblQtyTotal.Text = ds.Tables[2].Rows[0][("Qty_Total")].ToString().Trim();
lblRevenueTotal.Visible = true;
lblRevenueTotal.Text = ds.Tables[3].Rows[0][("Revenue_Total")].ToString().Trim();
gvRevenueReport.DataBind();
PopulateDetails(ViewState["SortExpression"].ToString(), ViewState["SortDirection"].ToString());
}
}
protected void btnGo_Click(object sender, EventArgs e)
{
populateGridView();
}
public void PopulateDetails(string strSortExp, string strSortDir)
{
DataView dv = ((DataTable)ViewState["DataSource"]).DefaultView;
DataTable NewsTable = dv.ToTable();
DataView NewsView = NewsTable.DefaultView;
if (NewsView != null)
{
NewsView.Sort = strSortExp + " " + strSortDir;
gvRevenueReport.DataSource = NewsView;
gvRevenueReport.DataBind();
}
}
protected void gvRevenueReport_Sorting(object sender, GridViewSortEventArgs e)
{
string SortExpression = e.SortExpression;
ViewState["SortExpression"] = SortExpression;
if (ViewState["SortDirection"].ToString() == "DESC")
{
ViewState["SortDirection"] = "ASC";
}
else
{
ViewState["SortDirection"] = "DESC";
}
PopulateDetails(ViewState["SortExpression"].ToString(), ViewState["SortDirection"].ToString());
}
}
}

Some Tough Abbreviations

MIME - Multipurpose Internet Mail Extensions

User Defined Function in Sql Server

http://msdn2.microsoft.com/en-us/magazine/cc164062.aspx

Paging

http://www.geocities.com/Jeff_Louie/net_paging.htm