Asp.Net choose in Sql

Question

It will be very simple, I know. I have seen so many different ways to use sql in asp.net without a real standard. I want to know how to select from sql database in asp.net correctly and get some records. For example: select all userid.

String sql = 
  "SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";

string strCon = System.Web
                      .Configuration
                      .WebConfigurationManager
                      .ConnectionStrings["SocialSiteConnectionString"]
                      .ConnectionString;

SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();

/*
 This is where I need to know how to retrieve the information from the
 above command(comm). I am looking for something similiar to php's
 mysql_result. I want to access the records kind of like an array or some
 other form of retrieving all the data.
 Also when the new SqlCommand is called...does that actual run the
 SELECT STATEMENT or is there another step. 
*/

conn.Close();

Answer 1

I think this is what you are looking for.

String sql = "SELECT [UserId] FROM [UserProfiles] WHERE NOT [UserId] = 'CurrentUserId'";

string strCon = System.Web
                      .Configuration
                      .WebConfigurationManager
                      .ConnectionStrings["SocialSiteConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(strCon);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader nwReader = comm.ExecuteReader();
while (nwReader.Read())
{
    int UserID = (int)nwReader["UserID"];
    // Do something with UserID here...
}
nwReader.Close();
conn.Close();

I have to say, however, that the general approach can use a lot of settings. First, you could at least start by simplifying access to your ConnectionString. For example, you can add the following to the Global.asax.cs file:

   using System;
   using System.Configuration;

   public partial class Global : HttpApplication
   {
      public static string ConnectionString; 

      void Application_Start(object sender, EventArgs e)
      { 
          ConnectionString = ConfigurationManager.ConnectionStrings["SocialSiteConnectionString"].ConnectionString;
      }
      ...
    }

Now, throughout the code, just open it using:

SqlConnection conn = new SqlConnection(Global.ConnectionString);

, , "". , :

        using (BSDIQuery qry = new BSDIQuery())
        {
            SqlDataReader nwReader = qry.Command("SELECT...").ReturnReader();
            // If I needed to add a parameter I'd add it above as well: .ParamVal("CurrentUser")
            while (nwReader.Read())
            {
                int UserID = (int)nwReader["UserID"];
                // Do something with UserID here...
            }
            nwReader.Close();
        }

DAL. , , , "BSDIQuery" ( , ). , .

Answer 2

( , ):

    public DataTable ExecuteQueryTable(string query)
    {
        return ExecuteQueryTable(query, null);
    }

    public DataTable ExecuteQueryTable(string query, Dictionary<string, object> parameters)
    {
        using (SqlConnection conn = new SqlConnection(this.connectionString))
        {
            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = query;

                if (parameters != null)
                {
                    foreach (string parameter in parameters.Keys)
                    {
                        cmd.Parameters.AddWithValue(parameter, parameters[parameter]);
                    }
                }

                DataTable tbl = new DataTable();
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(tbl);
                }

                return tbl;
            }
        }
    }

Answer 3

:

String sql = "SELECT [UserId] FROM [UserProfiles] WHERE [UserId] != @CurrentUserId";

string strCon = System.Web
                      .Configuration
                      .WebConfigurationManager
                      .ConnectionStrings["SocialSiteConnectionString"].ConnectionString;

DataTable result = new DataTable();
using (var conn = new SqlConnection(strCon))
using (var cmd = new SqlCommand(sql, conn))
{
    cmd.Parameters.Add("@CurrentUserID", SqlDbType.Int).Value = CurrentUserID;
    conn.Open();

    result.Load(cmd.ExecuteReader());
}

Answer 4

SqlCommand .

, ExecuteReader - .

-, , DataSet/DataTable. - , ADO.NET .

, ExecuteReader . SqlDataReader, - , , , Read, .

Answer 5

PHP -

while ($row = mysql_fetch_array ($result)) 
{
  //this assumes you're doing something with foo in loop
  $foo = $row["userid"];

  //using $foo somehow
}

in .NET, you are doing something else. Believe me, based on the PHP background, the transition from PHP to .NET is not easy. There are many things that seem strange. After a while it will make sense! Just stick to it. I personally like it.

Well .. if you have a DataSet, as you say, you can do something like this,

//assuming you have a DataSet called myDataSet
for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
{
  //likewise assuming here you're doing something with foo in loop
  string foo = myDataSet.Tables[0].Rows[i]["userid"].ToString();

  //similarly do something with foo in loop
}

This does the same thing as the PHP snippet.