C# ADO.NET console application

[krukinews]

Hi,
I'm trying to write small program (console application) that reads from
Event Log end writes data to SQL Server. I'm new to C# and ADO.NET and
for last 3 days I have read many tutorials and I think that I
understand basic (only basic) concepts of ADO.NET.

But I simple can't create console application that uses Master Detail
concept.

Table structure (keys only).
tblServer
ID_Server (auto inc)

tblEventLogs
ID_EventLog (auto inc)
ID_Server

tblEventLogEntries
ID_EventLogEntry (auto inc)
ID_EventLog

//my code

public static SqlDataAdapter CreateDataAdapter(string selectSQL,
SqlConnection conn) {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(selectSQL, conn);

SqlCommandBuilder cb = new SqlCommandBuilder(da);

da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();

return da;
}

SqlConnection connMain = new
SqlConnection("server=server;uid=uid;pwd=pws;database=database;");
connMain.Open();

SqlDataAdapter daServers = CreateDataAdapter("SELECT * FROM
tblServers", connMain);
SqlDataAdapter daEventLogs = CreateDataAdapter("SELECT * FROM
tblEventLogs", connMain);
SqlDataAdapter daEventLogEntries = CreateDataAdapter("SELECT * FROM
tblEventLogEntries", connMain);

DataSet dsMain = new DataSet();

daServers.FillSchema(dsMain, SchemaType.Source, "tblServers");
daEventLogs.FillSchema(dsMain, SchemaType.Source, "tblEventLogs");
daEventLogEntries.FillSchema(dsMain, SchemaType.Source,
"tblEventLogEntries");

daServers.Fill(dsMain, "tblServers");
daEventLogs.Fill(dsMain, "tblEventLogs");
daEventLogEntries.Fill(dsMain, "tblEventLogEntries");

dsMain.Tables["tblServers"].DefaultView.Sort = "ServerName";
dsMain.Tables["tblEventLogs"].DefaultView.Sort = "ID_Server,LogName";

//my implementation of Current record (any better idea?)
DataRow rowServer;
DataRow rowEventLog;

int tmp =
dsMain.Tables["tblServers"].DefaultView.Find(strComputerName);

if (tmp < 0) {
rowServer = dsMain.Tables["tblServers"].NewRow();
rowServer["ServerName"] = strComputerName;
dsMain.Tables["tblServers"].Rows.Add(rowServer);
daServers.Update(dsMain, "tblServers");
} else {
rowServer = dsMain.Tables["tblServers"].Rows[tmp];
}

Problem is that I need ID_Server of new record to create records in
tbl_EventLogs but rowServer["ID_Server"] of new record is always 0. I
think that this has to do something with diconnected record set (or
not).

Any sugestions are welcome.


Mihalic Krunoslav

 

[Nicholas Paldino [.NET/C# MVP]]

Mihalic,

Once you have the structure of the data in your data set, create
DataRelation instances that link the parent and the child tables correctly.
If you do this correctly, then when you run the tables through the data
adapter for update, it should detect the relations, and get the appropriate
id of the parent.

Hope this helps.

Hi,
I'm trying to write small program (console application) that reads from
Event Log end writes data to SQL Server. I'm new to C# and ADO.NET and
for last 3 days I have read many tutorials and I think that I
understand basic (only basic) concepts of ADO.NET.

But I simple can't create console application that uses Master Detail
concept.

Table structure (keys only).
tblServer
ID_Server (auto inc)

tblEventLogs
ID_EventLog (auto inc)
ID_Server

tblEventLogEntries
ID_EventLogEntry (auto inc)
ID_EventLog

//my code

public static SqlDataAdapter CreateDataAdapter(string selectSQL,
SqlConnection conn) {
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(selectSQL, conn);

SqlCommandBuilder cb = new SqlCommandBuilder(da);

da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();

return da;
}

SqlConnection connMain = new
SqlConnection("server=server;uid=uid;pwd=pws;database=database;");
connMain.Open();

SqlDataAdapter daServers = CreateDataAdapter("SELECT * FROM
tblServers", connMain);
SqlDataAdapter daEventLogs = CreateDataAdapter("SELECT * FROM
tblEventLogs", connMain);
SqlDataAdapter daEventLogEntries = CreateDataAdapter("SELECT * FROM
tblEventLogEntries", connMain);

DataSet dsMain = new DataSet();

daServers.FillSchema(dsMain, SchemaType.Source, "tblServers");
daEventLogs.FillSchema(dsMain, SchemaType.Source, "tblEventLogs");
daEventLogEntries.FillSchema(dsMain, SchemaType.Source,
"tblEventLogEntries");

daServers.Fill(dsMain, "tblServers");
daEventLogs.Fill(dsMain, "tblEventLogs");
daEventLogEntries.Fill(dsMain, "tblEventLogEntries");

dsMain.Tables["tblServers"].DefaultView.Sort = "ServerName";
dsMain.Tables["tblEventLogs"].DefaultView.Sort = "ID_Server,LogName";

//my implementation of Current record (any better idea?)
DataRow rowServer;
DataRow rowEventLog;

int tmp =
dsMain.Tables["tblServers"].DefaultView.Find(strComputerName);

if (tmp < 0) {
rowServer = dsMain.Tables["tblServers"].NewRow();
rowServer["ServerName"] = strComputerName;
dsMain.Tables["tblServers"].Rows.Add(rowServer);
daServers.Update(dsMain, "tblServers");
} else {
rowServer = dsMain.Tables["tblServers"].Rows[tmp];
}

Problem is that I need ID_Server of new record to create records in
tbl_EventLogs but rowServer["ID_Server"] of new record is always 0. I
think that this has to do something with diconnected record set (or
not).

Any sugestions are welcome.


Mihalic Krunoslav

 

[Victor Pereira]

Please.. use try and catch, to catch all exceptions, and protect your code.

Reguards,

Victor