|
Hello,
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
I appreciate your helps.
|
|
|
|
|
Vu Truong wrote:
My application is required to monitor a database in MS SQL. Can I use C#/ADO.NET to develop the application that is to detect a new record entered or updated in MS SQL?
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure (or, if all you're doing is say, logging changes to a table back into another table for audit purposes, just use the trigger itself to update the log table)
An extended stored procedure is an exported function from a DLL (C-style), so you'll probably need to expose your C# code in a manner that SQL Server can understand, or write a C++ wrapper that can interop with your code.
Ian Darling
"If we've learned anything from history, it's that those who feed trolls are condemned to repetitive conversations. Or something like that." - Eric Lippert
|
|
|
|
|
Ian Darling wrote:
Off the top of my head (I've never tried this, so YMMV), you could add a trigger to the database which on the relevant changes which calls into an extended stored procedure
I recently did just that.
I wrote several XP's to send messages to a specified server and port.
Of course there is an issue of protocol for the message format.
In general to write an XP you need the following :
1. Make sure you use the right srv header :
i.e. place
C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\DEVTOOLS\INCLUDE
before
C:\Program Files\Microsoft Visual Studio\VC98\INCLUDE
in the list of include directories.
#include <srv.h>
2. Make sure you link with the OpenDS library :
#pragma comment(lib, "opends60") // MSSQL srv_*() functions
3. Make sure all your XP's are compiled as C not C++.
i.e. wrap with
#ifdef __cplusplus
extern "C" {
#endif
...
#ifdef __cplusplus
}
#endif if functions are in a cpp file.
4. Provide a __GetXpVersion() function in your library :
__declspec(dllexport) ULONG __GetXpVersion( void )
{
return(ODS_VERSION);
}
I ended up writing two sets of functions :
CMKMSSQL_API SRVRETCODE xp_cmkInitSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendSvcMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastSvcMsg ( SRV_PROC *SP );
and
CMKMSSQL_API SRVRETCODE xp_cmkInitGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkTermGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkSendGisMsg ( SRV_PROC *SP );
CMKMSSQL_API SRVRETCODE xp_cmkBroadcastGisMsg ( SRV_PROC *SP );
The *SvcMsg ones just send raw data to the specified server:port.
The *GisMsg ones send protocol formated messages to one of my GIS servers.
xp_cmkInit*Msg is called to create a connection to a specified server:port
Usage : EXEC xp_cmkInitSvcMsg <@server> <@port> [@handle OUTPUT]
The output handle is a long and is used to by the Term/Send functions.
xp_cmkTerm*Msg is called to close a connection.
Usage : EXEC xp_cmkTermSvcMsg <@handle>
xp_cmkSend*Msg sends a blob of data using the connection associated with the specified handle.
Usage : EXEC xp_cmkSendSvcMsg <@handle> <@msg>
Because the message is sent using
sock->Send( (byte*)srv_paramdata(SP, 2), srv_paramlen(SP, 2) );
The message can be any data type.
xp_cmkBroadcast*Msg sends a message to all connections.
Usage : EXEC xp_cmkBroadcastSvcMsg <@msg>
This is the one i call in the triggers.
...cmk
Save the whales - collect the whole set
|
|
|
|
|
what is wrong with this code.
string commandString = "SELECT * FROM Company WHERE (Country = 'Brazil')";
System.Data.OleDb.OleDbDataAdapter DataAdpter9 = new System.Data.OleDb.OleDbDataAdapter(commandString, oleDbConnection1);
DataAdpter9.Fill(dataSet4);
dataGrid1.DataSource = dataSet4.Tables["Company"].DefaultView;
It gives me this error using try/catch:
"Object reference not set to an instance of an object"
DataSet and mispelled DataAdapter are all declared in the form_load.
|
|
|
|
|
You probably forgot to declare the dataset4 as new dataset before trying to fill it.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi,
There could be several possible reasons for this kind of error.. It would have been easier to tell if oyu would have debugged the application and told exactly which line is throwing this exception. Anyway... the possibilities are...
a) You haven't instantiated a connection object.
b) You haven't instantiate a dataset object
c) Form Load event is not attached...(check in InitializeComponent() for the event)
Hope this info solves your prob.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
Dear all,
I get a stupid error while executing this update procedure and can't find the mistake.
The SQL statement works fine in the Enterprise Manager:
"UPDATE tb_cont_SmartSell_orders SET OR_state = N'printing' WHERE (OR_state = N'E-Mail')"
But when I'm using the procedure I get the error message:
..ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed....
If I remove "rs1.close" from the procedure it works but I don't understand why.
I call the procedure with "UpdateOrderState "E-Mail", "printing"
======
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)
Dim strUpdate, rs1, rsconnect1
Set rsConnect1 = Server.CreateObject("ADODB.Connection")
Set rs1 = Server.CreateObject("ADODB.Recordset")
'rs1.CursorLocation = adUseServer
'rs1.CursorType = adOpenKeyset
'rs1.LockType = adLockOptimistic
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"
rs1.open strUpdate, gDBCONSTRING
rs1.close
set rs1 = nothing
set rsconnect1 = nothing
end Sub
======
Many Thanks in advance!
Pete
|
|
|
|
|
The 'problem' is you do not open a recordset, but you are executing a command. Therefore you cannot close the recordset...
What you can (should) do is this:
<br />
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)<br />
<div style="margin-left:3em"><br />
Dim strUpdate, cn<br />
Set cn = Server.CreateObject("ADODB.Connection")<br />
cn.Open gDBCONSTRING<br />
<br />
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"<br />
<br />
cn.Execute strUpdate<br />
cn.Close <br />
set Cn = nothing<br />
</div><br />
end Sub<br />
Wout Louwers
|
|
|
|
|
Many thanks Wout!
It's working fine now.
br Pete
|
|
|
|
|
Hi, 
Can i use activexscript in transform data task to determine whether the transformation of current table field failed or succeed? This is because i need to insert the failed field to another table. I don't want to validate the dtssource to do this because the transformation still can be failed.
This is the program logic :
Function Main
DTSDestination("Destination") = DTSSource("Source")
Main = DTSTransformStat_OK
(Validate current transformation here)
IF (TRANSFORMATION FAILED) THEN
(INSERT FAILED FIELD VALUE TO A TABLE)
END IF
END FUNCTION
Can i do this in the script? Or i need to use other task like data driven task, activex task, etc.
Thanks for the help.
|
|
|
|
|
i was some how able to get a resultset wanted by my boss. But can this query be optimized further.
<br />
DECLARE @name nvarchar(50)<br />
<br />
DECLARE cur_name CURSOR FOR<br />
SELECT url <br />
FROM urltable<br />
<br />
OPEN cur_url<br />
FETCH NEXT FROM cur_name INTO @name<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
select name from mytable where name like '%' + @name+ '%'<br />
<br />
FETCH NEXT FROM cur_name INTO @name<br />
END<br />
CLOSE cur_name <br />
DEALLOCATE cur_name
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
You could try
<br />
Select name<br />
From MyTable<br />
Inner Join urlTable on MyTable.name like ''% + urlTable.url + '%'<br />
but I'm not sure if it will work...
Wout Louwers
|
|
|
|
|
thx, but query is verry resource and time consuming. any suggestion to optimize the resource usage also..
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
The resource and time consuming part is like '%' + urlTable.url + '%'.
As far as I known you cannot solve this because of the first '%'.
But you could try:
<br />
Select name<br />
From MyTable, urlTable<br />
Where MyTable.name like ''% + urlTable.url + '%'<br />
Or
<br />
Select name<br />
From MyTable, urlTable<br />
Where CharIndex(MyTable.name, urlTable.url) > 0<br />
or create a full-text catalog on urlTable and use Contains... You can read about full-text catalogs in the SQL Server Books Online
Wout Louwers
|
|
|
|
|
Hi
I'm new to C# and have a little problem inserting Data into a table!
I want to crate a little CD Database:
The table has the following rows: CDID, Artist and Album!
The Code I wrote looks like this:
private void button1_Click(object sender, System.EventArgs e)
{
System.Data.SqlClient.SqlConnection myConnection =
new System.Data.SqlClient.SqlConnection("Data Source=AUTUMN/NetSDK; Initial Catalog=mediadb; User Id=sa;Password=mypwd;");
string myInsertQuery = "INSERT INTO CD (CDID, Artist, Album) Values(" + txtCDID.Text.ToString() + ",'" + txtArtist.Text + "','" + txtAlbum.Text +"')";
System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
This code doesen't work and I'm wondering why? Everytime i hit the button, i gen an Error message. Something like SQl Exception.... and Systemerror....
Can someone help me out?
Thank
Autumn
|
|
|
|
|
Can you please specify what error message you got when you run the code?
Thanks.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi
I got a Systemerror.... SQL Exception when I tried t insert the Data! But I found out, that I made some basic mistakes while coding...
However... it works now!
Thanks
Autumn
|
|
|
|
|
I want to retrieve the Autoinc-ID of the last inserted record.
With sqlserver the command for that is: "select scope_identity()".
But that only seems to work if I use an insert string that includes the values, like:
"insert into Testtable (Name) values ('Charlie')";
In that case "select scope_identity()" returns the ID of the last inserted record. But I prefer using parameters (because of double quotes, date-formatting,...):
"insert into Testtable (Name) values (@Name)";
cmd.Parameters.Add("@Name",SqlDbType.Char, 25).Value = "Charlie";
If I use parameters like in the sample above, I get a null returned on "select scope_identity()".
Any ideas?
Thanks, Bruno.
|
|
|
|
|
SCOPE_IDENTITY only returns values inserted within the current scope (i.e. when you're doing insert and you're asking for the identity). Use @@IDENTITY instead to return teh last identity value for the table.
Hope it helps,
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thats right, @@IDENTITY returns the last identity value for the table. But that might be a different value, depending if a different user on another machine or even a different process on the same machine have entered a record in the meantime. You can verify that , if you open 2 instances of queryanalzer and insert records.
For retrieving the ID of the record just inserted one definitely has to use scope_identity.
thanks anyway, Bruno.
|
|
|
|
|
Create a stored procedure to do the insert, and return @scope_identity from the sproc. This will work since you are in the 'scope' of the insert and saves a round trip as well...
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
I know, but I want to stay away from SP, as the program runs with MySQL and ProgreSQL as well.
Anyway, I figured it out meanwhile.
The reason why I get a NULL return on "select scope_identity" is, because the parametrized INSERT is done via sp_executesql, which is executed in a different scope, whereas INSERT without parameters is executed directly (wihtin the same scope).
So what I do now is as follows:
cmd.CommandText = "INSERT ...; SELECT @id = SCOPE_IDENTITY()";
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
cmd.Parameters["@id"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine("ID: {0}", cmd.Parameters["@id"].Value);
Bruno.
|
|
|
|
|
I initally posted this on the ASP.Net board but probably should of been posted here.
In my web app, I have a page that uses a stored procedure that returns multiple results. However, if 2 users request data on this page at the same time, I receive the following error, "The SqlCommand is currently busy Open, Fetching." In my connect string I have "pooling=true;". Is there another option to retrieve multiple recordset in a single trip to the database? Why doesn't ADO.Net simply open up another connection to the database?
Also to note, I am executing a cross-tab query in the procedure so the number of columns returned are not defined. Therefore using a dataset is probably out of the question.
Any help would be appreciated.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
hi,
Try using SQL Transaction object and try to run the stored proc from the transaction object... this might solve ytour problem.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
The connection is pooled, but the SqlCommand object or your connection instance is not. You need to create (or clone) a new SQlCommand and dataReader object for each user, and hand it a new connection (not a shared instance).
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
