Greetings folks. I'm still trying to wrap my head around WCF and I've made some progress already but I'd like to better understand the role and usage of the app.config files. The examples I've seen so far mostly tells you what to put in the app.config files without giving a real good explanation for why what goes where. Also, the sample app.config files tend to include a whole lot of information which I suspect is not even necessary but I'm having a hard time stripping it down to the bare necessities. Could anyone point me to an article perhaps that deal with the app.config files (for both the host and the client side), starting with the most basic of app.config files and then explaining how to build on that for more specific requirements. If I may bother you with the specific issue I'm struggling with right now: I have a WCF host and client which works perfectly - as long as both the host and the client are on the same machine. When the client attempts to connect from another machine I get "The server has rejected the client credentials". The config files in my project include a number of sections which I'm not sure what they mean or whether they're necessary but it comes from some or other tutorial and so I didn't bother to make sure that the config files are exactly what I need. Now I need to start understanding them. I know for instance that I should be able to disable security (which is fine for now) by adding <security mode="None" /> to the binding section in the app.config file of the client but when I replace the existing tag in the file, it throws another exception (The socket connection was aborted), this time even when trying to connect from the local machine. I'd expect that the host config file should also get something to tell it to ignore security but I can't find anything currently in there that hints to any security anyway. In case you really feel like seeing what I've done, here are my two app.config files, first for the host:

Jörgen Andersson wrote:

I believe one subquery is unnecessary. Try:

SELECT *
FROM Orders
WHERE OrderID IN (
SELECT TOP 1 OrderID
FROM Orders O1
GROUP BY CustomerID
ORDER BY Inserted
)

I'm not sure I understand that inner query. For one OrderID in the SELECT clause should surely be part of an aggregate function seeing as it is not in the GROUP BY clause. Also, for Inserted to be contained in the ORDER BY clause it will also have to be part of an aggregate function. What am I missing?

BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:

SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)

Of course this query relies on the existence of OrderID, a primary key of the Orders table. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do :) The great thing about this query is that the outermost query can have a SELECT * and, as you say, could even join the Orders table with any other table that might contain linked information on the orders. Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed.

Thanks. I've been shying away from Over(), ROW_NUMBER and Partition By because they're rather Microsoft specific. I agree, generally there's nothing inelegant about using a subquery but in this particular case it is anything but elegant. Consider the table in my example and let's say there are a few more columns: ContractID (INT) OrderSource (INT) AgentID (INT) ProvinceID (INT) DeliveryMethod (INT) So, in order to get what I want, using GROUP BY and subqueries, it would end up looking something like this:

SELECT
CustomerID,
MIN(OrderPlaced),
--(Some or other subquery to get the value for AmountOrdered),
--(Some or other subquery to get the value for ContractID),
--(Some or other subquery to get the value for OrderSource),
--(Some or other subquery to get the value for AgentID),
--(Some or other subquery to get the value for ProvinceID),
--(Some or other subquery to get the value for DeliveryMethod)
FROM Orders
WHERE ProductID = 123
GROUP BY CustomerID

I'm sure you'll agree that the above query is pretty much the antithesis of elegance.

Here's a problem I've come up against on a number of occasions and I have so far failed to come up with an elegant query to deal with it. I feel the GROUP BY clause might have to come into play but I don't really know how. Let's say I have a table called Orders containing information on orders received from customers. The table has the following columns: CustomerID (INT) OrderPlaced (DATETIME) ProductID (INT) AmountOrdered (INT) So I want to write a query that will show me the information from this table on the first order for a specific product (let's say id 123) received by every customer. The following query will get me almost there:

SELECT CustomerID, MIN(OrderPlaced)
FROM Orders
WHERE ProductID = 123

It only gets me almost there though because notice how the AmountOrdered field is missing from the query and unless it's part of an aggregate function I can't add it. I can add it with a subquery but that is where the query loses its elegance rapidly, especially considering that the real world tables I'm dealing with usually have more than just one or two extra columns. Any ideas?

OK, so I'm slowly making progress with WCF. I have a question on callbacks and where they can be called from. I've found this awesome article which, almost exactly, addresses what I'm trying to achieve. The hosting application has the following block of code:

using (ServiceHost host = new ServiceHost(typeof(BeerService)))
{
host.Open();

Console.WriteLine("Service is ready for requests.  Press any key to close service.");
Console.WriteLine();

Console.Read();

Console.WriteLine("Closing service...");

}

Inside BeerService is where the magic happens. I'd like to trigger some of that magic from inside the server side application, the code block above, but how do I do that? I don't have access to an instance of BeerService, all I have is access to host which is an instance of ServiceHost, not BeerService. Essentially, this is what I'd like to be able to do, but can't:

using (ServiceHost host = new ServiceHost(typeof(BeerService)))
{
host.Open();

Console.WriteLine("Service is ready for requests.  Press any key to close service.");
Console.WriteLine();

int c;
do
{
    c = Console.Read();
    if (c == 'b')
        // Here is the line that I'd like to be able to call but can't because MakeBeerRun() is a member of BeerService and I don't have access to an instance of it
        host.MakeBeerRun("The Party Owner", 12);
} while (c == 'b');

Console.WriteLine("Closing service...");

}

Thanks, I'll definitely look into message queues also. Of course you're right as regards TCP/IP being full duplex. My misunderstanding comes from the typical way in which I've always been used to implementing the application layer protocol on top of the TCP layer. In it's most basic form TCP comms consist basically of two streams, one upstream and one downstream. The server can put any message at any time into the downstream while the client can put any message at any time into the upstream. So yes, that is the textbook definition of full duplex. What makes WCF more suitable to me (I think) is the fact that the implementation of all this seems to be much easier. With TCP, I'd have to write a thread that constantly reads the downstream and decide what is a response to a message that was sent on the upstream and what is a message that simply arrives unsolicited. With WCF the response to an upstream message comes in the form of a return value to the function that is called while an unsolicited message comes in the form of a callback function that is called.

jschell wrote:

That statement isn't any more or less true for WCF using TCP.
And without qualification it is false for TCP. A tcp connection always has two ends. One end accepts (server) and one ends initiates (client.)

OK, maybe I need to explore this to make sure I understand it correctly. My understanding is that, with TCP/IP, a message can only be sent from server->client if the client requested it from the server. in the example of my sandwich lady application, when the receptionist hits the button to notify everybody of the sandwich lady's arrival there is no way that the server can initiate a message to be sent to the client. The clients will have to continually poll the server for a notification. This means the client applications will have to sit on a timer (which consumes CPU/memory resources) so that it can continually message the server (which consumes network bandwidth) to which the server can respond if appropriate. It also means the client can only act on the notification every 30sec, 1min or however quick you choose to set up the timer but more often equals more resources wasted and less often means longer delay before the client knows of the sandwich lady's arrival. I was of the understanding, but I could be wrong, that with WCF this can be implemented differently. Once a connection is established between a client and a server, the server can initiate a message to the client which will call a callback function in the client application. The difference is that the client sits and does nothing (other than looping through the main UI thread) until the server sends it a message whenever it decides to do so. So the server still accepts and the client initiates but, once the connection has been established, either the server or the client can initiate a message. Have I got this all wrong?

jschell wrote:

What happens if the receptionist is late? Thus the application will not be running.

It doesn't matter. This sandwich lady system is just a means for me to get to grips with the technology. The eventual system will be far more robust. But yes, if the receptionist is late, the client side applications will have no server side application to connect to and will either notify you as such and terminate or it will retry the connection every 10 minutes or so. Thanks

I'm not sure WCF is the way I should go here but I'm keen to get to learn a new technology so I thought I should investigate it at least. We are developing a system in which we need a server-side application (with GUI, so not a Windows service) and several client side applications that communicate with this server side application. All of this happens on the internal network so I figured that TCP binding might be suitable. The one approach would be to write some TCP/IP server routines on the server-side application and have the clients communicate with it over TCP sockets for which we'd have to implement certain messaging. What I dislike about this approach is the fact that all comms will be request-response type. In other words, the server can only send messages to the client if the client sent a message to the server. So the possibility of full-duplex comms of WCF seems like a perfect solution. The thing I'm unsure of is whether WCF would allow for a server-side application that has a GUI and allows for user interaction. I thought I might try getting to know WCF first by writing a small mini system (in C#), the sandwich lady notification system, consisting of a "server" side application which runs on the receptionist's computer. A number of other computers in the office then has a client application. The client application can send a message to the server application to subscribe itself to the notifications. When the sandwich lady arrives the receptionist can then open the GUI of the application on her machine and click a button which triggers the app to send a message to all the clients that has subscribed which in turn pops up a notification on the client machine. I realise there might be better ways to achieve this particular solution but it is a reasonable analogy of what we ultimately want to achieve with out bigger system so I figure I might gain the skills I need if I can figure out how to write this little system. My question is, could someone tell me please whether WCF is indeed the technology that should be used for this? I have only just started reading up on it but I am getting the impression that with WCF, the server side application will have to take the form of a service, not a desktop application. Could anyone give me some clarity on this please and possibly point me in the right direction for what I'm trying to achieve? TIA

Goodness! I didn't even notice there was a forum dedicated to WCF. Thanks for pointing out.

I'm not sure WCF is the way I should go here but I'm keen to get to learn a new technology so I thought I should investigate it at least. We are developing a system in which we need a server-side application (with GUI, so not a Windows service) and several client side applications that communicate with this server side application. All of this happens on the internal network so I figured that TCP binding might be suitable. The one approach would be to write some TCP/IP server routines on the server-side application and have the clients communicate with it over TCP sockets for which we'd have to implement certain messaging. What I dislike about this approach is the fact that all comms will be request-response type. In other words, the server can only send messages to the client if the client sent a message to the server. So the possibility of full-duplex comms of WCF seems like a perfect solution. The thing I'm unsure of is whether WCF would allow for a server-side application that has a GUI and allows for user interaction. I thought I might try getting to know WCF first by writing a small mini system, the sandwich lady notification system, consisting of a "server" side application which runs on the receptionist's computer. A number of other computers in the office then has a client application. The client application can send a message to the server application to subscribe itself to the notifications. When the sandwich lady arrives the receptionist can then open the GUI of the application on her machine and click a button which triggers the app to send a message to all the clients that has subscribed which in turn pops up a notification on the client machine. I realise there might be better ways to achieve this particular solution but it is a reasonable analogy of what we ultimately want to achieve with out bigger system so I figure I might gain the skills I need if I can figure out how to write this little system. My question is, could someone tell me please whether WCF is indeed the technology that should be used for this? I have only just started reading up on it but I am getting the impression that with WCF, the server side application will have to take the form of a service, not a desktop application. Could anyone give me some clarity on this please and possibly point me in the right direction for what I'm trying to achieve? TIA

I have a DataGridView on a form which is populated through databinding from a table in my database. I've followed, more or less, the process outlined on this MSDN page[^] I've implemented the databinding both ways so that, by clicking on an "Update" button, the changes that have been made on the DataGridView is stored back into the DB. All works well, but... Imagine the table used for the databinding being something like this:

USERS

• UserID (INT)
• UserName (VARCHAR)
• UserType (INT)

And here's the problem, that UserType field references the identifier in a separate table like this one:

USERTYPES

• UserType (INT)
• UserTypeDescription (VARCHAR)

Currently, my databinding is done with a simple query like this:

SELECT * FROM USERS;

The problem is that the value displayed in the third column is an integer which means nothing to the end user. If I wasn't interested in two way databinding but only wanted to display the contents of the table in the DataGridView I could easily have overcome that problem by using the following query:

SELECT UserID, UserName, UserTypeDescription FROM USERS INNER JOIN USERTYPES ON USERS.UserType = USERTYPES.UserType;

Unfortunately I need for the end user to be able to change that value and I'd like to put a ComboBox in all the cells of that column which is populated from the USERTYPES table. Would really appreciate some advice. ***EDIT*** Maybe I should add that I have some experience with adding a ComboBox (rather a DataGridViewComboBoxCell) in a DataGridView but that has always been to DataGridViews that have not been databound to a table. Here's how I would've done that:

private void myDataGridView_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
for (int row = 0; row < (sender as DataGridView).Rows.Count; row++)
{
// Create a new DataGridViewComboBoxCell that will replace the existing UserType cell in the relevant column
DataGridViewComboBoxCell newCell = new DataGridViewComboBoxCell();

    // Populate the DataSource of the combo box
    using (SqlCommand sqlCommand = new SqlCommand("SELECT UserType, UserDescription FROM UserTypes", mySQLConnection))
    {
        using (SqlDataReader sqlData

No, I believe that works only for Webforms, not for Winforms as is the case here. I don't understand why that method is not available in winforms as it would have been the perfect solution for this problem of mine.

I have a form containing a DataGridView which I want to populate immediately upon creating the new form. The DataGridView is populated from a SQL query and I do it as follows:

using (SqlCommand mySqlCommand = new SqlCommand("SELECT * FROM MyTable", mySqlConnection))
{
using (SqlDataReader mySqlDataReader = sqlCommand.ExecuteReader())
{
DataSet myDataSet = new DataSet();
DataTable myDataTable = new DataTable();
myDataSet.Tables.Add(myDataTable);
myDataSet.Load(mySqlDataReader, LoadOption.PreserveChanges, myDataSet.Tables[0]);
myDataGridView.DataSource = myDataSet.Tables[0];
}
}

myDataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
foreach(DataGridViewRow myRow in myDataGridView.Rows)
// Do something with some of the values in the row

Now, the above code works perfectly except for the last two lines of code, which automatically resizes each column to fit the widest cell and which iterates through the DataGridView and does something with some of the values. The reason this won't work is because, by the time they are executed, the databinding for the DataGridView might not yet be finished. One solution is to add

myDataGridView.DataBindingComplete += new DataGridViewBindingCompleteEventHandler(myDataGridView_DataBindingComplete);

And then put those lines of code inside the event handler. For various reasons, it happens from time to time that I'd rather have those lines of code inside the original function that populates the form. I'd be quite happy to enter a loop that will wait for the data binding to complete before I continue but I don't know how. Any suggestions?

OK, that sounds like a good approach. Thanks. I'll investigate that.

I have an interesting requirement and was wondering if anyone might be able to point me in a direction that could get me started. I've developed an application that is used on many workstations in the company and all access the same database. From time to time I see little issues in the DB that I can't figure out how a user might have managed to make the particular change. It might be a bug in my code but it's such a difficult issue to debug as I usually can't even replicate the problem. So I was wondering if it would be possible to write some or other function that is called every time the user clicks on any control on the form. This procedure then stores the name of the control, the date and time and perhaps even the coordinates of the click (or something like that) to a List which can then either be written to a local log file or to some table in the DB. This way I'd be able to see exactly the sequence of clicks that preceded a certain change in the DB. The problem is, the application is huge and I don't want to go add a line of code that calls this function at the start of every OnClick() event handler. Is there a way I could have my function called for every click that happens on any control in any form in the application before the relevant OnClick() event handler is called? Any ideas on alternative approaches that might help me track the users' mouse clicks would also be very welcome.

Thanks. Would you mind explaining the advantage of an INNER JOIN over an IN?

Thanks. But why would you say that I should use an = test as opposed to an IN test? What if the UPDATE statement affects more than one row? Is there any specific disadvantage to usign an IN test that I should consider?

Do you guys know of any easy way to have one column in a table of which the value auto updates every time an UPDATE statement is called for a specific row? Either one of the following two scenarios would be fine but I'm not sure what the easiest way would be of implementing it: 1. Let's say there's a column in the table called 'Version' (int). Every time an UPDATE statement is called, the value of the 'Version' column is automatically incremented by 1 for every row affected. 2. Let's say there's a column in the table called 'Modified' (datetime). Every time an UPDATE statement is called, the value of the 'Modified' column is automatically set to the current date and time. I have a similar concept to this in my DB with a column named 'Inserted' which has a default value of GetDate(). So for every INSERT into the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this for UPDATE as well. Any ideas? My best idea so far would be to create a trigger on the table, something as follows:

CREATE TRIGGER update_mytable
ON MyTable
FOR UPDATE
AS
BEGIN
UPDATE MyTable
SET Modified = GETDATE()
WHERE RecordID IN (SELECT RecordID FROM INSERTED)
END

But I'm hoping that there might be a more elegant solution.

How silly of me. I simply assumed that EXEC MyProc @MyParam = @MyParam wouldn't work. Just tested it and no problem at all. :doh: Thanks.