SQL Database update from C# console application

[Guest]

I'm stuck. I have created a console application that does its job of
collecting the data I want it to however I am stuck on writing this data back
to a database. All of the examples I have been able to find on using the
SQLConnectin, SQLDataAdapter and DataSet objects all point me to a forms
application where you can visually modify the data the send the
update,insert,delete commands. I have not been able to locate how to update
or insert data on a DataSet that is not bound to a visual object of some kind.

Thanks for any assistance...



FYI... I am attempting this with VS2005 Beta 2 and SQL 2005 AprilCTP.

Thanks!

 

[W.G. Ryan MVP]

You reference the values directly...

ie
DataSetName.Tables[TableIndexOrName].Rows[RowIndex].Columns[ColumnIndexOrName]
= WhateverValue;

 

[Guest]

okay, let me make sure I am on the right track...

I can call the fill method to load the dataset from the database, compare my
discovered data with the data in the dataset by directly refrencing the
fields as you said, then call the update method to send the updated data back
to the database.

If I need to insert a new row of data do I just call an Add method to the
dataset and reference the table name?

You reference the values directly...

ie
DataSetName.Tables[TableIndexOrName].Rows[RowIndex].Columns[ColumnIndexOrName]
= WhateverValue;

I'm stuck. I have created a console application that does its job of
collecting the data I want it to however I am stuck on writing this data
back
to a database. All of the examples I have been able to find on using the
SQLConnectin, SQLDataAdapter and DataSet objects all point me to a forms
application where you can visually modify the data the send the
update,insert,delete commands. I have not been able to locate how to
update
or insert data on a DataSet that is not bound to a visual object of some
kind.

Thanks for any assistance...



FYI... I am attempting this with VS2005 Beta 2 and SQL 2005 AprilCTP.

Thanks!

 

[Jimbo]

I'm stuck. I have created a console application that does its job of
collecting the data I want it to however I am stuck on writing this data back
to a database. All of the examples I have been able to find on using the
SQLConnectin, SQLDataAdapter and DataSet objects all point me to a forms
application where you can visually modify the data the send the
update,insert,delete commands. I have not been able to locate how to update
or insert data on a DataSet that is not bound to a visual object of some kind.

Thanks for any assistance...

I think this is what your after. Below is a code snippet that updates
two databases, one via ODBC and another via OLE. Hope this helps.


oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
odbcConnection1 = new System.Data.Odbc.OdbcConnection();

oleDbConnection1.ConnectionString = @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet
OLEDBatabase Password=;Data Source=""C:\Documents and Settings\All
Users\Documents\DATA.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet
OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny
None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet
OLEDB:Encrypt Database=False";

odbcConnection1.ConnectionString =
"STMT=;OPTION=524292;DSN=Looe;UID=XXXX;PASSWORD=XXXX;SOCKET=;DESC=;DATABASE=YYYY;SERV"
+ "ER=192.168.0.7;PORT=3306";

oleDbConnection1.Open();
odbcConnection1.Open();

OleDbCommand odc = oleDbConnection1.CreateCommand();
odc.CommandText = "UPDATE.........";
odc.ExecuteNonQuery();

OdbcCommand oc = odbcConnection1.CreateCommand();
oc.CommandText = "UPDATE............";
oc.ExecuteNonQuery();

oc.Close();
odc.Close();




Cheers
Jimbo

 

[W.G. Ryan MVP]

okay, let me make sure I am on the right track...

I can call the fill method to load the dataset from the database, compare
my
discovered data with the data in the dataset by directly refrencing the
fields as you said, then call the update method to send the updated data
back
to the database. Yes

If I need to insert a new row of data do I just call an Add method to the
dataset and reference the table name?
Yes

You reference the values directly...

ie
DataSetName.Tables[TableIndexOrName].Rows[RowIndex].Columns[ColumnIndexOrName]
= WhateverValue;

I'm stuck. I have created a console application that does its job of
collecting the data I want it to however I am stuck on writing this
data
back
to a database. All of the examples I have been able to find on using
the
SQLConnectin, SQLDataAdapter and DataSet objects all point me to a
forms
application where you can visually modify the data the send the
update,insert,delete commands. I have not been able to locate how to
update
or insert data on a DataSet that is not bound to a visual object of
some
kind.

Thanks for any assistance...



FYI... I am attempting this with VS2005 Beta 2 and SQL 2005 AprilCTP.

Thanks!