How do I execute an SQL statement?

Question

I am very new to programming, self-learnt most of it. hope someone can give me advice on the codes.

basically, the problem I am having is that my SQL statement does not execute to retrive the data from my database. it just assigns userPermissionsOne with SELECT MemberOne FROM aspnet_Users WHERE MemberOne LIKE '_'.

Any help is very much appreciated.

userName = User.Identity.Name;  
userName = userName.ToUpper();  
selectedUserName = betDropDownList.SelectedValue;  
userPermissionsOne = "SELECT MemberOne FROM aspnet_Users WHERE MemberOne LIKE '___'";  
userPermissionsTwo = "SELECT MemberTwo FROM aspnet_Users WHERE MemberTwo LIKE '___'";  
userPermissionsThree = "SELECT MemberThree FROM aspnet_Users WHERE MemberThree LIKE '___'";  

if (string.Compare(userName, selectedUserName, true) == 0 || User.IsInRole("Administrator"))
{
    hinzufugenButton.Enabled = true;
    loschenButton.Enabled = true;
    bearbeitenButton.Enabled = true;
    betDropDownList.Enabled = true;
}
else if (userName == "ENL")
{
    if (string.Compare(userPermissionsOne, selectedUserName, true) == 0 ||
        string.Compare(userPermissionsTwo, selectedUserName, true) == 0 ||
        string.Compare(userPermissionsThree, selectedUserName, true) == 0)
    {
        hinzufugenButton.Enabled = true;
        loschenButton.Enabled = true;
        bearbeitenButton.Enabled = true;
    }
    else
    {
        hinzufugenButton.Enabled = false;
        loschenButton.Enabled = false;
        bearbeitenButton.Enabled = false;
        betDropDownList.Enabled = false;
    }
}

Answer 1

Assuming you are using SqlServer as your database:

The SqlCommand and SqlConnection classes will be of use to you here, so I would have a look at those.

Also Stored Procedures are a great way to go as they are more secure, and perform slightly faster than sending the query to the server. A quick google for the benefits of Stored procs will help you here

One thing to make note of is there are different ways to execute your query. The SqlCommand class has quite a few methods for instance ExecuteNonQuery() will just run a sql command that does not return data (such as an insert or delete) whereas ExecuteScalar will execute a query that returns only one value.

There are other options such as creating a DataTable and then using a DataAdapter to fill the table.

I will post some examples of my database connect code, sorry it's in VB but you should be able to convert to C# easily enough.

Firstly this is a connection using a data adapter

  Dim dt As New DataTable

  ' There are plenty of options you can use with the SqlConnection constructor so you can just modify this to suit your needs
  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

  Dim cmd As New SqlCommand
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "spGetMenuItemsForTickets"
  ' parameters could be added here by doing
  ' cmd.parameters.addwithvalue("@ParamName",value)
  cmd.Connection = conn

  Using da As New SqlDataAdapter(cmd)
     conn.Open()
     da.Fill(dt)
     conn.Close()
  End Using

This is an example of using ExecuteScalar

Dim names As String = String.Empty

  Dim ds As New DataTable

  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

  Dim cmd As New SqlCommand
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "spGetUserNamesForUpdate"
  cmd.Parameters.AddWithValue("@TicketID", TicketID)

  cmd.Connection = conn

  conn.Open()

  names = cmd.ExecuteScalar()

  conn.Close()

This is an example of ExecuteNonQuery It is pretty much identical to Scalar but without the returned value

     Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

     Dim cmd As New SqlCommand

     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "spAddFileToTicket"

     cmd.Parameters.AddWithValue("@ticketID", TicketID)
     cmd.Parameters.AddWithValue("@filename", Filename)
     cmd.Parameters.AddWithValue("@filePath", Filepath)
     cmd.Parameters.AddWithValue("@comments", Comment)

     cmd.Connection = conn

     conn.Open()
     cmd.ExecuteNonQuery()
     conn.Close()

Also in your SQL where you do LIKE ' __ ' you will probably want to do LIKE '%__%' as the % are the wildcards, you can use one or both of them, but just doing LIKE '__' would not match a value such as ___1 or 1___ etc

Answer 2

You have a lot of options in how to execute a SQL statement with .NET. Read up on the following for starters. These are the first three technologies I usually consider when deciding how to access the database:

• ADO.NET
• Dapper
• Entity Framework

From Microsoft:

ADO.NET provides the most direct method of data access within the .NET Framework. For a higher-level abstraction that allows applications to work against a conceptual model instead of the underlying storage model, see the ADO.NET Entity Framework.

Lately, my go-to technology for database access is Dapper. It is fast, lightweight, and plain convenient to code with.