We have a legacy database which provides access to it using ODBC (read only). We are developing an extension to the application using ASP.NET C# and SQL server as database. We have written a Windows service which runs frequently (every 10 minutes) to import
data from few tables into SQL server database. Although this is working, we believe this is not an efficient way and data may be out of sync for potentially 10 minutes.
We are new to C# and .NET development so I was wondering if there is a way for the windows service to know that a new record is created in the source database so that we can also create in the SQL server database. The source database does not provide database
triggers.
if there is a way for the windows service to know that a new record is created in the source database so that we can also create in the SQL server database.
Hi,
As far as I see, I think a better way for you at present is to watch when to create a new record and then continue to create a new database.
So you can use an Insert Trigger to cope with that problem.
The problem is that the source database is not SQL server and it does not provide insert trigger facility. Sevice broker and notification services would have been ideal for this use but I can't find any example of using across multiple databases (i.e. non
SQL and SQL).
Unless your Kerridge database provides some kind of mechnism to issue notifications, or you can perhaps detect changes through some kind of file watcher, there is not much that can be done. In any event, this is not a problem that can be solved through ASP.NET.
Neetu Sharma
0 Points
3 Posts
Import Record (New / Updated) from an ODBC source into SQL Database
Nov 09, 2012 08:44 AM|LINK
We have a legacy database which provides access to it using ODBC (read only). We are developing an extension to the application using ASP.NET C# and SQL server as database. We have written a Windows service which runs frequently (every 10 minutes) to import data from few tables into SQL server database. Although this is working, we believe this is not an efficient way and data may be out of sync for potentially 10 minutes.
We are new to C# and .NET development so I was wondering if there is a way for the windows service to know that a new record is created in the source database so that we can also create in the SQL server database. The source database does not provide database triggers.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Import Record (New / Updated) from an ODBC source into SQL Database
Nov 10, 2012 01:30 AM|LINK
Hi,
As far as I see, I think a better way for you at present is to watch when to create a new record and then continue to create a new database. So you can use an Insert Trigger to cope with that problem.
For more about Trigger, you can see this：
http://msdn.microsoft.com/en-us/library/ms189799.aspx
Reguards!
Neetu Sharma
0 Points
3 Posts
Re: Import Record (New / Updated) from an ODBC source into SQL Database
Nov 10, 2012 07:11 AM|LINK
Thank you for quick response.
The problem is that the source database is not SQL server and it does not provide insert trigger facility. Sevice broker and notification services would have been ideal for this use but I can't find any example of using across multiple databases (i.e. non SQL and SQL).
Many thanks
Neetu Sharma
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Import Record (New / Updated) from an ODBC source into SQL Database
Nov 10, 2012 07:13 AM|LINK
What kind of db are you now using？
Neetu Sharma
0 Points
3 Posts
Re: Import Record (New / Updated) from an ODBC source into SQL Database
Nov 10, 2012 07:35 AM|LINK
The database is a Kerridge (KCML), used by car dealers. This is a legacy database however provides ODBC access.
Mikesdotnett...
All-Star
154955 Points
19872 Posts
Moderator
MVP
Re: Import Record (New / Updated) from an ODBC source into SQL Database
Nov 10, 2012 07:53 AM|LINK
Unless your Kerridge database provides some kind of mechnism to issue notifications, or you can perhaps detect changes through some kind of file watcher, there is not much that can be done. In any event, this is not a problem that can be solved through ASP.NET.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter