C# SqlCommand.ExecuteReader方法代码示例

 public Category(string name)
 {
     try
     {
         conn.Open();
         SqlCommand command = new SqlCommand(String.Format(SqlSelectId, name.ToString()), conn);
         SqlDataReader reader = command.ExecuteReader();
         if (reader.Read() == false)
         {
             throw new Exception("There is no ctegory having the name=" + name);
         }
         this.id = int.Parse(reader["id"].ToString());
         this.name = name;
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         conn.Close();
     }
 }

        // Lấy Messages cần gửi
        public List<DiscussMessage> GetMessagesChat()
        {
            var messageChat = new List<MessagesChat>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT dbo.Messages.Id, dbo.Messages.[Content], dbo.Messages.SenderId, dbo.Messages.SendDate, dbo.Messages.Type
                                                            FROM  dbo.Messages
                                                            WHERE dbo.Messages.Sent = 'N' ORDER BY dbo.Messages.SendDate ASC", connection))
                {
                    command.Notification = null;
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();
                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        messageChat.Add(item: new MessagesChat
                        {
                            Id = reader["Id"].ToString(),
                            Content = reader["Content"].ToString(),
                            SenderId = reader["SenderId"].ToString(),
                            UserName = "",
                            SendDate = reader["SendDate"].ToString(),
                            Type = reader["Type"].ToString(),
                            GroupId = ""
                        });
                    }
                }
            }

            return messageChat;
        }

		public static async Task<List<string>> GetPickerData (string make)
		{
               List<string> result = new List<string>();

               using (var conn = new SqlConnection("Server=tcp:willies.database.windows.net,1433;Database=Willies Database;User [email protected];Password=Williescycles1;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
               {
                    try
                    {
                         conn.Open();
                    }
                    catch (Exception e)
                    {
                         
                    }

                    SqlDataReader myReader = null;
                    SqlCommand myCommand = new SqlCommand("SELECT DISTINCT YR FROM Parts Where Make LIKE 'H%'", conn);
                    myReader = myCommand.ExecuteReader();

                    while (myReader.Read())
                    {
                         result.Add(myReader.GetString(0));
                    }

                    conn.Close();

               }
               return result;
		}

 public Comment(int id)
 {
     SqlDataReader reader = null;
     try
     {
         DatabaseUtils.safeOpen(conn);
         SqlCommand command = new SqlCommand(String.Format(SqlSelect, id.ToString()), conn);
         reader = command.ExecuteReader();
         if (reader.Read() == false)
         {
             throw new Exception("There is no comment having the id=" + id);
         }
         this.id = id;
         this.idUser = reader["id_user"].ToString();
         this.text = reader["text"].ToString();
         this.idPhoto = int.Parse(reader["id_photo"].ToString());
         this.isAcc = int.Parse(reader["is_accepted"].ToString()) == 1;
         this.timeAdded = DateTime.Parse(reader["time_added"].ToString());
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         DatabaseUtils.safeClose(conn);
         DatabaseUtils.safeClose(reader);
     }
 }

        public static void EnvironmentHostNameTest()
        {
            SqlConnectionStringBuilder builder = (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr) { Pooling = true });
            builder.ApplicationName = "HostNameTest";

            using (SqlConnection sqlConnection = new SqlConnection(builder.ConnectionString))
            {
                sqlConnection.Open();
                using (SqlCommand command = new SqlCommand("sp_who2", sqlConnection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            int programNameOrdinal = reader.GetOrdinal(COL_PROGRAM_NAME);
                            string programName = reader.GetString(programNameOrdinal);
                            
                            if (programName != null && programName.Trim().Equals(builder.ApplicationName))
                            {
                                // Get the hostname
                                int hostnameOrdinal = reader.GetOrdinal(COL_HOSTNAME);
                                string hostnameFromServer = reader.GetString(hostnameOrdinal);
                                string expectedMachineName = Environment.MachineName.ToUpper();
                                string hostNameFromServer = hostnameFromServer.Trim().ToUpper();
                                Assert.Matches(expectedMachineName, hostNameFromServer);
                                return;
                            }
                        }
                    }
                }
            }
            Assert.True(false, "No non-empty hostname found for the application");
        }

 public Photo(int id)
 {
     SqlDataReader reader = null;
     try
     {
         DatabaseUtils.safeOpen(conn);
         SqlCommand command = new SqlCommand(String.Format(SqlSelect, id.ToString()), conn);
         reader = command.ExecuteReader();
         if (reader.Read() == false)
         {
             throw new Exception("There is no photo having the id=" + id);
         }
         this.id = id;
         this.idAlbum = int.Parse(reader["id_album"].ToString());
         this.idCategory = int.Parse(reader["id_category"].ToString());
         this.description = reader["description"].ToString();
         this.timeAdded = DateTime.Parse(reader["time_added"].ToString());
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         DatabaseUtils.safeClose(conn);
         DatabaseUtils.safeClose(reader);
     }
 }

 //for select statements using datareader
 public static SqlDataReader ExecuteReader(string SelectStatement)
 {
     SqlConnection con = new SqlConnection(ConnectionString);
     con.Open();
     SqlCommand com = new SqlCommand(SelectStatement, con);
     SqlDataReader dr = com.ExecuteReader();
     return dr;
 }

 public SqlDataReader UserLogin(string UserName, string Password)
 {
     string SQL = "SELECT * FROM TblUser" + " WHERE Username = '" + UserName + "' "
     + "AND Password = '" + Password + "' ";
     SqlCommand Command = new SqlCommand(SQL, con);
     SqlDataReader DataReader = Command.ExecuteReader();
     return DataReader;
 }

 private void OpenMarsConnection(string cmdText)
 {
     using (SqlConnection conn = new SqlConnection((new SqlConnectionStringBuilder(BaseConnString) { MultipleActiveResultSets = true }).ConnectionString))
     {
         conn.Open();
         using (SqlCommand cmd1 = new SqlCommand(cmdText, conn))
         using (SqlCommand cmd2 = new SqlCommand(cmdText, conn))
         using (SqlCommand cmd3 = new SqlCommand(cmdText, conn))
         using (SqlCommand cmd4 = new SqlCommand(cmdText, conn))
         {
             cmd1.ExecuteReader();
             cmd2.ExecuteReader();
             cmd3.ExecuteReader();
             cmd4.ExecuteReader();
         }
         conn.Close();
     }
 }

            // Get list of all of the Users
            public static List<User> listAll()
            {
                try
                {
                    List<User> users = new List<User>();
                    SqlConnection sqlConnection1 = new SqlConnection(Globals.connectionString);
                    SqlCommand cmd = new SqlCommand();
                    SqlDataReader reader;

                    // Possible columns: sid, status, createdate, updatedate, accdate, totcpu, totio, spacelimitl, timelimit, resultlimit, name, dbname, password, denylogin, hasaccess, instname, isntgroup, isntuser, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin,dbcreator, bulkadmin, loginname
                    cmd.CommandText = @"SELECT sl.sid, sl.status, sl.name, sl.createdate, sl.updatedate, sl.accdate FROM sys.syslogins sl
                                    join sys.sql_logins sql on sl.sid=sql.sid
                where sql.is_disabled = 0 and sl.password IS NOT NULL and sl.hasaccess = 1 and sl.isntname = 0 and not sl.name like '##%##' and sl.name not like 'sa';";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = sqlConnection1;

                    sqlConnection1.Open();

                    reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            //int sid = reader.GetInt16(0);
                            int sid = 0;
                            int status = reader.GetInt16(1);
                            String name = reader.GetString(2);
                            DateTime createdate = reader.GetDateTime(3);
                            DateTime updatedate = reader.GetDateTime(4);
                            DateTime accdate = reader.GetDateTime(5);
                            users.Add(new User(sid, status, name, createdate, updatedate, accdate));
                        }
                    }
                    reader.Close();
                    sqlConnection1.Close();
                    return users;
                }
                catch (Exception ex)
                {
                    return new List<User>();
                }
            }

        private Message GetMessageForSending()
        {
            try
                {
                using ( SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString) )
                    {
                    conn.Open();

                    using ( SqlCommand cmd = new SqlCommand("select top 1 [Id] TaskId, [Description] MessageText, [MobilePhone] MobilePhone from [SMSJournal] where Sended = @NotSended and DevicePhoneNumber = @DevicePhoneNumber order by CreationDate", conn) )
                        {
                        cmd.Parameters.AddWithValue("@NotSended", false);
                        cmd.Parameters.AddWithValue("@DevicePhoneNumber", Settings.Default.NativePhoneNumber);

                        using ( SqlDataReader dataReader = cmd.ExecuteReader() )
                            {

                            if ( dataReader.Read() )
                                {
                                long sendingTaskId = ( long ) dataReader["TaskId"];

                                return new Message(dataReader["MobilePhone"].ToString(), ( dataReader["MessageText"] as string ).Trim())
                                {
                                    TaskId = sendingTaskId
                                };
                                }
                            }
                        }
                    }
                }
            catch (Exception exp)
                {
                NotifyOnError(exp);
                }
            return null;
        }

        private void MultipleExecutesInSameTransactionTest(string connectionString, string tempTableName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction trans1 = connection.BeginTransaction();
                SqlTransaction trans2 = connection.BeginTransaction();
                SqlTransaction trans3 = connection.BeginTransaction();

                SqlCommand com1 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com1.Transaction = trans1;
                com1.ExecuteNonQuery();

                SqlCommand com2 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com2.Transaction = trans2;
                com2.ExecuteNonQuery();

                SqlCommand com3 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com3.Transaction = trans3;
                com3.ExecuteNonQuery();

                trans1.Rollback();
                trans2.Rollback();
                trans3.Rollback();

                com1.Dispose();
                com2.Dispose();
                com3.Dispose();

                SqlCommand com4 = new SqlCommand("select top 1 au_id from " + tempTableName, connection);
                com4.Transaction = trans1;
                SqlDataReader reader4 = com4.ExecuteReader();
                reader4.Dispose();
                com4.Dispose();

                trans1.Rollback();
            }
        }

    public static DataTable DataTableFromQuery(string q, SqlConnection scon)
    {
        SqlCommand cmd = new SqlCommand(q, scon);
        SqlDataReader dataread;
        DataTable dt = new DataTable();

        try
        {
            scon.Open()
            dataread = cmd.ExecuteReader();

            foreach (DataRow row in dataread.GetSchemaTable().Rows)
            {
                dt.Columns.Add(row["ColumnName"].ToString(), System.Type.GetType(row["DataType"].ToString()));
            }

            while (dataread.Read())
            {
                DataRow row = dt.NewRow();

                for (int i = 0; i < dataread.FieldCount; i++)
                {
                    row[i] = dataread.GetValue(i);
                }

                dt.Rows.Add(row);
            }

            dataread.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cmd.Dispose();
            scon.Close()
            scon.Dispose()
        }

        return dt;
    }

            public static SqlDataReader ExecuteSQL_DR(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
            {
                SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));

                objCmd.CommandType = CommandType.Text;

                ProcessParams(objCmd, @Params); //Parse Parameters

                objCmd.Connection.Open();
                using (objCmd)
                {
                    return objCmd.ExecuteReader(CommandBehavior.CloseConnection);
                }

                return objCmd.ExecuteReader();
                //				objCmd.Dispose();
                //				objCmd = null;
            }

        /// <summary>
        /// Reset the user password.
        /// </summary>
        /// <param name="username">User name.</param>
        /// <param name="answer">Answer to security question.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        /*public override string ResetPassword(
         string username,
         string answer
        )
        {

            if (!EnablePasswordReset)
            {
                throw new NotSupportedException("Password Reset is not enabled.");
            }

            if ((answer == null) && (RequiresQuestionAndAnswer))
            {
                UpdateFailureCount(username, FailureType.PasswordAnswer);

                throw new ProviderException("Password answer required for password Reset.");
            }

            string newPassword =
              System.Web.Security.Membership.GeneratePassword(
              newPasswordLength,
              MinRequiredNonAlphanumericCharacters
              );

            ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);

            OnValidatingPassword(args);

            if (args.Cancel)
            {
                if (args.FailureInformation != null)
                {
                    throw args.FailureInformation;
                }
                else
                {
                    throw new MembershipPasswordException("Reset password canceled due to password validation failure.");
                }
            }

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand("User_GetPasswordAnswer", sqlConnection);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
            sqlCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;

            int rowsAffected = 0;
            string passwordAnswer = String.Empty;
            SqlDataReader sqlDataReader = null;

            try
            {
                sqlConnection.Open();

                sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SingleRow & CommandBehavior.CloseConnection);

                if (sqlDataReader.HasRows)
                {
                    sqlDataReader.Read();

                    if (sqlDataReader.GetBoolean(1))
                    {
                        throw new MembershipPasswordException("The supplied user is locked out.");
                    }

                    passwordAnswer = sqlDataReader.GetString(0);
                }
                else
                {
                    throw new MembershipPasswordException("The supplied user name is not found.");
                }

                if (RequiresQuestionAndAnswer && (!CheckPassword(answer, passwordAnswer)))
                {
                    UpdateFailureCount(username, FailureType.PasswordAnswer);

                    throw new MembershipPasswordException("Incorrect password answer.");
                }

                SqlCommand sqlUpdateCommand = new SqlCommand("User_UpdatePassword", sqlConnection);

                sqlUpdateCommand.CommandType = CommandType.StoredProcedure;
                sqlUpdateCommand.Parameters.Add("@password", SqlDbType.NVarChar, 255).Value = EncodePassword(newPassword);
                sqlUpdateCommand.Parameters.Add("@username", SqlDbType.NVarChar, 255).Value = username;
                sqlUpdateCommand.Parameters.Add("@applicationName", SqlDbType.NVarChar, 255).Value = applicationName;
                rowsAffected = sqlUpdateCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                //Add exception handling here.
            }
            finally
//.........这里部分代码省略.........