|
i like to know is it right approach to registering SQL Dependency again from onchange event ?
one guy review my code and told me registering everything again and again after each notification. As I can recall this will create a queue each time
so i comment this code in onchange event
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);
<pre>
if (e.Source == SqlNotificationSource.Timeout)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
CallWebService();
}</pre>
just see i comment this line //((SqlDependency)sender).OnChange -= OnDataChange; //RegisterNotification(); but after commenting i found onchange event is firing first time and it is not firing from second time.
guide me with right approach. here is my partial code
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
CallWebService();
}
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ActivityDate FROM [dbo].tablename";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
thanks
tbhattacharjee
|
|
|
|
|
i have implemented sql dependency in windows service. when data will be changed in table then onchange event will fire and from there i am invoking a web service.
i will share my full code. i tested many time in my pc before installing the window service production pc. i works. suppose if i install in production pc today then it works for today but when i test next day then i was onchange event not firing.
so i found onchange event firing only first day and from the next day onchange event not firing. may be i made some mistake in code. so it is my request please some one see my code in details and help me where i made the mistake for which it is not working properly.
public partial class PartIndexer : ServiceBase
{
static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
SqlDependency dep;
<pre>
public PartIndexer()
{
InitializeComponent();
}
private string GetLoggedInUser()
{
string userName = "";
if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
{
userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
}
return userName;
}
#region OnStart
protected override void OnStart(string[] args)
{
BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
RegisterNotification();
MailSend();
BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion
#region RegisterNotification
private void RegisterNotification()
{
string tmpdata = "";
BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
System.Data.SqlClient.SqlDependency.Start(connectionString);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(OnDataChange);
SqlDataReader dr = cmd.ExecuteReader();
{
if (dr.HasRows)
{
dr.Read();
tmpdata = dr[0].ToString();
}
}
dr.Dispose();
cmd.Dispose();
}
}
catch (Exception ex)
{
BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
}
finally
{
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
}
}
#endregion
#region OnDataChange
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
((SqlDependency)sender).OnChange -= OnDataChange;
BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);
if (e.Source == SqlNotificationSource.Timeout)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Source != SqlNotificationSource.Data)
{
MailSend();
BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
Environment.Exit(1);
}
else if (e.Type == SqlNotificationType.Change)
{
StartIndex();
BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
}
else
{
BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
}
RegisterNotification();
}
#endregion
#region StartIndex
void StartIndex()
{
}
#endregion
#region MailSend
/// <param name="strStatus" />
void MailSend()
{
/* mail send code */
}
#endregion
#region OnStop
protected override void OnStop()
{
BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}
another issue i noticed that when i start my windows service and leave it run in one day when i try to stop or restart the the windows service then i saw windows can not stop the service. it means definitely there is some flaw in my code which i am not being able to point out. so please help me.
another review my code and told me every time a new queue will be created at db side which may causes the problem. what is wrong in code for which every time a new queue will be created at db. thanks
thanks
tbhattacharjee
|
|
|
|
|
I have an applications that pulls data from Sql Server and push to Oracle database which is in remote server. The remote server can be connected through VPN. Initially I tried to install Oracle client version 11g, and then different versions, but everything failed. My OS is windows 8.1, googled and find out that 11G will not support on Win8.1. So I tried in Windows 7 OS without installing client,using the example given in this link. However I am still getting an error message saying as "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed".
My OS is Windows 7 64 bit. I have downloaded and installed the Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support from the Oracle site.
I am not sure how to proceed with this. My question is:
1) The connection string provided in the article - I put the host name as Server IP. Is this correct? So where should I put the db name?
2) Should again require a tnsnames.ora file? If so, should I create a new one and put it in the same folder of exe?
I am stuck at this point. Please provide any help on this. Thanks in advance
|
|
|
|
|
You MUST have an Oracle Client installed for your app to work. Preferrably, one that is compatible with your Oracle server version.
Next, you got the bad image format exception because your Oracle Client is 32-bit and your code is compiled to target AnyCpu. This means your code will run as 32-bit on a 32-bit O/S and 64-bit on a 64-bit O/S. Since you cannot mix 32 and 64-bit code in the same process, you got that error message.
The fix is simple. Recompile your app to target x86 only. Go into your Project Properties, Build tab and look for the Platform Target option.
|
|
|
|
|
Thanks Dave,
I have installed Oracle Client earlier and tried. I have installed 64 bit as well. Then, in one of the forums, it is found that I need to install 32 bit as well, so I installed both. What I will do now is, I will remove everything and start afresh, let me try once again. Meanwhile, could you please let me know where should I find the file tnsnames.ora?
|
|
|
|
|
You don't need a TNSNAMES.ORA file. All it is is a text file with predefined connection strings in it. That's all.
You can put the very same connection string in your OracleConnection object and it'll work.
But, it's found under the path %ORACLE_HOME%/network/admin.
|
|
|
|
|
Ok. Thanks. Let me do the exercise once again and will update.
|
|
|
|
|
I removed everything and installed fresh everything in C drive itself. So I got tnsnames and sqlnames ora files automatically. I tried ping from DOS prompt using ping and tnsping which was also successful. However, When I tried from dotnet, I am getting an error message, "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll". I have installed win64_11gR2_client in Windows 7, and the dotnet version is Visual Studio 2012 Enterprise Edition (Trial).
My code is as below:
private void ConnectOracleDB()
{
string sqlstring = string.Empty;
Logger myLogger = new Logger();
string myConnectionString = GenerateConnectionString();
OracleConnection myConnection = new OracleConnection();
myConnection.ConnectionString = myConnectionString;
try
{
myLogger.AddtoLogFile("Trying to connect Oracle DB", "Page Load");
myConnection.Open();
}
catch (Exception ex)
{
myLogger.AddtoLogFile(ex.Message, "Connecting to Oracle DB");
}
myConnection.Close();
}
private string GenerateConnectionString()
{
return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {server ip} )( PORT = 1521 ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {db name}) ) ); User Id= abcd; Password = 1234;";
}
Update:
I fixed this now, as I copied the same to the bin directory on the Application root directory. But now another error comes as "Object doesn't exist" .. 
modified 25-Apr-15 9:30am.
|
|
|
|
|
All you have to do is Google the error message "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll"and start reading.
|
|
|
|
|
|
How to open the Docx file using 'microsoft.office.interop.word' this DLL.
I Used Following code.
Object SubTemplateFilePath = @"D:\TemplateMaster\Test1.docx";
Microsoft.Office.Interop.Word._Application oWord;
Microsoft.Office.Interop.Word._Document oDoc;
oDoc = oWord.Documents.Open(SubTemplateFilePath);
When pointer go above statement then Silverlight application go endless loop.
When Application put on local machine then application work very well but when application on IIS then get the error.
I put all the setting in IIS. For Eg identity is localsystem but it is not working.
Please help me.
|
|
|
|
|
So is this code running on the web server or is it part of a Silverlight app running on the client?
Does the client have the same version of Word installed as the version you referenced when you built the code?
|
|
|
|
|
Instead of using the Office Interop libraries, you may want to take a look at DocX[^], which is a pure C# library for handling Microsoft Word files.
I am not sure if it works in Silverlight (I can't find anything about that configuration), but if it does it might help you.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
The browser does not have access to your D: unless the app is installed as out of browser on your local system with elevated permissions.
You need to download the docx and get the user to open the file, you cannot do it from the browser (Silverlight).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I all ready installed on server but still I have problem.
|
|
|
|
|
Installed what on the server!
I repeat, you need to download the DocX file as a binary (probably), get the user to save it on their local (D drive and then the user must open the file in word. Without elevated permissions Silverlight cannot access a local drive.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
My questions are about the certification exam 70-483 - MCSD programming in C#
The web provides inconsistent info. Even the testing company (Pearson Vue) fails to provide clear answers. My questions are:
- the number of questions on the actual exam, does the number vary between exams?
- duration of the exam, i.e. max time allowed
- how is the passing score computed? Is the numeric score reported on the certification? Is the numeric score private?
- how the test is conducted? Probably online at a testing center. Is it possible to use any materials like books or web during the test?
- the format of the answers: all are multiple choice (A,B,C,D)? Some sources claim that selected answers are fill-in, or drag-drop, is it true?
modified 24-Apr-15 17:21pm.
|
|
|
|
|
CRobert456 wrote: - the number of questions on the actual exam, does the number vary between exams?
The number of questions varies depending on your answers to previous questions.
CRobert456 wrote: - duration of the exam, i.e. max time allowed
You'll get that when you sign up for the exam.
CRobert456 wrote: - the format of the answers: all are multiple choice (A,B,C,D)? Some sources claim that selected answers are fill-in, or drag-drop, is it true?
There are all kinds of question types on the exam.
Seriously? Who gives a crap about how many questions, how long and what type of questions are on the test? Those things are entirely irrelevant. The entire point of the test is DO YOU KNOW THE MATERIAL? Do any of your questions relate to that? Nope.
|
|
|
|
|
I wonder if the OP would be interested in a doctorate, I believe they are available for a modest sum.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
if (this.DataCenterLaborFileUpload.HasFile)
{
var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
string currentName = "";
decimal currentHours = 0.00M;
decimal currentFTE = 0.00M;
string ResourceName = "";
if (extension == ".csv")
{
StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
DataTable dt = new DataTable();
dt.Columns.Add("txtName");
dt.Columns.Add("txtHours");
dt.Columns.Add("txtFTE");
while (!csvreader.EndOfStream)
{
DataRow dr = dt.NewRow();
var line = csvreader.ReadLine();
var values = line.Split(',');
if (values[0].Trim() != "Pers.No.")
{
using (DbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLStaffingConn"].ConnectionString))
{
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT ResourceName FROM StaffTracking order by PersonnelResourceType, ResourceName";
using (DbDataReader row = cmd.ExecuteReader())
{
while (row.Read())
{
ResourceName = row.GetString(0);
if (ResourceName != (values[1].Trim()))
{
if (values[1].Trim() == currentName)
{
currentHours = currentHours + Convert.ToDecimal(values[9].Trim());
}
else
{
if (currentName == "")
{
dr["txtName"] = currentName;
dr["txtHours"] = currentHours;
dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
dt.Rows.Add(dr);
dt.AcceptChanges();
}
currentHours = Convert.ToDecimal(values[9].Trim());
currentName = values[1].Trim();
}
}
}
}
}
}
}
}
DataRow drfinal2 = dt.NewRow();
drfinal2["txtName"] = currentName;
drfinal2["txtHours"] = currentHours;
drfinal2["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
dt.Rows.Add(drfinal2);
dt.AcceptChanges();
gvDataCenterLabor.DataSource = dt;
}
gvDataCenterLabor.DataBind();
}
}
Here is my csv file:
Pers.No. Name HomeFID Copy Range/Value from Column V Worked Date Job Code Account Pay Type Activity Type Hours
11111111 Doe Jane USA7064810 US1-08333.01.08.02.03 20150223 00S15H 9.61E+09 410 8
11111111 Doe John USA7064810 US1-08333.01.08.02.03 20150210 00S15H 9.61E+09 410 9
11111111 Smith Jane USA7064810 US1-08333.01.08.02.03 20150226 00S15H 9.61E+09 410 8
So if Doe Jane is not in the SQL table I would like to only display her.
It's only displaying the ones from the CSV file that are in the SQL Table.
|
|
|
|
|
I would like to convince you to start over 
Use this for reading CSV: A Fast CSV Reader[^] - you'll be happy ever after
Currently you open an SqlConnection, execute a query and close the connection for every single record in the CSV-file. You'll get away with it if your CSV-file only contains a few records but it's nasty and will become a bottleneck when your CSV-file becomes larger.
Instead, I would suggest you to either load your database-table into a System.Data.DataTable if it's not extremely large and match it to the CSV-records or to insert your CSV-records into a temporary table in the database and perform a Select-query with a not-exists-subquery[^].
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Hi Sascha,
I did what you said and create a temporary table in sql server and was able to get the results I wanted.
SELECT ResourceName FROM [SP2010_EDCStaffing_AppDB].[dbo].[Tempname] where not exists
(select Resourcename from Stafftracking where StaffTracking.id = Tempname.id);
Now, How would I code this in a C#? Just read both tables and display the differences?
Thanks,
Norris
|
|
|
|
|
Hi Norris,
I'm not sure I understand correctly. Sounds like you did this query with SQL Server Management Studio (or similar) and have no new C#-code yet, is that correct?
/Sascha
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
Yes I was able to load my csv file into a table and do a query against both table and got the results I was looking for. I haven't done any new C# code.
|
|
|
|
|
Use Linq-To-SQL
If it's not broken, fix it until it is
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
