Newbie DataTable question

[Ben]

Stupid question for the day,



Just playing around with rewriting my website in .Net, I want to add a
record to my database table, in the old ADO, it was simple you could
create a connection and recordset and then rs.AddNew etc... in .Net it
seems they recommend that you populate a DataTable then use the
NewRow method



Isnt this bringing back a entire copy of the table (in this case 40,000
users), if so isn't this very inefficient? I know you can run a
insert via a ExecuteNonQuery which is the way I have done it in the
past (usually passing parameters to stored procs)



But I would have thought you should be able to add a row (Datarow) in
this case to a table without returning the entire table when you
connect to it.



Probably being very stupid

heres my code

try
{
// Initializations
string database = "MyDB.mdb";
string connectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z:\Web\Database\" +
database;

//connection
OleDbConnection cnn = new OleDbConnection();
cnn.ConnectionString = connectionString;
cnn.Open();

OleDbCommand com = cnn.CreateCommand();
com.CommandText = "Select * from tblUser";

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = com;

//DataSet ds = new DataSet();
//da.Fill(ds, "Users");

DataTable dt = new DataTable();
da.Fill(dt);

//DataRow dr = dt.Rows[0];
DataRow dr = dt.NewRow();

dr["FirstName"] = FirstName.ToString();
dr["Surname"] = Surname.ToString();
dr["Email"] = Email.ToString();

dt.Rows.Add(dr);
da.Update(dt);

//close up

cnn.Close();
}
catch (OleDbException ee)
{
Console.WriteLine(ee.ToString());
}

 

[bruce barker \(sqlwork.com\)]

the notion of in-memory datasets, is you only keep a copy of what you need.
the premise is that normally you only need a subset of the data. a dataset
keeps track of the status of a row (new, modified,deleted,unchanged). then
the adapter can look at the row status to determine whether to insert,
delete, update, or do nothing.

in your case you are loading the dataset solely to get the structure. you
could use the folowwing statement.

com.CommandText = "Select * from tblUser where 1 = 0";

which would only return column info and no data. a better approach is to use
typed datasets. here you use a wizard to build a dataset class that initials
the table and column info. this removes the requirement to do a dummy
database select. also you get a dot notion for accessing column data.

-- bruce (sqlwork.com)

 

[Ben]

thanks Bruce,

yeah as you where writing i had tried that, bit quicker, but then it
wont allow me to insert data

"Update requires a valid InsertCommand when passed DataRow collection
with new rows."

so i guess i need to change

da.SelectCommand = com;

to

da.InsertCommand = com;

that doesnt work . any more words of wisdom? the main reason i am
doing this is to avoid having to handle what data is being put into
each field when the users submit the data. creating a Insert string
seem to me the wrong way to do this.