Getting auto-number with DataSet

  • Thread starter Thread starter Shahar
  • Start date Start date
S

Shahar

Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.
 
This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is what I
ended up doing.
 
Are you suggesting using a GUID as a primary key for a row, instead of using
an identity column in SQL??


Jeff Ogata said:
This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is what I
ended up doing.

Shahar said:
Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.
Click to expand...
Click to expand...
 
Apologies for not being clearer -- I meant for Access. I believe the way to
do this with SQL Server is to write a stored procedure which does the update
and returns the value of @@IDENTY as an output param. The suggestion for
using the guid was to make up for the fact that you cannot do something
similar with Access. If you really need to use auto-number w/ Access, I
think the article I linked to shows you how, but in my particular case, it
was less work to use the guid.

Frank Drebin said:
Are you suggesting using a GUID as a primary key for a row, instead of using
an identity column in SQL??


Jeff Ogata said:
This article may help: http://tinyurl.com/7gkr

I also saw a recommendation that instead of using auto-number to generate
unique ids, use a guid instead. This method is much simpler, and is
Click to expand...
what
I
ended up doing.

Shahar said:
Hi

I need to get a field name 'ID'(that is an auto-number field) right
after I add a new row to table, it's work like that:

myCommand.ExecuteNonQuery();
myCommand.CommandText = "SELECT @@Identity"; // the auto-number fiels
int iId = (int)myCommand.ExecuteScalar();


Can I do it with DataSet aswell ?


DataSet ds = new DataSet();

OleDbConnection con = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=..\..\..\..\..\Sql\Sql.mdb");
OleDbCommand cmd = new OleDbCommand("Select * from Employees");
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


cmd.Connection = con;

con.Open();

adapter.Fill(ds, "Employees");

DataRow row = ds.Tables["Employees"].NewRow();
row["Name"] = "name";
ds.Tables["Employees"].Rows.Add(row);

if (ds.HasChanges())
{
DataSet dsUpdate = ds.GetChanges();
adapter.Update(dsUpdate, "Employees");

// Need to get the ID auto-number ???
}

Shahar.
Click to expand...
Click to expand...
Click to expand...
 
Shahar,
You need to add a RowUpdatedEventHandler to your DataSet. It should
look something like this.

private void HandleRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
{
OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY",
e.Command.Connection, e.Command.Transaction);
e.Row["keyColumnNameHere"] = Convert.ToInt32(cmd.ExecuteScalar());
e.Row.AcceptChanges();
cmd.Dispose();
}
}

Ron Allen
 
Back
Top