select, insert, update, del with ADO.net with C#

[Azel Low]

Hi, is there anyone who can point or provide me with some samples of how to Select, Insert Update and delete using OLEDB in C# Preferably a simple basic example that doesn't use stored procedure. thanks.

[Uwe Keim]

I use something similar to this for INSERT an new record:

OleDbConnection conn = new OleDbConnection( DB_STR );
OleDbDataAdapter da = new OleDbDataAdapter( "SELECT TOP 1 * FROM Guestbook", conn );
OleDbCommandBuilder cb = new OleDbCommandBuilder( da );
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";

DataSet ds = new DataSet();
da.Fill( ds );

DataTable table = ds.Tables[0];
DataRow row = table.NewRow();

row["Date"] = DateTime.Now;
row["Name"] = name;
row["EMail"] = email;
row["Text"] = text;
table.Rows.Add( row );

da.Update( ds );

conn.Close();

And for INSERT or UPDATE:

OleDbConnection conn = new OleDbConnection( DB_STR );
// do your query for a specific recordset.
// if it returns none, you must create a new, otherwise modify the existing.
OleDbDataAdapter da = new OleDbDataAdapter( "SELECT TOP 1 * FROM Guestbook WHERE ID="+ID, conn );
OleDbCommandBuilder cb = new OleDbCommandBuilder( da );
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";

DataSet ds = new DataSet();
da.Fill( ds );

DataTable table = ds.Tables[0];

// create new.
if ( table.Rows.Count==0 )
{
DataRow row = table.NewRow();

row\["Date"\] = DateTime.Now;
row\["Name"\] = name;
row\["EMail"\] = email;
row\["Text"\] = text;

table.Rows.Add( row );

}
// modify existing.
else
{
DataRow row = table.Rows[0];

row\["Date"\] = DateTime.Now;
row\["Name"\] = name;
row\["EMail"\] = email;
row\["Text"\] = text;

}

da.Update( ds );

conn.Close();

For the SELECT and DELETE I just use single-statement SQL-queries, which I format with string.Format(). -- - Free Windows-based CMS: www.zeta-software.de/enu/producer/freeware/download.html - See me: www.magerquark.de

[Azel Low]

thanks. I got a question. what is the following code for? cb.QuotePrefix = "["; cb.QuoteSuffix = "]"; thanks =)