1

I am working on a localhost site that queries, and updates a database. I have not worked with ASP.NET very much.

I am wondering if it would be possible to find out the current windows user, then select all records in the database that match the located username.

I know how to do this with vbscript, but I need to make it work in a cshtml file.

  1. How do I locate the user?
  2. What would my select sentence look like?

Any help or suggestions are appreciated.

Thanks.

Edit:

Here is the code that I use to display data from a specific user:

@{
var db = Database.Open("Database") ;
var selectCommand = "SELECT * FROM Table WHERE UserID = 'asmith'";
var searchTerm = "";

var selectedData = db.Query(selectCommand, searchTerm);
var grid = new WebGrid(source: selectedData, defaultSort: "Team", rowsPerPage:20);

}

When I change WHERE UserID = 'asmith' to WHERE UserID = @Environment.UserName, I receive the error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 48,Token in error = . ] and the below is highlighted in red.

Line 15:     var selectedData = db.Query(selectCommand, searchTerm);

Edit #2:

This Successfully queries the database and returns the correct data, but isn't clean and is causing an issue with another query on the page.

var CurrUser = Environment.UserName;
var db = Database.Open("Database") ;
var selectCommand = "SELECT * FROM Table WHERE UserID = @0";
var searchTerm = @CurrUser;
Jack
  • 2,741
  • 2
  • 24
  • 32
  • 1
    Going through the [Create an Intranet Site](http://msdn.microsoft.com/en-us/library/gg703322%28v=vs.98%29.aspx) may help. What you are looking for is called Windows Authentication. – MikeSmithDev Feb 04 '13 at 20:01
  • @MikeSmithDev This will be useful and might be the way to go, though I would like to figure out how to send the username in a query without the Authentication as well. The user should already be Authenticated. I just want the UserID for logging and viewing purposes. I am able to use `@Environment.UserName` to display the current user, but for some reason I can't successfully add it to a SELECT sentence. I would like to do something like `var selectCommand = "SELECT * FROM Database WHERE UserID = @Environment.UserName"; ` – Jack Feb 05 '13 at 00:46
  • Please include you code for your `SELECT` and whatever error you are having. – MikeSmithDev Feb 05 '13 at 02:04
  • I have included my code and error above. – Jack Feb 05 '13 at 02:45

2 Answers2

0

You're missing the single quotes:

var selectCommand = "SELECT * FROM Table WHERE UserID = 'asmith'";

should be:

var selectCommand = "SELECT * FROM Table WHERE UserID = '@" + Environment.UserName + "'";

Environment.UserName is a string. You're querying where UserID. Are you sure UserID shouldn't be an int or some uniqueidentifier?

Pete
  • 10,651
  • 9
  • 52
  • 74
  • `"SELECT * FROM Table WHERE UserID = '@" + Environment.UserName + "'";` removed the error, but does not return any data. `UserID` is not an `int`. As far as being a `uniqueidentifier`, there could be multiple occurrences. – Jack Feb 05 '13 at 03:09
  • What happens when you query SQL directly with `SELECT * FROM Table WHERE UserID = 'SOMEUSERTHATEXISTS'`? You should step through the code and make sure that `Environment.UserName` is giving you the username you expect. How are the names getting into your database to begin with? – Pete Feb 05 '13 at 03:46
  • `SELECT * FROM Table WHERE UserID = 'SOMEUSERTHATEXISTS'` correctly returns all records that match the query. It is when I try to use a variable that it is not working. `@Environment.UserName` placed in the body of the page displays the current username, so that is why I am stumped. Right now I am manually entering the `UserID`, but my goal will be to automatically write the username to the database when a user inputs information, then read all records `WHERE` the Username matches the `UserID` from the database. – Jack Feb 05 '13 at 14:34
  • Did you set a break point and make sure that the username is actually getting passed to the query? `Environment.UserName` may be returning the app pool your site is running under. See: http://stackoverflow.com/questions/8841816/system-web-httpcontext-current-user-identity-name-vs-system-environment-username – Pete Feb 05 '13 at 14:53
  • I am slightly confused. If I do this: `var CurrUser = Environment.UserName;` I am able to successfully write `@CurrUser` to the database, So I would expect that I could use `@CurrUser` in my query. Like this: `SELECT * FROM Board WHERE UserID = '@" + CurrUser + "'";`, but that doesn't work. Does that answer your question? – Jack Feb 05 '13 at 15:23
  • It does answer my question but now I'm in the same position as you. I'll put a bounty on this question if not answered by the time it's eligible for a bounty. – Pete Feb 05 '13 at 15:45
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/24003/discussion-between-jack-and-pete) – Jack Feb 05 '13 at 22:11
  • I added a little bit more code up top where I was able to make it work but not in a clean way, because it messed up another query on the page. – Jack Feb 05 '13 at 22:12
0

Just to clean up and resolve an old post, I would change the select to something more like this:

var currentUser = Request.LogonUserIdentity.Name.Substring(Request.LogonUserIdentity.Name.LastIndexOf(@"\") + 1);
var selectCommand = "SELECT * FROM Table WHERE UserID = @0";
var selectedData = db.Query(selectCommand, currentUser);
Jack
  • 2,741
  • 2
  • 24
  • 32