Link to home
Start Free TrialLog in
Avatar of quest_capital
quest_capital

asked on

How to retrieve my last ID from an Insert in a 3trier setup.

I want to retrieve my last ID from an Insert in a 3trier setup.
Every thing works fine Insert, Update, etc
However I just want to know how to bring back the last ID from  my Insert statment. I have the correct sql statment and it return the right number but how do I take that number from my data layer to the user layer.
Please modify the code below.
/////////////////////////////////// User.cs
...
protected void bt_Add_Click(object sender, EventArgs e)
    {
        try
        {
            BOCustomer 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();
            //lb_error.Text = DACustomer.returnvalue;
        }
        catch (Exception err)
        {
            lb_error.Text = err.Message.ToString();
        }
 
    }
...
 
 
/////////////////////////////////// BOCustomer.cs
using System;
using System.Data;
 
namespace _3tierarchitecture
{
	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()
		{
			cusData = new DACustomer(this);
		}
 
//		~BOCustomer()
//		{
//			cusData = nothing;
//		}
 
		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());
				}
			}
		}
 
		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 ...");
				}
 
			}
		}
 
		public String cusID
		{
			get
			{
				return this.cusId;
			}
			set
			{
				this.cusId = value;
				if (this.cusID == "")
				{
					throw new Exception("Please provide ID ...");
				}
 
			}
		}
 
		public String Address
		{
			get
			{
				return this.address;
			}
			set
			{
				this.address = value;
 
				if (this.Address == "")
				{
					throw new Exception("Please provide address ...");
				}
			}
		}
 
		public String Tel
		{
			get
			{
				return this.tel;
			}
			set
			{
				this.tel = value;
				if (this.Tel == "")
				{
					throw new Exception("Please provide Tel ...");
				}
 
			}
		}
 
		public void Add()
		{
			cusData.Add(this);
		}
 
		public void Update()
		{
			cusData.Update();
		}
 
		public DataSet Find(String str)
		{
			DataSet data = null;
			data = cusData.Find(str);
			return data;
		}
 
	}
}
 
 
/////////////////////////////////// DACustomer.cs
using System;
using System.Data.OleDb;
using System.Data;
 
namespace _3tierarchitecture
{
 
	public class DACustomer
	{
		private OleDbConnection cnn;
		private BOCustomer cus;
 
		//local variables
		private String strTable="";
		private String strFields="";
		private String strValues="";
		private String insertStr="";
 
        //public static string returnvalue = autoID;
		
		//this needs to be changed based on customer table fields' Name
		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 customer)
		{
			// A reference of the business object class
			cus = customer;
		}
		
		public void Add(BOCustomer cus)
		{
			OpenCnn();
 
            strTable = "Insert into " + thisTable;
 
            if (cus.cusID != null)
                strFields = cus_ID + ",";
            if (cus.LName != null)
                strFields = strFields + cus_LName + ",";
            if (cus.FName != null)
                strFields = strFields + cus_FName + ",";
            if (cus.Address != null)
                strFields = strFields + cus_Address + ",";
            if (cus.Tel != null)
                strFields = strFields + cus_Tel + ",";
 
            int strFieldsL = strFields.Length;
            strFields = strFields.Substring(0, strFieldsL - 1);
 
            if (cus.cusID != null)
                strValues = "'" + cus.cusID + "',";
            if (cus.LName != null)
                strValues = strValues + "'" + cus.LName + "',";
            if (cus.FName != null)
                strValues = strValues + "'" + cus.FName + "',";
            if (cus.Address != null)
                strValues = strValues + "'" + cus.Address + "',";
            if (cus.Tel != null)
                strValues = strValues + "'" + cus.Tel + "',";
 
            int strValuesL = strValues.Length;
            strValues = strValues.Substring(0, strValuesL - 1);
 
            String str = strTable + " (" + strFields + ") values (" + strValues + ")";
            
            OleDbCommand cmd = new OleDbCommand(str,cnn);
 
			cmd.ExecuteNonQuery();
 
            cmd.CommandText = "Select @@Identity";
            int autoID = (int)cmd.ExecuteScalar();
 
            CloseCnn();			
		}
 
 
		public void Update()
		{
			OpenCnn();
			
			String fields = "";
            if (cus.LName != null)
                fields = cus_LName + " = '" + cus.LName + "', ";
            if (cus.FName != null)
                fields = fields + cus_FName + " = '" + cus.FName + "', ";
            if (cus.Address != null)
                fields = fields + cus_Address + " = '" + cus.Address + "', ";
            if (cus.Tel != null)
                fields = fields + cus_Tel + " = '" + cus.Tel + "', ";
 
            int fieldsL = fields.Length;
            fields = fields.Substring(0, fieldsL - 2);
 
            String selectStr = "UPDATE " + thisTable + " set " + fields + " where cus_ID = '" + cus.cusID + "'";
 
			OleDbCommand cmd = new OleDbCommand(selectStr,cnn);
 
			cmd.ExecuteNonQuery();
			
			CloseCnn();
		}
 
		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()
		{
			String cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Documents and Settings\\DuncanH\\My Documents\\quest\\3trier\\customer.mdb;";
			cnn = new OleDbConnection(cnnStr);
			cnn.Open();
		}
 
		private void CloseCnn()
		{
			cnn.Close();
		}
 
	}
 
}

Open in new window

Avatar of ororiole
ororiole
Flag of United States of America image

Normally if you are using an identity statement you do not want the user to be able to enter an ID. Trying to insert a ID in a column defined with identity will result in an error. Let the identity provide you with the ID.
So I would:
      try
        {
            BOCustomer cus = new BOCustomer();
            cus.cusID = txtID.Text.ToString(); <-----remove this line.
            cus.LName = txtLName.Text.ToString();
---------------------------------------------------
in DACustomer: public void Add(BOCustomer cus)
      ...      
int autoID = (int)cmd.ExecuteScalar();
cus.cusID = autoID.ToString();  <--this returns it to your user layer
------------------------------------------------

There are other things I would recommend changing, but its a bit fragile so I dont want to break what you have working. I would recommend you change your sql command to a stored proc and return the @@Identity as a return value from the stored procedure. By making two commands you run the risk of losing the @@identity value if anything is executed by someone in between your commands.


 
ASKER CERTIFIED SOLUTION
Avatar of Anurag Agarwal
Anurag Agarwal
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of quest_capital
quest_capital

ASKER

anuragal:

Do you mean for
return autoid;
to be
return nextId;
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
anuragal:

Sweet that worked well and you get the points, but since we are on topic can you show me the syntax on how to get back mulitple fields of data like if I wanted to get back lets say ID and First Name from a stored procedure.

thx
again
Yeah Sure...

Here is the code for retreiving selective fields from database. through Stored Procedure

This SP will return you only the ID & NAME then in C# code you can read them through datareader.

Anurag

(Request->Can you specify ZONE while giving away the points if yes then please select the "Programming for ASP.NET" zone. Thanks)
SqlCommand myCMD = new SqlCommand("MyStoredProc", nwindConn);
myCMD.CommandType = CommandType.StoredProcedure;
nwindConn.Open();
SqlDataReader myReader = myCMD.ExecuteReader();
BOCustomer customer = new BOCustomer();
while (myReader.Read())
{
    customer.cusID = myReader["CUS_ID"].ToString();
    customer.FName = myReader["CUS_F_NAME"].ToString();
}
 
myReader.Close();
nwindConn.Close();
 
 
**********************SP************************
Create a Stored Procedure with a query like
**********************************************
Select CUS_ID, CUS_F_NAME from tblCustomer

Open in new window

anuragal:

I guess I was not clear I understand how to get data back from a stored procedure, the problem is geting that data back from a 3tier enviornment.
Lets say I had the code below how do I setup the syntax for the  Data(the return values) , Busniss and Presentation layer.
/////////////////////////////////////// DACustomer
public int Add(BOCustomer cus)
		{
.......
cmd.CommandType = CommandType.StoredProcedure;
            OleDbParameter oParam1 = cmd.Parameters.AddWithValue("@autoid", 0);
            OleDbParameter oParam2 = cmd.Parameters.AddWithValue("@lname", 0);
            oParam1.Direction = ParameterDirection.Output;
            oParam2.Direction = ParameterDirection.Output;
            OpenCnn();
 
			cmd.ExecuteNonQuery();
........
}

Open in new window

for getting back the data from Data access layer to business & presentation you have to create a function that will return a customer type of object

like

public BOCustomer FetchCustomer()
{
   //Here you will fetch the data from database & then iterate the datareader & fill the customer object then return that object back
}

same type of function in Business layer and from this function call Data access layer function. And same type of code in presentation layer.

Anurag
anuragal:

I figured it out  see below:
Thx for all you help
////////////////////// DACustomer
...
cmd.CommandText = "Select @@Identity";
            int autoID = (int)cmd.ExecuteScalar();
            string ver = "mydesktop";
 
            object[] mydata = { autoID, ver };
 
            CloseCnn();
 
            return mydata;
...
//////////////// BOCustomer
...
private object[] outParam;
...
public object[] OutParam
        {
            get
            {
                return this.outParam;
            }
            set
            {
                this.outParam = value;
            }
        }
...
public void Add()
		{
            outParam = cusData.Add(this);
		}
////////////////////////// Output
lb_error.Text = Convert.ToString(cus.OutParam[0]) + " : " + Convert.ToString(cus.OutParam[1]);

Open in new window