Tuesday, December 8, 2009

Interview Q?

Dynamic sql:
Dynamic SQL is generally used for sending SQL statements to the database
manager from interactive query building graphical user interfaces and
SQL command line processors as well as from applications where the complete
structure of queries is not known at application compilation time and
the programming API supports dynamic SQL.

ynamic SQL can be used from the following interfaces:
Applications and external routines that employ APIs that support dynamic SQL including: Embedded SQL, JDBC, CLI, ADO.NET
Interactive DB2 GUI interfaces including: DB2 Command Editor in the DB2 Control Center
DB2 Command Line Processor
DB2 Command Windows


Commit inside trigger:

Yes, We can used commit inside trigger.
For that required to use set pragma_ananomous on;

Wednesday, October 7, 2009

Some Q....

Diff between Union and union all is:
Union: select all distinct values from 2 tables.. and
Union all: select all values from both tables

you all know. Then you try this one
Q1.
select all column_name from table1 Union
select all column_name from table2.

guess what is the result? ..
.........................

Q2.
select distinct column_name from table1 Union All
select distinct column_name from table2.
what is the result? ..

try out find the result .....

Tuesday, September 8, 2009

How to add .ascx control in aspx page?

1)Create .ascx control. eg WebUserControl1.ascx

2)Register that control in aspx page

<%@ Register TagPrefix="web" TagName="WebControl" Src="~/WebUserControl1.ascx"%>

3) And use that control in aspx page by adding bellow line:
web:WebControl ID="cnt" runat="server"

Thursday, July 23, 2009

How to send Sms?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO.Ports;
using MailtoSMS;

namespace MailtoSMS
{
class sendSMS
{
String PORT;
public sendSMS()
{
try
{
char[] ch = new char[10];
string reply;
string[] s = System.IO.Ports.SerialPort.GetPortNames();
for (int i = 0; i < s.Length; i++)
{
SerialPort sp1 = new SerialPort(s[i].ToString());
sp1.Open();
sp1.Write("at\r\n");
System.Threading.Thread.Sleep(3500);
reply = sp1.ReadExisting();
if (reply.Contains("OK"))
{
PORT = s[i].ToString();
sp1.Close();
break;
}
}
SerialPort sp = new SerialPort(PORT);
sp.Open();
sp.Write("at\r\n");
System.Threading.Thread.Sleep(3500);
reply = sp.ReadExisting();
if (reply.Contains("OK"))
{
try
{
int i = 26;
char ctrlz = (char)i;
//textBox1.Text = textBox1.Text + s.ToString() + "\r\n";
sp.Write("at+cmgf=1\r\n");
System.Threading.Thread.Sleep(3500);
reply = sp.ReadExisting();
sp.Write("at+cmgs=\"" + Form1.MobNo + "\"\r\n");
System.Threading.Thread.Sleep(3500);
sp.Write(Form1.message + ctrlz);
reply = sp.ReadExisting();
sp.Close();
sp.Dispose();
}
catch (ObjectDisposedException ex)
{
Console.WriteLine(ex.Message);
}
}

//for(int i=0; i
//textBox1.Text = textBox1.Text + s.ToString() + "\r\n";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}

Monday, July 20, 2009

How to print document?


protected void btnPrint_Click(object sender, EventArgs e)
{
btnPrint.Visible = false;
btnEdit.Visible = false;
Response.Write("");
}

Thursday, July 2, 2009

Menu Control:

.aspx

Menu ID="Menu1"
runat="server"
orientation="Horizontal"/"Vertical"
StaticEnableDefaultPopOutImage="false"



.aspx.cs

Single Menu


MenuItem fixmenu =new MenuItem();
fixmenu.Text = "Home";
fixmenu.NavigateUrl = "~/Sample1.aspx";
Menu1.Items.Add(fixmenu);


Parent and there child :

//PARENT1
MenuItem parentMenu = new MenuItem();
parentMenu.Text = "Parent";
parentMenu.NavigateUrl = "~/Sample.aspx";
//CHILD1
MenuItem childMenu = new MenuItem();
childMenu.Text = "Child1";
childMenu.NavigateUrl = "~/ChildMenu.aspx";
parentMenu.ChildItems.Add(childMenu);
//CHILD2
MenuItem childMenu2 = new MenuItem();
childMenu2.Text = "Child2";
childMenu2.NavigateUrl = "~/ChildMenu.aspx";
parentMenu.ChildItems.Add(childMenu2);

Menu1.Items.Add(parentMenu);

In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database?

USE database
GO
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

In SQL Server 2005 How to Find Tables With Foreign Key Constraint in Database?

USE database
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Wednesday, July 1, 2009

Short-cuts In VS 2008?

There are 450 keyboard shortcuts in key VS 2005.
If you want to see all these then use following macro.
To Run Macro
1.Tools->Macro->Macro IDE OR ALT+F11
2.Expan MyMacro->Open Module1
3.Copy the following code
4.Run Macro (F5)

Check file c:\\Shortcuts.html

'Start Macro
Public Module Module1

Public Sub ListShortcutsInHTML()

'Declare a StreamWriter
Dim sw As System.IO.StreamWriter
sw = New IO.StreamWriter("c:\\Shortcuts.html")

'Write the beginning HTML
WriteHTMLStart(sw)

' Add a row for each keyboard shortcut
For Each c As Command In DTE.Commands
If c.Name <> "" Then
Dim bindings As System.Array
bindings = CType(c.Bindings, System.Array)
For i As Integer = 0 To bindings.Length - 1
sw.WriteLine("")
sw.WriteLine("" + c.Name + "")
sw.WriteLine("" + bindings(i) + "")
sw.WriteLine("")
Next

End If
Next

'Write the end HTML
WriteHTMLEnd(sw)

'Flush and close the stream
sw.Flush()
sw.Close()
End Sub
Public Sub WriteHTMLStart(ByVal sw As System.IO.StreamWriter)
sw.WriteLine("")
sw.WriteLine("")
sw.WriteLine("")</span><br /><br /><span style="color: rgb(51, 102, 255);"> sw.WriteLine("Visual Studio Keyboard Shortcuts")</span><br /><span style="color: rgb(51, 102, 255);"> sw.WriteLine("")
sw.WriteLine("")

sw.WriteLine("")
sw.WriteLine("

Visual Studio 2005 Keyboard Shortcuts

")

sw.WriteLine("")
sw.WriteLine("")
sw.WriteLine("")


End Sub

Public Sub WriteHTMLEnd(ByVal sw As System.IO.StreamWriter)
sw.WriteLine("
CommandShortcut
")

sw.WriteLine("")

sw.WriteLine("")
sw.WriteLine("")
End Sub

End Module

Wednesday, June 24, 2009

Delete all database without deleting constaints?

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO


EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
go

Sunday, May 31, 2009

Use Of Accordian in ASP.NET?

Design :

UpdatePanel ID="updateCustomer" runat="server"
ContentTemplate
asp:PlaceHolder ID="phCustomerTestimonial" runat="server asp:PlaceHolder
ContentTemplate
asp:UpdatePanel


Codding Part:
Accordion customerTestimonialsAccordion = new Accordion();


MemberService memberService = new MemberService();
MemberData.FeedbackDataTable feedback = new MemberData.FeedbackDataTable();
int i = 0;
customerTestimonialsAccordion.ID = "customerTestimonialsAccordion";
customerTestimonialsAccordion.SelectedIndex = 0;
customerTestimonialsAccordion.FadeTransitions = true;
customerTestimonialsAccordion.FramesPerSecond = 50;
customerTestimonialsAccordion.TransitionDuration = 250;
customerTestimonialsAccordion.SuppressHeaderPostbacks = true;
feedback = memberService.GetTestimonial();
DataView feedBackView = new DataView(feedback);
foreach (DataRowView feedbackRowView in feedback)
{
AccordionPane accordionPane = new AccordionPane();
LinkButton parentCustomerTestimonial =new LinkButton();
parentCustomerTestimonial.Text = feedbackRowView["FeedbackDescription"].ToString();
parentCustomerTestimonial.ID = feedbackRowView["FeedbackID"].ToString();
accordionPane.HeaderContainer.Controls.Add(parentCustomerTestimonial);
customerTestimonialsAccordion.Panes.Add(accordionPane);
i += 1;
}
phCustomerTestimonial.Controls.Add(customerTestimonialsAccordion);
}

Monday, May 25, 2009

How to handle events using java script?

function KeyPressed()
{
document.onkeydown = keydown;
function keydown(evt)
{
if (!evt)
evt = event;
if(evt.keyCode == 46 evt.keyCode == 8)
{
var value = document.getElementById('<%= txtValue.ClientID %>').value;
var show =parseInt(value );
if(show < 250)
{
document.getElementById('<%= txtValue.ClientID %>').innerText = show + 1;
}
}
else if(evt.keyCode != 37 evt.keyCode != 38 evt.keyCode != 39 evt.keyCode != 40)
{
var value = document.getElementById('<%= txtValue.ClientID %>').value;
var show = value-1;
document.getElementById('<%= txtValue.ClientID %>').innerText = show;
}
alert(evt.keyCode);
//return false;
}
}

Thursday, May 21, 2009

Open popup using string builder/in Server side:

StringBuilder Scr = new StringBuilder(); Scr.Append(""); Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "OpenFile", Scr.ToString());

Friday, May 15, 2009

Javascript for open popup in NewWindow:



function popUp(url, width, height) {
var intWidth=1000;
var intHeight=690;
var x='http://PersonalInformation.aspx';
if(width > 0) intWidth = width;
if(height > 0) intHeight = height;
var winPopUp = window.open(x + url,'popup','width=' + intWidth + ',height=' + intHeight + ',scrollbars,resizable,left=30,top=45');
if(typeof window.focus != 'undefined') javascript: winPopUp.focus();
}



PostBackUrl='javascript:popUp("",1024,600)'

Thursday, May 14, 2009

Javascript for email validation !

Javascript for email validation !


function check()
{
var elem= document.getElementById("txtEmail");
var str = elem.value;
var re = /^[\w-]+(\.[\w-]+)*@([\w-]+\.)+[a-zA-Z]{2,7}$/;
if (!str.match(re))
{
alert("Verify the e-mail address format.");
}
else
{
alert("Correct!");
}
}

Friday, May 8, 2009

Thursday, May 7, 2009

How to attach PDF when sending mail?

Attachment when sending mail :-
string attachment = Session["PdfFileName"].ToString();
Attachment att = new Attachment(attachment);
message.Attachments.Add(att);
SmtpClient emailClient = new SmtpClient(SMTPServer);

Monday, April 27, 2009

How 2 send mail using asp.net?

ASP.NET Email Tutorial
This tutorial will show you how to send a simple email message using ASP.NET 2.0 and C#
Sending a email using ASP.NET 2.0 and C# 2.0 is actually very simple. First, you will need to import the System.Net.Mail namespace.The System.Net.Mail namespace contains the SmtpClient and MailMessage Classes that we need in order to send the email.
using System.Net.Mail; // Importent
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!We use the btnSubmit_Click event to do the work.We then call the emailClient.Send to send the message using the variables from our ASP.NET coded page.
protected void btnSubmit_Click(object sender, EventArgs e){
try{
MailMessage message = new MailMessage(txtFrom.Text, txtTo.Text, txtSubject.Text, txtBody.Text);SmtpClient emailClient = new SmtpClient(txtSMTPServer.Text);emailClient.Send(message);litStatus.Text = "Message Sent";}catch (Exception ex){
litStatus.Text=ex.ToString();}}
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
The front end .aspx page looks something like this:
To
From
SMTP Server
Subject
Body
Action
Status

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
The flow for the code behind page is as follows.
using System;using System.Data;using System.Configuration;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.Net.Mail;public partial class _Default : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e){}protected void btnSubmit_Click(object sender, EventArgs e){
try{
MailMessage message = new MailMessage(txtFrom.Text, txtTo.Text, txtSubject.Text, txtBody.Text);SmtpClient emailClient = new SmtpClient(txtSMTPServer.Text);emailClient.Send(message);litStatus.Text = "Message Sent";}catch (Exception ex){
litStatus.Text=ex.ToString();}}}


It is not possible to change SMTPServer (in codebehind ) hence change in web.config :

in code behind :
add String str = ConfigurationManager.AppSetting("SMTPServer");

Monday, April 20, 2009

How to uploadfile?

Random random = new Random();
string logoKey;
string brandLogo;
//Generate random key.
logoKey = Convert.ToString(random.Next());
if (fuUploadLogo.FileName == "")
{
MessageBox(this, "Select the logo image");
fuUploadLogo.Focus();
return;
}
brandLogo = fuUploadLogo.FileName;
if (brandLogo.Trim().Length > 0)
{
brandLogo = "Coupon" + logoKey + brandLogo;
brandLogo = brandLogo.ToString().Replace(" ", ".");
}

if ((brandLogo.ToString() != "") && (fuUploadLogo.HasFile == true))
{
fuUploadLogo.SaveAs(Server.MapPath("../Common/BrandLogo/" + Convert.ToString(brandLogo)));
}
}

//Show message if file is not uploaded.
public void MessageBox(System.Web.UI.Page page, string Msg)
{
string MsgScript = "";
if (!(page.IsStartupScriptRegistered("MessageScript")))
page.RegisterStartupScript("MessageScript", MsgScript);
}

Friday, April 17, 2009

Three Tier Architecture in ASP.NET

These layers are described below.
1. Application layer or Business layer

2. Business layer a. Property layer(Sub layer of business layer)
3. data layer
Advantages of three Tier Architecture.
The main characteristic of a Host Architecture is that the application and databases reside on the same host computer and the user interacts with the host using an unfriendly and dump terminal. This architecture does not support distributed computing (the host applications are not able to connect a database of a strategically allied partner). Some managers found that developing a host application take too long and it is expensive. Consequently led these disadvantages to Client-Server architecture.
Client-Server architecture is 2-Tier architecture because the client does not distinguish between Presentation layer and business layer. The increasing demands on GUI controls caused difficulty to manage the mixture of source code from GUI and Business Logic (Spaghetti Code). Further, Client Server Architecture does not support enough the Change Management. Let suppose that the government increases the Entertainment tax rate from 4% to 8 %, then in the Client-Server case, we have to send an update to each clients and they must update synchronously on a specific time otherwise we may store invalid or wrong information. The Client-Server Architecture is also a burden to network traffic and resources. Let us assume that about five hundred clients are working on a data server then we will have five hundred ODBC connections and several ruffian record sets, which must be transported from the server to the clients (because the Business layer is stayed in the client side). The fact that Client-Server does not have any caching facilities like in ASP.NET, caused additional traffic in the network. Normally, a server has a better hardware than client therefore it is able compute algorithms faster than a client, so this fact is also an additional pro argument for the 3.Tier Architecture. This categorization of the application makes the function more reusable easily and it becomes too easy to find the functions which have been written previously. If programmer wants to make further update in the application then he easily can understand the previous written code and can update easily.

* Application layer is the form where we design using the controls like textbox, labels, command buttons etc.
*Business layer is the class where we write the functions which get the data from the application layer and passes through the data access layer.
*Data layer is also the class which gets the data from the business layer and sends it to the database or gets the data from the database and sends it to the business layer.
*Property layer is the sub layer of the business layer in which we make the properties to sent or get the values from the application layer. These properties help to sustain the value in a object so that we can get these values till the object destroy.

What is a 3-tier architecture?

What is a 3-tier architecture?
Three-tier (layer) is a client-server architecture in which the user interface, business process (business rules) and data storage and data access are developed and maintained as independent modules or most often on separate platforms. Basically, there are 3 layers, tier 1 (presentation tier, GUI tier), tier 2 (business objects, business logic tier) and tier 3 (data access tier). These tiers can be developed and tested separately.
What is the need for dividing the code in 3-tiers? Separation of the user interface from business logic and database access has many advantages. Some of the advantages are as follows:
1.Reusability of the business logic component results in quick development. Let's say we have a module that handles adding, updating, deleting and finding customers in the system. As this component is developed and tested, we can use it in any other project that might involve maintaining customers.
2.Transformation of the system is easy. Since the business logic is separate from the data access layer, changing the data access layer won�t affect the business logic module much. Let's say if we are moving from SQL Server data storage to Oracle there shouldn�t be any changes required in the business layer component and in the GUI component.
3.Change management of the system is easy. Let's say if there is a minor change in the business logic, we don�t have to install the entire system in individual user�s PCs. E.g. if GST (TAX) is changed from 10% to 15% we only need to update the business logic component without affecting the users and without any downtime.
4.Having separate functionality servers allows for parallel development of individual tiers by application specialists.
5.Provides more flexible resource allocation. Can reduce the network traffic by having the functionality servers strip data to the precise structure needed before sending it to the clients.
Using the code
This component has 3 tiers. Tier 1 or GUI tier with a form will be called FrmGUI, tier 2 or business logic will be called BOCustomer short for Bussniess Object Customer and finally the tier 3 or the data tier will be called DACustomer short for Data Access Customer. I have compiled all the 3 tiers in the same project for ease of work. I am including all the source code along with the MS Access database that is used to test this project in the included zip file.
User Interface tier
This is a chunk of code from the user interface. I am only including the functions that are used to call the middle tier or the business logic layer.
I am keeping a reference to business logic layer as BOCustomer.
Collapse Copy Code//This function get the details from the user via GUI
//tier and calls the Add method of business logic layer.
private void cmdAdd_Click(object sender, System.EventArgs e)
{
try
{
cus = new BOCustomer();
cus.cusID=txtID.Text.ToString();
cus.LName = txtLName.Text.ToString();
cus.FName = txtFName.Text.ToString();
cus.Tel= txtTel.Text.ToString();
cus.Address = txtAddress.Text.ToString();
cus.Add();
}
catch(Exception err)
{
MessageBox.Show(err.Message.ToString());
}
}
//This function gets the ID from the user and finds the
//customer details and return the details in the form of
//a dataset via busniss object layer. Then it loops through
//the content of the dataset and fills the controls.

private void cmdFind_Click(object sender, System.EventArgs e)
{
try
{
String cusID = txtID.Text.ToString();

BOCustomer thisCus = new BOCustomer();

DataSet ds = thisCus.Find(cusID);

DataRow row;
row = ds.Tables[0].Rows[0];

//via looping
foreach(DataRow rows in ds.Tables[0].Rows )
{
txtFName.Text = rows["CUS_F_NAME"].ToString();
txtLName.Text = rows["CUS_L_NAME"].ToString();
txtAddress.Text = rows["CUS_ADDRESS"].ToString();
txtTel.Text = rows["CUS_TEL"].ToString();
}
}
catch (Exception err)
{
MessageBox.Show(err.Message.ToString());
}

}
//this function used to update the customer details.
private void cmdUpdate_Click(object sender,
System.EventArgs e)
{
try
{
cus = new BOCustomer();
cus.cusID=txtID.Text.ToString();
cus.LName = txtLName.Text.ToString();
cus.FName = txtFName.Text.ToString();
cus.Tel= txtTel.Text.ToString();
cus.Address = txtAddress.Text.ToString();

cus.Update();
}
catch(Exception err)
{
MessageBox.Show(err.Message.ToString());
}
}
Business Logic layer
Here, I am including all the code for this tier. Basically it has properties that are needed to define the customer object. But as I mentioned it is just a dummy customer and many other properties can be added if required. It also has all the methods including Add, update, find that are required to maintain customer details.
This is the middle tier and acts between the GUI and the Data access layer. It keeps a reference to the data access tier as cusData = new DACustomer(). It also has a reference to System.Data namespace as sometimes it returns details in the form of DataSet to the GUI tier.
Collapse Copy Codeusing System;
using System.Data;
namespace _3tierarchitecture
{
///
/// Summary description for BOCustomer.
///


public class BOCustomer
{
//Customer properties
private String fName;
private String lName;
private String cusId;
private String address;
private String tel;

private DACustomer cusData;

public BOCustomer()
{
//An instance of the Data access layer!
cusData = new DACustomer();
}



///
/// Property FirstName (String)
///

public String FName
{

get
{
return this.fName;
}
set
{
try
{
this.fName = value;

if (this.fName == "")
{
throw new Exception(
"Please provide first name ...");
}
}
catch(Exception e)
{
throw new Exception(e.Message.ToString());
}
}
}

///
/// Property LastName (String)
///

public String LName
{
get
{
return this.lName;
}
set
{
//could be more checkings here eg revmove ' chars
//change to proper case
//blah blah
this.lName = value;
if (this.LName == "")
{
throw new Exception("Please provide name ...");
}

}
}

///
/// Property Customer ID (String)
///

public String cusID
{
get
{
return this.cusId;
}
set
{
this.cusId = value;
if (this.cusID == "")
{
throw new Exception("Please provide ID ...");
}

}
}

///
/// Property Address (String)
///

public String Address
{
get
{
return this.address;
}
set
{
this.address = value;

if (this.Address == "")
{
throw new Exception("Please provide address ...");
}
}
}

///
/// Property Telephone (String)
///

public String Tel
{
get
{
return this.tel;
}
set
{
this.tel = value;
if (this.Tel == "")
{
throw new Exception("Please provide Tel ...");
}

}
}

///
/// Function Add new customer. Calls
/// the function in Data layer.
///

public void Add()
{
cusData.Add(this);
}


///
/// Function Update customer details.
/// Calls the function in Data layer.
///

public void Update()
{
cusData.Update(this);
}

///
/// Function Find customer. Calls the
/// function in Data layer.
/// It returns the details of the customer using
/// customer ID via a Dataset to GUI tier.
///

public DataSet Find(String str)
{
if (str == "")
throw new Exception("Please provide ID to search");

DataSet data = null;

data = cusData.Find(str);

return data;
}
}
}
Data Access Layer
The data tier has details to manipulate an MS Access database. However, all these details are transparent and do not affect the business logic layer. This module has a reference to the business logic layer as BOCustomer cus. To make the usage easier and be supported by any other database I have created some constants including the database name and field name that need to be changed as per the customer table details. This is an usable module for any database after making these changes.
Collapse Copy Codeusing System;
using System.Data.OleDb;
using System.Data;
namespace _3tierarchitecture
{
///
/// Summary description for DACustomer.
///

public class DACustomer
{
private OleDbConnection cnn;
//change connection string as per the
//folder you unzip the files
private const string CnnStr =
"Provider=Microsoft.Jet.OLEDB.4.0;Data " +
"Source= D:\\Rahman_Backup\\Programming\\" +
"Csharp\\3tierarchitecture\\customer.mdb;";
//local variables
private String strTable="";
private String strFields="";
private String strValues="";
private String insertStr="";

//this needs to be changed based on customer
//table fields' Name of the database!
private const String thisTable = "tblCustomer";
private const String cus_ID = "CUS_ID";
private const String cus_LName = "CUS_L_NAME";
private const String cus_FName = "CUS_F_NAME";
private const String cus_Tel = "CUS_TEL";
private const String cus_Address = "CUS_ADDRESS";

public DACustomer()
{
}

public DACustomer(BOCustomer cus)
{
// A reference of the business object class
}

//standard dataset function that adds a new customer
public void Add(BOCustomer cus)
{
String str = BuildAddString(cus);

OpenCnn();
//Open command option - cnn parameter is imporant
OleDbCommand cmd = new OleDbCommand(str,cnn);
//execute connection
cmd.ExecuteNonQuery();

// close connection
CloseCnn();

}

//standard dataset function that updates
//details of a customer based on ID
public void Update(BOCustomer cus)
{
OpenCnn();

String selectStr = "UPDATE " + thisTable +
" set " + cus_LName + " = '" + cus.LName + "'" +
", " + cus_FName + " = '" + cus.FName + "'" +
", " + cus_Address + " = '" + cus.Address + "'" +
", " + cus_Tel + " = '" + cus.Tel + "'" +
" where cus_ID = '" + cus.cusID + "'";
OleDbCommand cmd = new OleDbCommand(selectStr,cnn);
cmd.ExecuteNonQuery();

CloseCnn();
}

//standard dataset function that finds and
//return the detail of a customer in a dataset
public DataSet Find(String argStr)
{
DataSet ds=null;
try
{
OpenCnn();

String selectStr = "select * from " + thisTable +
" where cus_ID = '" + argStr + "'";
OleDbDataAdapter da =
new OleDbDataAdapter(selectStr,cnn);
ds = new DataSet();
da.Fill(ds,thisTable);

CloseCnn();

}
catch(Exception e)
{
String Str = e.Message;
}
return ds;
}
private void OpenCnn()
{
// initialise connection
String cnnStr = CnnStr;
cnn = new OleDbConnection(cnnStr);
// open connection
cnn.Open();
}
private void CloseCnn()
{
// 5- step five
cnn.Close();
}

// just a supporting function that builds
// and return the insert string for dataset.
private String BuildAddString(BOCustomer cus)
{
// these are the constants as
// set in the top of this module.
strTable="Insert into " + thisTable;
strFields=" (" + cus_ID +
"," + cus_LName +
"," + cus_FName +
"," + cus_Address +
"," + cus_Tel + ")";

//these are the attributes of the
//customer business object.
strValues= " Values ( '" + cus.cusID +
"' , '" + cus.LName +
"' , '" + cus.FName +
"' , '" + cus.Address +
"' , '" + cus.Tel + "' )";
insertStr = strTable + strFields + strValues;

return insertStr;

}
}
}