In my C# Code Generator I build functions for Add,Select, Update and Delete.
When working with related tables where the Id from one table is stored in a
second table, it is helpful to automatically get the identity of the newly added
record.
- m_dbConnection is the Connection string to your database
- aLastIn is the name of our table
- Id is unique column used in our table
- m_Id is the private class variable for Id
Using the C# Class Generator, the generated code is as follows:
// Add a new Record ------------------------------------------------
public void Add() // Add Table Entry
{
string insertCmd = "INSERT INTO aLastIn (UserId,LastIn,IpAddress) ";
insertCmd += " VALUES (@m_UserId,@m_LastIn,@m_IpAddress); ";
insertCmd += " SELECT @thisId=SCOPE_IDENTITY() FROM aLastIn ";
// for pre-SQL 2000 use the @@Identity global varaible - note in
// a high transaction enviornment, the result returned by @@IDENTITY
// may not always be accurate since it is global and will return
// the id of the last record added - not necessarily your record.
// insertCmd += " SELECT @Id=@@identity FROM aEvents ";
SqlConnection m_SqlConnection = new SqlConnection(m_dbConnection);
SqlCommand m_SqlCommand = new SqlCommand(insertCmd, m_SqlConnection);
m_SqlCommand.Parameters.Add(new SqlParameter("@m_UserId", SqlDbType.Int));
m_SqlCommand.Parameters["@m_UserId"].Value = m_UserId;
m_SqlCommand.Parameters.Add(new SqlParameter("@m_LastIn", SqlDbType.DateTime));
m_SqlCommand.Parameters["@m_LastIn"].Value = m_LastIn;
m_SqlCommand.Parameters.Add(new SqlParameter("@m_IpAddress", SqlDbType.Char,20));
m_SqlCommand.Parameters["@m_IpAddress"].Value = m_IpAddress;
SqlParameter returnParam
= m_SqlCommand.Parameters.Add(new SqlParameter("@tId",SqlDbType.Int));
returnParam.Direction
= ParameterDirection.Output;
try
{
m_SqlConnection.Open();
m_SqlCommand.ExecuteScalar();
m_Id = (int)m_SqlCommand.Parameters["@tId"].Value;
}
catch (Exception e)
{
throw new Exception("Error in CaLastIn:Add()-> " + e.ToString());
}
finally
{
m_SqlCommand.Dispose();
m_SqlConnection.Close();
m_SqlConnection.Dispose();
}
} // end Add
Top 