Best way to add, update, and delete a database [* Resolved *]

[steve_rm]

Hello,

I have a database application and have been experiencing problems adding, updating, and deleting using the data table.
Is there a more efficient and robust way to do this. My code l have used is listed below. Someone told me it is better to use the dataset rather than the datatable. If this is so would it be possible to convert the below code to using the dataset. I am not sure on how to use the syntax.

cnnTeacher.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibrarySystem.mdbersist Security Info=False"; 
OleDbDataAdapter daTeacher = new OleDbDataAdapter("SELECT * FROM Teacher",cnnTeacher);
OleDbCommandBuilder cbTeacher = new OleDbCommandBuilder(daTeacher);
daTeacher.Fill(dtTeacher);

DataRow drNewTeacher = dtTeacher.NewRow();

drNewTeacher["TeacherID"] = txtAddIDNumber.Text;
drNewTeacher["FirstName"] = txtAddFirstName.Text;
drNewTeacher["LastName"] = txtAddLastName.Text;
drNewTeacher["E-mail"] = txtAddEmail.Text;

dtTeacher.Rows.Add(drNewTeacher);
daTeacher.Update(dtTeacher); //Error - Syntax error in INSERT INTO

Many thanks in advance,

Steve

[DJ_Catboy]

Hi,

I personally use all SQL statements if I can and just execute them at the database using a connection.execute method. I always feel that if it is possible without using special objects it must be quicker (SQL statements being just pure text).

Hope this is useful?

DJ

[sagey]

don't use inline sql. use storedprocs (or queries as it looks like you are connecting to access). inline sql is bad cos:

means you have to re-compile/deploy your app if you decide to change some sql

it doesn't benefit from the optimisation that takes place in storedprocs (again not sure whether this happens in access)

security.

i've never ever seen a good reason for using inline sql other than a developers own ****ness. "oh well i needed to dynamicaly build parameters onto the end of the where clause so i thought i'd inline the sql." bollocks!

[DJ_Catboy]

very nicely put.....! but the guy has a point...... stored procs are much better.... however, I believe you wanted alternatives to using .NET objects? Well there are two more alternatives in here somewhere.... just have to untangle the code examples from the abuse!

DJ

[steve_rm]

Hello,

I did the insert using SQL. I am using MS Access so i don't think l can use stored procedures. So is this the best way using access. See the code below.

Can anyone tell me if this is the best way. I am interested in learning more about this.

cnnBook.ConnectionString = @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IBS Library System\LibraryMIS.mdb;Persist Security Info=False"; 
     
                cnnBook.Open(); //Open connection 
                OleDbCommand cmdBook = cnnBook.CreateCommand(); //Create a new command 

                cmdBook.CommandText = @"INSERT INTO Book(RefNumber,Title,Author,Category,Publisher,YearPublished) VALUES('126','mybook','steve','best books','pub','22/08/2004')";// SQL for inserting 

                cmdBook.CommandType = CommandType.Text; //type of command, text or store procedure 

                cmdBook.ExecuteNonQuery(); //Execute the command 
                 
                cnnBook.Dispose(); //Release all the resources than then close

Many thanks in advance,

Steve

[hellswraith]

In your initial post, you only told the dataadapter how to select the data, not how to insert it back.

What you need to do is create a command object that is for inserting. Then set the dataadapters insertcommand property equal to that command.